Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

[AEM Gems Webinar] Accelerating Experience Manager as a Cloud Service development with Rapid Development Environments
SOLVED

HELP! SUMIF ADOBE LIVECYCLE FOR (REPEATING ROW)

Avatar

Level 1

I'm new here and I would like to seek some assistance from the Adobe Gurus to complete this form using JavaScript and SUMIF. I'm experiencing issues getting my form to work properly. I've discovered a syntax that might enable me to obtain the proper formula, but it fails in various circumstances. My concern is this.

I want to sum up the "Amount Due" if the "Running days" were in line with this scenario.
For example, "1 to 30 Days," "32 to 60 Days." Here is an example: if the running days are between 1 and 30 days, the formula works! In this case, I altered the name of the cell depending on the text headers: cell 5 as "running days" and cell 7 as "amount due."



Gaddiel_11_0-1670309854710.png

But if I changed the value of second row and try if it can sum up the amount simultaneously, the formula doesn't work 😞

Gaddiel_11_1-1670310160931.png

Here's my formula by the way for 1 - 30 days field

var sum = 0;

var rows = xfa.resolveNodes("Details[0].AmountDue");
var rows = xfa.resolveNodes("Details[1].AmountDue");

for (var i=1; i < xfa.resolveNodes("Details[0].AmountDue").length;i++)
for (var i=1; i < xfa.resolveNodes("Details[1].AmountDue").length;i++)


var item = xfa.resolveNodes("Details[0].AmountDue");
var item = xfa.resolveNodes("Details[1].AmountDue");


{

if (xfa.resolveNode("Details[0].RunningDays").rawValue >= "1" && xfa.resolveNode("Details[0].RunningDays").rawValue <= "30" && xfa.resolveNode("Details[0].RunningDays").rawValue !== "")

if (xfa.resolveNode("Details[1].RunningDays").rawValue >= "1" && xfa.resolveNode("Details[1].RunningDays").rawValue <= "30" && xfa.resolveNode("Details[1].RunningDays").rawValue !== "")
{

sum += xfa.resolveNode("Details[0].AmountDue").rawValue;
sum += xfa.resolveNode("Details[1].AmountDue").rawValue;

}
}
sum;





My formula for 32 - 60 days field

var sum = 0;

var rows = xfa.resolveNodes("Details[0].AmountDue");
var rows = xfa.resolveNodes("Details[1].AmountDue");

for (var i=1; i < xfa.resolveNodes("Details[0].AmountDue").length;i++)
for (var i=1; i < xfa.resolveNodes("Details[1].AmountDue").length;i++)


var item = xfa.resolveNodes("Details[0].AmountDue");
var item = xfa.resolveNodes("Details[1].AmountDue");


{

if (xfa.resolveNode("Details[0].RunningDays").rawValue >= "31" && xfa.resolveNode("Details[0].RunningDays").rawValue <= "61" && xfa.resolveNode("Details[0].RunningDays").rawValue !== "")


if (xfa.resolveNode("Details[1].RunningDays").rawValue >= "31" && xfa.resolveNode("Details[1].RunningDays").rawValue <= "61" && xfa.resolveNode("Details[1].RunningDays").rawValue !== "")
{

sum += xfa.resolveNode("Details[0].AmountDue").rawValue;
sum += xfa.resolveNode("Details[1].AmountDue").rawValue;

}
}
sum;







1 Accepted Solution

Avatar

Correct answer by
Level 10

Hi, 

your script can’t work since you're overwriting existing variables with others. Use the resolveNodes() method with a predicate to filter just those rows, that contain the values in a range.  This can look like this: This sample assumes that the table is named „Table" and contains rows named „Details“. 

 

 

// Summarize all amounts where the runnings day are between 1–30.
var nSum = 0,
	oAmounts = Table.resolveNodes("Details.[Within($.RunningDays, 1, 30) eq 1].AmountDue");
for (var i = 0; i < oAmounts.length; i += 1) {
	nSum += oAmounts.item(i).rawValue;
}
this.rawValue = nSum;

 

 

// Summarize all amounts where the runnings day are between 31–61.
var nSum = 0,
	oAmounts = Table.resolveNodes("Details.[Within($.RunningDays, 31, 61) eq 1].AmountDue");
for (var i = 0; i < oAmounts.length; i += 1) {
	nSum += oAmounts.item(i).rawValue;
}
this.rawValue = nSum;

 

View solution in original post

5 Replies

Avatar

Employee

Hi @Gaddiel_11 ,

find a possible solution here https://acrobat.adobe.com/link/track?uri=urn:aaid:scds:US:87aec40b-f842-41da-ad1e-e3fbed5f0eab

You needed to approach this differently and not use hardcoded indices. See my quick and dirty approach - but that shuld be a good base for you to continue.

Kosta_Prokopiu1_0-1670320698009.png

 

Avatar

Correct answer by
Level 10

Hi, 

your script can’t work since you're overwriting existing variables with others. Use the resolveNodes() method with a predicate to filter just those rows, that contain the values in a range.  This can look like this: This sample assumes that the table is named „Table" and contains rows named „Details“. 

 

 

// Summarize all amounts where the runnings day are between 1–30.
var nSum = 0,
	oAmounts = Table.resolveNodes("Details.[Within($.RunningDays, 1, 30) eq 1].AmountDue");
for (var i = 0; i < oAmounts.length; i += 1) {
	nSum += oAmounts.item(i).rawValue;
}
this.rawValue = nSum;

 

 

// Summarize all amounts where the runnings day are between 31–61.
var nSum = 0,
	oAmounts = Table.resolveNodes("Details.[Within($.RunningDays, 31, 61) eq 1].AmountDue");
for (var i = 0; i < oAmounts.length; i += 1) {
	nSum += oAmounts.item(i).rawValue;
}
this.rawValue = nSum;

 

Avatar

Level 2

Thank you for helping oit Radzmar.

 

I am pretty sure this guidance meant a lot for Gaddiel.