Forum Discussion

JakeRattner1's avatar
JakeRattner1
Qrew Cadet
3 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.'"& [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
------------------------------
3)
formula queries

11 Replies

Sort By
  • MattStephens's avatar
    MattStephens
    Qrew Cadet
    2 years ago
    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
      12 months 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
      ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    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
    ------------------------------
    • JakeRattner1's avatar
      JakeRattner1
      Qrew Cadet
      3 years ago
      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
      ------------------------------
      • CurtisMiddleton's avatar
        CurtisMiddleton
        Qrew Trainee
        2 years ago
        *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
        ------------------------------