1

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!

Ben M
  • 15
  • 2

1 Answers1

0

I don't think NetSuite supports CAST.

vVinceth
  • 905
  • 5
  • 7
  • I thought the same thing but the formula works fine when I manually set the value of the time zone rather than sourcing it from the field. If NetSuite didn't support CAST one would think it wouldn't work in any scenario but I could be wrong. I just can't figure out why the formula works when the time zone is manually set. What's the difference between that and sourcing it from a field other than a possible formatting issue? – Ben M Aug 13 '20 at 13:15
  • It may work for some instance, but it is a function that is not supported. Maybe the actual bug is that it is working, instead of not working. – vVinceth Aug 18 '20 at 03:14
  • That makes sense. I'll leave this open for now to see if anyone has more input on the topic but thanks for your help! – Ben M Aug 19 '20 at 13:08