Size Query - Ranking and then Querying the Next Ranked Record

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

Forum Discussion

Size Query - Ranking and then Querying the Next Ranked Record