Forum Discussion

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

formula queries

Quick_BaseJunki

Qrew Captain

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

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
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
------------------------------
- LizChartrand
  Qrew Cadet
  3 years ago
  Hello again,
  
  I realized what the issue was and with a little more discussion came up with the following,
  
  1) Rank records Chronologically (in my case, it's based on a Lease start date and omits records that do not fit criteria)
  
  var text CTR = "{6.EX.'" &[Related Unit]& "'}AND{39.LTE.'" & [Lease Start] & "'}AND{142.XEX.'Application in Process'}AND{168.EX.''}";
  
  If([Lease Status]="Application in Process", null,
  If(ToText([Lease Canceled])<>"",null,
  If([Resident Transferring to Another Unit]="Yes",null,
  Size(GetRecords($CTR)))))
  
  2) Then, using that Query field as a reference, Find the next valid Ranked record (includes same omissions) *but is sort of limited to a specified number of records which may prove problematic in the long term:
  
  var text NextOne =
  "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+1) &"'}";
  
  var text NextTwo =
  "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+2) &"'}";
  
  var text NextThree =
  "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+3) &"'}";
  [...]
  
  var text NextTen =
  "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& ([Chronological Lease Terms Rank]+10) &"'}";
  
  var text NextOneCheck = ToText(GetFieldValues(GetRecords($NextOne),475));
  var text NextTwoCheck = ToText(GetFieldValues(GetRecords($NextTwo),475));
  var text NextThreeCheck = ToText(GetFieldValues(GetRecords($NextThree),475));
  [...]
  var text NextTenCheck = ToText(GetFieldValues(GetRecords($NextTen),475));
  
  Min(
  $NextOneCheck,
  $NextTwoCheck,
  $NextThreeCheck,
  [...]
  $NextTenCheck)
  
  here's how all that looks in a report:
  A single unit (203), with lease records organized/ranked according to lease start dates, (Lease 1 and Lease 2), then from lease 1, you can reference lease 2
  
  For actual application of this I then used another query field to pull in values for Gross rent:
  
  var text NextLease =
  "{6.EX.'"&[Related Unit]&"'}AND{168.EX.''}AND{142.XEX.'Application in Process'}AND{291.XEX.'Yes'}AND{475.EX.'"& [Query Next Valid Lease (LLCR Renewals)] &"'}";
  
  SearchAndReplace(ToText(GetFieldValues(GetRecords($NextLease,[_DBID_RENTAL_DETAIL]),69)), ";" , "<br>")
  
  ----
  I think this is a decent solution for the moment, but for step 2 do you have any suggestion for a better more ever-green way to write this out?
  
  Thank you for your help!
  
  ------------------------------
  Liz Chartrand
  ------------------------------