How to catch an exception in a Netsuite saved search formula?

0
votes

I have a saved search that looks at a custom text field on a Sales Order. The custom field should contain an ISO date. I want to get all records where that date is greater than today. My first attempt was to try Formula(Date) with the formula below and condition "is greater than today"

TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD')

However this gave me an "Unexpected Error" when I tried to do the search. I worked out this was because not every record has an ISO date, some are null and some have been edited to contain some non-date data. It seems that if any row returns an error the whole saved search fails. I therefore want to exclude anything that doesn't parse as a date from my search. However, I could find no way of catching an exception from the TO_DATE function. For the moment I have:

CASE WHEN REGEXP_LIKE({custbody_est_delivery},'(\d{4})-(\d{2})-(\d{2})') THEN TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD')ELSE TO_DATE('2099-12-31', 'YYYY-MM-DD') END

This works, but it is a horrible hack and would fail if someone e.g. wrote in '9999-99-99' in the field.

How can I catch exceptions in a Netsuite saved search formula? I'm looking for something like the pseudocode below:

IFERROR(TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD'), <do something sensible>)
1
netsuitesaved-searches

1 Answers

1
votes

You can use the NVL2 function to conditionally take care of any empty field values.

e.g. 

NVL2({custbody_est_delivery}, TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD'), '')

See the Help page titled SQL Expressions for all of the formula functions available to you.

Personally I'd say any field values that are invalid dates are a data problem and should be corrected with a mass update or CSV import, and validation rules should be added to your custom field such that no invalid dates are allowed.