2 Replies Latest reply on Sep 24, 2012 2:43 PM by brentjohn

    Running Outstanding Balance



      Running Outstanding Balance



           I have spent the last 3 days searching the FM Forum (as well as other FM forums) for a solution to me problem:

           I have 2 tables –


           Table #1: ProjectSetUp




           Field:PS_Amount(cal: ps_labor +ps_ material)

           Plus several other fields not relevant to my issue.


           Table #2: PaymentRec

           Field:PR_Name(text) drop down list with names from table #1


           Field:PR_Prev_Bal(look up PS_Amount {from table #1 through a relation based on PS_Name = PR_Name})

           Field:PR_Bal(cal: pr_prev_bal – pr_paid)

           Plus several other fields not relevant to my issue.


           What I want to do is create a running outstanding balance of PS_Amount for future payments.



        • 1. Re: Running Outstanding Balance

               IT looks like you have this relationship in place. Do you?

               ProjectSetup::PS_Name = PaymentRec::PR_Name

               While I do not recommend using a Name field as the match field in a relationship (a serial number ifled in ProjectSetup matching to a number filed in PaymentRec is a much better option), I'll go with it for now.

               Define PR_pkPRID as an auto-entered serial number field.

               In Manage | Database | relationships, make a new table occurrence of PaymentRec by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box as Prev_PAYMENTSREC.

               We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

               Add it to your relationships like this:

               PaymnentsRec::PR_Name = Prev_PAYMENTSREC::PR_Name AND
               PaymnentsRec::PR_pkPRID ≠ Prev_PAYMENTSREC::PR_pkPRID

               This relationship matches to every record in PaymentsRec with the same value in PR_Name EXCEPT for the current record.

               PR_Prev_Bal can now be defined as:

               ProjectSetup::Amount - Sum ( Prev_PAYMENTSREC::PR_Paid )

               The problems with using a name as a match field in a relationship are:

               Names are not necessarily unique

               Names are subject to change due to client/management directives and also to correct possible data entry errors. If you create a new record in ProjectSetup, log several related records in PaymentsRec and then have to change the project name, your related records in PaymentRec will disappear if you don't carefully update them to the same new name as you do for the matching record in ProjectSetup.

          • 2. Re: Running Outstanding Balance


                 Once agian, you are da man!!!!

                 And yes, I agree about using serail numbers - I will go through my database and change it.