Forum Discussion

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

formula queries

JakeRattner1

Qrew Cadet

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

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