cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seadude
Memorable Member
Memorable Member

Change sort order of weekdays based on user input?

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:

seadude_0-1625341629040.png

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
timl
Super User
Super User

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

 

timl_0-1625432012706.png

 

View solution in original post

8 REPLIES 8
mdevaney
Super User
Super User

@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
Super User
Super User

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

 

timl_0-1625432012706.png

 

seadude
Memorable Member
Memorable Member

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:

2021-07-07_PA_Forums.gif

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

seadude
Memorable Member
Memorable Member

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

I'll play around with this option too!

seadude
Memorable Member
Memorable Member

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

 

2021-07-07_Timls_Amazing.gif

 

 

 

 

timl
Super User
Super User

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
Memorable Member
Memorable Member

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
Super User
Super User

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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (4,607)