Forum Discussion

AmyChristensen1's avatar
AmyChristensen1
Qrew Trainee
3 years ago

Formula to pull record ID from another table based on parameters

I may be coming at this from the completely wrong direction, but I have two related tables and would like to create a field that pulls the correct record ID for a "vehicle assignment" that is associat...
MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
3 years ago
OK, let's worry about the blank end date issue later once we this this working.
Try this next version


var text QUERY =
"{10.OBF.'" & [Date] & "'}"
& " AND "
& "{11.OAF.'" & [Date] & "'}"
& " AND "
& "{9.EX.'"&[Vehicle OR P&P GPS #]& "'}";

ToNumber(
Left(
ToText(
GetFieldValues(
GetRecords($QUERY, [_DBID_VEHICLE_ASSIGNMENTS])
, 3)
),
";")
)

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
AmyChristensen1's avatar
AmyChristensen1
Qrew Trainee
3 years ago
Worked like a charm! Start and End date are actually fields 6 and 7, but all I had to do was drop those numbers in. If we can get the null end dates accounted for, I think this gets me where I need.

------------------------------
Amy Christensen
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    I'm actually not sure what the Syntax is to check for a null vale in a Formula Query Filter.  Maybe someone else on the forum will see this thread and help us to it more elegantly.

    But meanwhile, Tick Tock, we can't wait around hoping for that to happen. 

    So please make a helper field on the Vehicle Assignment record  called [End Date is Blank?] with a formula of 

    IsNull(End Date])

    I will assume that it ended up being field ID 99.

    Try replacing the formula variable QUERY with this.  I have never had to use an OR in a Formula Query before so LMK if this works.  not sure about the use of parentheses .....

    var text QUERY =
    "{10.OBF.'" & [Date] & "'}"
    & " AND "
    & "({11.OAF.'" & [Date] & "'}"
    & " OR "
    & "{99.EX.1})"
    & " AND "
    & "{9.EX.'"&[Vehicle OR P&P GPS #]& "'}";


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