2 Replies Latest reply on Apr 27, 2015 9:32 AM by RyanMcReynolds

    Constrain Found Set Confusion

    RyanMcReynolds

      Title

      Constrain Found Set Confusion

      Post

      I've been working on this for the better part of the weekend and seem to have made no progress in spite of searches on this forum, and various attempts at understanding through trial and error. 

      I am running a database that is keeping track of invoices, and product inventory. I need to create monthly reports for the sake of reporting sales tax in two states, I would like these reports to display data sorted by invoice with itemized products listed within each invoice, separated by state over the date range.

      I have no problems constraining the invoices/data within the date ranges but cannot constrain the invoices/data with respect to the state they have been sold to. 

      This is where my current attempt is at:

      Set Variable [$$TAX_REGION ; value:Company Dashboard::Tax Region Sort]

      Go to Related Record [Show only related records, Match found set; From table: "Invoice Data; Using layout "Monthly Sales Tax" (Invoice Data); new window]

      Set Field [Invoices::Tax Region; $$TAX_REGION]    

      Constrain Found Set [ ]

      Thank you for your help.

           

       

       

       

       

        • 1. Re: Constrain Found Set Confusion
          philmodjunk

          Assuming that you have this data model:

          Invoices----<Invoice Data>-----Productions

          And you want to pull up all Invoice Data records for the current found set of Invoices and then constrain the found set to only invoice data records for a specific state, your script should be:

          Set Variable [$$TAX_REGION ; value:Company Dashboard::Tax Region Sort]
          Go to Related Record [Show only related records, Match found set; From table: "Invoice Data; Using layout "Monthly Sales Tax" (Invoice Data); new window]
          Enter Find Mode [] ----> clear the pause check box
          Set Field [Invoices::Tax Region; $$TAX_REGION]   
          Constrain Found Set [ ]

          But since you are using Go To Related Records with the "match found set" option, you can also first perform a find on your invoices layout to limit your original found set of invoices to just one state and then the constrain will not be needed, just the Go to Related records step.

          And it is also possible to produce a report where the line items (invoice data) records are grouped by state rather than limiting the records to just one state. Each group can start a new page also. This can be done by adding a sub summary layout part (or two parts, above and below the body) that use Inovices::tax Region as their "when sorted by" field. You would then use GTRR (go to related records) followed by sort records to sort (group) them by tax region.

          It's also possible to use a sub summary layout part in place of the layout body part (When sorted by product ID) to produce a report with one row for each product sold in each tax region.

          For more on summary reports, see: Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Constrain Found Set Confusion
            RyanMcReynolds

            Thank you Phil, worked like a charm.