cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrissahagun
Resolver II
Resolver II

Sort Gallery by Count of Matching Records From Another SP List

Hello.  New to PowerApps here...  Is there any way to sort a gallery that is getting data from an SP List based on the count of matching records in another SP list?  Sample data below:


PETS (This is my Gallery that I want to sort by count of each pet's vet visits - descending)

 

Pet Name   Type

Spot           Dog

Ming          Cat

Sleepy        Guinea Pig

Peanut        Hamster

 

 

VET VISITS (this is where the count should happen)

 

Vet        Pet Name      Date

John      Ming            12-10-2020

Mary     Sleepy          2-4-2021

John      Ming            4-30-2021

Mary     Peanut          5-15-2021

 

So in the PETS gallery, I'd like the order to be: "Ming", then "Peanut", then "Sleepy", then "Spot"

 

Is this even possible?

2 REPLIES 2
Adrian_Celis
Super User
Super User

@chrissahagun 

Yes it's possible.

You can add an extra column using the AddColumns function to have the count of vet visits per pet and then sort from there.

 

So in your gallery you can have your Items property like:

AddColumns(PETS As Pets,
"VetVisitCount",
CountRows(Filter('Vet Visits','Pet Name' = Pets.PetName))
)

So you'll have a table of data that will look something like this

Adrian_Celis_0-1646975860441.png

 

Now you can sort descending using the 'VetVisitCount' column

 

ianfernandezlo
Regular Visitor

Hi, @chrissahagun. There's a lot of way to do this and here is one method you can use:

 

Here is what I did to sort your gallery:

1.) First, I created a collection named "colVetVisits" with columns Date, PetName, Vet:ianfernandezlo_1-1646975442051.png

2.) I added a button named "Sort" and updated its "OnSelect" property:

//Renamed "PetName" column to ThisPetName in colVetVisits and added all data in a new collection named "colVetVisitsRenamed"
ClearCollect(colVetVisitsRenamed,RenameColumns(colVetVisits,"PetName","ThisPetName"));

//From the new collection "colVetVisitsRenamed", I added a new column "CountPerPet".
//This new column has a value for each record that counts ThisPetname from colVetVisitsRenamed in column PetName in colVetVisits
//The resulting collection is saved into a new collection colVetVisitCounted
ClearCollect(colVetVisitCounted,AddColumns(colVetVisitsRenamed,"CountPerPet",CountRows(Filter(colVetVisits,PetName=ThisPetName))))

 3.) Finally, in the "Items" property of the gallery, I entered the following to sort CountPerPet and ThisPetName

SortByColumns(colVetVisitCounted,"CountPerPet",Descending,"ThisPetName",Ascending)

This resulted to your wanted sorting in the gallery:

ianfernandezlo_2-1646976386355.png

 

Let me know if this helps 🙂 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (4,495)