AnsweredAssumed Answered

Looking up a value from records in another table

Question asked by skashanchi on Oct 11, 2015
Latest reply on Oct 19, 2015 by skashanchi

I am having trouble getting something to work which seems pretty simple to me (I am sure it is once you know how to do it correctly!).

 

This is what I need to do:

 

  • A dispatcher is looking at a list of trips (created in the Trips table) in a list view.
  • The dispatcher updates several values in each of these records directly from the list view. The dispatcher effectively dispatches the trip to a vehicle by entering the desired vehicle # in the corresponding field. When the dispatcher assigns a vehicle, I need to look up the corresponding driver for that vehicle for that day and that time of day.

 

This vehicle assignment is done in another table (VehicleAssignment) where every morning the fleet manager creates a record for each vehicle that has the date, the vehicle #, the driver # and the shift start and end times. So the records for a given day would look like something like this:

 

AssignmentDate      Vehicle      Driver         ShiftStart         ShiftEnd

10/9/15                    01            56               0700               1400

10/9/15                    02            45               0600               1300

10/9/15                    03            99               0700               1230

 

So for example when a dispatcher is looking at a trip in the trips table and he enters 01 in the Trips::VehicleNumber field, I need to find the matching record in the VehicleAssignment table and return the corresponding driver number (56) and enter it into the Trips:DriverNumber field, assuming that the time this assignment is being done in between 0700 and 1400.

 

So I need to find the record in the VehicleAssignment table where:

 

VehicleAssignment::AssignmentDate = CurrentDate (because trips are only assigned for the current day)

VehicleAssignment::VehicleNumber = Trips::VehicleNumber (the number assigned by the dispatcher)

VehicleAssignmnet::ShiftStart is < current time (verifying that a driver is indeed assigned to a certain vehicle at the moment a dispatch is being done)

VehicleAssignment::ShiftEnd is > current time

 

Questions:

1. What is the best way to do this lookup?

 

2. Where do I execute this script? I am thinking that I would want to do it as a script trigger for the Trips::VehicleNumber on OnObjectModify. This way the script runs any time the dispatcher makes a change to the vehicle number value.

 

Any help is appreciated.

Outcomes