2 Replies Latest reply on Dec 22, 2015 7:53 AM by Extensitech

    Find most recent date in a scripted find


      Hi there


      I'm putting together a find routine which needs to return the most recent records based on a date that each one of them has.  The date is typically an annual anniversary and therefore is the same for many records, but with a different year.


      I need to find only the most recent records, whatever the quantity.  So far, my find script is pretty simple:


      • Enter find mode
      • Set field [Customers::CustomerID_PK; $Variable1]
      • Set field [Customers::SaleCategory; $Variable2]
      • Perform find


      This would return the following data:


      CustomerID_PK          SaleCategory     AnniversaryDate

      1217                           Fabrics               31/01/2014

      1217                           Fabrics               31/01/2014

      1217                           Dyes                  31/01/2014

      1217                           Fabrics               31/01/2013

      1217                           Fabrics               31/01/2013

      1217                           Dyes                  31/01/2013

      1217                           Fabrics               31/01/2015

      1217                           Fabrics               31/01/2015

      1217                           Dyes                  31/01/2015


      How would I get this to return ONLY the 31/01/2015 data?  This cannot be a set value as these dates will vary between customers and could be any point in the year, so it needs to be the most recent (maximum?) date in this found set.


      Thanks in advance guru's

        • 1. Re: Find most recent date in a scripted find

          You could sort ascending on the field AnniversaryDate, and go to the last record. That will be the most recent date.

          • 2. Re: Find most recent date in a scripted find

            What is the SaleCategory table, and is there another table besides Customer and SaleCategory? Where is the anniversarydate stored? It appears, from your example of returned data, that you're seeing duplicates, with the same customer, category and date?


            Does the "most recent date" mean the most recent for the customer? The most recent for a customer/salecategory combination?


            It seems like, at some point in this structure, you'll need a TO to sort the dates in question in descending order. You can then calculate the key of the "top" record in that sort order, and make a second TO relating the "top" fk to the pk of the record you found. You could then GTRR.


            Sorry if that sounds obscure, but with some more information about the tables and purposes, it might be easier to explain.


            Chris Cain