Skip to main content
Question

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

  • October 29, 2021
  • 0 replies
  • 791 views
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.'"& [StateID] &"'}", "DBID"), 212)

In the query, I'm returning a text list that shows all the 'prices' (fid212), where the 'stateID' (fid249) matches the 'StateID' on the record.  This part works.

Now I'm trying to figure out for to derive the min and/or max from the resulting text list.  

I've used SearchAndReplace to modify the text list in a few different ways to that I can pass it into the Max function, but no luck yet.  

Do you konw how to do this??  Thanks!

------------------------------
Jake
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
At this time we are only able to Sum the values that are returned or count them with the size function. But there is no max function yet.
One assumes that they will be offering this in the near future


------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Forum|alt.badge.img+3
  • MattStephens
  • Registered
  • October 31, 2022
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
------------------------------
Terms & ConditionsAccessibility statement