Discussions

 View Only
Expand all | Collapse all

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

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

    Posted 10-29-2021 18:50
    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
    ------------------------------


  • 2.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 10-29-2021 18:57
    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
    ------------------------------



  • 3.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 10-29-2021 19:15
    Hi Mark, 

    Thanks for replying so fast.  Agreed.  A ready-made function doesn't exist and hopfully it will be soon. I'm hoping there will be a ton of new functions once Quickbase is ready.

    In the meantime, I'm wondering if there is a creative solution for getting the min/max out of a text list.  For example, I can use get the min/max from the text list by using the following formula:

    Max(
    ToNumber(Part([Text],1,";")),
    ToNumber(Part([Text],2,";")),
    ToNumber(Part([Text],3,";")),
    ToNumber(Part([Text],4,";")),
    ToNumber(Part([Text],5,";")),
    ToNumber(Part([Text],6,";")),
    ToNumber(Part([Text],7,";"))
    )

    But the above seems like a poor approach because I may have to add "ToNumber(Part([Text],1000,";")" some day soon.  Updating formulas like that is a nightmare.

    Thanks!
    Jake

    ------------------------------
    Jake Rattner
    ------------------------------



  • 4.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 10-30-2021 11:29
    Jake, until we get a function like MaxValues, you will just need to "enjoy" using Copy/Paste to grow your formula to keep up with your data.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 11-01-2021 13:28
    With formula queries being so fresh in Quickbase and a big change up for what can be accessed in formulas the team is very focused right now in cleaning up a few items that came up from the launch and assessing the overall performance of formula queries in QB as it gets more adoption across even more accounts. I definitely suggest as you are using formula queries right now if you have any feedback for us on your experience or things you would like to see in the future like a MaxValues function that you put that feedback into our recently launched new feedback experience. You can find it right where we used to collect feedback on the My Apps page under the Feedback tab. From there you can add your feedback or see if there is other feedback like yours you want to support.

    ------------------------------
    Evan Martinez
    ------------------------------



  • 6.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 09-05-2022 15:38
    Edited by Curtis Middleton 09-05-2022 16:51
    *Edited - Further testing invalidated some of what I had a originally posted.
    Hey all,

    I came across this post trying to find a solution myself for another issue I was encountering.

    Here is what I was able to put together to solve the issue:
    Right(ToText(GetFieldValues((GetRecords("{3.GT.0}", Dbid())),3)), " ")

    So this field pulls together a list of all records in existence (Record ID greater than 0) on a given table (Dbid() in this scenario). By using the Right() function and reference a delimiter character (a blank space in for this solution), it will give you the "max" value. If you use Left(), it will give you the "min" value.

    ------------------------------
    Curtis Middleton
    ------------------------------



  • 7.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 09-06-2022 10:30
    The other solution to getting at a as yet non existent Record ID# is to use in an API Button is to use the  SaveBeforeNavigating syntax.



    The code below works to add a record and then blend the user on the newly added record in edit mode.
    var text RID = "%%rid%%";

    var text AddShipment = URLRoot() & "db/" & Dbid() & "?a=nwr";
    var text EditShipment = URLRoot() & "db/" & Dbid() & "?a=er&rid=" ;

    var text URL =
    $AddShipment
    & "&rdr=" & URLEncode($EditShipment) & $RID;


    "<a class='SaveBeforeNavigating' data-replaceRid=true style=\"text-decoration:none; background: #4b7097; border-radius: 5px; color: #ffffff; display: inline-block; padding: 0px 0px 0px 0px; width:100px; text-align: center; text-shadow: none; border: 2px solid #030404; font-size: 12px \"href='"
    & $URL
    & "'>Save and Display in Edit Mode</a>"

    //NOTE: The %%RID%% does not seem to like being URLEncoded.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Example of nested URLs with non encoded %%RID%%


    $URLONE & ($rid)
    & "&rdr=" &URLEncode($URLTWO) & ($rid) 






    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 8.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 10-31-2022 13:18
    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
    ------------------------------



  • 9.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 05-24-2023 14:14

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



  • 10.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 05-24-2023 20:45

    Hi Edward,

    I think you'll find all your answers in this awesome short video from Quickbase Junkie: 

    Rank Records Using the Size Query Function in Quickbase

    YouTube remove preview
    Rank Records Using the Size Query Function in Quickbase
    View this on YouTube >






    ------------------------------
    Matt Stephens
    ------------------------------



  • 11.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 05-24-2023 21:57

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



  • 12.  RE: Formula Queries - Finding Min/Max of Value Using Foreign Key in Other Table

    Posted 05-24-2023 23:07
    Edited by Edward Hefter 05-24-2023 23:10

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