Hello everyone,

Please, take a look at the attached picture.

It's a portal for my Invoicelines, for my Invoice Solution for an insurance company, although I've hit a wall.

There is 3 fields in the portal.

Line - Description - Amount

Now, I want the "Amount" (number field), to auto-calculate a bunch of different things, based on what is standing in the "Description" field.

Say "Description" says "Netpremium" or "Grosspremium" (Line 1, "Nettopræmie") the "Amount" field, should only paste in the pure cost for the insurance policy.

Now, my company would like to get a 15% "Fee" (Line 2, "Honorar") of this "Netpremium" or "Grosspremium", so I would like the "Amount" field in the portal to calculate a 15% fee based on line 1, "Netpremium" / "Nettopræmie".

The calculation for calculating the Fee is on place, although I can't seem to get the "Amount" field, to calculate correctly since it's a new invoiceline / new record, with a blank "Amount" field.

I can't seem to find, a solution on how to do this.. Any help please?

Now, since the solution needs to be waterproof, I would love if possible, so regardless of the "Invoiceline" (1, 2, 3), it would calculate correct anyhow - say a user does not type "Netpremium" or "Grosspremium" in line 1, but line 4 or somewhere complete else.

Is it possible?

Is there another or better Invoice / Invoiceline / Invoice calculation solution?

Please feel free to yell, if you need any more help, I'm in desperate need!

Best regards,

Christoffer

There are several approaches:

Create a calculation field in the InvoiceLines table, say, cAmountPremium, defined as

Case ( "Description" = "Netpremium" or Description = "Grosspremium" ; amount )

In the Invoice table, create a calc field defined as

Sum ( InvoiceLines::cAmountPremium ) * 1,15

Or do it the other way around:

InvoiceLines table::cAmountPremium =

Case ( "Description" = "Netpremium" or Description = "Grosspremium" ; amount * 1,15 ), and

Invoice::cSumPremium =

Sum ( InvoiceLines::cAmountPremium )

or create a field to

onlycalculate the fee:InvoiceLines table::cPremiumFee =

Case ( "Description" = "Netpremium" or Description = "Grosspremium" ; amount * 0,15 )

the Sum() that field in Invoices and add it to the Sum() of the regular amount.

Depends on what figures you need to display on an invoice and/or in the individual lines.

Note that while all these solutions should work nicely (wouldn't be much of a solution otherwise … ;-), they have the drawback that they use unstored calculations which must be re-evaluated every time they are displayed.

So you may want to use that same calculation within a script to update a regular number field whenever you add/edit/delete InvoiceLine records.