Filtering and Sorting Dataverse table in power apps with different types of columns

[patilm]

I want to implement screen in canvas power apps where user can do free text search on all column in gallery whose DataSource is a Dataverse table, as well as can sort the data by number/text/choice column.

Using collection, we can achieve this requirement easily, e.g. for choice column we can add new column in collection with choice column's value. Also it doesn't give delegation warning.

But my requirement is to use data table directly instead of collection. If we use data table as a DataSource of gallery, I am not able to use some columns for sorting. Also, it gives delegation warning in free text search formula.

How can we achieve this requirement using data table?

The main concern behind not using collection is the app may face performance issue on creation of collection for large data in data table.

Any idea or suggestion would be appreciated.

Thanks!!

[anandm08]

hi @patilm ,

Sorting can be done using the SortByColumns function. If you want to provide the user with the ability to sort by different columns, you can use a variable that stores the name of the column to sort by.

Here’s how you can set up sorting:

1. Add a dropdown or buttons for the user to select the column to sort by.
2. Store the selected column name in a variable, e.g., varSortColumn.
3. Use the SortByColumns function with the selected column name.

Example for the Items property of the gallery:

SortByColumns(
    Filter(
        YourDataverseTable,
        Or(
            Search(TextColumn1, txtSearchBox.Text),
            Search(TextColumn2, txtSearchBox.Text),
            Text(NumberColumn) in txtSearchBox.Text,
            Text(LookUp(ChoiceColumnTable, ChoiceColumnField = YourDataverseTable.ChoiceColumn).ChoiceColumnField) in txtSearchBox.Text
        )
    ),
    varSortColumn,
    If(varSortDescending, Descending, Ascending)
)

[patilm]

Hello @anandm08 , 

Thanks for your reply.

We are doing the same thing on headers, but some columns are not recognized by the sorting formula.

On select of the sorting icon - 

Set(varSortColumn,"status");Set(varSortDirection,If(varSortDirection=SortOrder.Ascending,SortOrder.Descending,SortOrder.Ascending));

Items of gallery -

SortByColumns(
    Filter(
        table name, txtSearchBox.Value in 'Customer Name' Or txtSearchBox.Value in Text('Status (status)') Or txtSearchBox.Value in 'Request Type' Or txtSearchBox.Value in 'Order Number' Or txtSearchBox.Value in 'End Date' Or txtSearchBox.Value in Comments Or txtSearchBox.Value in 'Customer Reference' Or txtSearchBox.Value in 'Customer Number'
    ),
    varSortColumn,
    varSortDirection
)

[anandm08]

If the issue persists with specific columns, create a dynamic sorting approach by using a Switch function to handle specific transformations or subfields:

SortByColumns(
    Filter(
        'Order Clarification Managements',
        Or(
            TextSearchBox.Text in 'Customer Name',
            TextSearchBox.Text in Text('Status (lenze_status)'.Value),
            TextSearchBox.Text in 'Request Type',
            TextSearchBox.Text in 'Order Number',
            TextSearchBox.Text in Text('End Date', "[$-en-US]mm/dd/yyyy"),
            TextSearchBox.Text in Comments,
            TextSearchBox.Text in 'Customer Reference',
            TextSearchBox.Text in 'Customer Number'
        )
    ),
    Switch(
        varSortColumn,
        "lenze_status", Text('Status (lenze_status)'.Value),
        "End Date", Text('End Date', "[$-en-US]mm/dd/yyyy"),
        varSortColumn  // Default case
    ),
    varSortDirection
)

[patilm]

Hey @anandm08 , thanks for your reply but my sorting issue is resolved.

But delegation warning is there in free text search.

[anandm08]

can you share the code where you are getting delegation issues?

[patilm]

Mainly on "in" "Or" functions

 Filter(
        'Table name', txtSearchBox.Value in 'Customer Name' Or txtSearchBox.Value in Text('Status') Or txtSearchBox.Value in 'Request Type' Or txtSearchBox.Value in 'Order Number' Or txtSearchBox.Value in 'End Date' Or txtSearchBox.Value in Comments Or txtSearchBox.Value in 'Customer Reference' Or txtSearchBox.Value in 'Customer Number'
    )

[anandm08]

try this:

Filter(
    'Table name',
    StartsWith('Customer Name', txtSearchBox.Text) ||
    StartsWith(Text('Status'), txtSearchBox.Text) ||
    StartsWith('Request Type', txtSearchBox.Text) ||
    StartsWith('Order Number', txtSearchBox.Text) ||
    StartsWith('End Date', txtSearchBox.Text) ||
    StartsWith(Comments, txtSearchBox.Text) ||
    StartsWith('Customer Reference', txtSearchBox.Text) ||
    StartsWith('Customer Number', txtSearchBox.Text)
)

[patilm]

Ok, tried this but same delegation warning on OR condition. 
Also warning on "StartsWith" function