Find record with previous date?

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 ...

Formulas and functions

MarkShnier__You

Qrew Legend

I'd love to see your formula :)

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

MikeTamoush

Qrew Commander

3 years ago

Here is the youtube video

https://www.youtube.com/watch?v=sHU8pB38N2Y

Essentially you start by ranking them. [Rank] =

var text Query = "{Condition 1} AND {34.BF.'" & [My Date] & "'}"; //34 is My Date field

Size(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.'" & $RanktoCompare & "'}"; //80 is rank field

var 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?