Unanswered

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

(0) Share

Report

Posted on by 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!!

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

Sort by

anandm08 1,936 Super User 2024 Season 2 on at

Like (0)

Report
Copy link

Link copied!
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:
Add a dropdown or buttons for the user to select the column to sort by.
Store the selected column name in a variable, e.g., varSortColumn.
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) )

Was this reply helpful? Yes No
patilm 16 on at

Like (0)

Report
Copy link

Link copied!
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 )

Was this reply helpful? Yes No
anandm08 1,936 Super User 2024 Season 2 on at

Like (0)

Report
Copy link

Link copied!
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 )

Was this reply helpful? Yes No
patilm 16 on at

Like (0)

Report
Copy link

Link copied!

Hey @anandm08 , thanks for your reply but my sorting issue is resolved.
But delegation warning is there in free text search.

Was this reply helpful? Yes No
anandm08 1,936 Super User 2024 Season 2 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
patilm 16 on at

Like (0)

Report
Copy link

Link copied!
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' )

Was this reply helpful? Yes No
anandm08 1,936 Super User 2024 Season 2 on at

Like (0)

Report
Copy link

Link copied!
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) )

Was this reply helpful? Yes No
patilm 16 on at

Like (0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No