Sort a table by a dynamic column

(0) Share

Report

Posted on by Hanan-khamissy

140

In my app i have a table that i want to give the user the option to sort by different columns.

the user choose the column name through a combobox and the sort type by another combobox

combobox1: items : ["column1","column2","column3"]

combobox2: items: ["SortOrder.Ascending","SortOrder.Descending"]

button : OnSelect: Set(selectedSort,ComboBox1.Selected.Value);
Set(selectedColumn,ComboBox1.Selected.Value)

Table1 : Items: Sort('DataBase','selectedColumn',SortOrder.Ascending)

It doesn't work ! i cann't find what's wrong

Categories:

Building Power Apps

All responses (4)

Answers (2)

Sort by

Scott_Parker 1,090 on at

Like
a
(0)

Report
Copy link

Link copied!

It is a limitation with the Sort() function and other functions that refer to columns by name. You must, without exception, provide column names as primitive strings. That is, hard-coded text values. You can't change the column name dynamically using a variable like you are attempting to do here.

To get around this limitation, you will have to write separate Sort() formulas, one for each column you wish to sort by, and then pick one using a Switch() or If() function.
Verified answer

v-qiaqi@microsoft.com Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Hanan-khamissy,

As @Scott_Parker mentioned, you need to write an if statement to sort by columns one by one based on selected column name.

If(ComboBox1.Selected.Value="column1","column2","column3" Sort('DataBase',"column1",If(ComboBox2.Selected.Value="SortOrder.Ascending", SortOrder.Ascending,SortOrder.Descending)), ComboBox1.Selected.Value="column1", Sort('DataBase',"column2",If(ComboBox2.Selected.Value="SortOrder.Ascending", SortOrder.Ascending,SortOrder.Descending)), ComboBox1.Selected.Value="column1", Sort('DataBase',"column3",If(ComboBox2.Selected.Value="SortOrder.Ascending", SortOrder.Ascending,SortOrder.Descending)) )
Hanan-khamissy 140 on at

Like
a
(0)

Report
Copy link

Link copied!
The problem is that in addition to the sorting I have a complete set of filters
It means in each
'DataBase'
i need to replace it with a complex of filters
Verified answer

Scott_Parker 1,090 on at

Like
a
(0)

Report
Copy link

Link copied!
Not necessarily. You can use use With() to so you only have to write the filters for 'DataBase' in one place in the code. Such as:
With( {DataBase: Filter(<your code here, etc.>)}, If(ComboBox1.Selected.Value="column1", Sort('DataBase',"column1",If(ComboBox2.Selected.Value="SortOrder.Ascending", SortOrder.Ascending,SortOrder.Descending)), ComboBox1.Selected.Value="column2", Sort('DataBase',"column2",If(ComboBox2.Selected.Value="SortOrder.Ascending", SortOrder.Ascending,SortOrder.Descending)), ComboBox1.Selected.Value="column3", Sort('DataBase',"column3",If(ComboBox2.Selected.Value="SortOrder.Ascending", SortOrder.Ascending,SortOrder.Descending)) ) )