Change sort order of weekdays based on user input?

[seadude]

Hello,

I'm creating a collection of weekdays using:

Clear(colWeekdays);
ForAll(
    Calendar.WeekdaysLong(), 
    Collect(colWeekdays,
        {
            order: CountRows(colWeekdays) + 1,
            day: ThisRecord.Value
        }
    )
);

The `Calendar.Weekdayslong()` function always starts with Sunday. 

I need to give user ability to change the Start of the Week with a dropdown:

Instead of hardcoding something like this for each scenario:

Switch(ddStartOfWeek.Selected.Value,
    "Monday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Monday"},
        {order: 2, day: "Tuesday"},
        {order: 3, day: "Wednesday"},
        {order: 4, day: "Thursday"},
        {order: 5, day: "Friday"},
        {order: 6, day: "Saturday"},
        {order: 7, day: "Sunday"}
    ),
    "Tuesday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Tuesday"},
        {order: 2, day: "Wednesday"},
        {order: 3, day: "Thursday"},
        {order: 4, day: "Friday"},
        {order: 5, day: "Saturday"},
        {order: 6, day: "Sunday"},
        {order: 7, day: "Monday"}
    ),
    "Wednesday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Wednesday"},
        {order: 2, day: "Thursday"},
        {order: 3, day: "Friday"},
        {order: 4, day: "Saturday"},
        {order: 5, day: "Sunday"},
        {order: 6, day: "Monday"},
        {order: 7, day: "Tuesday"}
    ),
    "Thursday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Thursday"},
        {order: 2, day: "Friday"},
        {order: 3, day: "Saturday"},
        {order: 4, day: "Sunday"},
        {order: 5, day: "Monday"},
        {order: 6, day: "Tuesday"},
        {order: 7, day: "Wednesday"}
    ),
    "Friday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Friday"},
        {order: 2, day: "Saturday"},
        {order: 3, day: "Sunday"},
        {order: 4, day: "Monday"},
        {order: 5, day: "Tuesday"},
        {order: 6, day: "Wednesday"},
        {order: 7, day: "Thursday"}
    ),
    "Saturday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Saturday"},
        {order: 2, day: "Sunday"},
        {order: 3, day: "Monday"},
        {order: 4, day: "Tuesday"},
        {order: 5, day: "Wednesday"},
        {order: 6, day: "Thursday"},
        {order: 7, day: "Friday"}
    ),
    "Sunday",
    ClearCollect(colSortedWeek,
        {order: 1, day: "Sunday"},
        {order: 2, day: "Monday"},
        {order: 3, day: "Tuesday"},
        {order: 4, day: "Wednesday"},
        {order: 5, day: "Thursday"},
        {order: 6, day: "Friday"},
        {order: 7, day: "Saturday"}
    )
)

Is there a way to do it more programmatically using something like `Sequence(7)` and the original `colWeekdays` collection?

[mdevaney]

@seadude 
Assuming the Items property of your weekdays dropdown uses this code

Calendar.WeekdaysLong()

You can use this code to convert Calendar.WeekdaysLong() to a comma-separated string, split the string at the selected date, re-order the days at the split point and then convert back into a table once again.  

Set(
    colSortedWeek,
    With(
        {
            varStringWeekdays: Split(
                Substitute(
                    Concat(
                        Calendar.WeekdaysLong(),
                        Value & ","
                    ),
                    ddStartOfWeek.Selected.Value,
                    "~" & ddStartOfWeek.Selected.Value
                ),
                "~"
            )
        },
        With(
            {
                varTableSplit: Split(
                    Last(varStringWeekdays).Result & First(varStringWeekdays).Result,
                    ","
                )
            },
            FirstN(
                varTableSplit,
                CountRows(varTableSplit) - 1
            )
        )
    )
)

Not exactly low code, but it works 😄

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[timl · Accepted solution]

Hi @seadude 

One technique you could use is to create a 'transformed' order column. Let's say the user specifies Wednesday. You would prefix days 3 and above with 'a', and days under 3 with 'b'. You can then carry out an 'alpha' sort to return the days in the desired sequence. Taking your colWeekdays collection, here's the syntax to sequence the days starting from Wednesday.

Sort(colWeekdays, If(order>3, "a" & order, "b" & order))

[seadude]

Now THAT is tricky! How would you prefix the collection items based on user selection? Are you actually just handling it all right there in the `Sort()` !?! WOW!

Trying to wrap my head around it... my brain wants to do something like: 

Data: 

ClearCollect(colDays,
    {order: 1, dayName: "Monday"},
    {order: 2, dayName: "Tuesday"},
    {order: 3, dayName: "Wednesday"},
    {order: 4, dayName: "Thursday"},
    {order: 5, dayName: "Friday"},
    {order: 6, dayName: "Saturday"},
    {order: 7, dayName: "Sunday"}
)

OnChange of ddSelectedDay:

Set(varStartSort, LookUp(colDays, dayName = ddSelectedDay.Selected.dayName, order));
Clear(colDays1);
ForAll(colDays,
    Collect(colDays1,
        {
            order: 
                If(
                    ThisRecord.order >= varStartSort, 
                    Concatenate("a", ThisRecord.order), 
                    Concatenate("b", ThisRecord.order)
                ),
            dayName: ThisRecord.dayName
        }
    )
)

Results in:

This is rather ugly (duplicating collection names). I am tripping on your solution!! Neat.

[seadude]

Dang! That is some PowerFX wrangling right there @mdevaney ! Ok!

I'll play around with this option too!

[seadude]

@timl , very elegant! I have never seen Sort() used this way.

Though its not clear to me how the "a" and "b" play a role here. I'm unsure how PowerApps maintains the order when multiple records are prepended with the same value ("a" for example).

[timl]

Hi @seadude 

Thanks for the great annimation, which perfectly illustrates the technique and how it's all handled in the Sort()!

Just to clarify the sequencing using the Wednesday example, the Sort function effectively operates against an input that looks like this:

ClearCollect(colDays,
{order: "b1", dayName: "Monday"},
{order: "b2", dayName: "Tuesday"},
{order: "a3", dayName: "Wednesday"},
{order: "a4", dayName: "Thursday"},
{order: "a5", dayName: "Friday"},
{order: "a6", dayName: "Saturday"},
{order: "a7", dayName: "Sunday"}
)

This hopefully shows how Sort would order in the sequence "a3,a4,a5,a6,a7,b1,b2", and thus retain the correct day sequence.

[seadude]

Ah! There it clicked for me... prefixing ORDER not DAYNAME! For some reason I was seeing "aWednesday", "aThursday", etc. in my head.... 

That is very clear now and SO clever @timl . 

[timl]

Thanks alot @seadude 🙂 I'm really pleased that makes sense and how it provides a simple solution.