Sort two columns at one time

(0) Share

Report

Posted on by AR-03030545-0

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.

Categories:

Using Apps Using Formulas

General Questions

All responses (13)

Answers (0)

AR-03030545-0 29 on at

Like (0)

Report
Sort two columns at one time

Hi @MS.Ragavendar,

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:

From the Automate flow (Convert CompletedTime to UTC because previously, it was set as Text)

From the patch configuration
Suggested answer

MS.Ragavendar 1,066 on at

Like (0)

Report

Sort two columns at one time

@AR-03030545-0,

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 29 on at

Like (0)

Report

Sort two columns at one time

Hi @MSR@08012015

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 1,066 on at

Like (0)

Report

Sort two columns at one time

@AR-03030545-0,

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 29 on at

Like (0)

Report
Sort two columns at one time

Hi @MSR@08012015

CompletedTime is used at/for this only:

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)

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 1,066 on at

Like (0)

Report

Sort two columns at one time

@AR-03030545-0,

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 29 on at

Like (0)

Report

Sort two columns at one time

Hi @MSR@08012015

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 1,066 on at

Like (0)

Report
Sort two columns at one time

@AR-03030545-0,

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 29 on at

Like (0)

Report

Sort two columns at one time

Hi @MSR@08012015

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?
MS.Ragavendar 1,066 on at

Like (0)

Report
Sort two columns at one time

@AR-03030545-0,

If sorting still doesn't work as expected:

Check the data type of CompletedTime – Ensure it's stored as a DateTime value, not a string.

Debug using a Collection - Add ClearCollect(collectionName, datascource) before ForAll to inspect the sorted output.

Display collectionName in a gallery to verify sorting behavior.

Try sorting CompletedTime in descending order (SortOrder.Descending) to see if any change occurs

With( { orderSeq: Filter( SortByColumns( Orders, "Date_x002f_Time", SortOrder.Ascending, "CompletedTime", SortOrder.Ascending ), Status = "Urgent" && (Progress = "Order Received" || Progress = "In Progress" || Progress = "Delivery") ) }, ClearCollect(testOrders, orderSeq), // Collect sorted data for inspection ForAll( Sequence(CountRows(orderSeq)), Patch( Index( orderSeq, Value ), {RowNo: "U" & Value} ) ) )