Hello,

I'm trying to filter a dynamic list and somehow it doesn't work. When I do not apply the filter the dynamic list return exactly what I want so that part looks good. When using the defined filter I get an error:

_The column prefix 'dbo.Zoekopdrachten' does not match with a table name or alias name used in the query. _

Hope someone sees the error I made.

BTW I'm using selfservicing with version 1.0.2004.1

        Dim filter As IPredicateExpression = New PredicateExpression
        Dim dateStart As Date = DatumVanaf.SelectedDate
        Dim dateEnd As Date = DatumTotEnMet.SelectedDate

        filter.Add(FactoryClasses.PredicateFactory.Between(ZoekopdrachtenFieldIndex.Datum, dateStart, dateEnd))
    
        ' TREFWOORDEN
        Dim fldsTrefwoorden As New HelperClasses.ResultsetFields(2)
        fldsTrefwoorden.DefineField(ZoekopdrachtenFieldIndex.Trefwoord, 0, "Trefwoord", "Trefwoord")
        fldsTrefwoorden.DefineField(ZoekopdrachtenFieldIndex.Trefwoord, 1, "Aantal", "Trefwoord", AggregateFunction.Count)

        Dim groupByClause As IGroupByCollection = New GroupByCollection
        groupByClause.Add(fldsTrefwoorden(0))

        Dim dlTrefwoorden As New DataTable
        Dim dao As New DaoClasses.TypedListDAO
        dao.GetMultiAsDataTable(fldsTrefwoorden, dlTrefwoorden, 0, Nothing, filter, Nothing, True, groupByClause, Nothing, 0, 0)

That's because you have defined an alias for the table (entityAlias) in your fields definition of the dynamic list.

Use the following lines instead and try again:

 Dim fldsTrefwoorden As New HelperClasses.ResultsetFields(2)
        fldsTrefwoorden.DefineField(ZoekopdrachtenFieldIndex.Trefwoord, 0, "Trefwoord", "")
        fldsTrefwoorden.DefineField(ZoekopdrachtenFieldIndex.Trefwoord, 1, "Aantal", "", AggregateFunction.Count)

And if you are filtering on a field returned in the dynamic list, you may use it in your filtering, as follows:

filter.Add(FactoryClasses.PredicateFactory.Between(fldsTrefwoorden[i], dateStart, dateEnd)) // where i is the index of the field in the dynamic list

Thanks Walaa, that works perfect!

Can I also use this to sort de dynamic list?

sorter.Add(FactoryClasses.SortClauseFactory.Create(fldsTrefwoorden(1), SortOperator.Descending))

Here fldsTrefwoorden(1) is the field with the aggregate function. With this line of code I get an error. The argumants are invalid for the Create method.

How can I create a sortexpression to sort this dynamic list on the field containing the aggregate value?

Use the following line of code instead:

ISortExpression sorter = new SortExpression(new SortClause(fldsTrefwoorden[1], null, SortOperator.Descending));