expanding table sumif inside an expanding table

ileanaa5623486

21-06-2018

Hello Adobe Community,

I am new to javascript and livecycle and everything I know I have learned from youtube videos and these forums, and your help will be greatly appreciated. I have a form with two expanding tables (Table0 and Table2), Table 0 has two columns were the user can input a city name and an amount. I need Table2 to sum the amounts in Table0 that equal a city name, note that T2 expands and the code I found on the thread below doesn't work for me because I need the expanding sumif inside an expanding table (SumIf in an expanding table )

My form is in the attached link: Citysum.pdf - Google Drive

The current output (see image below) only sums the first city value in Table2. Please help!

current output.PNG

Accepted Solutions (1)

Accepted Solutions (1)

radzmar

MVP

25-06-2018

The task isn't that simple as it sounds, as you have to performs several loops in a row to get the desired result.

Here's a script that does the job as you described it.

var aCities = [], // Array for city names

oRows = Table0.resolveNodes("Row1[*]"), // node list of input table rows

oResults, oSources, i, j, oNode, oTest, iSum,

isExisting = function (value)  { // compare function

return value === this.testValue;

};

// check every input table row to collect all entered city names

for (i = 0; i < oRows.length; i += 1) {

oNode = oRows.item(i); // create an object ot the current node

oTest = {testValue : oNode.City.rawValue}; // save its city name into a test object

if (!aCities.some(isExisting, oTest)) { // check if the same city doesn't already exists in the array of city names

aCities.push(oNode.City.rawValue); // if not add it to the array

}

}

// add as may rows in the output table as city names found in the input table

Table2._Row1.setInstances(aCities.length);

oResults = Table2.resolveNodes("Row1[*]"); // node list of output table rows

// for every city name calculate the total amount

aCities.forEach(function (cCity, iIndex) {

iSum = 0; // reset the summary

oSources = Table0.resolveNodes('Row1.[City eq "' + cCity + '"]'); // filter all rows in the input table with the current city name

// Build the total amount from the filted nodes

for (j = 0; j < oSources.length; j += 1) { //

iSum += parseInt(oSources.item(j).Amount.rawValue, 10);

}

oResults.item(iIndex).City.rawValue = cCity; // output the city name

oResults.item(iIndex).TotalAmt.rawValue = iSum; // output the total amount

});

Answers (3)

Answers (3)

_Bruce_Robertson

MVP

10-09-2018

Hi,

At line 16 add the following code

oRows = Table1.resolveNodes("Row1[*]"), // node list of input table rows 

for (i = 0; i < oRows.length; i += 1) { 

oNode = oRows.item(i); // create an object ot the current node 

oTest = {testValue : oNode.City.rawValue}; // save its city name into a test object 

if (!aCities.some(isExisting, oTest)) { // check if the same city doesn't already exists in the array of city names 

  aCities.push(oNode.City.rawValue); // if not add it to the array 

}

This is the same except for the Table0 is now Table1

Then after line 31 add the following code

oSources = Table1.resolveNodes('Row1.[City eq "' + cCity + '"]'); // filter all rows in the input table with the current city name 

// Build the total amount from the filted nodes 

for (j = 0; j < oSources.length; j += 1) { // 

  iSum += parseInt(oSources.item(j).Amount.rawValue, 10); 

}  

ileanao85220353

07-09-2018

Hi Radzmar, thank you for providing an answer to my original question. Now I have another question, what if we had multiple source tables? See revised file with one additional source table Citysumv2.pdf - Google Drive

1567487_pastedImage_1.png