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

Sort gallery based on the most listed item

Hi guys,

 

I have 3 tables, first one with different instruments (instruments), second one with different sets (sets) and the last one with the 2 primary keys (many-to-many). So a set can contain multiple instruments and an instrument can be in multiple sets.

 

Additionally I have the column "amount" in my many-to-many table. So a set can contain a specific instrument multiple times. Basically I want to know the total amount of the instruments in sets and display all the instruments including their amount in a gallery sorted descending by the amount- here is an example:

 

InstrumentID    SetID     Amount

Instrument_A    Set_B     1

Instrument_B    Set_B     2

Instrument_A    Set_A     3

Instrument_C    Set_C     4

Instrument_A    Set_C     2

 

So the Instrument_A should be listed at first with total amount of 6, Instrument_C second with amount of 4 and Instrument_B third with amount of 2.

 

Instrument            Amount

Instrument_A                   6

Instrument_C                   4

Instrument_B                   2

 

Does anyone know how to do it?

 

Thanks

2 REPLIES 2
Community Support Team
Community Support Team

Re: Sort gallery based on the most listed item

Hi @katunar ,

 

I think this cannot be achieved currently. Unless you want to create a new collection and in the formula you need to count for each record manually:

ClearCollect(C1, {InstrumentID:"Instrument_A",Amount:Sum(Filter(Table3,InstrumentID="Instrument_A"),Amount)}, 
{InstrumentID:"Instrument_B",Amount:Sum(Filter(Table3,InstrumentID="Instrument_B"),Amount)},
{InstrumentID:"Instrument_C",Amount:Sum(Filter(Table3,InstrumentID="Instrument_C"),Amount)})

Then use C1 as the data source of the gallery and sort like this:

SortByColumns( C1, Amount,Descending )

Regards,

Mona

 

Regards,

Mona

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

Re: Sort gallery based on the most listed item

Thanks for your reply @v-monli-msft

 

I think this won't help because it is a dynamic data source in which new records will be created time by time and I can't change the collection everytime a new instrument is patched.

 

If there is another way, please let me know.

 

Thanks

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 385 members 5,902 guests
Please welcome our newest community members: