2 Replies Latest reply on Jan 19, 2012 8:16 AM by philmodjunk

    Finding records by comparing two date fields in releated tables

    BrianSmall

      Title

      Finding records by comparing two date fields in releated tables

      Post

      Hi,

       

      Using FM Pro 11 on a Mac,

       

      I'm trying to create a report for each parent record that lists all the test results for the parent that are after a date in the parent record. I have created a report and it has all the test records. I'd guess it's a find that I'm not thinking of in a FM way, I'm pretty sure I could do it via a loop and variables and create a temporary table to report off of, but that doens't seem to be a very optimal FM solution.

       

      Parent Record:

      Name, ID, Start_Date

       

      Test Record

      ID, Test_Date, Result1, Result2 ...

       

      So for each Parent I would want any test records that are after the Start_Date.

       

      Thanks,

       

      Brian

        • 1. Re: Finding records by comparing two date fields in releated tables
          Sorbsbuster

          Well, I was burnt last week by a very similar question... So, first: is the date in the Parent record the same date for all records?  In other words, is it a global field?  If so you can script the find.

          If the date in the parent is potentially different for each you will have to create a calculated field and search on it, I believe.

          I think you mean that you want a list of the Child Records, not a list of the parent records.  Set the calculation in the Test Record table.  An example would be

          IncludeYesNo = Case ( Test_Date < ParentRelationship::StartDate ; "No" ; "Yes" )

          then search in the Test Records table for 'Yes' in that field.

          If you want to find all parent records that has any Test Record after that date, then in the parent table put that related field (from the Test Records) and search for 'Yes' in it.

          • 2. Re: Finding records by comparing two date fields in releated tables
            philmodjunk

            Looks like this relationship:

            Parent::ID = Test::ID

            Define a calculation field, cAfterStartFlag as:

            Parent::Start_Date < Test_Date

            With number specified as the return type.

            This field will return 1 if  test date is after the parent record's start date.

            Now, build your report layout by basing it on the test table, not the Parent and perform a find for all records with a 1 in the cAFterStartFlag.

            This is a find on an unstored calculation field so it will not be very fast for large data sets. You can speed this up by using an auto-enter field option to copy the Start date into a matching field in Test and then referring to it in your calculation. If you choose to use this method, you should also put in place a script that runs from a script trigger on Parent::Test_Date, to update the related Test::Start_Date fields any time the date is changed in the parent record.