How do I write a filter which does WHERE field_A IN (SELECT field_b FROM foo Where foo.field_c = 'C') ?

Hi, quoting from the help:

// C# - Adapter IPredicateExpression filter = new PredicateExpression(); filter.Add(new FieldCompareSetPredicate( YourEntityFields.Field, null, FooFields.Fieldb, null,
SetOperator.In, null));

Plesae see the How Do I section from the manual for details.

goose wrote:

Hi, quoting from the help:

// C# - Adapter IPredicateExpression filter = new PredicateExpression(); filter.Add(new FieldCompareSetPredicate( YourEntityFields.Field, null, FooFields.Fieldb, null,
SetOperator.In, null));

Plesae see the How Do I section from the manual for details.

Goose, thanks for the reply but I had already seen that in the help. I actually copied out the text from the help file but added the extra where clause in the select statement.

The help text shows this

How do I write a filter which does WHERE field IN (SELECT fieldb FROM foo) ?

I am looking for one more step in passing in an extra Where. What I am looking for is this.

How do I write a filter which does WHERE field IN (SELECT field_b FROM foo WHERE  foo.field_c =  'SOME VALUE' ) 

I could not figure out how to set up the PredicateExpression for this type of query.

Thanks to anyone who can shed some light.

instead of sending null in the las parameter of the new FieldCompareSetPredicate send an valid IPredicate (i.e. the filter you want to apply e.g. WHERE foo.field_c = 'SOME VALUE' ). In order to create more complex sub querys please see the several constructor overloads of FieldCompareSetPredicate class.

goose wrote:

instead of sending null in the las parameter of the new FieldCompareSetPredicate send an valid IPredicate (i.e. the filter you want to apply e.g. WHERE foo.field_c = 'SOME VALUE' ). In order to create more complex sub querys please see the several constructor overloads of FieldCompareSetPredicate class.

Thanks Goose. Upon looking futher I seen where this topic existed in the help docs. I could have swarn that I looked for that. I guess I should've looked harder.

Anyway I wanted to post the code for anyone else who may be interested.

            if (functionalGroupId.HasValue)
            {
                FunctionalLeadsTypedList leads = new FunctionalLeadsTypedList();
                IRelationPredicateBucket bucket = leads.GetRelationInfo();
                bucket.PredicateExpression.Add(FunctionalLeadFields.FunctionalGroupId == functionalGroupId);
                ISortExpression sorter = new SortExpression(UserFields.LastName | SortOperator.Ascending);

                bucket.PredicateExpression.Add(new FieldCompareSetPredicate(FunctionalLeadFields.UserId, 
                    null, ProjectUserFields.UserId, null,
                    SetOperator.In, (ProjectUserFields.ProjectId == projectId)));

                using (DataAccessAdapter adapter = DBAdapter.NewDataAdapter)
                {
                    adapter.FetchTypedList(leads.GetFieldsInfo(), leads, bucket, 0, sorter, false);
                }
                return leads;
            }
            else
            {
                return new FunctionalLeadsTypedList();
            }