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
------------------------------
Original Message:
Sent: 12-04-2022 16:34
From: Mark Shnier (Your Quickbase Coach)
Subject: Formula to pull record ID from another table based on parameters
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
Original Message:
Sent: 12-04-2022 16:08
From: Amy Christensen
Subject: 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 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
------------------------------