Forum Discussion

LizChartrand's avatar
LizChartrand
Qrew Cadet
3 years ago

Size Query - Ranking and then Querying the Next Ranked Record

Hello!

I recently figured out how to rank records in QB by using the Size function to organize records chronologically.
I'd like to take that a step further and be able to rank records that fit a set of criteria and then query the very next record in the ranked sequence.

In other words, if I have 3 records Ranked 1, 2, 3 -
I'd like to be able to query information on Record 2 from Record 1 and, I'd like to be able to reference Record 3's information on Record 2.

If anyone has any suggestions, even if it's not specifically the size function, I'm all ears!

Thank you!

------------------------------
Liz Chartrand
------------------------------
formula queries

4 Replies

Sort By
  • Quick_BaseJunki's avatar
    Quick_BaseJunki
    Qrew Captain
    3 years ago
    Hi Liz,

    I believe what you're looking for is the GetFieldValues() function paired with a query that includes your ranking. 

    This video shows you how to use the GetFieldValues function along with another query ranking field. In your case, I would also include a formula in the query like [Rank]-1 to get the prior records rank.

    Video will start at the point where this is demonstrated: https://youtu.be/HVaUfnuBp4s?t=780

    ------------------------------
    Quick Base Junkie
    ------------------------------
    • LizChartrand's avatar
      LizChartrand
      Qrew Cadet
      3 years ago
      Hi Quick Base Junkie!
      Thank you for your response!

      I think I'm still getting stuck with my rank (Size Query Field) in tandem with a GetFieldValues() field.

      Here's what I have so far for my Rank:

      var text NRTR = "{6.EX.'" &[Related Unit]& "'}AND{39.LT.'" & [Lease Start] & "'}AND{142.XEX.'Application in Process'}AND{168.EX.''}AND{261.XEX.'Yes'}";

      If([Lease Status]="Application in Process",null,
      If(ToText([Lease Canceled])<>"",null,
      If([Resident Transferring to Another Unit]="Yes",null,
      Size(GetRecords($NRTR))+1)))

      -or this-

      var text NRTR = "{6.EX.'" &[Related Unit]& "'}AND{39.LTE.'" & [Lease Start] & "'}AND{142.XEX.'Application in Process'}AND{168.EX.''}AND{261.XEX.'Yes'}";

      If([Lease Status]="Application in Process",null,
      If(ToText([Lease Canceled])<>"",null,
      If([Resident Transferring to Another Unit]="Yes",null,
      Size(GetRecords($NRTR)))))


      This is attempting to omit a few Statuses, which it is doing, but I'm noticing the Rank, when looking at a list of records in a report is: null, 1, 3.
      Do you have any suggestions on how to make it null, 1, 2, for a group of 3 records that includes a record that should be ignored/omitted from being ranked?
      Or, is there a way to salvage this in the next GetFieldValues()

      For instance,
      If I need to stick with null,1,3,
      How might I write a GetFieldValues() Query that pulls a value from the Rank 3 Record, from the perspective of the Rank 1 Record?



      Thank you for your help!

      ------------------------------
      Liz Chartrand
      ------------------------------
      • Quick_BaseJunki's avatar
        Quick_BaseJunki
        Qrew Captain
        3 years ago
        I would look at it this way...

        Where you are seeing the value of "3" it is saying that it found 2 records matching the query + 1 in your formula.

        You believe there should only be 2.

        What's causing the discrepancy from what you expect the query to return and what is returning?

        Try putting the same query items into a report filter based on the values from the record with the "3" rank and see what comes it displays.

        From there you may see where adjustments need to be made.

        -Sharon

        ------------------------------
        Quick Base Junkie
        ------------------------------