2 Replies Latest reply on Mar 12, 2013 4:23 AM by atwharton

    Find the last date based upon another field

    atwharton

      Title

      Find the last date based upon another field

      Post

           Hi,

            

           Just wondering if anyone can help. I have a table (see example below) where inspections are completed on one item several times. I wish to just show the last inspection in my layout. I know its something to do with performing a find but i cannot get the formula correct.

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
                          INSP ID                     EQP ID                     EX DATE                     NEXT EXAM DATE (Calculation of EX DAte + 30 days)
                          1                     1                     24/06/12                     24/07/12
                          2                     1                     23/07/12                     23/08/12
                          3                     1                     20/08/12                     20/09/12
                          4                     2                     15/07/12                     15/08/12
                          5                     2                     10/08/12                     10/09/12

           I want the find to to show the following result:-

            

                                                                                                                                                                                                                                                                                   
                          INSP ID                     EQP ID                     EX DATE                     NEXT EXAM DATE (Calculation of EX DAte + 30 days)
                          3                     1                     20/08/12                     20/09/12
                          5                     2                     10/08/12                     10/09/12

            

            

           Plz help
      smiley

        • 1. Re: Find the last date based upon another field
          philmodjunk

               It's a bit of a challenge when you want to find a record without specifiying a specific value. Since you don't know what date was the "last date" and that "last date" may differ for each item of equipment, you end up performing a find and then sorting the found set to put the most recent date first or last. that seems rather cumbersome here.

               But if you use two tables, one for equipment and one for inspections with a relationshp that links them by EQP ID, you can set up a relationship that automatically shows you the most recent inspection data for each record in your equipment table.

               Set up this relationship:

               Equipment----<Inspections

               Equipment::EQP ID = Inspections::EQP ID

               Double click the relationship line and then select a sort order for inspections that sorts your records by INSP ID in descending order.

               Then you can set up a layout based on Equipment, but add the needed fields from Inspections to your layout to show the information from the most recent inspection for each equipment record.

          • 2. Re: Find the last date based upon another field
            atwharton

                 Many thanks all working now.