Batch Copy Process for VZA Assignments

The document will guide you through the process to batch copy Vision Zero assignments.

Purpose

To copy a batch of assignments to eliminate form entry.

Process

1) Open the Assignments object in Records View

2) Exports all records as CSV to create a backup.

3) Filter assignments for the previous 8 or 12 weeks (Sun/Mon to Monday) using the Assignment Date Time field.

Ex. Assignment Date Time is after 01/01/2022 20:00 (before start of Sunday-Monday overnight shift) and Assignment Date Time is before 02/28/2022 04:01 (after 4am Monday when the overnight shift ends)

4) Export records to CSV. ~ 286 records

5) Open CSV in Excel and save on the Network Drive as Assignments_Export_Date

G:\ATD\ATD_GIS\02_ENT_APPLICATIONS\app_VZA_Vision_Zero_in_Action\Schedules\Assignments

6) Save As Assignments_Import_Date

7) Insert a column to the left of Assignment Date Time, column B, and enter formula:

=(LEFT(C2,10))+84 for 12 weeks. Format the column as a Short Date. Fill Down the column. This gives us our new start date.

8) Insert a column to the left of Assignment Date Range , column D, and enter formula:

=(RIGHT(E2,10))+84 . Format the column as a Short Date. Fill Down the column. This gives us our new end date.

9) Insert a column to the left of Assignment Time Range, column F, and enter formula:

=LEFT(G2,5) . Fill Down the column. This gives us our start time.

10) Insert aother column to the left of Assignment Time Range, column G, and enter formula:

=RIGHT(H2,5) . Fill Down the column. This gives us our end time.

11) Insert a new column to the left of Assignment Date Time, column C, and enter formula: =CONCATENATE(TEXT(B2,"mm/dd/yy")&" "&TEXT(G2,"hh:mm")&" to "&TEXT(E2,"mm/dd/yy")&" "&TEXT(H2,"hh:mm")). Fill Down the column. This combines our new dates with the start & end times.

12) Remove the old Assignment Date Time column header from column D and title the new column C with Assignment Date Time.

13) Copy the text values from columns B, G, E, & H into Assignment Start Date, Assignment Start Time, Assignment End Date, and Assignment End Time, columns J, K, L, & M respectively at this point. Paste as values. Format Assignment Start Date and End Date as Short Dates.

We should be able to concatenate these new fields for the new Assignment Date Time for future VZA batch imports to make things easier.

14) Update the Created Date and Created By for the first row. Created By should be your email. Fill down both columns.

15) Save and Import the CSV into the Assignments object by clicking on Import in the Assignments object Records view.

16) Import the modified CSV import file.

17) For Does the CSV have a row at the top with a name for each column? Choose - Yes, the headers are on Row 1

18) Do you want to update existing assignments records with this import? Choose - No, you want to insert each row as a new record

19) Confirm the field mapping of the columns and set the Created By to Email.

20) Uncheck Modified By and Modified Date. Click Next and Start Import. The records will be imported into the object.

21) Confirm all the records have been imported and data populated by filtering the Created Date and checking the Assignments Calendar.