LLBLGenProDataSource and Complex Filtering

Posts   
 
    
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 16-Dec-2014 15:45:29   

How i can add a complex filteriong to the LLBLGenProDataSource thru Codebehind?

This is the query of the DataSource:

SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1;

and i want to add in the filtering

SELECT COLUMN1, COLUMN2, COLUMN3 FROM TABLE1 WHERE COLUMN1 IN (SELECT ID FROM TABLE2 WHERE COLUMN4 IN (1,2,3,4);

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Dec-2014 20:18:13   

You can set the FilterToUse property with the needed predicateExpression.

eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 16-Dec-2014 20:23:19   

Can you give me an example? I know how to do it for the same table but not for another table that is not part of the primary query

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 16-Dec-2014 21:35:27   

Please check the FieldCompareSetPredicate in docs.

eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 16-Dec-2014 23:18:35   

I manage to construct something like this:

Dim filterPredicate = New FieldCompareSetPredicate(PrGlAccountsDistributionFields.CategoryId, Nothing, CoPayCategoryFields.CategoryId, Nothing, SetOperator.In, (CoPayCategoryFields.PayrollType = "EA"))

but i need to add an "AND COMPANY_ID=1" to look like this:

Dim filterPredicate = New FieldCompareSetPredicate(PrGlAccountsDistributionFields.CategoryId, Nothing, CoPayCategoryFields.CategoryId, Nothing, SetOperator.In, (CoPayCategoryFields.PayrollType = "EA" and CoPayCategoryFields.CompanyId=1))

how i can achive that?

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 17-Dec-2014 07:58:12   

Do something like this:

Dim filterPredicate = New FieldCompareSetPredicate(PrGlAccountsDistributionFields.CategoryId, Nothing, CoPayCategoryFields.CategoryId, Nothing, SetOperator.In, New PredicateExpression(CoPayCategoryFields.PayrollType = "EA" And CoPayCategoryFields.CompanyId=1))

More info in the docs.

David Elizondo | LLBLGen Support Team
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 17-Dec-2014 13:24:38   

When I run it with that filter always return the following error, see attachments

Attachments
Filename File size Added on Approval
Error1.png 25,521 17-Dec-2014 13:25.00 Approved
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 17-Dec-2014 13:25:45   

And this error. See attachment

If I remove the filter, the error does not show.

Attachments
Filename File size Added on Approval
Error2.png 236,033 17-Dec-2014 13:25.56 Approved
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 17-Dec-2014 13:58:13   

You're using selfservicing (at least the selfservicing datasourcecontrol), according to that stacktrace, however you use the adapter constructor to construct the FieldCompareSet predicate. So use:

Dim filterPredicate = New FieldCompareSetPredicate(PrGlAccountsDistributionFields.CategoryId,  CoPayCategoryFields.CategoryId, SetOperator.In, New PredicateExpression(CoPayCategoryFields.PayrollType = "EA" And CoPayCategoryFields.CompanyId=1))

This would have been easier if you had given any info whatsoever what you're using: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=7722 Please do that next time, thanks.

Frans Bouma | Lead developer LLBLGen Pro
eirizarry
User
Posts: 48
Joined: 30-Mar-2011
# Posted on: 17-Dec-2014 16:12:01   

Now is working, thanks for the help.

Elvin