cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DefinitelyNoBot
Helper I
Helper I

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
Drrickryp
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
Drrickryp
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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (43,904)