4 Main Steps
Excel Cleanup
Import to datagrip
import from table to main tblPermits table
Create Roles
...
Excel Cleanup
First thing to do is update the headers to match the following:
...
Acronym can be filled with “N/A.”
Subsidiary be the same 100% through, so just copy and paste.
The above line applies for Acronym as well.
I would send an email if Facility or Program is null.
DocsRequired can be set to 0 if it’s null.
StartDate = 1900-01-01 if null
EndDate = DueDate if null.
DueDate = EndDate if null.
If both EndDate and DueDate are null set to 2125-01-01.
You can get a rough idea of what the frequency should be based on the start date and due date if its null. The list of frequencies is in the ActionItem db as tblFrequency.
...
Import into datagrip
Save the excel sheet as a CSV file.
Import into datagrip.
...
Select the file.
Not sure if it matters but my settings are below.
...
Import into tblPermits
In the test environment, you can easily delete all permit data by running the following stored procedure. I do this every time I import a new excel sheet.
...
| Code Block |
|---|
EXEC sp_ImportTable @TableName |
...
Create Roles
Now you can create the new roles for the new subsidiary. You can find those here under email reminders.
Note: The acronym for the role should match the acronym in the tblPermits table linked to each task/permit.
...
Rolling the dates forward
Joseph will most likely ask for the dates to be rolled forward. All this means is he wants them to be renewed so that there are no expired tasks to start. What I do for this is start with the query below.
...