cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bobgodin
Helper I
Helper I

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

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.

bobgodin_0-1654815102095.png

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

9 REPLIES 9
TheRobRush
Super User
Super User

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

_____________________________________________________________________________________
Like my answer? - Hit that Thumbs Up. Resolved the Issue? - Hit Accept as Solution.
This helps others find solutions to future issues!
WarrenBelz
Super User
Super User

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
Helper I
Helper I

@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
Helper I
Helper I

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

@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
Helper I
Helper I

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

bobgodin_0-1654912356102.png

where am I going wrong plse?

WarrenBelz
Super User
Super User

@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
Helper I
Helper I

@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
Helper I
Helper I

@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

Helpful resources

Announcements
Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Super User 2 - 2022 Congratulations

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

September Events 2022

Check out all of these events

Attend in person or online, there are incredible conferences and events happening all throughout the month of September.

Government Carousel

New forum: GCC, GCCH, DoD - Federal App Makers (FAM)

In response to the unique and evolving requirements of the United States public sector, Microsoft has created Power Apps US Government.

Users online (1,989)