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

 

View solution in original post

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (3,570)