Forum Discussion

JakeRattner1's avatar
JakeRattner1
Qrew Cadet
4 years ago

Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

Hi QB Community, I'm excited about the new Formula Query and am trying to use it to find the minimum and max fields in a seperate table.   I start with this: GetFieldValues(GetRecords("{249.EX....
3)
formula queries
MattStephens's avatar
MattStephens
Qrew Cadet
Solved... (work around)
Put a query function in your "prices" table (the one you are querying) that counts the number of records that have a GTE (greater than or equal to) price and same StateID.
If it returns "1" then it's the max price for that StateID.

So with:
fid212 is Price
fid249 is StateID

Formula Checkbox "First Max Price for StateID", fid999

//Query parts
var text GTEPrice = "{212.GTE.'"& [Price] & "'}";
var text SameState = "{249.EX.'"& [StateID] & "'}";
var text RIDLTE = "{3.LTE.'"& [Record ID#] & "'}";  //will find the first record if multiple "prices" have the same price.

//Number of Records = 1 if it's the First Max Price for StateID
1 = Size(GetRecords($GTEPrice &"AND"& $SameState &"AND"& $RIDLTE));

Then, in your other table, have another query function that finds that record and sum values (of that one record) to get the max price.

Formula Numeric "Max Price for State"
//Query parts
var text SameState = "{249.EX.'"& [StateID] & "'}";
var text IsFirstMax = "{999.EX.'1'}"; //'1' finds a true checkbox

SumValues(GetRecords($SameState &"AND"& $IsFirstMax, "DBID"), 212)

​​Let me know if this works for you!​

------------------------------
Matt Stephens
------------------------------
EdwardHefter's avatar
EdwardHefter
Qrew Cadet
2 years ago

I'm trying to use this method and it seems like there is a problem with the logic.

Not counting the state part of the equation, if I have records:
RID    Value
1          10
2         10
3         200
4         100

Record 1 only has 1 record that is GTE 10 and has a RID LTE 1 (itself)
Record 2 has 2 records that are GTE 10 and has a RID LTE 2 (itself and #1)
Record 3 is back down to 1 record that is GTE 200 and has a RID LTE 3 (itself)
Record 4 has 2 records that are GTE 100 and has a RID LTE 4 (itself and #3) 

I can't figure out how to work the GTE and LTE (as well as LT, GT, XEX, etc.) to get a unique value for RID 3, whether it is a 0 or 1.

Any suggestions? Something I might be missing?



------------------------------
Edward Hefter
www.Sutubra.com
------------------------------
    • EdwardHefter's avatar
      EdwardHefter
      Qrew Cadet
      2 years ago

      Thanks - I had seen that before and her videos have been extremely helpful to me! I am trying to find the highest value. I can rank them, but if I have 2 records in "first place," I can't do a "sumvalues" to find the highest number.

      I might be able to do a sumvalue of the highest ranking and then divide by the number of records, though. I'll give that a try tomorrow.



      ------------------------------
      Edward Hefter
      www.Sutubra.com
      ------------------------------
      • EdwardHefter's avatar
        EdwardHefter
        Qrew Cadet
        2 years ago

        I was trying to figure out what the next serial number should be in a set of records with different airplane types, and each airplane type gets its own serial number with a 3 letter suffix, and the serial numbers increment for that airplane type. Sounds like the perfect use of a maxvalue function...I can't wait!

        First step was to strip off the letters, which was easy enough using the "notright" function.

        Then I ranked all of the serial numbers using the great suggestion from Matt Stephens and Quickbase Junkie!

        First rank all the serial numbers (this is field 42):

        var Text GTESerial = "{43.GT."&[Number part of S/N]&"}"; //43 is [Number part of S/N]
        var text SameModel = "{7.EX.'"&[Aircraft or Float Type]&"'}"; //7 is [Aircraft or Float Type]
         
        Size(GetRecords($GTESerial & "AND" & $SameModel))+1

        This will end up in the situation where, if two airplanes of the same type have the same serial number (gotta do the error checking!), they can both be number 1. So, I used a little math to divide the sum of all the number 1s by the number of records: 

        var Text FindLowest = "{42.EX.1}"; 
        var text SameModel = "{7.EX.'"&[Aircraft or Float Type]&"'}";
         
        (SumValues(GetRecords($FindLowest&"AND"&$SameModel),43)
        /
        Size(GetRecords($FindLowest&"AND"&$SameModel))) + 1

        Time to put this in my toolbox and thank all the people who came before me and wrote up their solutions!



        ------------------------------
        Edward Hefter
        www.Sutubra.com
        ------------------------------