NetSuite Empty Fields and SQL import

Answered

PSA Admin

• July 21, 2020 15:45

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

• Official comment

  

  Matt Graney Chief Product Officer

  • July 21, 2020 17:53

  

  

  

  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
• 

  Matt Graney Chief Product Officer

  • July 21, 2020 16:02

  

  

  

  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

   

  0
• 

  PSA Admin

  • July 21, 2020 17:51
  • Edited

   

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

  Jim

  0

Please sign in to leave a comment.