cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Andreas_Boeck
Level: Powered On

Sort Gallery with Variable storing relational data

Hi community,

 

I use Common Data Service (CDS) to store a relational database for a canvas PowerApp.

There are 2 entities: 1. Companies and    2. Functions

'Companies' has those fields:  CompanyName, Address

'Functions' have those fields: FunctionType, Company, Director.

 

So 'Functions' tell you which director has which function in which company.

 

I set up this 1:n relationship in CDS.

 

Now I have a gallery which shows such functions and related fields:

e.g.     Company Name,  Functiontype

           XYZ corp ltd. ,  board member

           ......

 

now I want to enable a user to sort this table by clicking a SortButton next to each column heading, so he can choose by which columns he wants to sort this table.

 

My solution:

1. create a variable 'varSortPriority'

2. set those 2 SortButtons'  OnSelect property to:

    1. "Set(varSortPriority, functions.ForKeyCompany.CompanyName))"

    2. "Set(varSortPriority, functions.FunctionType))"

 

WHEREAS,  'functions' refers to the CDS entity.

WHEREAS   'ForKeyGroupco' refers to the lookupfield of the 'functions entity' that was created as the CDS relationship

3. set gallery.items property to :   "Sort(functions,varSortPriority)"

 

 

RESULT:   Sorting only works when varSortPriority = "functionType", but it does not work when it is set to "ForKeyCompany.CompanyName"

 

Can anybody please help?   

I fear that in general Powerapps does not yet allow to store data from related entities in variable or collections. If true that would be a big disappointment.

 

Thanks!

Andreas

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Sort Gallery with Variable storing relational data

Hi @Andreas_Boeck ,

Do you want to sort based on a lookup field?

The mainly reason of the problem that you met is not setting variable as lookup column.

The mainly reason is that sort based on a lookup field need  special formula.

Please note that one lookup column actually one record of the related entity. 

 

Try this formula:

1)button1's OnSelect: Set(var1,a)

  button2's OnSelect: Set(var1,b)

2)Set the gallery's Items:

If(var1="a",Sort(Functions,FunctionType)
   var1="b",Sort(Functions,Company.CompanyName)
)

Here's a similar issue for your reference:

https://powerusers.microsoft.com/t5/General-Discussion/Sort-Search-issues-with-lookup-column-from-Sh...

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: Sort Gallery with Variable storing relational data

Hi @Andreas_Boeck ,

Do you want to sort based on a lookup field?

The mainly reason of the problem that you met is not setting variable as lookup column.

The mainly reason is that sort based on a lookup field need  special formula.

Please note that one lookup column actually one record of the related entity. 

 

Try this formula:

1)button1's OnSelect: Set(var1,a)

  button2's OnSelect: Set(var1,b)

2)Set the gallery's Items:

If(var1="a",Sort(Functions,FunctionType)
   var1="b",Sort(Functions,Company.CompanyName)
)

Here's a similar issue for your reference:

https://powerusers.microsoft.com/t5/General-Discussion/Sort-Search-issues-with-lookup-column-from-Sh...

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Andreas_Boeck
Level: Powered On

Re: Sort Gallery with Variable storing relational data

Thank you for your help. It works fine now.

 

But, it is somehow a workaround as this means that I need a big IF formula in the gallery's ItemsProperty and such IF formula is not dynamic.

e.g. if I would have 20 columns for which I need sorting, then this if statement needs 20 conditions - if I want to add or drop columns then I need to manually also change the if formula.

 

Storing the reference to the selected column would be the way more elegant solution - see here.  Because, I would then not have to use the clumsy IF statement and could instead simply insert the SortingVariable which stores the reference to the selected sorting column in the SortFormula of the Gallery's Items Property.

(As said this works fine already, but only if references to columns within the same entity are stored in such SortingVariable, but it seems to fail as soon as I want to  store the reference for a column of a related entity    using the 1:n relationship).

 

 

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 220 members 5,070 guests
Recent signins:
Please welcome our newest community members: