NetSuite Empty Fields and SQL import

Integrator.io
, , , ,
1

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

2

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

3

Thank you! I didn't realize handlebar expressions were evaluated in that context beyond simple field substitutions.

Jim

4

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