7 Replies Latest reply on Jan 20, 2014 10:44 AM by philmodjunk

    Future Payment

    FilipePimentel

      Title

      Future Payment

      Post

           Good day, 

           I am setting up a db and need to set up a field (c_Balance) to show only payment made, no future payments...

           I.e:

           I have two tables Plots and Payment (related). 

           On the Plot, I have fields amount (price for the plot) and a cBalance (Price - s_sumopayment). 

           I need to forecast payment, ie: When we sell the land, we now that the buyer will pay a certain amount everymonth...so we enter this amount on the payment field and set the date for the future. 

           How can I set the cBalance to show the balance only for the payment made? 

           Or do I need to change my structure? 

           Thanks

           FP

        • 1. Re: Future Payment
          philmodjunk

               Presumably, s_sumopayment is a field of type summary. Am I correct?

               the value returned by a summary field depends on the context in which it is referenced. If you are on a layout based on Payment, simply omitting the payment records with dates that are still in the future will return the correct current balance.

               If you refer to cBalance from Plots, you'll get a total based on all related payment records. To get just a subTotal of all "to date" payment records would require either a relationship that excludes future payments or a filtered portal where the portal filter excludes future payments.

               Plots::__pkPlotID = Payments::_fkPlotID AND
               Plots::cToday > Payments::PaymentDate

               is a relationship that will omit future payments. cToday would be an unstored calculation field with "date" as its return type using this expresson: Get ( CurrentDate ).

               A similar expression could be used in a one row filtered portal to display the same value.

          • 2. Re: Future Payment
            FilipePimentel

                 Great works like a charm...

            • 3. Re: Future Payment
              FilipePimentel

                   Ok. Just spoke to the FC and she wants some changes...I am a little stuck. 

                   She wants the feature of we discussed above.

                   However, she also wants a feature where she will only confirm the payment upon a checkbox tick (paid). What she means is she does not want simply the payment to go through as paid if it was not in fact paid. (Hope you can understand what I mean).

                    So both features should work together. I mean all future payments will be entered to ensure they are in the system, but they should only reflect in the balance if they were marked paid - even if the date for payment has not arrived or has past.

                   Thanks for any help

                   FP 

              • 4. Re: Future Payment
                FilipePimentel

                     Hello Filemaker gurus....

                     Any ideas on my question above: How can I a feature where I will only confirm the payment upon a checkbox tick (paid), but at the same time, it allows me to enter future payments on the payment fields and not have these payments being added up to the cbalance.

                      

                I am trying to ensure that the payment does not go through as paid if it was not in fact paid. (Hope you can understand what I mean). 

                     So both features should work together. I mean all future payments will be entered to ensure they are in the system, but they should only reflect in the balance if they were marked paid - even if the date for payment has past/arrived.

                     Thanks for any help

                • 5. Re: Future Payment
                  philmodjunk

                       Just as you can include the date to exclude records with future dates, you can use the value of the check box field to include only items that have actually been paid. Since I wouldn't expect you to mark a future dated record as "paid", you may not need the original date based relationship you may only need to match data by whether or not it has been marked "paid".

                       Plots::__pkPlotID = Payments::_fkPlotID AND
                       Plots::cPaid > Payments::CheckboxField

                       cPaid would be defined to always return the same value you set up your check box value list to enter when the check box is clicked. Example:  If the check box enters the text "Paid" when selected, cPaid would have "Paid" as its sole calculation term with Text selected as its result type.

                  • 6. Re: Future Payment
                    FilipePimentel

                         PhilModJunk, 

                          

                         Thanks for the reply...Can i just confirm that the relationship

                    Plots::cPaid > Payments::CheckboxField is not

                    Because when I use the > nothing happen with the cbalance...but when I use < it works fine.

                         Regards,

                    • 7. Re: Future Payment
                      philmodjunk

                           Sorry, this operator should be =. I copied and pasted the original calculation and edited it to produce this example. I missed changing that detail.