Discussions

 View Only
Expand all | Collapse all

Formula to pull record ID from another table based on parameters

  • 1.  Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 16:08
    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 associated with a "driving event". This formula isn't giving me any results, but I can't figure out where my thinking is going wrong. Happy to provide additional context, just not sure what is helpful.

    If(
    (
    (
    ([Vehicle Assignment - Start Date]<[Date]) and
    (([Vehicle Assignment - End Date]>[Date]) or
    (IsNull([Vehicle Assignment - End Date]))))
    ),

    (GetFieldValues(GetRecords("{9.EX.'"&[Vehicle OR P&P GPS #]&"'}", [_DBID_VEHICLE_ASSIGNMENTS]), 3))
    )

    ------------------------------
    Amy Christensen
    ------------------------------


  • 2.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 16:35
    Can you explain more in words what you are trying to do here? Which Record ID are you trying to retrieve and in words how do we know which Record ID you want?

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



  • 3.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 16:46
    I will try, please just keep in mind I may use the wrong vocab as I am self-taught and muddling through here.

    I have two tables:

    - Vehicle Assignments (shows the start and end date for when a specific vehicle was assigned to a specific driver and project. Vehicle, Driver, and Project all have their own tables related to Vehicle Assignments, just FYI))

    - Driving Data (imported data from our telematics system that includes the Vehicle and Driver for driving events)

    I am trying to get the driving data for a specific vehicle that occurred during the assignment period associated with that Vehicle Assignment record so that we can analyze the driving behavior for that assignment/driver/time period.

    Record ID is the key field for the Vehicle Assignment table, and it is a legacy table that I am not trying to mess with. For that reason, I am trying to use a formula to identify the correct Vehicle Assignment record that corresponds with each Driving Data record, by matching up the Vehicle name between the two tables and then looking at the start/end date parameters from the Vehicle Assignment versus the driving event date.

    Hopefully that makes sense!

    ------------------------------
    Amy Christensen
    ------------------------------



  • 4.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 16:48
    I should also say I am very open to the possibility that I am over-complicating what is necessary to accomplish what I am trying to do!

    ------------------------------
    Amy Christensen
    ------------------------------



  • 5.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 17:01
    If we were viewing a Vehicle Assignment record, what is it that you are trying to pull from Driving Data onto that record?  Presumably, during that Assignment period there would have been many Driving Data Records.

    What is your ideal result?  To be able to view the Driving Data records as an embedded report on the Assignment Record?

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



  • 6.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 17:30
    An embedded report would be of use, but the end goal is to be able to create reports that can summarize and cross-reference data between the two tables, which can then roll up to other reports. 

    I'm actually trying to aggregate data between this report and several others, I just started with the Vehicle Assignments table because it is already connected to those other reports and is likely to be able to function as a pass through once I can get the Driving Event record to "see" the Vehicle Assignment that corresponds based on the date parameters and Vehicle #.

    The end goal is that I could create reports to summarize the driving behavior by Vehicle or by Driver, bounded by the Vehicle Assignment that is in place at the time of the activity. The relationships between Vehicle, Driver, and Vehicle Assignment tables are well-established and I can easily pull data up/down between those tables and into reports that combine info from these records.

    What I'm struggling with at the moment is that the Driving Event data is coming from an external CSV report and is therefore not "hooked in" to any of these existing tables. I have fields that I can match up between these reports and existing tables, namely Driver ID and Vehicle #, but they are not "connected" to any other tables because they are just text that is being pulled in from this report. I'm trying to find a way to relate this info to the other tables ​that will allow me to being pulling information between them in ways that will enable data analysis.​

    ------------------------------
    Amy Christensen
    ------------------------------



  • 7.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 17:35
    So is your goal to essentially calculate a value for [Related Vehicle Assignment] on each Driving Event record?  ie so each Driving Event knows its Vehicle Assignment for that day?

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



  • 8.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 17:42
    I think that states  it accurately. However, the automatically created Reference Field of "Related Vehicle Assignment" appears to just be blank for all the records, and I can't find any way to calculate a value for that existing field, hence just trying to create a new lookup field.

    ------------------------------
    Amy Christensen
    ------------------------------



  • 9.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 17:33
    An example may be useful. One thing I would like to be able to do is to pull up a Vehicle Assignment record and be able to see the latest Driving Event date. This would help us identify telematics devices that may have stopped reporting, or be a flag that a driver has job abandoned and left the vehicle sitting somewhere.

    This is only one metric, but I think it is illustrative of the sorts of things I am trying to enable by creating a field on the Driving Data table that can identify the Record ID of the Vehicle Assignment that corresponds to each Driving Event.

    ------------------------------
    Amy Christensen
    ------------------------------



  • 10.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 17:51
    I will try the formula, but without a test app, it may not work the first time!

    // I will assume that the field ID in Vehicle Assignments for the start date is 10 and the End Date is 11.
    // also assume that the field to identify the vehicle in vehicle assignments is fid 9.

    I will assume that the Driving data field for the event date is called [Date] and it is in date format (not date/time)

    Note that the vertical alignment of the formula is just me trying to match up my parentheses.

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



  • 11.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 18:04
    Much appreciated!

    Two issues:

    1. I need a rule that will also include records where the end date is blank. I could be wrong, but I don't believe that is accounted for here.

    2. As written I get a syntax error on line 4. I have not even begun to get into variables to know why it isn't happy.



    Again, very grateful for the help!

    ------------------------------
    Amy Christensen
    ------------------------------



  • 12.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 18:12
    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
    ------------------------------



  • 13.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 18:22
    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
    ------------------------------



  • 14.  RE: Formula to pull record ID from another table based on parameters

    Posted 12-04-2022 18:45
    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
    ------------------------------