2 Replies Latest reply on Sep 30, 2015 9:47 PM by skashanchi

    Creating Multiple Records automatically and populating fields based on a value list

    skashanchi

      I am needing to write a script that would automatically create a number of records and populate certain field in each of these records based on a value list that I am creating elsewhere.

       

      So here is the scenario:

       

      We need to create a schedule each day that assigns all the vehicles in our fleet to a driver for that day. I have a table for Drivers and a table for Vehicles and a 3rd table called VehicleAssignment.

       

      So every day we need to create several records, one for each vehicle.


      Each of these records will have:


      AssignmentDate (which should be today's date)

      Vehicle # (should be populated automatically based on a value list)

      StartTime (entered manually)

      EndTime (entered manually)

      Driver# (entered manually)

       

      I already have value lists that are populated by the Vehicle #'s and Driver #'s. So Every day I would like to create as many records as there are in the Vehicle# list, and populate the date field and the Vehicle# fields in each record, such that we have a record for each of the vehicles. So we are basically creating what looks like a spreadsheet when seen in list view and then the fleet manager will go through and assign all the appropriate data.

       

      So if I have 3 vehicles, numbers 01, 02, and 03 I would like to be able to press a button and see:

       

      Date                    Vehicle #      Driver #      Shift Start     Shift End

      Today's Date          01

      Today's Date          02

      Today's Date          03

       

      So it's pretty simple (for now). What I have no idea how to do:

       

      1. Refer to a value list and determine how many items are in the value list to determine the number of records that need to be created

      2. Insert the data from the value list into the Vehicle# field, stepping through each item in the list.

       

      I am assuming this is pretty simple, but have never done anything like it before. Any help is appreciated.

        • 1. Re: Creating Multiple Records automatically and populating fields based on a value list
          erolst

          Since you say that

          skashanchi wrote:

          Each of these records will have:

          AssignmentDate (which should be today's date)

          Vehicle # (should be populated automatically based on a value list)

          StartTime (entered manually)

          EndTime (entered manually)

          Driver# (entered manually)

           

          you can set the date field to auto-enter the current date, and only need to set the VehicleID.

           

          One approach would be to simply import the (active) vehicles (the primary key only) into that table; the other is to grab a list and process it …

          skashanchi wrote:

          Refer to a value list and determine how many items are in the value list to determine the number of records that need to be created

          … like so:

           

          # [ you already have $listOfVehicleIDs ]

          Go to Layout [ VehicleAssignment ( VehicleAssignment ) ]

          Set Variable [ $vehicleCount ; ValueCount ( $listOfVehicleIDs ) ]

          Loop

            Exit Loop if [ Let ( $i = $ + 1 ; $i > $vehicleCount )

            New Record

            Set Field [ VehicleAssignment::vehicleID ; GetValue ( $listOfVehicleIDs  ; $i ) ]

          End Loop

          • 2. Re: Creating Multiple Records automatically and populating fields based on a value list
            skashanchi

            Great, thanks! I did not know about the ValueCount function at all. Makes a lot of sense. I will give it a try tomorrow.