Sort Gallery or Group by Toggle Switch

[CW]

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

[CW]

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 · Accepted solution]

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

[CW]

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]

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

[CW]

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]

@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"
)

[CW]

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.

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 · Accepted solution]

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

[CW]

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:

😃

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!!