Answered
NetSuite Empty Fields and SQL import
I am importing to SQL server using a NetSuite listener and an SQL import update query. The query fails, however, if the source data is missing a field. This happens because, apparently, when a field is empty/null in NS, rather than include a "name:empty value" pair, the export for the listener does not include the empty field in the JSON object at all. If I'm using a map, I can specify options if the data is missing, but there's no way to do that in an SQL query. For instance, "isnull()" doesn't work if the field doesn't even exist.
Thanks for any insights!
Jim
0
Comments
Glad that worked for you, Jim. That, and more! There are a wide range of macro functions you can use, see https://docs.celigo.com/hc/en-us/articles/360039326071-Handlebars-Library.
-Matt
Hi Jim,
You wrote:
If I'm using a map, I can specify options if the data is missing, but there's no way to do that in an SQL query.
There is in fact a way to do that. In your SQL query builder for the import you can test for missing fields using the {{#if}} handlebar function. If for example you were expecting a field called optionalField, you can test for it as follows, and use its value if it's present, and otherwise use a default.
{{#if data.optionalField}}{{data.optionalField}}{{else}}My Default Value{{/if}}Hope that helps!
Matt
Thank you! I didn't realize handlebar expressions were evaluated in that context beyond simple field substitutions.
Jim
Please sign in to leave a comment.