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.)
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.
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?
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?
"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 :)
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.