Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Apps - Building Power Apps
Suggested answer

Sort two columns at one time

(0) ShareShare
ReportReport
Posted on by 67
Hi all,
 
I have a hard time in sorting these items based on CompletedTime. It already follows the Date_x002f_Time ASC condition, but how do I fix my code to have it sort CompletedTime next?
 
Here's my current code:
I'd really appreciate if anyone could help me figured this out.
Thank you so much.
  • AR-03030545-0 Profile Picture
    67 on at
    Sort two columns at one time
     
    An update: I managed to convert CompletedTime from text to date and time data type inside SPL. 
     
    I/m almost there. Orders with earliest CompletedTime managed to top the later one. However, it cannot override the Date_x002f_Time condition. In fact, orders with no CompletedTime is arranged at top, and those with CompletedTime at bottom.
     
     
    I tried to sort by using CompletedTime and TimeCreated (auto created inside any SPL) since it is basically the same like Date_x002f_Time and used the code that you gave to test it, but it did not work.
     
    Expected order ararngement should be: U1, U3, U4, U2
  • AR-03030545-0 Profile Picture
    67 on at
    Sort two columns at one time
     
    I couldn't see or have the access to change the site settings, need to ask permission from my dept admin, I guess.
     
    But you are saying that UTC+8 was applied twice in whch I assume it came from these:
    1. From the Automate flow (Convert CompletedTime to UTC because previously, it was set as Text)
    2. From the patch configuration
    Are my assumptions correct?
  • Suggested answer
    MS.Ragavendar Profile Picture
    2,647 on at
    Sort two columns at one time
     
    Yes, the issue is likely due to time zone differences between your system and SharePoint (SPL). SharePoint stores DateTime values in UTC (Coordinated Universal Time) by default, while your app might be using a local time zone.
     
    3:50 PM (your local time) -> Converted to UTC: 7:50 AM -> But if SharePoint stores it as 23:50 (11:50 PM), then it's likely assuming a different offset (e.g., UTC+8 was applied twice).
     
    Check for your timezone in the regional settings.
     
  • AR-03030545-0 Profile Picture
    67 on at
    Sort two columns at one time
     
    I already changed the data type. 
    Why does the time stored inside SPL is not the same as what I defined inside my system?
     
    I tested by defining the Order Need/CompletedTime at 3:50PM.
    But inside SPL, it stated as 23:50PM...
     
    Do you know why?
    This is what I did in changing the data type.
     
    This is the patch code of Pre-Order Time I did on the Submit Order button:
    'Pre-Order Time': If(
        !IsBlank(PreOrderTime.Text) && Trim(PreOrderTime.Text) <> "",
        DateAdd(
            DateValue(Text(Now(), "[$-en-US]yyyy/mm/dd")) + TimeValue(PreOrderTime.Text),
            TimeZoneOffset(Now()),
            Minutes
        ),
        Blank()
    ),
     
    I forgot to tell you that I have a flow that refer to Pre-Order Time column. This flow is used for auto triggering from Pre-Order - Urgent or Normal based on time condition set (time submitted - Pre-order Time). 
  • MS.Ragavendar Profile Picture
    2,647 on at
    Sort two columns at one time
     
    Yes exactly correct, Instead of that you showing the items in gallery right.
     
    Apply the sorting to the columns in the gallery, let the user will sort the columns accordingly this gives more flexibility to the users. 
     
    Instead of the changing the backend schema.
  • AR-03030545-0 Profile Picture
    67 on at
    Sort two columns at one time
     
    CompletedTime is used at/for this only:
     
    1. User interface
      • Submit button - to patch CompletedTime (Pre-Order Time defined by users)
      • Text field a.k.a PreOrderTime.Text- to store CompletedTime (Pre-Order Time defined by users)
    2. Runner interface/Dashboard like shown in the picture
      • Text field - to display the CompletedTime (or in the dashboard it is stated as Order Need)
    I took some time to analyze this to ensure that this stuff won't affect the system if I change the data type. 
     
    But, just to clarify, I still can go back (like undo/change the data type from DateTime to text again) to changing things in the way it used to be IF it gives some impact to the system, right? :")
  • MS.Ragavendar Profile Picture
    2,647 on at
    Sort two columns at one time
     
    Yes, changing the data type of CompletedTime from Text to DateTime in SharePoint will affect the whole system if other parts of your app, flows, or reports rely on the existing text format.
     
    For e.g, If records are stored as text ("2:56 PM"), changing the column type won't auto-convert old values to DateTime. You may need to manually update old records.
  • AR-03030545-0 Profile Picture
    67 on at
    Sort two columns at one time
     
    I am using SharePoint List. If I changed the data type of CompletedTime from text/string to DateTime, won't it affect the whole system? FYI, the system has been published to be used to users but stop for a while because I need to do this improvement. 
  • Suggested answer
    MS.Ragavendar Profile Picture
    2,647 on at
    Sort two columns at one time
     
    Patch(
        Orders,
        Defaults(Orders),
        {
            'Date/Time': Now(), // Ensure this remains DateTime
            'Pre-Order Time': If(
                !IsBlank(PreOrderTime.Text) && Trim(PreOrderTime.Text) <> "",
                DateAdd(
                    DateValue(Text(Now(), "[$-en-US]yyyy-mm-dd")),
                    Value(Hour(TimeValue(PreOrderTime.Text))),
                    Hours
                ) + Time(
                    Hour(TimeValue(PreOrderTime.Text)),
                    Minute(TimeValue(PreOrderTime.Text)),
                    0
                ),
                Blank()
            )
        }
    )
    
     
     
    I believe this will works because its storing CompletedTime as a valid DateTime field.
     
    Ensures sorting works correctly in your gallery because it is no longer a text string.
     
    In the dataverse table ensure that the column type for CompletedTime is DateTime and not Text.
     
     
     
  • AR-03030545-0 Profile Picture
    67 on at
    Sort two columns at one time
     
    Patch(
            Orders,
            Defaults(Orders),
            {
                'Date/Time': Text(
                    Now(),
                    DateTimeFormat.LongDateTime
                ),'Pre-Order Time': If(
                !IsBlank(PreOrderTime.Text) && Trim(PreOrderTime.Text) <> "",
                Text(TimeValue(PreOrderTime.Text), "h:mm AM/PM"), ...
            }
    )
     
    This is how I configured for Date_x002f_Time and CompletedTime (Pre-Order Time) on the Submit button. 
    Perhaps this could be the reason why it is not working out?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 791 Most Valuable Professional

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 410

#3
mmbr1606 Profile Picture

mmbr1606 275 Super User 2025 Season 1