6 Replies Latest reply on Nov 25, 2013 12:10 PM by IlseVerhaert

    Calculating money owed by client

    IlseVerhaert

      Title

      Calculating money owed by client

      Post

           I have a rather complicated question, and I hope you can help me out. Let me briefly outline the context.

      Table 1:
           - Field: ClientID (text)
           - Field: Date this client started to be a client (Date)
           - Field: Payment plan (value list: "$30 - $15"; "$10-$10"; "0 - $10"). This payment plan works as the following: The first amount is an initial payment, when becoming client. The second payment is the monthly payment which the client needs to pay in the following months (indefinitely). e.g. $30 -$15 means an initial payment of $30, and thereafter $15 each month
            

      Table 2:
           - Field: ClientID (text) (yet related with table1)
           - Field: Monthly invoice: How much should the client pay that month. This will depend on the start date and the payment plan. So, if Client X started this month and signed up for the payment plan $30-$15, this field should display $30. If Client X is client for already several months, this field should display $15. => how to calculate this?
           - Field: How much money should the client have paid since his start until the current date. This should summarize his initial payment, and the following monthly payments. => how to calculate this?

           I guess I should combine several functions for these calculations, such as the 'case-function'. Could someone help me out please? Thank you so much!

            

            

            

        • 1. Re: Calculating money owed by client
          philmodjunk

               You need a "billing date" date field in Table 2 that records a date from the month for which they will be billed.

               Set up an auto-enter calculation for MonthlyInvoice:

               If ( Month ( BillingDate ) = Month ( Table1::DateStarted ) And Year ( BillingDate ) = Year ( Table1::DateStarted )  ;
                   GetAsNumber ( RightWords ( Table1::PaymentPlan ; 1 ) ) ; //initial payment
                   GetAsNumber ( LeftWords ( Table1::PaymentPlan ; 1 ) ) // monthly payment
                  ) // IF

               In many contexts, the second field can be a summary field that totals MontlyInvoice. This will work if the current found set is all the records in Table2 for a single client, or in a sub summary layout part when the records are sorted by client or when referenced via the relationship from Table 1. A sum function can also be used in a calculation field defined in Table 1 to compute the same total.

          • 2. Re: Calculating money owed by client
            IlseVerhaert

                 Waw you really helped me out for the monthly payments, thank you very much!

                 How should I solve the fact that the second field (payments year to date), is not the sum of all totals of monthlyInvoice per client (could be client since 2 years)?

                  

            • 3. Re: Calculating money owed by client
              philmodjunk

                   What determines what set of records that you do want to use to produce the total?

                   Keep in mind that a summary field returns a total from a) a found set of records, b)  a related set of records,  c) a filtered, related set of records or d) a sorted sub group of records, depending on how you design the context in which it is used. It's a matter of how you design your layout and how you set up the "group" of records from which the total is computed.

              • 4. Re: Calculating money owed by client
                IlseVerhaert

                What determines what set of records that you do want to use to produce the total?
                     ==> I want to get a sum of the monthly invoiced payments of the current year. 

                     I now have an additional question, which makes the situation a little bit more complicated:

                     - The Client needs to pay the fee per product he purchases from us, therefore I added the following (and it works great, thank you)

                If ( Month ( BillingDate ) = Month ( Table1::DateStarted ) And Year ( BillingDate ) = Year ( Table1::DateStarted )  ;
                    GetAsNumber ( RightWords ( Table1::PaymentPlan ; 1 ) ) * Table1::TotalAmountFilters ; //initial payment
                    GetAsNumber ( LeftWords ( Table1::PaymentPlan ; 1 ) ) * Table1::TotalAmountFilters // monthly payment
                   ) // IF

                - If a client makes an additional order for products (always same payment plan per client), he has two choices: 
                => Or catch up with his payments (e.g. if he purchases 10 products more after 2 months and opted initially for the $30-$15 payment, he will pay $450 as initial payment and in the following months $150)
                => Or - in the same payment case - pay $300 as initial payment and $150 in the following months.

                     In order to add this to the original calculation and thus get the complete monthly invoice, I assume I should create an additional field in Table:1 that states e.g. 'catch up' or 'no catch up'. But how would the formula then look like?

                      

                     Thank you!!

                • 5. Re: Calculating money owed by client
                  philmodjunk

                       I would be inclined to put the initial payment and monthly payments in separate fields of table 1, BTW, seems a simpler way to go and the fields can still be populated from the choice you make with your value list.

                       Why would anyone select the second option when the first is $150 dollars more than the second? What do you do with that extra monthly fee x 10 products amount? Is it an amount that was already paid or an amount that is to be paid next month?

                       Either way, this doesn't seem to be a part of what is needed to compute the total owed to date by the customer and you can get that total form a summary field if your found set is limited to a single client for the single year--something that can be done with a scripted find. Or you can define a relationship that matches to all such records by client and by year to get the correct group of related records.

                       What you now seem to be asking about is a system for recording how the amount owed would be paid...

                  • 6. Re: Calculating money owed by client
                    IlseVerhaert

                         Previously, clients had to opt to catch up with there payments if they did a second order. From now on; this won't be necessary anymore. However, I still need to program it so I can have two different 'expected monthly invoices'. So, this was the formula that is absolutely correct for the clients that catched up with their payments, thank you for this:

                    If ( Month ( BillingDate ) = Month ( Table1::DateStarted ) And Year ( BillingDate ) = Year ( Table1::DateStarted )  ;
                        GetAsNumber ( RightWords ( Table1::PaymentPlan ; 1 ) ) * Table1::TotalAmountFilters ; //initial payment
                        GetAsNumber ( LeftWords ( Table1::PaymentPlan ; 1 ) ) * Table1::TotalAmountFilters // monthly payment
                       ) // IF

                         But the new clients won't catch up anymore with the payments. I guess I should then divide the 'TotalAmountFilters', into 'AmountFiltersDelivery1', 'AmountFiltersDelivery2', etc? I still will need one formula, but then add an 'If' function for 'Catch up' or 'NotCatchUp'?

                          

                         Thank you!!