25 Replies Latest reply on Jun 30, 2009 7:03 AM by MarcelBoucher

# Calculate page subtotals in repeating dynamic subform

Hello,

I have a repeating subform that can span many pages, at the bottom of the list I have a grand total.......thats great

I would like to add a subtotal for each page of the repeating subform. How can I do this?

I have the following code for my grand total:

// Acquire all "Amount" Fields there are inside a subform

var

flds = xfa.form.form1.resolveNodes("\$form.form1.table1_rows[*].tvos_t1");

// Loop over list of fields and add up values

var

sum = 0;

for

(var i=0;i<flds.length;i++)

{

// Test fields value to make sure it is a number before

// including it in the Sum

if(!isNaN(flds.item(i).rawValue))

sum

+= flds.item(i).rawValue;

}

// Ensure that "sum" is the last value run in the script

sum;

I guess I will need some pagination settings as well,

all help hugely appreciated!

• ###### 1. Re: Calculate page subtotals in repeating dynamic subform

If you post the form I will have a look.

Paul

• ###### 2. Re: Calculate page subtotals in repeating dynamic subform

Hello Paul,

I am still working on the form you helped me with earlier in the week, except now i need to learn a new concept. Anyway I put together a new file too make it easier for you.

the holiday_payments.pdf file connects to the holiday_payments.xls file (please rename to holiday_payments.txt to holiday_payments.xls)

When you get the form working you will see the subform spans across 3 pages, I would like a subtotal shown on the bottom of page 1 and 2 and a grand total on the bottom page 3. I hope it is fairly straight forward:)

thanks for your help again really appreciate it.

• ###### 3. Re: Calculate page subtotals in repeating dynamic subform

I put the page total fields on the master pages and resized the content Area accordingly. The code is on the Doc Ready event of the MasterPage fields. I comment the 1st one. This will work for any number of pages ...not just 3.

Let me know what you think

Paul

• ###### 4. Re: Calculate page subtotals in repeating dynamic subform

Hello Paul & Anyone else who is looking for same problem or who can help!!

This is very good, but 1 slight problem. I dont know if there is anyway to get around this.

The last page also has a subtotal and as you can see its way below the rest of the page - even the grand total, which does not make sense since the subtotal should always be before the grand total  !!

My idea behind this was actually that the subtotal should be on every page just below the last "AmountPaid" field, and the grand total should be on the last page just below the very last AmountPaid Field (the last page should have no subtotal - just a grand total, but i dont mind if both are on last page)

So i think their has to be some kind of "Flowed" solution rather then to put the total on the bottom of the page.

So example

------------------------------------

>>PAGE 1 START<<

1

2

3

4

5

----

15 PAGE SUBTOTAL

>>PAGE 1 FINISH<<

------------------------------------

------------------------------------

>>PAGE 2 START<<

2

4

6

8

10

----

30 PAGE SUBTOTAL

>>PAGE 2 FINISH<<

------------------------------------

------------------------------------

>>PAGE 3 START<<

2

5

----

7 PAGE SUBTOTAL

52 GRAND TOTAL

>>PAGE 3 FINISH<<

------------------------------------

or if it can be designed without the subtotal on the last page and only a grand total this would be even better. I simply have no clue about this! can any1 help?

• ###### 5. Re: Calculate page subtotals in repeating dynamic subform

If we hide the subtotal on the last page will this give you want you want? You will not get a subtotal in the flowable part unless we modify your form structure to have a footer.

Paul

• ###### 6. Re: Calculate page subtotals in repeating dynamic subform

Honestly I would like it in a flowed format and change the sturcture to include a footer if its not too much trouble to build, im sure ive seen a few people asking about this example on the internet but nobody seems to know how to do it. Otherwise if it will take long too make then if you show me the way to make the last field invisible that will be ok too.

• ###### 7. Re: Calculate page subtotals in repeating dynamic subform

Here you go ....

Paul

• ###### 8. Re: Calculate page subtotals in repeating dynamic subform

Its ok whenever you get a chance, would be nice to get a good example

• ###### 9. Re: Calculate page subtotals in repeating dynamic subform

Just did it an updated the message ...check it again.

paul

• ###### 10. Re: Calculate page subtotals in repeating dynamic subform

Perfect Paul !!

Thank you very much for this,

I see you added a new subform and the following code:

var

allFieldsonPage = xfa.layout.pageContent(xfa.layout.page(this) - 1, "field", false);

for

(i=0; i<allFieldsonPage.length;i++){

currentField

= allFieldsonPage.item(i);

if (currentField.name == "AmountPaid"){

this.rawValue

= this.rawValue + currentField.rawValue;

}

}

How does the subform "understand" that it should show on every page instead of at the end of the previous subform?

This part here?

var allFieldsonPage = xfa.layout.pageContent(xfa.layout.page(this) - 1, "field", false);

I cant see any other code additions, am i missing somewhere?

• ###### 11. Re: Calculate page subtotals in repeating dynamic subform

The code is what does the calculation of the subTotal. The 1st line returns all of the Field objects on the page, then I loop through all objects add add up the ones called AmountPaid.

The controlling of the placement of the Header and Footer is not done through script. Click on the sf! subform and have a look at the Pagination properties.. I set the overflow leader and trailer to show the appropriate subform.

