3 Replies Latest reply on Jul 30, 2009 9:20 AM by etripoli

    Calculating with percentages

    Socio.t

      Title

      Calculating with percentages

      Post

      Hello everyone,

       

      I have a challenge that goes far away from my knowledge in FMP, so I really need your help. I'm being a bit of pain in the last weeks with my posts.

       

      I have this enpenses and invoice management database.

       

      In a part of this database I'm keeping the record of the Suppliers Invoices. Normally I would like to be able to click, pay a supplier and it would allow me to select the invoice I want to pay and pay it.

       

      But I want to have the flexibility to pay to that supplier whatever amount I decide, either it corresponds to the total of the invoices or not.

       

      So the idea is that I select a Supplier, doing this it should list all the unpaid invoices from this supplier.

      Then I should be able to click on a button that says "Pay supplier" and it would ask for the amount.

      Once I set the amount, as far as it isn't bigger then the total of the invoices that I have to pay, it would start setting invoices as payed, starting from the oldest.

      But in case that one of the invoices would be not tottaly covered it would subtract the an amount that is still available from that invoice and show the value of the invoice that remains to pay.

       

      I know that it is kind of complex, but please let me know if there is a way of doing it, and please don't hesitate in asking if you need some more information to help me.

       

      Thank you very much for the help.

       

      Best regards,

       

      Luís

        • 1. Re: Calculating with percentages
          etripoli
            

          I'm the kind of person who likes to keep track of changes, so I would say that you need to create a Payment table, where you show how much you applied to each invoice.  Then, with that table related to the Invoices table, you could show the remainder per invoice by calculation.

           

          Invoices

          InvoiceID

          InvoiceNumber

          VendorID

          InvoiceAmount

          cn_ remaining = InvoiceAmount - Sum ( Payments:: PaymentAmount )

           

          Payments

          InvoiceID

          VendorID

          PaymentAmount

          PaymentDate

          • 2. Re: Calculating with percentages
            Socio.t
              

            Hello etripoli,

             

            This is good idea you've got here. But I'm missing one thing there.

             

            As I said previously I want to say the amount I payed to a vendor and it will split this amount through the invoices automatically.

             

            As an example:

            I have two invoices to pay to a vendor. Each one is 750.

            If I set the payment amount as 1000 I want it to discount this 1000 from both invoices, starting from the oldest. This way I should see that I'm still have 500 to pay from the last invoice I received.

             

            It is a big challenge for me to get this working, but I know that there should be a way with some calculations.

             

            Thank you very much for all the help.

            • 3. Re: Calculating with percentages
              etripoli
                

              In this case, a script is what you want to use.  You'll need to make sure the structure is in place first.  Add the table and fields mentioned before, then make a relationship between the invoices table, and the payments table, by Invoice ID.  Create a global field in the Invoice table, (g_payment) to store the beginning payment amount.  Now for the script, a very basic version:

               

              Show Custom Dialog ["Enter Payment Amount"; "Please enter the payment amount for this vendor."; Invoice::g_payment]

              If [Get ( Lastmessagechoice ) = 2]

                Exit Script[]

              End If

              Set Variable [$pmt_amt; Value:Invoice::g_payment]

              Set Variable [$invoice_id; Value:Invoice::InvoiceID]

              Loop

                If [Invoice::cn_remaining > 0]

                  Set Variable [$paid_amt; Value:Min (  $pmt_amt; Invoice::cn_remaining )]

                  Set Variable [$pmt_amt; Value:$pmt_amt - $paid_amt]

                  Go to Layout ["Payments" (payments)]

                  New Record/Request

                  Set Field [Payments::InvoiceID; $InvoiceID]

                  Set Field [Payments:: PaymentAmount; $paid_amt]

                  Set Field [Payments:: PaymentDate; get ( currentdate )]

                  Commit Records/Requests [No dialog]

                  Go to Layout [original layout]

                End If

                Go to Record/Request/Page [Next; Exit after last]

                Exit Loop If [$pmt_amt = 0]

                Set Variable [$invoice_id; Value:Invoice::InvoiceID]

              End Loop