I have a Gallery which connects to a Sharepoint list and in that Gallery I have 5 sub Galleries connecting to other Sharepoint lists so I can calculate water usage and over-usage. All the calculations work fine and display perfectly in my app however I want to sort the Gallery by the "Overuse" value which is in a control called Label24_1. This control sits directly in the main Gallery but it is a calculated field using 2 other Labels from sub Galleries. I want to sort it Descending so I can see the Overuse straight away (values greater than zero). I tried putting a button on the page which I was trying to get to Filter and Sort the Gallery after it had populated but this didn't work for me.
I do have a Search field and this is what I currently use to Filter and Sort the Gallery: SortByColumns(If(!IsBlank(TextInput2),Search(Auths,TextInput2.Text,"Title", "wma", "zone", "clientref"),Auths),"clientref")
So, I'm currently sorting by clientref which is a column in the Auths Sharepoint list but would like to Sort by that Label24_1 value
I have tried a number of things but I got the circular reference error and I'm just not sure how to get over this - any help greatly appreciated
@WarrenBelz I have found a solution albeit a bit of a dirty alternative I think.
I created a button on my main page and put this code on the OnSelect property: ClearCollect(colOveruse,Filter(Gallery6.AllItems,Label20.Text-Label24.Text>0 ))
I then created a new Gallery using the Collection as my Items and put it on my main page on top of the existing one. I lined the fields up so they match almost perfectly and then I then show and hide the Galleries when the user types a value into my searchbox ensuring I don't collect all the records from my Sharepoint list - just chunks of data. It works pretty well although it is probably an unauthordox way of doing things 🙂 thanks for your help again guys
@WarrenBelz When I "connect" the sub Gallery for the AEs I have this code on the Items property:
Filter(AEs_1,wma=ThisItem.wma) - is this what I should be using in my SortByColumns? if so how do I do it?
thanks
My initial thought is that AEs_1.thisyear would be a Table (you need to specify which record from this List you are using in the calculation).
@WarrenBelz thank you for that advice - first let me explain what I am trying to achieve
I have 3 tables (lists) involved in the calculations: Auths, AEs and MeterReadings.
Looking at my screenshot this is how I calculate - I have 5 galleries within my main gallery
The calculations are as follows:
Entitlement: Auths.VolumeML * AEs.thisyear + Auths.Sanddvalue
Use: MAX(MeterReadings.reading) - MIN(MeterReadings.reading)
OverUse: Use - Entitlement (MAX(MeterReadings.reading) - MIN(MeterReadings.reading) - Auths.VolumeML * AEs thisyear + Auths.Sanddvalue)
I tried that code in the Items property of my main Gallery and was able to get this working:
SortByColumns( Search( AddColumns( Auths, "OverUse", (volumeML + sanddvalue)), TextInput2.Text, "Title", "wma", "zone" ), "OverUse",Descending )
As soon as I add the AEs field to the expression it fails with invalid arguement type:
where am I going wrong plse?
Yes - you need to go back further and put in the formula from those two labels - essentially refer to a list or collection in all the elements of the formula.
also I had to change the period after Auths,"OverUse" to a comma as it wouldn't accept the period
@WarrenBelz thank you so much - I tried the following code on the Items property of my main Gallery:
SortByColumns( Search( AddColumns( Auths, "OverUse", Label20.Text-Label24.Text ), TextInput2.Text, "Title", "wma", "zone" ), "OverUse" ) but I am getting an error relating to a circular reference
could it be because the values from Label20 and Label24 are also calculations coming from sub galleries?
Hi @bobgodin ,
Firstly @TheRobRush is on the right track - the easiest way is to put the formula from your calculated column in the AddColumns as below
SortByColumns(
Search(
AddColumns(
Auths,
"SortCol".
YourLabelFormulaHere
),
TextInput2.Text,
"Title",
"wma",
"zone"
),
"SortCol"
)
Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.
Visit my blog Practical Power Apps
It's honestly just guessing without seeing how exactly your data is set up on all the lists, but I have a feeling you are going to have to switch to some form of an AddColumns() to be able to sort by a calculated field
WarrenBelz
791
Most Valuable Professional
MS.Ragavendar
410
Super User 2025 Season 2
mmbr1606
275
Super User 2025 Season 2