Forum Discussion

MikeTamoush's avatar
Qrew Commander
2 years ago

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.


My query narrows down to records with these due dates:


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

There will be no ties so that is not a worry

Mike Tamoush

3 Replies

  • 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's avatar
      Icon for Qrew Legend rankQrew Legend
      I'd love to see your formula :)

      Mark Shnier (Your Quickbase Coach)
      • MikeTamoush's avatar
        Qrew Commander
        Here is the youtube video

        Essentially you start by ranking them. [Rank] =

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


        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