Inconsistent Date Formats in AppSheet Detail View

[BlueTech]

Hi everyone,

I’m experiencing an issue with inconsistent date formats in one of my AppSheet apps:

• My app is based in the UK, the Google Sheet uses the UK locale, and my browser is also set to the UK region.

• A virtual column with the expression SELECT(Transactions[Date]... displays a list of dates in the correct UK format (DD/MM/YYYY) in the Expression Assistant and Form View.

• However, in the Detail View, the list of dates are displayed to US format (MM/DD/YYYY) after saving the record.

(For context, what I'm trying to do is get a list of dates that the customer has existing related charges and deduct them from a list of dates that we need to charge them and then with a repeat loop we'll charge them for the remaining dates. But so far it cannot deduct the existing transactions as the dates are not matched so the action that calls the loop just freezes the whole app as there's no way for it to determine when to stop repeating.)

What I’ve Tried:

• Verified table and app locale settings (set to “United Kingdom”).

• Checked the connected Google Sheet—dates are stored in UK format.

This issue only occurs in the Detail View, and all my other apps using the same setup work fine.:

1. Has anyone else encountered this issue?

2. Could this be a rendering bug in Detail View? It's the same issue across different devices and browsers, and different users using this same app.

3. Are there additional settings I should check?

Thanks for your help!

[Aurelien]

Hi @BlueTech 

I think you checked everything that needs to be checked.

Looks like a bug to me, you may want to file a ticket with support here: Contact Us - AppSheet Help

[AleksiAlkio]

Is the UK in the top of your browser's language list?

[BlueTech]

Yes, UK is the top option. 

[Suvrutt_Gurjar]

Yes, it sounds that it is a bug based on some testing done. The bug seems to be that if the dates list are computed in the detail view by way of expression, then the dates render in mm/dd/yyyy format, irrespective of the app, browser and sheet  locale.

As the screenshot below shows, the date directly entered in the detail view in the field [Customer_Since] is correctly rendered in dd/mm/yyyy format as per app, browser and sheet locale. The date of 20th November 2024

Similarly dates are rendered correctly in dd/mm/yyyy format in the related transactions table in the [Transaction_Date] column when the related transactions records were created. 

However when these transactions dates from the "Transactions" table were referred in the column [Dates_List] in the parent Customers table by way of the expression [Related Transactions][Transaction_Date] , those dates were rendered in the mm/dd/yyyy format as highlighted in red below.

The workaround till the bug is addressed could be to have a VC in the "Transactions" table called say [Date_UK_Format] with an expression something like TEXT([Date], "DD/MM/YYYY") and use that in your SELECT(Transactions[Date_UK_Format]......)

The dates will then render in the UK format in the list as shown below in the [Dates_List_UK] column. However point to note will be this will be a list with element type as text and not element type as date.

[AleksiAlkio]

@Suvrutt_Gurjar  Good catch!

[Suvrutt_Gurjar]

Thank you Aleksi. 👍

[BlueTech]

Thanks a lot!

In the end, I managed to achieve my goal by using the following expression:

[Days to Charge][Date] - [Days Already Charged][Date]

Days to Charge is the first virtual column (SELECT(Dates[Date]..., containing a list of rows, where the date column is also the key column.

Days already charged is SELECT(Transactions[KeyColumn] 

Basically we're bringing in the entire transaction row rather than just the list of dates, and then deducting the dates on the existing transactions from the dates of the days to charge.

I also had to add another hidden column that updates with an action ([Trigger]+1) after every loop run, as I noticed that the Remaining Dates column doesn't immediately update with the correct list of remaining dates, as there's no change to this row.

Hopefully, someone else can find this helpful as well.