2 Replies Latest reply on Sep 4, 2012 12:02 PM by MichaelLane

    Filtering unpaid invoices

    MichaelLane

      Title

      Filtering unpaid invoices

      Post

      Greetings

      My database has 2 tables - [Main] and [Jobs] linked by the field [Client_ID].

      In the [Jobs] table there are fields [Gross_Total] and [Date_Paid]. What I would like to do is have a field [Balance] in the [Main] table which calculates all the Gross_Totals for a specific client's jobs where the field [Date_Paid] is empty.

      As always, any assistance would be much appreciated.

        • 1. Re: Filtering unpaid invoices
          philmodjunk

          What I would like to do is have a field [Balance] in the [Main] table which calculates all the Gross_Totals for a specific client's jobs where the field [Date_Paid] is empty

          There are multiple ways to get that unpaid total. Here are two:

          Define an cUnpaid_Total field in jobs as:

          If ( Not Date_Paid ; Gross_Total )

          date_Paid must be a field of type date. If Date_Paid is empty, this field returns a value, if it has a date, this field will be empty.

          Then a summary field defined in Jobs can compute the total of cUnpaid_Total or a calculation field in Main can use Sum ( Jobs::cUnpaid_Total ) to compute the same total. Either field, the calculation from Main or the Summary from Jobs can be placed on your layout for Main and they will show the correct total

          • 2. Re: Filtering unpaid invoices
            MichaelLane

            Once again I am amazed by the speed and accuracy of Phil's answer.

            Many thanks.