Find record with previous date?

Question

Is there a way I can use a formula query to, using a set of criteria, find the record with a previous date?

ie - return the maximum date that is prior to the current date.

Example:

My query narrows down to records with these due dates:

1-2-22
4-5-22
7-7-22
10-20-22

The earliest date returns nothing
Record with 4-5-22 returns 1-2-22
Record with 7-7-22 returns 4-5-22
etc...

There will be no ties so that is not a worry

------------------------------
Mike Tamoush
------------------------------

miketamoush · Answer

I was able to use QuickBase Junkies ranking system, using the Size function in query, to pull this off. I suspect until Queries give us a min/max function, this might be the way.

------------------------------
Mike Tamoush
------------------------------

markshnier__you · Answer

I'd love to see your formula :)------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

miketamoush · Answer

Here is the youtube videohttps://www.youtube.com/watch?v=sHU8pB38N2YEssentially you start by ranking them. [Rank] = var text Query = "{Condition 1} AND {34.BF.'" &amp; [My Date] &amp; "'}"; //34 is My Date fieldSize(GetRecords($Query))Then you can use that Rank Field how you want. In my case:var number RanktoCompare = [Rank]-1;var text Query = "{Condition 1} AND {80.EX.'" &amp; $RanktoCompare &amp; "'}"; //80 is rank fieldvar date PriorSampleDate = ToDate(ToText(GetFieldValues(GetRecords($Query),6)));var date PriorDueDate = ToDate(ToText(GetFieldValues(GetRecords($Query),34)));This can fail if you have a 'tie' when ranking (equal dates). In my case, that can't happen. QB Junkie I think shows some ideas to combat that in her video.------------------------------Mike Tamoush------------------------------

Forum Discussion

Find record with previous date?