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

JakeRattner1
Qrew Cadet
4 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
------------------------------
- MarkShnier__You
  Qrew Legend
  4 years ago
  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
  ------------------------------
  - EvanMartinez
    Quickbase Staff
    4 years ago
    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
    ------------------------------
- CurtisMiddleton
  Qrew Trainee
  3 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
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    3 years ago
    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
    ------------------------------

Forum Discussion

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