Community
Showing results for 
Search instead for 
Do you mean 
Reply

Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Tuned Listener
Posts: 6
Country: Australia

Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Hi! I've been searching extensively on the forum on how to filter CustomEntities (custom tables) and have sort of hit a brick wall.

 

I have a custom table in my database called Invoice Details which contains invoice line items imported from our accounting system.  I need to be able to get a list of only those line items for a specific Invoice Number (which is a string field on the custom Invoice Details table.)

 

 

I can return a collection of ALL invoice detail items without a problem.. but for whatever reason I cannot get the filter working. 

 

Following is some code which uses the recommend method of filtering Custom Tables, which are modelled on explanation of filtering custom tabels elsewhere in the forum.

 

Any help and suggestions would be very welcome.  If I can get this up and running, I will post up a 'how tutorial', since it's clear that this is a common task that is not well documented.

 

Best regards,

JS 

 

 

Function GetInvoiceDetailsCollectionByInvoiceNumber(ByVal InvoiceNumber As String) As Act.Framework.CustomEntities.CustomEntityList(Of SalesItemSubEntity)

' set up the descriptor for the custom invoice details table (subentity)

 Dim descriptor As CustomEntities.CustomEntityDescriptor = ActiveActFramework.CustomEntities.GetEntityDescriptor("Invoice Details")

' set up the custom entity manager types to the SalesItemSubEntity class, that inherrits from Act.Framework.CustomEntities.CustomSubEntity

 Dim manager As Act.Framework.CustomEntities.CustomEntityManager(Of SalesItemSubEntity) = _

ActiveActFramework.CustomEntities.GetSubEntityManager(Of SalesItemSubEntity)(descriptor)

' set up the Invoice Number field descriptor

 Dim fdInvNo As Act.Framework.CustomEntities.CustomEntityFieldDescriptor = manager.GetCustomEntityFieldDescriptor("Invoice Number", _

Act.Framework.MutableEntities.FieldNameType.Alias)

' Act.Framework.MutableEntities.FieldNameType.Alias)

' set up the sort order to invoice

 Dim invSort As New SortCriteria(manager.GetCustomEntityFieldDescriptor("Invoice Number", _

Act.Framework.MutableEntities.FieldNameType.Alias), _

ListSortDirection.Ascending)

' set up the search criteria to InFilterCriteria. Invoice Number is the search item.

 Dim searchCriteria As New InFilterCriteria(fdInvNo, New String() {InvoiceNumber})

'Get sales data

 Return manager.GetCustomEntities(New SortCriteria() {invSort}, _

New InFilterCriteria() {searchCriteria})

End Function

 

 

 I have created the SalesItemSubEntity class as follows....

 

 

Public Class SalesItemSubEntity

Inherits Act.Framework.CustomEntities.CustomSubEntity

Sub New(ByVal state As CustomSubEntityInitializationState)

MyBase.New(state)

End Sub

End Class

Copper Super Contributor
Posts: 59
Country: USA

Re: Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Hi Joe,

 

The code you have shown seems correct to me, unless I'm missing something. What error/exception are you getting? 

Hugo Vale
Datastream Custom Solutions
http://www.datastreamcs.com
Tuned Listener
Posts: 6
Country: Australia

Re: Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Dear Hugo,

 

The code does not give an exception, but the list always returns with zero (0) entitlies whenever not matter what InvoiceNumber is passed.  For example, I have 4 test invoices in the Invoice Details table, with Invoice Number "111", "111", "222" and "222" respectively.  When I call the function with the parameter InvoiceNumber set to "111"   the list returned has a zero length... nothing is being returned.  Likewise when I use the InvoiceNumber "222."   For whatever reason, the filter appears to be filtering out ALL records.

 

 

Copper Super Contributor
Posts: 59
Country: USA

Re: Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Hi Joe,

 

So you are saying that if you use this line instead:

Return manager.GetCustomEntities(New SortCriteria() {invSort}, Nothing) 

 

You get all the rows returned. Could it be the invoice numbers are stored as integers instead of strings?

If you replace the filter line by this:

Dim searchCriteria As New InFilterCriteria(fdInvNo, New Integer() {111}) 

 

Do you still get no rows returned? 

Hugo Vale
Datastream Custom Solutions
http://www.datastreamcs.com
Tuned Listener
Posts: 6
Country: Australia

Re: Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Dear Hugo,

 

Thanks for your help mate.  The Invoice numbers where all strings, so that was not the problem.

 

I finally worked out the issue. The Invoice Details custom table was linked to another table called Invoices by the Invoice number.  This was stopping me from performing the filter for some reason. I removed the Invoices custom table and renamed the Invoice Number field on the Invoice Details table to "Myob Invoice Number" and then the code ran perfectly.

 

I suspect - though I'm not sure about this - that ACT was getting confused because I had two custom tables with fields aliased as "Invoice Number"

Anyway, problem solved!

 

Best regards,

JS

 

Copper Super Contributor
Posts: 59
Country: USA

Re: Filtering CustomEnties using GetCustomEntities with InFilterCriteria

Hi Joe,

 

I didn't remember that. I'm actually suprised it let you create both fields with the same name. Whenever I tried to do that, I would get an exception on creation. Glad you got it figured out.

 

Cheers 

Hugo Vale
Datastream Custom Solutions
http://www.datastreamcs.com