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