I have some data which has a detail table which in turn has another table in. I want to update a field within the first table but how can I do this?
I have the below which is looping through and getting the subtotal no problem but the last line isn’t updating the field as normally I’d use record.fields… but you can’t see the Analysis value when you use this
//lopp round analysis lines
for (a = 0;a<record.Analysis.length;a++){
var SubTotal = 0.00
//loop round item lines and add subtotal together
for (l = 0;l<record.Analysis[a].Line.length;l++) {
SubTotal = SubTotal+record.Analysis[a].Line[l].iTotal;
}
logger.info(SubTotal);
record. Analysis[a].SubTotal = SubTotal;
}
First of all, this has to be done in the DataMapper.
Second, you should be using the record.set() method to update fields in each record/table.
There are two ways to do this: you can either compute the SubTotal field for each record inside each loop, or compute all SubTotal fields at once at the end of the data mapping process.
Attached is a config ( NestedTotals.OL-datamapper ) that demontrates both methods. The main table is called INVOICE and may contain more than one invoice. Each INVOICE contains an embedded table named ITEM.
There is an extract step in the main loop named Computed main table field which adds up all the Total fields from all ITEM records and stores the result in the field VerifiedTotal. That’s the first method I mentioned earlier.
The second method is demonstrated in the last step of the process, named Alternate method. This one is a Script that loops through each INVOICE sums up all ITEM totals, and stores the result in the VerifiedTotal2 field in each INVOICE, using the record.set() method.
As you can see, both VerifiedTotal and VerifiedTotal2 contain the same value in each INVOICE.
