Discussions

 View Only
  • 1.  Find record with previous date?

    Posted 08-01-2022 17:37
    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
    ------------------------------


  • 2.  RE: Find record with previous date?

    Posted 08-01-2022 18:06
    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
    ------------------------------



  • 3.  RE: Find record with previous date?

    Posted 08-02-2022 08:51
    I'd love to see your formula :)

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



  • 4.  RE: Find record with previous date?

    Posted 08-02-2022 09:52
    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
    ------------------------------