I have my customer's GMT/UTC time zone saved in a custom Free-Form Text field on the customer record (for reference, field ID custentity2 in the first formula below). The time zone is set by a scheduled script using Google Maps API. For example if the customer is located in CDT the field value is -5:00.
I'm using the time zone value to output the customer's current time in another custom Free-Form Text field on the customer record and a saved search. I'm using the following formula in the current time field on the customer record and it is working as expected. It outputs "08/11/2020 03:33 PM," the current date and time offset by the customer's time zone.
TO_CHAR(CAST(CURRENT_TIMESTAMP AT TIME ZONE {custentity2} AS DATE),'MM/DD/YYYY HH12:MI AM')
The issue I have is outputting the current time field on a saved search. I get "ERROR: Invalid Expression" when running the saved search. To test, created a new formula text field on the saved search using my formula above and I get the same error.
I have tested this using the below formula on the saved search and it works fine. I just manually set the time zone as a string instead of sourcing it from the custom field.
TO_CHAR(CAST(CURRENT_TIMESTAMP AT TIME ZONE '-5:00' AS DATE),'MM/DD/YYYY HH12:MI AM')
This leads me to suspect it may be a formatting issue with the time zone value in the custom field but I have tried everything I know to convert it to a string with no success. The value is stored in a Free-Form Text field so it should be a string by default but I could be missing something here.
I can also output the time zone field's value on the saved search and I can even use it in another formula on the saved search. I used a CASE function as a test and I was able to output the value of the time zone without a problem.
Am I doing something wrong? Or maybe this just isn't possible to do? Maybe there are limitations on the CAST function when using a saved search? Any help is greatly appreciated, thanks!
