如何基于SharePoint多选Month列对Power Apps画廊按最小月份排序？

背景与需求 #

现有一个基于SharePoint列表的Power Apps任务监控应用，使用灵活高度Gallery实现基于多选Month列的过滤（已通过设置Gallery项的Visible属性完成过滤逻辑）。现在需要对Gallery列表进行排序：将Month列中包含最小月份的任务排在最上方——比如任务A含January、March、April，因最小月份是January排首位；任务C含February、August、November，排在任务A之后、含March的任务B之前。

当前代码 #

Gallery的Items属性 #

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

Gallery项的Visible属性 #

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

已创建的集合 #

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

解决方案 #

通过AddColumns给每个任务添加最小月份数值字段，再基于该字段排序。将Gallery的Items属性修改为以下代码：

Sort(
    AddColumns(
        Filter(Workplan,
            (IsBlank(RadioRegime.Selected) || Regime.Value = RadioRegime.Selected.Value) && 
            (IsBlank(ComboBoxYear2.Selected) || Year = ComboBoxYear2.Selected.Value)
        ),
        "MinMonthNum",
        // 计算当前任务Month列中的最小对应数值
        Min(
            ForAll(
                ThisRecord.Month,
                LookUp(MonthMapping, MonthName = Value, NumericValue)
            )
        )
    ),
    MinMonthNum,
    Ascending
)

逻辑说明 #

1. AddColumns：为过滤后的每一条任务添加MinMonthNum字段，存储该任务所有选中月份对应的最小数值
2. ForAll(ThisRecord.Month, LookUp(...))：遍历当前任务的所有选中月份，从MonthMapping集合中匹配对应的数值
3. Min(...)：从匹配到的数值中取出最小值，作为排序的依据
4. Sort(..., MinMonthNum, Ascending)：按最小月份数值升序排列，数值越小的月份（如January=1）排在越靠前的位置

注：若任务包含"All"选项，由于MonthMapping中它的数值为13，会自动排在所有普通月份之后，符合需求。

内容的提问来源于stack exchange，提问作者Burner