Calculate page subtotals in repeating dynamic subform

[ebb01983]

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:

I guess I will need some pagination settings as well,

all help hugely appreciated!

[Former Community Member]

If you post the form I will have a look.

Paul

[ebb01983]

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.

[Former Community Member]

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

[ebb01983]

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?

[Former Community Member]

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

[ebb01983]

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.

[Former Community Member · Accepted solution]

Here you go ....

Paul

[ebb01983]

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

[Former Community Member]

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

paul

[ebb01983]

Perfect Paul !!

Thank you very much for this,

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

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

This part here?

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

[Former Community Member]

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

[ebb01983]

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.

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

[Former Community Member]

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

[ebb01983]

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?

[Former Community Member]

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

Paul

[ebb01983]

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)

[Former Community Member]

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

paul

[ebb01983]

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:(

[Former Community Member]

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

[ebb01983]

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