Summary of values in a repeating tables (totals, etc...)

[TimothyP]

Hi,

For testing purposes I have a very simple database in Access.

Tables:

INVOICE
INVOICELINE
CUSTOMER

Where each INVOICE has one or more INVOICELINES
and each INVOICE has exactly one Customer.

The INVOICE line has the following fields
string Product
double UnitPrice
int Count
double Tax

The database itself does not contain totals.

I managed to create a form in order to create a new INVOICE with
INVOICELINES. It is correctly saved to the database.

(Using a Repeating Table (master section) and a repeating table (detail
section)
which seems to contain a repeating table with the invoice lines

But I want to display the totals on my form.

I manged to display the total price without taxes and with taxes for
every INVOICELINE using an Expression field

I added a footer to the repeating table for the INVOICELINES
There I would like have the following 3 totals

The totals of the unitprice (this works fine with an expression field)
The totals of the price (unitprice * count ... for each INVOICELINE)
The final total for all the INVOICELINES including taxes

These last two I can't seem to do.
I was expecting I'd be able to use the values of the
expression textboxes to count the sum, but that doesn't seem to work.

How can I solve this without adding fields to my database.
I played around with adding text (element) fields but didn't work either.


Thank you.

 

[Franck Dauché]

Hi Timothy,

Have you tried by code, but looping through your table and adding values?

Regards,

Franck Dauché

 

[TimothyP]

I was hoping it could be done without code,
we where thinking of deploying infopath to our end-users,
to allow them to create their own submition forms
but their skills do not include coding.


It would have been better if the expression fields had a identifier
which could be used in another expression field.

= sum(@myExpresion)

 

[Franck Dauché]

Hi Timothy,

In that case, you may really looked into managed code. This way, some of
the business logic can be compiled into an assembly that is embedded into
your xsn file and that your users can't modify, even if they go in design
mode.

Regards,

Franck Dauché