2 Replies Latest reply on Mar 7, 2014 8:28 PM by GaryJohansen

    Constraining Related Records

    GaryJohansen

      Title

      Constraining Related Records

      Post

           I just got Filemaker Pro 13 Adv.  

           I’ve created a layout showing customer information, the customer ID is used to create a relation to invoices. Invoices are kept in a current invoice file (for the current year) and archive file.

           What I’m trying to do is run a report that gives me YTD sales total and transaction counts by customer, all previous sales total and transaction counts (no problem so far) but also the counts and totals for a date range of the previous year. This way they can look at this years YTD and compare it to last years YTD for each customer.

           My problem is as I loop through the file I copy the current YTD and total Archive data into the appropriate fields, and after the total Archive data is copied into the report I want to constrain the related Archive records using the Archive invoice date as a range.

           Ideally I'd like to use a Custom Dialog Box to accept the date range into a global variable.at the beginning of the routine.

           Any suggestions?

           Thanx in advance.

        • 1. Re: Constraining Related Records
          philmodjunk
               

                    as I loop through the file I copy the current YTD and total Archive data into the appropriate fields,

               I don't see any need to copy any data via a looping script. That both creates redundant copies of your data and is slow.

               You can match to record via a date range without any need to copy data and you can use that relationship to pull up aggregate values such as counts and totals.

               To match by a date range you use one of two relationship methods:

               Table1::DateStart > Table2::Date AND
               Table1::DateEnd < Table2::date

               or you can use:

               Table1::DateList = Table2::Date

               where DateList is a return separated list of Dates from DateStart toe DateEnd. Both DateStart and DateEnd can be global fields and you can use a custom dialog to ask the user for these two dates.

          • 2. Re: Constraining Related Records
            GaryJohansen

                 Thank you, that solved my problem and I'm percolating along again.