1 Reply Latest reply on Oct 19, 2015 7:23 AM by skashanchi

    Looking up a value from records in another table


      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.