Need assistance with distinct and sort code for listbox

[kriggo15]

I have a Listbox I need help with sorting. I have a SharePoint source that looks like the below table.

Item	Category	Order	Field4	Field5	….
Item 1	Cat1	2
Item 2	Cat3	1
Item 3	Cat3	1
Item 4	Cat2	3
Item 5	Cat2	3
Item 6	Cat1	2
Item 7	Cat4	4
Item 8	Cat4	4

I want it to show up in the list box with just the unique list of Categories in the order listed.
Example:

Cat3

Cat1

Cat2

Cat4

I can easily get the Distinct to work (below) but cannot get the sort part working. I've tried Sort and SortByColumns.

Distinct(colCategories2, Category)

Thanks

[eka24]

Try

Sort(Distinct(colCategories2, Category),Result, Ascending)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[kriggo15]

@eka24, your code does not sort based on the order, just the category itself. 

[eka24]

Create a collection Onvisible of the screen;

ClearCollect(MyCol2,Distinct(colCategories2, Category));

ClearCollect(MyCol3, AddColumns (MyCol2, "OrderNo",Order))

Then change the formula on the listbox to:

Sort(MyCol3,Order, Ascending)

Or

Sort(MyCol3.Result,Order, Ascending)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[kriggo15]

Thanks @eka24, I'm struggling a little here... So far, I have the following code, because I want "All Categories" at the top of the list and this part works fine:

ClearCollect(colCategories2, {Service:"All Categories"});
Collect(colCategories2, 'Intake Service');
ClearCollect(MyCol2,Distinct(colCategories2, Category));

When I go to add ClearCollect(MyCol3, AddColumns(MyCol2, "OrderNo",Order), Order is not an option. It only allows Result or This Record. If I use Result, I just get the Categories again.

[eka24]

Please use exactly what I gave And see if it works. If it works, we will find a way of dealing with the all category.

Can you try and give feedback

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[kriggo15]

I did exactly as you provided but Order is still not an option. It says the name is not valid and the identifier is not recognized. It wants either Result or ThisRecord as the 3rd argument.

[v-bofeng-msft · Accepted solution]

Hi @kriggo15 :

Could you tell me what Order's data type is?Is it number type?

I assume Order is a nubmer type field.

Please try this code:

Distinct(
    Sort(
        AddColumns(
            GroupBy(
                colCategories2,
                "Category",
                "TheGroup"
            ),/*Group records by Category field*/
            "SumOrder",
            Sum(
                TheGroup,
                Order
            )
        ),/*Create a new SumOrder column, fill the field with the sum of order*/
        SumOrder
    ),/*Sort by the SumOrder field*/
    Category
)

Best Regards,

Bof