Expand my Community achievements bar.

SOLVED

Calculating subtotals/page totals

Avatar

Level 3

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.

1 Accepted Solution

Avatar

Correct answer by
Level 10

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!

View solution in original post

15 Replies

Avatar

Level 1

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;

}

Avatar

Level 3

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

Avatar

Level 3

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

Avatar

Level 10

How does the structure of you table look like? Please upload you form or post at least some screenshots.

Avatar

Level 3

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.

Avatar

Correct answer by
Level 10

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!

Avatar

Level 3

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!

Avatar

Level 10

Hi,

Have a look at this version

https://sites.google.com/site/livecycledesignercookbooks/home/loanrequest_111318a.updated.pdf?attred...

The row total calculation expression needs to be "$ = Quantity * Value" ... which is what radzmar suggested.

Regards

Bruce

Avatar

Level 3

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!!

Avatar

Level 10

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

Avatar

Level 3

Thanks for the insight, BR001​.

I appreciate all your help.

Avatar

Level 3

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.