Sort multiple columns.

[DidacRoig]

I have this code in my galley that filters my data whitout delegation problems:

SortByColumns(
If(
!IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text)
);
!IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text)
);
!IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
Result.Value = fResult.Selected.Value
);
!IsBlank(fSupplier) && IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(AID;Filter.Text)
);
!IsBlank(fSupplier) && IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value
);
!IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
StartsWith(AID;Filter.Text)
);
!IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text)
);
!IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
Result.Value = fResult.Selected.Value;
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
!IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Supplier;fSupplier.Text);
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value;
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value
);
IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
StartsWith(AID;Filter.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
Result.Value = fResult.Selected.Value;
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && !IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(DateYear;fDate.Text);
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
Result.Value = fResult.Selected.Value
);
IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text)
);
IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
Result.Value = fResult.Selected.Value;
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && IsBlank(fDate) && !IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
Result.Value = fResult.Selected.Value;
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(AID;Filter.Text)
);
IsBlank(fSupplier) && IsBlank(fDate) && IsBlank(fResult) && !IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(AID;Filter.Text);
StartsWith(Material;fMaterial.Text)
);
IsBlank(fSupplier) && IsBlank(fDate) && IsBlank(fResult) && IsBlank(Trim(Filter)) && !IsBlank(Trim(fMaterial));
Filter(
Homologation;
StartsWith(Material;fMaterial.Text)
);
Homologation
);
varSortColumn;
If(varSortDirection = "Ascending"; SortOrder.Ascending;SortOrder.Descending)
)

The variable varSortColumn is a variable conected to a button that each column in my gallery has,the problem is that it only sorts my Homologation value, it does not filter the other fields and it is changing. Any suggestions?

My data seach code can be improved or meke it shorter?

[theapurva · Accepted solution]

Hello @DidacRoig ,

Please try below code:

Sort( Filter( Homologation, IsBlank(fSupplier)||StartsWith(Supplier,fSupplier.Text), IsBlank(fDate)||StartsWith(DateYear,fDate.Text), IsBlank(fResult)||Result.Value = fResult.Selected.Value, IsBlank(Trim(Filter))||StartsWith(AID,Filter.Text), IsBlank(Trim(fMaterial))||StartsWith(Material,fMaterial.Text)),varSortColumn,
If(varSortDirection = "Ascending", SortOrder.Ascending,SortOrder.Descending)
)

[DidacRoig]

It worked but i have 3 questions:

-In my varSortColumn it appears that may be possible to have delegation problems, but even limiting my data row limit to 2, the filters work properly, then why Power Apps marks my variable with delegation problem?

-I changed you code to be like this:

SortByColumns(
Filter(
Homologation;
IsBlank(fSupplier) || StartsWith(
Supplier;
fSupplier.Text
);
IsBlank(fDate) || StartsWith(
DateYear;
fDate.Text
);
IsBlank(fResult) || Result.Value = fResult.Selected.Value;
IsBlank(Trim(Filter)) || StartsWith(
AID;
Filter.Text
);
IsBlank(Trim(fMaterial)) || StartsWith(
Material;
fMaterial.Text
)
);
varSortColumn;
If(
varSortDirection = SortOrder.Ascending;
SortOrder.Ascending;
SortOrder.Descending
)
)

Because i want a button in each of my columns to change the sort order of my gallery, but it only works with homologation, when my varSortColumns change to materials (or any other) it still sort on my homologation column, any suggestions?

[theapurva]

Hello,
can you check your OnSelect formula of other columns?

You need to set the exact column name to varSortColumn variable on click of the button

[DidacRoig]

This is my app when sorting by homolagation, it sort the values ascending well or descending i i click the sort button.

But when i click the Supplier value, it sort like this.

And with material, i checked my column name in the list and it is Materials, but it sort ike it is homologation, and my code in material button is this:

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

And with Creation Date and Required date, power apps give me an error.

[theapurva]

Hello @DidacRoig ,

I believe:

1. The material could be single line of text type column in backend.so Its sorting like that.

2. Can you paste the error pops up when you click on date columns? The column names for Creation Date and required date could be incorrect. Try putting the internal names there.

[DidacRoig]

This column is the default creation date that sharepoint creates when you create a table.

This is the error that pops up when i click on creation date and required date, i proved changing the var of the table name with:

-"CreationDate","Creation Date","Creation_x0020_Date"

And it still didn't work. my column is set like this:

Text(ThisItem.'Creation Date',"dd/mm/yyy")

And in the point 1 that you poseted, yes, it is a sigle line of text column because the id for materials may contain letters in some cases, is there a form to order that?

[DidacRoig]

I found the solution, The default Creation date column that sharepoint creates has the value of Created in power apps, so i changed the value of my varSortColumn to "Created" and with required date i changed to "Required_x0020_Date" , and it worked.

But still i have the problem with materil and supplier, the 2 of them are of type Single Line of text, is ther a form to order alfaphetically and numerically or something like that? 

[DidacRoig]

Or with the lenght of char in the String it work for me, is there a form?