Hello everyone,
I stumbled across the following problem and would appreciate ideas. In the following, "table" will mean a table in an Azure SQL Database.
I have a gallery based on a table (say, dbo.Devices) containing labels based on another table (say, dbo.Employees) linked via a foreign key (say, ID_Employees). I would like to make the gallery sortable by every column, including the names referenced in dbo.Employees. Obviously, sorting by ID_Employees does not help since I want an alphabetical ordering by names. I managed to do that by adding a cloumn to the main table using the AddColumns function as follows:
With(
{ FilteredList: /*<Doing some Pre-Filtering>*/ };
With(
{ FilteredList2:
AddColumns(
/*<Doing more operations on FilteredList> */;
"FullName"; Coalesce(LookUp('[dbo].[Employees]' As m; m.ID_Employees = ID_Employees; m.FirstName & " " & m.LastName); "Office")
)
};
Switch(
var_SortPriority;
/*<other columns to sort by>*/
"FullName";
SortByColumns(FilteredList2; "FullName"; If(SortDescending;Descending;Ascending));
/*<other columns to sort by>*/
SortByColumns(FilteredList2; "ID_Devices"; Ascending)
)
)
)
(If anyone is wondering: I used the Switch function here to make the Sort function delegable)
I realized, that adding a column like this destroys my EditForm since now the the source for the gallery is not the original table dbo.Devices anymore but a temporary collection in PowerApps I think (the "Item" property of the EditForm is set to gal_Devices.Selected).
Do you see another way to sort the gallery by employee names without adding a new column?
Note: I can create a new view in the SQL-Database, which should solve the problem in itself. I am very reluctant change the core reference of the App, though, since it has already grown reasonably complex and I don't know which implications a change of data source would entail...