Paul

• ###### 12. Re: Calculate page subtotals in repeating dynamic subform

Hello Paul,

I have 1 problem with my code for initialise in form1. If there is no data in my database i get the following error:

GeneralError: Operation failed.
XFAObject.first:12:XFA:form1[0]:initialize
first operation failed.  There are no records in the datasource.

var oDB = xfa.sourceSet.nodes.item(nIndex);

oDB.open();

oDB.first();

I think the problem lies here on line.

Sometimes my form will contain no data, so this is a problem for me because a form I have designed has 3 repeating subforms, if the 1st subform fails, the subsequent subforms do not work.

How can I bypass the first operation without breaking the form, if i remove it i get the following error:

GeneralError: Operation failed.
XFAObject.item:30:XFA:form1[0]:initialize
Index value is out of bounds

• ###### 13. Re: Calculate page subtotals in repeating dynamic subform

I agree ..I think it is that line as well. You wil have to get a count of records to see if there are any in the DB.  I think that there is a SQL command to get the number of records. Do not know if your DB will support that level of SQL though.

Paul

• ###### 14. Re: Calculate page subtotals in repeating dynamic subform

I think I have found a solution:

var

oDB = xfa.sourceSet.nodes.item(nIndex);

oDB.open();

if

(oDB.isEOF())

{

oDB.close();

}

else

{

oDB.first();

...............rest of code

...............

............

.....

.......

oDB.close();

}

It works for me, but can anyone foresee a problem with this implementation?

• ###### 15. Re: Calculate page subtotals in repeating dynamic subform

Try it with one record .....if it works then it is good to go.

Paul

• ###### 16. Re: Calculate page subtotals in repeating dynamic subform

Hello Paul,

Than you very much for your help on this, I thought I had it all complete but my boss wants a new feature added that Im not sure about. I hope you can help once again.

At the moment the document shows a subtotal on each page and a grand total on the last page, I have been asked into implement a carry-forward value, see example below for what i mean:

------------------------------------

>>PAGE 1 START<<

Amount Paid

1

2

3

4

5

----

15 PAGE SUBTOTAL

>>PAGE 1 FINISH<<

------------------------------------

------------------------------------

>>PAGE 2 START<<

Amount Paid

15 (CARRY FORWARD FROM PREVIOUS PAGE)

2

4

6

8

10

----

45 PAGE SUBTOTAL (INCLUDES THE CARRYFORWARD VALUE)

>>PAGE 2 FINISH<<

------------------------------------

------------------------------------

>>PAGE 3 START<<

Amount Paid

45 (CARRY FORWARD FROM PREVIOUS PAGE)

2

5

----

52 PAGE SUBTOTAL (INCLUDES THE CARRYFORWARD VALUE)

52 GRAND TOTAL

>>PAGE 3 FINISH<<

------------------------------------

I have used your form and modified it, please see attached. So far I have added a new subform (for the carry-forward value). I can carry forward the previous page value onto a new page, but i also need help with 3 other things:

1. the first page should "hide" the carry forward value because it will always be "0". (i assume we need code to say if page is not overflowed then hide subform - I dont know how to do this)

2. I need to modify the "footer" subform so that the calculation includes the carryforward value on the page. (as in example above)

3. I also need the "header" subform to be displayed on each page. at the moment because I added a new subform It wont show.

You can look at my form - ive modifed your form, or maybe even use your own form. (the excel file is still attached to one of my previous posts - rename txt to xls)

• ###### 17. Re: Calculate page subtotals in repeating dynamic subform

Here you go ....the code is on the Initialize event of the carry forward field.

paul

• ###### 18. Re: Calculate page subtotals in repeating dynamic subform

Hi Paul thanks for your help again,

the calculation for the "Footer" subform is still incorrect. It totals incorrectly, it should add all fields in AmountPaid + Carryforward field. For example page two of the document should have the page total value of 8218, and page 3 of the document should have a page total of 9472.

Also I need the Header on each page. Appreciate if you can help me on these two matters I have been trying for several hours now:(

• ###### 19. Re: Calculate page subtotals in repeating dynamic subform

But the footer is a page total ....if you want to add in the carry forward field simply add it to the total. I added script to do this in version 4.

Paul

• ###### 20. Re: Calculate page subtotals in repeating dynamic subform

meh i hate being a nuissance! but we still having a problem:, page 3 is wrong

• ###### 21. Re: Calculate page subtotals in repeating dynamic subform

Ok i have figured out the calculations and I am getting exactly what i expect now.

I still have 1 remaining problem to (it is do with pagination settings)............I need to have each page displayed in the following way.

subform carryforward

subform sf1

subform footer

subform totalsf1

at the moment only the first page is correct, each following page only shows

subform carryforward

subform sf1

subform footer

subform totalsf1

can someone help me with pagination settings, ive tried every combo i can think of but cant get the right order. form is attached below

• ###### 22. Re: Calculate page subtotals in repeating dynamic subform

solved, spent way too long on this and it was such a small thing!!!

I have put the carryforward subform inside the header subform, and set the header subform as the overflow leader. I also made the carryforward subform hidden if on the first page! Thanks for your help on this episode Paul.

• ###### 25. Re: Calculate page subtotals in repeating dynamic subform

Iuiiuuis