Need help on trasnform map scripts to add all the rows with same invoice number before inserting

[Raviteja K]

Hi

I need a help to write a transform script to add amount in all the rows with same invoice number coming from data source and map it to the target field once invoice is created. Where to write the script onStart, on Before? Please guide thank you 

[Anand Kumar P · Accepted solution]

Hi @Raviteja K ,

You have to write OnBefore TM  script will run before each row is transformed. It queries the import set row table for records with the same invoice number, adds up the amounts, and sets the total amount on the target record.

var invoiceNumber = source.u_invoice_number;
var amount = source.u_amount;
var gr = new GlideRecord('yourTable');
gr.addQuery('u_invoice_number', invoiceNumber);
gr.query();
var totalAmount = 0;
while (gr.next()) {
totalAmount += parseInt(gr.u_amount);
}
target.u_total_amount = totalAmount;

Mark it as helpful and solution proposed if it serves your purpose.
Thanks,
Anand

[Raviteja K]

Hi Anand

Thank you for the response, 'yourtable' refers to staging table?

[Anand Kumar P]

Yes your staging table.

[palanikumar]

Hi,

You can create a Transform script with when = onBefore. This will execute for each row of import set record, and before doing any processing. You can also use onAfter if you want the script to execute after processing the current import set record .

Thank you,

Palani