Briefly, I have a solution in which the format yielded when Get (CurrentDate) is executed in a script differs from the format yielded when I execute it in the Data Viewer or it is computed in a field calculation. Script does it wrong (different than computer's date format settings); Data Viewer and table are correct.
I have a Dates_Emailed text field on an invoice record that stores the dates that the invoice has been emailed; when a user emails the invoice a script sets Get (CurrentDate) as the first value in the Dates_Emailed field. There's a date calculation field Date_Last_Emailed with the calc GetValue ( Dates_Emailed; 1 ).
It's worked perfectly for years.
Recently I transferred data to an updated version of the solution, first time using the Data Migration Tool.
Suddenly, client machines are inserting the date into the Date_Last_Emailed field in the wrong format, so the Date_Last_Emailed field is showing a question mark, since it's not a valid format.
Here, for example, are the contents of the Dates_Emailed field from one invoice that was emailed two times before the DMT transfer, and once after:
We're on Macs; Language & Region are set on client machines and the server for the expected formatting:
If I debug the script, I can witness it inserting the date in the wrong format: MM/DD/YYYY; however, while executing the script step that inserts the date in that format, I execute Get (CurrentDate) in the Data Viewer, and get the correct format: YYYY-MM-DD.
So the script is executing the function and formatting the date one way, at the same time the Data Viewer is executing the function and formatting it according to the system preferences.
I have tried unhosting, launching unhosted, then rehosting the file on the server, ensuring that the Language & Region format is set correctly – made no difference.
One important note – during the Data File Migration transfer on my development machine, the date format was set for US date format (MM/DD/YYYY). I'm guessing this is what fouled up the scripting engine in the solution.
I tried rewriting the script line to determine if only that line's formatting was altered. Made no difference.
I wrote a new script, Set Variable $$DateTest: Get (CurrentDate); it sets it as MM/DD/YYYY, disregarding my system's Language & Region settings. Another solution on which I tried this same script yielded the correct format.
I know I could just parse out the date, but that's not the point here. What has happened to the solution's ability to produce as text Get (CurrentDate) when it's executed in a script, as opposed to the DataViewer? And, more importantly, how do I fix it?
(I've thought about migrating it again, this time ensuring the L&R settings are correct, but it's a big solution hosted in a remote location with dreadful internet speeds; it's a time-killer to pull down. Still, I may try that...)
Another question: is this a Data Migration Tool bug?