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

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

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

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

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

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

Formula to pull record ID from another table based on parameters

MarkShnier__You
Qrew Legend
2 years ago
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
------------------------------
- AmyChristensen1
  Qrew Trainee
  2 years ago
  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
  ------------------------------
- AmyChristensen1
  Qrew Trainee
  2 years ago
  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
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    2 years ago
    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
    ------------------------------

Forum Discussion

Formula to pull record ID from another table based on parameters