Has anyone tried to convert a free-form text to Date format in the saved search? I tried to use

TO_DATE

function in the formula, but when I hardcode

TO_DATE('2022/03', 'YYYY/MM')

is worked, but if I pulled from record

TO_DATE({custrecord_yyyymm}, 'YYYY/MM')

it failed 😞 Not sure where I did wrong...

Are you getting the unexpected error? Make sure all the results have that field populated with a valid value or add logic to handle that. You can also try to filter your search to 1-2 results with valid values.

I just tried to validate as well, seems to always fail in Saved Search formula ... however the exact same formula behaves as expected in Analytics Query/Dataset , so seems like a glitch/bug in saved searches in particular perhaps you could try asking in the suiteanalytics channel though as well

it worked for me with 1 valid result, it fails if it tries to do the TO_DATE function to something invalid or that doesn't follow YYYY/MM I think. You could add logic to handle those scenarios with a CASE

hmm would you mind sharing what your formula vs field value is? I tried with random custom record text field, then filtered results by unique internal id, but still had invalid expr for me

Ignore me, my formula didn't save and I saw the result of the hardcoded string. I agree, seems like a glitch.

Thank you! Another normal day in NetSuite :S

I believe you will need to do 3 things to make this work. 1. Use to the TO_DATE like you are. 2. Wrap that in a TO_CHAR Even to it already a string I think this prevents NetSuite from automatically converting to a date before your TO_DATE gets to it. 3. Use an NVL statement to deal with Blank Cells.