6 Replies Latest reply on Jun 22, 2015 8:32 AM by philmodjunk

    Find next oldest year in a range of records

    Stu412

      Title

      Find next oldest year in a range of records

      Post

      Hi there

      I have a data table and on every records there is a date.  The date can be the same for many records.  As part of what I need to do, I've been required to find the most recent date, so I've simply used the formula Max(datefield) which works.  I now need to find the next most recent date which I'm unsure how to do.  An extract of data would look like this:

      Amount | Date 

      100       | 31/01/2015

      210       | 31/01/2015

      450       | 31/01/2015

      300       | 31/12/2014

      My Maxdate formula returns 31/01/2015 and now I need to get a second formula to pick up the bottom row and pull through the 31/12/2014.

      It's not simply a case of (Date-One month) because the dates can vary.  I need FM to calculate the most recent date and from there check for the next most recent date, however long ago that may be.

      Thanks

       

        • 1. Re: Find next oldest year in a range of records
          philmodjunk

          Don't think there is a built in function to do that. From the existing records, you'd need to sort the records as shown and loop through them to get that value. The looping can be done with a script or, if you have FileMaker Advanced, a custom function.

          an alternative method might be to set up a related table linked by this date field with "allow creation..." enabled where you create one record for each unique date. Then sorting and going to the second record would return the same value every time. (Such records can be created from the current table with a single script step: Set Field [UniqueDates::Date ; currentTable::Date ] as this creates a new record automatically only if there is no matching record in UniqueDates.)

          • 2. Re: Find next oldest year in a range of records
            SteveMartino

            Is this a found set? A report?  Is the data a calculation or concatenation of Amount & " | " & Date?  Hard to understand what you mean by ..check for most recent date, then check by next most recent date...

            Couldn't this just be sorted by date, descending, or sort the relationship by date descending?

            If not, I would think you could try a script that omits the current max date records, with a constrain found set, script step.  Every time you call that script, the set of found records should decrease by omitting the most recent date records.

            • 3. Re: Find next oldest year in a range of records
              Stu412

              Hi there

              Yes, this is a found set running on a list report and from this set I need to pull columns of data per a given date period and align them next to each other, the most recent column always to the left.  I then need to calculate the next most recent column and place it to the right and so on.  All records need to be visible at all times, so I don't think Omit would be an option.

              Portals are not suitable because of the inconsistency of the data (that's just the way it is unfortunately) but a list layout is perfect for this and the task requirements.

              If there is no command within FM to do this, I can look at a script to goto the next record which is a) different to the maximum and b) the max of the remaining records.

              Presumably then the results can be posted to global fields for display?

               

              • 4. Re: Find next oldest year in a range of records
                SteveMartino

                Portals are not suitable because of the inconsistency of the data (that's just the way it is unfortunately) but a list layout is perfect for this and the task requirements

                Why, presumably you only need to see date, id, and maybe another field?  A report like this can be done with portals, virtual list, or web viewer.  Web viewer may be better when the number of columns is unknown.  Or maybe you have to compile this data and export it to Excel (probably not the best options)

                Can you show us a mock up of what the report should look like?

                • 5. Re: Find next oldest year in a range of records
                  Stu412

                  OK, 

                  So you may agree or disagree frown I've used a formula to split the relevant dates into their own columns from the single column they currently sit in:

                  "Previous" = If (MaxDate-1 = Right(Date;4);Date), and then "Previous2" = If (MaxDate-2 = Right(Date;4);Year and so on.  

                  I've then just used a Max(Previous) and Max(Previous2) to ensure the figure is available at the top of my list report.

                  I'm certain there's probably another slicker way to come to this conclusion but time is an enemy here as well and I'm not able to experiment too much to understand what that may be!  I may recode in 6 months when I know more :)

                   

                  • 6. Re: Find next oldest year in a range of records
                    philmodjunk

                    On the other hand, ExecuteSQL, might be able to do this with the GroupBy clause combined with code to access the second value in the list thus produced.