3 Replies Latest reply on Oct 8, 2015 6:09 AM by erolst

    Invoice Solution Calculation Help

    ChristofferRexen_1

      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

        • 1. Re: Invoice Solution Calculation Help
          erolst

          ChristofferRexen_1 wrote:

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

          […]

          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.

           

          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 only calculate 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.

          • 2. Re: Invoice Solution Calculation Help
            ChristofferRexen_1

            Hi erolst,

             

            Thanks for a quick reply, now it's not quite what i'm looking for.

            Maybe I haven't been detailed enough, for that i'm sorry.

             

            My company is an insurance broker, so we handle policies and invoices based on the policies.

            We create the policy ("Policy" table) first, type in all the details, hereafter create an invoice ("Invoice" table).

            The fields used to create an invoice / invoiceline, comes from 2 fields in the "Policy" table called "Fee" and "Premium".

            When an invoice is created, every invoiceline is created with the Foreign Key ID of the Invoice it is related to.

            Say Invoicenumber PK 1 - Invoiceline PK 1 / Invoice FK 1 - Invoiceline PK 2 / Invoice FK 1 / Invoiceline PK 3 / Invoice FK 1 etc.

            Now invoiceline 1. sets the "Amount" field with the Premium from Policy

            Now I would like the 2. invoiceline to calculate the fee, based on the description called "Fee" in the invoiceline, although it can't since the 2. invoiceline doesn't know what to base the calculation of, in this case, the 1. invoiceline called either "Netpremium" or "Grosspremium" which holds the "Amount" of the Policy Premium.

             

            How do I get this done, in a auto-calculated field?

            How do I get a related record, invoice / invoicelines, to make a calculation?


            On top of all this, I want the tax to be calculated, based on the same principles as above, although - I guess the idea is the same, as soon as you get a hang of it the first time.

             

            I really hope the description helps define what I am searching/looking for.

             

            Regards

            Christoffer

            • 3. Re: Invoice Solution Calculation Help
              erolst

              ChristofferRexen_1 wrote:

              When an invoice is created, every invoiceline is created with the Foreign Key ID of the Invoice it is related to.

              Say Invoicenumber PK 1 - Invoiceline PK 1 / Invoice FK 1 - Invoiceline PK 2 / Invoice FK 1 / Invoiceline PK 3 / Invoice FK 1 etc.

               

              Gee – so that's why they keep calling it a relational database. Brilliant! Need to write that down at once! Anyway …

               

              ChristofferRexen_1 wrote:

               

              Hi erolst,

               

              Thanks for a quick reply, now it's not quite what i'm looking for.

              Actually, it is, you just need to get more creative in fitting together the pieces of the puzzle …

               

              ChristofferRexen_1 wrote:

              Now I would like the 2. invoiceline to calculate the fee, based on the description called "Fee" in the invoiceline, although it can't since the 2. invoiceline doesn't know what to base the calculation of, in this case, the 1. invoiceline called either "Netpremium" or "Grosspremium" which holds the "Amount" of the Policy Premium.

               

              How do I get this done, in a auto-calculated field?

              How do I get a related record, invoice / invoicelines, to make a calculation?

               

              By using the fact that all lines records of an invoice can also be related to each other – since they do share the same invoice.

               

              You could use such a self-join by InvoiceFK and tell every record that is a "Fee" to watch out for (and sum) amounts from records that have a calculated fee – because they are of type "xxxPremium"

               

              Alternatively, use the existing relationship between Invoice and InvoiceLines; perform the sum calculations in the parent invoice and let the "fee" record pull it from there.

               

              This simply mean: make the invoice know what to sum, and pass that on to the Fee record (or have it pull it out).

               

              What you cannot do in all that is use an auto-enter field, because the auto-enter calculation isn't triggered by changes in related records.

               

              My suggestion would be: perform the calculations in the Invoice table if you're interested in seeing the fee figure(s); then either update the value(s) in the Fee record via script triggers that monitor the other records, or create the Fee record and its values on finalizing the invoice via a script. (If there doesn't exist a Fee record yet, create it; then – either way – update that record with the latest values.)

               

              If you're not interested in a static display, you could simply perform the necessary calculations during the finalizing and save yourself a few extra fields.