cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CW
Post Patron
Post Patron

Sort Gallery or Group by Toggle Switch

Hello folks.
I have a typical gallery and sort it as follows:

Gallery Items:

 

SortByColumns(Filter('Grocery Items',STATUS.Value=varStatusFilter&&TextSearchBox1_1.Text in Title),"Title",If(SortDescending1,Descending,Ascending))

 

This works perfect. 👍

 

Now, I have a Toggle above it. When I flick it on I want to change from SortByColumns to a GroupBy method, so I tried:

 

If(Toggle_Company1.Value=true, SortByColumns(Filter('Grocery Items',STATUS.Value=varStatusFilter&&TextSearchBox1_1.Text in Title),"Title",If(SortDescending1,Descending,Ascending)),DO GROUPBY STUFF HERE)

 

BUT, it yields red error lines????? 👎

 

I'm using the standard If(This=true,"Yup","Nope") technique but that doesn't seem to work on Gallery Items????

 

I tested the logic on a label:

 

If(ToggleCompany_1.Value = false,"Nope", "Yup") 

 

And that works fine. 👍

 

What the heck am I doing wrong please???? 

 

Thanks

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User III
Super User III

@CW 

Your Filter and your GroupBy are going to yield a different schema to the Gallery, this will not work!

What is simple - two galleries, one with the GroupBy and one with the Sort.

Set visible property of the Gallery based on the toggle.  

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

RandyHayes
Super User III
Super User III

@CW 

I live and breathe this stuff...couldn't begin to say where I learned that.  But I do know that groupby needs full values (simple values) and a Choice column is not...so, convert it to one.

 

So here is the thing about GroupBy - once you group you end up with a table that has the column you grouped by (distinct) and then a column that has all of the records associated with the group.

So, once you group, you cannot refer to the individual values of the group records (as they are now in the associated records).

 

So you have one of a couple choices - you can change to groupby to group by the title and store as well.

GroupBy(
    AddColumns(yourData,
        "choiceValue", ITEM_x0020_CATEGORY.Value
    ),
    "choiceValue",
    "Title",
    "Store",
    "records"
)

This may or may not be what you want - this will group by the Category, the Title and the Store.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

12 REPLIES 12
CW
Post Patron
Post Patron

EDIT: Further research and I found:

If(
    ToggleCompany_1.Value,
    SortByColumns(
        Filter(
            'Grocery Items',
            STATUS.Value = varStatusFilter && TextSearchBox1_1.Text in Title
        ),
        "Title",
        If(
            SortDescending1,
            Descending,
            Ascending
        )
    ),
    SortByColumns(
        Filter(
            'Grocery Items',
            STATUS.Value = varStatusFilter && TextSearchBox1_1.Text in Title
        ),
        "Title",
        If(
            SortDescending1,
            Descending,
            Ascending
        )
    )
)

 

So the false part I'll just add the group by expression................hopefully 😁 Yes I'm pretty happy with myself why? 🙂

RandyHayes
Super User III
Super User III

@CW 

Your Filter and your GroupBy are going to yield a different schema to the Gallery, this will not work!

What is simple - two galleries, one with the GroupBy and one with the Sort.

Set visible property of the Gallery based on the toggle.  

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

Thanks for chiming in Randy!!!!

 

You know, that was my first idea but thought I'd plow through and try and be MR. code king. 🙄

 

In fact, I have tried it and you're right, it doesn't work although honestly, I really don't understand why.

RandyHayes
Super User III
Super User III

@CW 

Well, it "could" work, but it is not a fun journey to get there.  The schemas of your results would need to match and that means you need to either do a lot of crazy stuff in the groupby to add lots of columns, or offset all of the column values from the Filter into the Gallery to be looked up and exclude from the filter.

It's messy!  I'd reserve that pain for when there absolutely is NO other option.  In this case...simple! Two galleries and a visible toggle = easy.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Well, I attempted it and nope, doesn't work.

I'm attempting to group by a category which in the SharePoint list and PowerApps form is a dropdown. It's easy to show the field in the gallery as 

ITEM_x0020_CATEGORY.Value

but it sure is not appreciated by the GroupBy function even given you surround them with quotes.

Such a type of field "Category" I think would be a standard field by which anyone would want to group something by but alas, PowerApps thinks everyone only uses text fields. Sure that would work but who groups by Title????? 😠   

RandyHayes
Super User III
Super User III

@CW 

No, you'll need to pull the value from the Choice column and then group by that.

ex.

GroupBy(
    AddColumns(yourData,
        "choiceValue", ITEM_x0020_CATEGORY.Value
    ),
    "choiceValue",
    "records"
)
_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Man that's excellent Randy. I was just reading the MS Doc on GroupBy and didn't see any reference to this syntax. Where did you figure that one out??

I hate to be a pest but it wont let me change the other fields such as Title and Store although the Category is now grouped on.

Category.png

Maybe I'm using the wrong type of gallery. The MS Doc looks like a simple gallery. 

 

BTW, I see the add columns there too. But what does "records" refer too?

RandyHayes
Super User III
Super User III

@CW 

I live and breathe this stuff...couldn't begin to say where I learned that.  But I do know that groupby needs full values (simple values) and a Choice column is not...so, convert it to one.

 

So here is the thing about GroupBy - once you group you end up with a table that has the column you grouped by (distinct) and then a column that has all of the records associated with the group.

So, once you group, you cannot refer to the individual values of the group records (as they are now in the associated records).

 

So you have one of a couple choices - you can change to groupby to group by the title and store as well.

GroupBy(
    AddColumns(yourData,
        "choiceValue", ITEM_x0020_CATEGORY.Value
    ),
    "choiceValue",
    "Title",
    "Store",
    "records"
)

This may or may not be what you want - this will group by the Category, the Title and the Store.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

No it can only be Category. But Store is actually (obviously) a lookup too, so I'm, currently trying to take the one part you added:

'Grocery Items',
        "choiceValue",
        ITEM_x0020_CATEGORY.Value

and create a new column based on that technique for store. BTW, Title is showing fine but two choiceValues :). I was going to start trying this: 

'Grocery Items',
        "choiceValue",
        STORE_.Value

to get this:

GroupBy(
    AddColumns(
        'Grocery Items',
        "choiceValue",
        ITEM_x0020_CATEGORY.Value,
        "storeValue",
        STORE_.Value
    ),
    "choiceValue",
    "Title",
    "storeValue",
    "records"
)

Thus:

Category1.png

😃

Am I a software engineer or what?? 😆 Thanks to you I understand the syntax a bit better. I don't know, the MS Doc shows something similar but their documentation always make you feel you can only learn things through osmosis. There's a lot of words but not enough "to the point".

I learned more from this discussion with you than I would've over a week reading their documentation. PLUS of course they only give example using text fields. Nobody EVER uses other type like lookups. Those are mythical.

THANKS AGAIN!! 

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (37,698)