Trying to Sort a Gallery by a calculated field in the Gallery

[bobgodin]

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

[TheRobRush]

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]

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

[bobgodin]

@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?

[bobgodin]

also I had to change the period after Auths,"OverUse" to a comma as it wouldn't accept the period

[WarrenBelz]

@bobgodin ,

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.

[bobgodin]

@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?

[WarrenBelz]

@bobgodin ,

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).

[bobgodin]

@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

[bobgodin]

@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