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);

You can set the FilterToUse property with the needed predicateExpression.

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

Please check the FieldCompareSetPredicate in docs.

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?

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.

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

And this error. See attachment

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

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.

Now is working, thanks for the help.

Elvin