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 ...
Forum Discussion
I'd love to see your formula :)
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
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
------------------------------
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
------------------------------
