Search code examples
sharepointpowerappsmultichoiceitems

Sort PowerApp Gallery using a multi select choice column from SharePoint list

I have a Powerapp to monitor tasks with a flexible height gallery that is sourced from a SharePoint list. I have selected a flexible height gallery as one of my columns I need to filter my gallery on is a multi select choice column. After attempting many different filters I followed this video from Reza - https://www.youtube.com/watch?v=44j2VRbdWjk&t=182s to use the visible properties to show or hide the item if it's value falls within the collection criteria. This all works as intended.

However I would like to know if I can sort the gallery list based on the same multi select choice column. The column is called Month and simply contains January - December and an All choice. This is because a task may be over several months. I now want to sort them based on the month with any task that contains the lowest possible month in the Month column at the top.

For example Task A may have the choices January, March, April. As it has January within the array it will be sorted at the top. Another, Task B, may have March, April. The lowest month is March so will be sorted below Task A. Task C may have February, August, November. The lowest month is February so will be below Task A but above Task B, if that makes sense.

Here is the item property for my gallery list, which is simple -

Filter(Workplan,
            (IsBlank(RadioRegime.Selected) || Regime.Value = RadioRegime.Selected.Value) && 
            (IsBlank(ComboBoxYear2.Selected) || Year = ComboBoxYear2.Selected.Value))

The visible properties for my gallery items -

IsEmpty(SelectedMonths) || "yes" in Concat(ForAll(SelectedMonths, 
If(Value in ThisItem.Month.Value, "yes","no")),Value) || "All" in ThisItem.Month.Value

I have made various collections needed throughout the app and some to try and resolve this -

ClearCollect(
    MonthOrder,
    "January",
    "February",
    "March",
    "April",
    "May",
    "June",
    "July",
    "August",
    "September",
    "October",
    "November",
    "December",
    "All"
);
ClearCollect(
    MonthChoices,
    Choices(Workplan.Month)
);
Clear(SelectedMonths
);
ClearCollect(
    MonthMapping,
    { MonthName: "January", NumericValue: 1 },
    { MonthName: "February", NumericValue: 2 },
    { MonthName: "March", NumericValue: 3 },
    { MonthName: "April", NumericValue: 4 },
    { MonthName: "May", NumericValue: 5 },
    { MonthName: "June", NumericValue: 6 },
    { MonthName: "July", NumericValue: 7 },
    { MonthName: "August", NumericValue: 8 },
    { MonthName: "September", NumericValue: 9 },
    { MonthName: "October", NumericValue: 10 },
    { MonthName: "November", NumericValue: 11 },
    { MonthName: "December", NumericValue: 12 },
    { MonthName: "All", NumericValue: 13 }
);
ClearCollect(
    MonthMapping2,
    { MonthName: "January", ShortCode: "Jan" },
    { MonthName: "February", ShortCode: "Feb" },
    { MonthName: "March", ShortCode: "Mar" },
    { MonthName: "April", ShortCode: "Apr" },
    { MonthName: "May", ShortCode: "May" },
    { MonthName: "June", ShortCode: "Jun" },
    { MonthName: "July", ShortCode: "Jul" },
    { MonthName: "August", ShortCode: "Aug" },
    { MonthName: "September", ShortCode: "Sep" },
    { MonthName: "October", ShortCode: "Oct" },
    { MonthName: "November", ShortCode: "Nov" },
    { MonthName: "December", ShortCode: "Dec" },
    { MonthName: "All", ShortCode: "All" }
)

I have tried all sorts and I am currently attempting to use the collection with a month order allocating the Month value a number and sort by the min value, however I do not know how to do this with a complex column as it allows for multi selections. I don't even know if it is possible, which I think it should be, but cannot find anything online to help resolve it. Any help is greatly appreciated.

Cheers.

Solution

  • You are working in the right direction with the MonthMapping collection :)

    Wrap your current Gallery.Items formula in an AddColumns function, and get the string value of the first selected item in that field. Let's say your field is called 'WorkplanMonths':

    AddColumns(Filter(Workplan,...),"StartMonth",First(WorkplanMonths).Value)

    Now wrap this again, this time looking up the corresponding numeric value:

    AddColumns(AddColumns(...),"StartMonthNum",LookUp(MonthMapping,MonthName = StartMonth,NumericValue))

    And finally wrap it all in Sort():

    Sort(AddColumns(...),StartMonthNum,SortOrder.Ascending)