expanding table sumif inside an expanding table

Avatar

Avatar

ileanaa5623486

Avatar

ileanaa5623486

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)

Avatar

Avatar

radzmar

MVP

Avatar

radzmar

MVP

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)

Avatar

Avatar

_Bruce_Robertson

MVP

Avatar

_Bruce_Robertson

MVP

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

}  

Avatar

Avatar

ileanao85220353

Avatar

ileanao85220353

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

Avatar

Avatar

ileanaa5623486

Avatar

ileanaa5623486

ileanaa5623486

06-07-2018

This is great, thank you so much!