1 Reply Latest reply on May 31, 2011 2:38 PM by philmodjunk

    What should I do if the match involves both parent and child fields?

    SimonLiu

      Title

      What should I do if the match involves both parent and child fields?

      Post

      I have 2 tables with fields respectively.

      Table {Customer} with:

       <pk_CustomerID>: Serial

      <Status>: Text, "Active"/"Inactive"

      Table {Invoice} with:

      <fk_CustomerID>

      <Shipment>: Text, "Shipped"/"Not Shipped"

      <Amount>: Number

      Tables are related:Customer::pk_CustomerID = Invoice:: fk_CustomerID


      How can I get a total amount of the invoices whose Customer status is "Active" and the invoice shipment status is "Shipped"?

      Screen_shot_2011-05-30_at_12.23.02_AM.png

        • 1. Re: What should I do if the match involves both parent and child fields?
          philmodjunk

          Assuming that you want to use a script...

          Go To layout [Invoices]
          Enter find mode [] //clear pause check box
          Set field [Invoices::Shipment ; "Shipped" ]
          Set FIeld[Customer::Status ; "Active" ]
          Set Error capture [on]
          Perform Find []

          This finds all the records a summary field on this layout would then report the total value of these invoices. The total count of records in your found set will tell the number of invoices. (Not sure what you meant by "total amount of invoices".

          This can also be done without scripting.

          Define a new relationship:

          AnyCustomer::anyfield x Invoices::anyfield

          (AnyCustomer is a new table occurrence of Customer created by selecting Customer and then clicking the button with two green plus signs.)

          Define a summary field in Invoices that either computes the total invoices ( or counts them).

          Place a portal to Invoices on a layout based on AnyCustomer. Specify this portal filter expression:

          Invoices::Shipment = "Shipped" and Customer::Status = "Active"

          make this a one row portal and place your summary field in this portal to display the total.

          (This example minimizes the number of new table occurrences needed, but requires a new layout just for this total. It's also possible to use different table occurrences in relationships so that you can place such a filtered portal on either a Customer or Invoice layout.)