cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Sort Gallery by foreign table column

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...

1 ACCEPTED SOLUTION

Accepted Solutions
Super User II
Super User II

Hi @DefinitelyNoBot 

Your premise that adding a column destroys the edit form is incorrect.  You simply need to change the Item property of the form to something more specific than Gallery1.Selected. Assuming that your Data source for the form is '[dbo].[Employees]', If you put 

 

Lookup('[dbo].[Employees]',ID=Gallery1.Selected.ID)

 

in the Item property, the edit form should work just fine. 

 

View solution in original post

2 REPLIES 2
Super User II
Super User II

Hi @DefinitelyNoBot 

Your premise that adding a column destroys the edit form is incorrect.  You simply need to change the Item property of the form to something more specific than Gallery1.Selected. Assuming that your Data source for the form is '[dbo].[Employees]', If you put 

 

Lookup('[dbo].[Employees]',ID=Gallery1.Selected.ID)

 

in the Item property, the edit form should work just fine. 

 

View solution in original post

Thank you for your answer. True, I had that misconception about the edit form. That solves my specific problem, thanks!

Still, I am curious about my earlier question since I am still learning a lot about PowerApps. So I would be curious about ideas regarding the original title...

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (93,388)