In the Adapter setup, how would I filter or sort on a specific field? I've copied and pasted the example straight from the "How do I" section of the documentation.

// C#
ResultsetFields fields = new ResultsetFields(3);
fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count
);
IRelationPredicateBucket bucket = new RelationPredicateBucket();
Page 392
bucket.Relations.Add(EmployeeEntity.Relations.EmployeeEntityUsingEmployeeIdReportsTo, "Employee", "Mana
ger", JoinHint.None);
IGroupByCollection groupByClause = new GroupByCollection();
groupByClause.Add(fields[0]);
groupByClause.Add(fields[1]);
DataAccessAdapter adapter = new DataAccessAdapter();
DataTable tlist = new DataTable();
adapter.FetchTypedList(fields, tlist, bucket, 0, null, true, groupByClause);

My situation is nearly identical, and if I try adding a "Sort clause" or "Filter clause" to the bucket, it will complain

"The multi-part identifier 'field name here' could not be bound."

The field I'm filtering/sorting on is already define in the Resultsetfields and the relation has been specified ... what else do I need to do?

Can you post the actual offending code that gives you that error?

If your code is truly almost identical to what you posted, my guess is that you are not specifying the same alias in your sort clause or filter than you are when you define your fields (or relations). The sample you posted uses aliases for fields AND tables. If your scenerio is simple, you may not need aliases at all.

So:

fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");

could become:

fields.DefineField(EmployeeFields.FirstName, 0);

HTH,

Phil

Thanks for the reply. Here is a copy of my code which you can see is nearly identical:

        
        DataAccessAdapter adapter = new DataAccessAdapter(true);
        DataTable tags; 
        IRelationPredicateBucket bucket;
        ResultsetFields fields; 
        ISortExpression sorter; 
        IGroupByCollection groupBy;

        fields = new ResultsetFields(3);
        fields.DefineField(KeywordFields.Keyword, 0, "Keyword", "Keyword");
        fields.DefineField(KeywordFields.KeywordId, 1, "KeywordId", "Keyword");
        fields.DefineField(ListingKeywordFields.KeywordId, 2, "Count", "ListingKeyword",                    
                                    AggregateFunction.Count);

        bucket = new RelationPredicateBucket();
        bucket.Relations.Add(ListingKeywordEntity.Relations.KeywordEntityUsingKeywordId,
                             "ListingKeyword",
                             "Keyword", JoinHint.None);
        
        groupBy = new GroupByCollection();
        groupBy.Add(fields[0]);
        groupBy.Add(fields[1]);

        sorter = new SortExpression();
        //sorter.Add(KeywordFields.Keyword | SortOperator.Descending);

        tags = new DataTable();
        adapter.FetchTypedList(fields, tags, bucket, 0, sorter, true, groupBy);
        adapter.CloseConnection();

The sorter line here (currently commented out) does not work: ** //sorter.Add(KeywordFields.Keyword | SortOperator.Descending);**

There is one "slight" difference from my code and the example in the documentation. For the third field defined

fields.DefineField(ListingKeywordFields.KeywordId, 2, "Count", "ListingKeyword", AggregateFunction.Count);

They use the same Entity (EmployeeFields) for all of them, while I use two different entities. Could this be the problem??

Compare this

    
fields.DefineField(KeywordFields.Keyword, 0, "Keyword", "Keyword");
fields.DefineField(KeywordFields.KeywordId, 1, "KeywordId", "Keyword");
fields.DefineField(ListingKeywordFields.KeywordId, 2, "Count", "ListingKeyword", AggregateFunction.Count);

versus

fields.DefineField(EmployeeFields.FirstName, 0, "FirstNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 1, "LastNameManager", "Manager");
fields.DefineField(EmployeeFields.LastName, 2, "NumberOfEmployees", "Employee", AggregateFunction.Count
);

I don't think the problem is with your using multiple tables. It looks to me like you have the relations set up correctly. It's always possible that I'm missing something obvious, of course.

I still think the problem has to do with aliases. I don't see any reason why you need them in this case. If you are using version 1, there might not be overloads to skip the aliases (I can't remember), so let me know if this simplified code doesn't compile:

fields.DefineField(KeywordFields.Keyword, 0);
fields.DefineField(KeywordFields.KeywordId, 1);
fields.DefineField(ListingKeywordFields.KeywordId, 2, AggregateFunction.Count);
[...]
bucket.Relations.Add(ListingKeywordEntity.Relations.KeywordEntityUsingKeywordId);

If the problem was with the aliases, your sort clause should work with the above code.

If this doesn't work, the next step would be to post the resulting working query and the resulting failing query. Unless someone else sees a problem that I'm missing with the code you posted . . .

Phil

That worked! Thanks. So you were right that it was the aliases causing problems. The only thing different from you version is that I had to add an alias for the "Count":

fields.DefineField(ListingKeywordFields.KeywordId, 2, "Count", AggregateFunction.Count);

I can kind of see why aliases would cause problems with sorting/filtering, but I'm wondering if there is way to still use aliases and have sorting/filtering. Either way this will work for me.

Thanks for all the help...

No sweat.

The alias can be used when using a sort or filter, you just have to use the same alias in the sort/filter that you used when you created the field (and/or relation).

Cheers,

Phil

Actually just one more question then. How do you sort/filter on aliases?

For example, in the code I had before, if I wanted to sort by "Count" how exactly do I reference this in the PredicateExpression? I tried this:

fields.DefineField(KeywordFields.Keyword, 0);
fields.DefineField(KeywordFields.KeywordId, 1);
fields.DefineField(ListingKeywordFields.KeywordId, 2, "Count", AggregateFunction.Count);

bucket.PredicateExpression.Add(((EntityField2)fields[2]) >= 1);

and this will apply the filter >= 1 on "KeywordId" and not "Count".

Really quick clarification:

The alias can be used when using a sort or filter, you just have to use the same alias in the sort/filter that you used when you created the field (and/or relation).

Just to be clear, in most (all?) cases, the alias you need to use is the table's alias, not the field's.

aliem wrote:

Actually just one more question then. How do you sort/filter on aliases?

For example, in the code I had before, if I wanted to sort by "Count" how exactly do I reference this in the PredicateExpression? I tried this:

fields.DefineField(KeywordFields.Keyword, 0);
fields.DefineField(KeywordFields.KeywordId, 1);
fields.DefineField(ListingKeywordFields.KeywordId, 2, "Count", AggregateFunction.Count);

bucket.PredicateExpression.Add(((EntityField2)fields[2]) >= 1);

and this will apply the filter >= 1 on "KeywordId" and not "Count".

I assume you meant "filter" and not "sort"?

In this case, you have to use a HAVING clause, as you are filtering on an aggregate. The having clause is part of the GroupByCollection. I don't know the syntax off the top of my head, so I would recommend searching for "having" in the docs.

Phil

(Edit) P.S. To find info on filtering on a field with an alias (which was your original question), search for SetObjectAlias in the docs.