calculate total in detail table

OL Connect Enterprise Designer
1

Hi,

I know it’s possible to calculate the total in the detail table, but this doesn’t seem to work for me.

this is what automaticly is generated by the designer:

var accumulator_1 = 0;

results.each(function(index) {

var field, result = “”;

field = record.tables[“detail”][index].fields[“sample”];

result += formatter.number(accumulator_1);

accumulator_1 += toNumber(field);

this.html(result);

});

I changed the formatter to number. What is my code supposed to look like?

kind regards,

Koen

2

Hi Koen,

Here is a script I am using now. You need to loop through the table to add up values in a certain column. The below code used a test Find Text called @test@. Result gets returned as the total. You need to change c1 to your table name in the line containing “record.tables.c1.length” and your table and the tables field you want to calculate in “record.tables.c1[i].fields.Amount;”

results.each(function(index) {

var result = 0;

var l = record.tables.c1.length;

for (i = 0; i < l; i++) {

result += record.tables.c1[i].fields.Amount;

}

this.html(result);

});

Regards,

S

3

This sort of calculation is readily done in your datamapper as well. In short, inside your loop where you’re creating the detail table in the datamapping, you can also be adding your line total to an invoice total field via an action step.

Attached is a sample of how to do this. In this data there are no totals, so the line total and invoice subtotal both needed to be calculated. You could take this a step further and calculate tax and the final total too.

https://learn.objectiflune.com/qa-blobs/5074381488242634062.ol-datamapper

This method relies on two key components.

First, you must create your total field ahead of time. You can see I’ve done this in the main Extraction step by including an empty javascript extraction to create InvoiceSubTotal.

Second, the Action step inside of the loop is used to write the values you’re currently extracting from your data back into your previously defined field using a logic of A = A + B.