Integrate MS SQL to Netsuite - Invoices and Line Items Not Being Added Properly

Joshua Fralick

• August 20, 2020 22:16

I have an ms sql database that contains and invoice header table and an invoice line item table; this is a one to many relationship. Normally, in SQL we would create a join statement to merge the datasets. In the case of integration with Netsuite, I am attempting to push the header record with all associated line items to the invoice object. Currently, when I do this, it is creating a new record for each line item rather than grouping them appropriately. I'm trying to use lookups and other methods to handle this children/line-items with no luck. Can anyone assist me with this issue?

0

• Official comment

  

  Mujtaba Ahmad 2

  • August 24, 2020 17:23

  Hey Joshua,

  IO import iterates an array generated from the export each object at a time. What is happening most likely in your case is that the SQL Export is generating an array of objects mostly like following 

  [
  { id: 1, orderId : '100', item : 'Item1' , quantity : 2, price : 10.0},
  { id: 1, orderId : '100', item : 'Item2' , quantity : 5, price : 15.0},
  { id: 2, orderId : '200', item : 'Item8' , quantity : 11, price : 138.0}
  ]

   

  You need ensure that the Invoices are grouped so that you have the data in an array of array format, like the following : 

  [
  [{ id: 1, orderId : '100', item : 'Item1' , quantity : 2, price : 10.0},
  { id: 1, orderId : '100', item : 'Item2' , quantity : 5, price : 15.0}],
  [{ id: 2, orderId : '200', item : 'Item8' , quantity : 11, price : 138.0}]
  ]

  Above format will treat every element from the export array as a single record and send all of this to import one record at a time. If you are finding it very hard to get the your SQL export to generate data in this format, you can try writing custom code at preMap to organise the data. 

  Hope this helps.

Please sign in to leave a comment.