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
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's avatar
AmyChristensen1
Qrew Trainee
3 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's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 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
    ------------------------------
    • AmyChristensen1's avatar
      AmyChristensen1
      Qrew Trainee
      3 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        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
        ------------------------------
    • AmyChristensen1's avatar
      AmyChristensen1
      Qrew Trainee
      3 years ago
      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
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        3 years ago
        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
        ------------------------------