web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : F7HIz8AM5Aex4mf0Y1mDhe
Power Apps - Building Power Apps
Unanswered

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

Like (0) ShareShare
ReportReport
Posted on 9 Jun 2022 22:56:27 by 380

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

  • bobgodin Profile Picture
    380 on 11 Jun 2022 at 06:21:38
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    @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

  • bobgodin Profile Picture
    380 on 11 Jun 2022 at 04:48:40
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    @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

  • WarrenBelz Profile Picture
    148,896 Most Valuable Professional on 11 Jun 2022 at 04:03:03
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    @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 Profile Picture
    380 on 11 Jun 2022 at 01:55:48
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    @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 Profile Picture
    148,896 Most Valuable Professional on 10 Jun 2022 at 08:33:40
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    @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 Profile Picture
    380 on 10 Jun 2022 at 07:57:10
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

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

  • bobgodin Profile Picture
    380 on 10 Jun 2022 at 07:52:35
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

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

  • WarrenBelz Profile Picture
    148,896 Most Valuable Professional on 10 Jun 2022 at 07:39:52
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    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

  • TheRobRush Profile Picture
    11,121 Moderator on 09 Jun 2022 at 23:02:55
    Re: Trying to Sort a Gallery by a calculated field in the Gallery

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410 Super User 2025 Season 2

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 2

Loading complete