Sort Gallery by Count of Matching Records From Another SP List

[chrissahagun]

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?

[Adrian_Celis]

@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

Now you can sort descending using the 'VetVisitCount' column

[ianfernandezlo]

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:

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:

Let me know if this helps 🙂