expanding table sumif inside an expanding table

[ileanaa5623486]

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!

[radzmar · Accepted solution]

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
});

[ileanaa5623486]

This is great, thank you so much!

[ileanao85220353]

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

[_Bruce_Robertson]

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); 

}