Need Help With PreSave Script

Avatar
Martin Hambalek

I have a requirement that Celigo support said would require a preSave script. I'm not sure how to implement this in my data loader flow using a csv file to Netsuite.

I have rows in a csv that represent a cash refund transaction. Each row has header data and line item data. A refund will typically have just two rows - an invoice row and an adjustment row denoted by a field called “invoice_type”. The line item data on each row has an item and an amount. 

My requirement is to check the invoice_type and if it equals “adjustment” then I want to map the item amount from that row into a header field on the cash refund.

According to the support person, I need to use a preSave script to modify the data to put the value of the refund_type = adjustment outside the array. I have no idea how or where to do this.

0

Comments

2 comments
Date Votes
  • Avatar
    Kathyana Queeman
    Technical Writer
    Celigo University Level 4: Legendary
    Engaged

    Hi Martin Hambalek, 

    I've moved your post to the Troubleshoot custom flows section for more visibility. Here are some resources on the pre save script and how to use it. 

    0
  • Avatar
    Nuri Ensing
    (Edited )
    NetSuite Integration Whiz
    Celigo University Level 4: Legendary
    Awesome Follow-up
    Engaged

    Hi,

    If I understand correctly, your CSV file contains one invoice line and one adjustment line for each cash refund transaction.

    In the data loader settings, you can first group the data so that the invoice and adjustment lines are grouped together into one array. This will make it easier to process both lines as part of a single transaction:
     

    For example, after grouping, your data would look like this:

    {
 "page_of_records": [
   {
     "rows": [
       {
         "invoice_number": "INV12345",
         "customer_name": "John Doe",
         "transaction_date": "2024-10-22",
         "invoice_type": "invoice",
         "item": "Product A",
         "amount": "1000.00",
         "refund_total": "1050.00",
         "header_adjustment": null
       },
       {
         "invoice_number": "INV12345",
         "customer_name": "John Doe",
         "transaction_date": "2024-10-22",
         "invoice_type": "adjustment",
         "item": "Adjustment Item",
         "amount": "-50.00",
         "refund_total": "1050.00",
         "header_adjustment": "50.00"
       }
     ]
   }
 ]
}

     Next, you can add a transform script to process the adjustment and map its amount to the header field. 

    Here’s an example of the script you can use:

    function transform(options) {
  // Early return if no records
  if (!options.record || !Array.isArray(options.record) || options.record.length === 0) {
    return options.record;
  }

  // Find the adjustment row
  const adjustmentRow = options.record.find(row => row.invoice_type === 'adjustment');
  
  // If we found an adjustment row, update all rows with its amount
  if (adjustmentRow) {
    const adjustmentAmount = Math.abs(parseFloat(adjustmentRow.amount)).toFixed(2);
  
    // Update all rows in the transaction to have the adjustment amount in header_adjustment
    return options.record.map(row => {
      const newRow = Object.assign({}, row);
      newRow.header_adjustment = adjustmentAmount;
      return newRow;
    });
  }
  
  // If no adjustment row found, return original records
  return options.record;
}

     

     

    This script will produce the following output:

    [
 {
   "invoice_number": "INV12345",
   "customer_name": "John Doe",
   "transaction_date": "2024-10-22",
   "invoice_type": "invoice",
   "item": "Product A",
   "amount": "1000.00",
   "refund_total": "1050.00",
   "header_adjustment": "50.00"
 },
 {
   "invoice_number": "INV12345",
   "customer_name": "John Doe",
   "transaction_date": "2024-10-22",
   "invoice_type": "adjustment",
   "item": "Adjustment Item",
   "amount": "-50.00",
   "refund_total": "1050.00",
   "header_adjustment": "50.00"
 }
 ]

     

    Does this help?
     

     

    2

Please sign in to leave a comment.

 

Didn't find what you were looking for?

New post