Calculating subtotals/page totals

zyrusc13762308

09-11-2018

Hello;

I'm trying to calculate row subtotals and a page total within a repeating table (see below).

subtotals.png

What code should I be using for the Total Value column to total each row and then the Page Total row at the bottom to add up all of the rows in the Total Value column? I know the easiest way is with FormCalc but everything I've tried has duplicated the values from the previous row to the new row when the Add '+' button is clicked.

I appreciate your help.

Accepted Solutions (1)

Accepted Solutions (1)

radzmar

MVP

13-11-2018

Well, the total value per row should be calculated with:

$ = Quantity * Value

And the page total with:

$ = Sum(Row1[*].TotalValue)

Both scripts are in FormCalc not JavaScript!

Answers (14)

Answers (14)

_Bruce_Robertson

MVP

14-11-2018

Hi,

I would use the shortest path possible, I also use the script editor to insert the path for me.

1) Ensure that the Script field of the Script Editor has the focus and the cursor is positioned where you want to insert the object reference.

2) Hold down the Ctrl key and on your form design view, click the object you want to reference. The cursor pointer changes to a 'V' when you when you are over a valid object.

If you did want an absolute path then you can use Shift-Ctrl-Click to insert a reference.

Bruce

zyrusc13762308

17-01-2019

Another quick question regarding this solution;

Is it possible, since this table contains repeating table rows that might extend across multiple pages, to have the footer row of each additional page sub-total all of the calculations for that particular page - not just the total of all calculations going on in the form (if that makes any sense)? So if the footer row is on the final page, it totals everything but if it extends to another page, the footer row for that page only calculates that page's sub-totals.

I appreciate the help.

zyrusc13762308

14-11-2018

Thanks radzmar​ and BR001​! Works perfectly now.

So quick question; when is it ideal to have the rest of the path in the expression? I think I do it absentmindedly but clearly there are times when an absolute path isn't necessary.

Thanks again!!

zyrusc13762308

13-11-2018

Thanks. Yeah, the basic calculation is scripted in FormCalc and works fine. It's when a row has been calculated and then the user needs to add another row to fill in more data in the table, the subtotal from the previous row is then duplicated to the new row (see screenshot below).

subtotals.png

In this example, the subtotal for the first row ($25) has been duplicated down through rows 2 and 3 despite the Quantity and Value columns containing differing values. Even if the values for Quantity and Value are blank, the Total Value column duplicates the value to the new row. Something tells me I need a script at the click event of the "Add Row" (i.e., '+' button) to clear the Total Value column for the next row when it's clicked. I also thought for a second that maybe that event could clear the Quantity or Value columns because apparently, one of those columns triggers the calculation in the Total Value column (Total Value column is blank until a number is entered in both Quantity and Value) but alas, could not solve the problem.

I managed to get the problematic page from the form up on DropBox:

https://www.dropbox.com/s/i6afbcuxuws376x/loanrequest_111318a.pdf?dl=0

Let me know if you don't have access to it.

Thanks again!

zyrusc13762308

13-11-2018

Thanks radzmar​. Here's the layout (if necessary) and the structure (hierarchy) for the form as I have it. I would post the code that I have in the Total Value cell at the moment but I've modified it and switched it around so much trying different things, I'm not sure if I should... LOL

Layout:

subtotals3.png

Hierarchy:

subtotals2.png

I can't post this particular form but let me know if there's anything else that I can supply that you can use to help me figure this out.

I really appreciate everything.

zyrusc13762308

12-11-2018

subieguy2​ thanks for the suggestion. I see this is in AcroForm but it shouldn't be all that hard for me to translate to LiveCycle JavaScript (I've done it before with other code). I appreciate it. I figure it's something that I'm missing that's not clearing the row subtotal when a new row is created. Everything else seems to be working fine, so maybe this will help. I'll keep you posted. Thanks

zyrusc13762308

12-11-2018

try67​ Yes, it is a LiveCycle form. Sorry, I should have put that in the description. It has the ability to add repeating rows to the table in the screenshot (see the plus/minus buttons to the right of each row).

subieguy2

12-11-2018

Something like this might work for you...

var qtyOne = this.getField("Quantity Field Row 1 Name").value;

var qtyTwo = this.getField("Quantity Field Row 2 Name").value;

var qtyThree = this.getField("Quantity Field Row 3 Name").value;

var valOne = this.getField("Value Field Row 1 Name").value;

var valTwo = this.getField("Value Field Row 2 Name").value;

var valThree = this.getField("Value Field Row 3 Name").value;

var totalOne = this.getField("Total Field Row 1 Name").value;

var totalTwo = this.getField("Total Field Row 2 Name").value;

var totalThree = this.getField("Total Field Row 3 Name").value;

if (qtyOne != ""){

totalOne = (qtyOne * valOne);

event.value = totalOne;

}

if (qtyTwo != ""){

totalTwo = (qtyTwo * valTwo);

event.value = totalTwo;

}

if (qtyThree != ""){

totalThree = (qtyThree * valThree);

event.value = totalThree;

}