Version 10.1.01 - The AccuImport Utility has been updated to support three new spreadsheet target table options which include:
•AR Misc AR Invoices - Imported to User Batch
•PR TimeCard Worksheet Records - Imported to Company Table
•JC Job List Records - Imported to Company Table
In addition, a custom framework has been built into the utility to all for more flexibility with tailored solutions during the import process. This new framework will be used by AccuBuild Consultants to make changes for specific company needs that can be handled via SQL Scripts much like the tailored solutions for MAR Reports.
This new framework now includes a Process Results screen if any of the import spreadsheet data is invalid for the above three new import file types. A new dialog screen will be displayed to show the errors when encountered. A Summary option list of errors will be presented by default, and you can select the Detail option list to see individual row numbers where the errors were discovered:
Sample Import Files - A new folder called AccuImporter is included with the update which is kept under the Samples Folder of the AccuBuild Program Folder which contains sample import files to test with the AccuImporter Utility Program.
IMPORTANT NOTE: Please contact AccuBuild Support for more information if you have specific needs for importing spreadsheet data into your company in order to automate time consuming data entry processes.
Custom Framework: The custom framework technical guidelines are outlined below for AccuBuild Consultants:
•Import Script Files - The Import Process relies on a series of script classes for each type of table data that is imported via the AccuImporter Utility Program. There are currently 4 distinct sets of script sequence numbers for each import table type that carry out the various stages of the import process as outlined below:
Step 1 - Creation of a user landing table to hold import data (Sequence Range: 1010 - 1990)
Step 2 - Validation of the import data (Sequence Range: 2010 - 2990)
Step 3 - Backup of Company Data files when applicable (Sequence Range: 3010 - 3990)
Step 4 - Pushing of Data to Final Landing Table (Sequence Range: 10 - 990)
Table ID Numbers - These ID numbers are used to identify the final landing tables for the import script and are used as part of the script sequence numbers. Currently there are 3 Table ID Numbers that are supported in the script library:
10 - ARMIBAT - User Batch Table for A/R Misc Invoices
20 - JOBLIST - Company Table for the Job List Table
30 - PMPERSONNEL - Company Table for Time Card Worksheet Record
The Steps for each of the above table types are then identified by the Range Number plus the Table ID Number - For Example, the JOBLIST table (Table ID = 20) uses the following sequence numbers in the script class:
Step 1 - Seq No 1020 - Create Landing Table for the Job List
Step 2 - Seq No 2020 - Validate the Import Data for the Job List
Step 3 - Seq No 3020 - Create a backup copy of the original company JOBLIST table
Step 4 - Seq No 20 - Push the Import Records to the Company JOBLIST table
Library Script Class for AccuImporter: These scripts are stored in the Library Script and can be tailored for individual script sequence numbers. The Library Script Class for the AccuImporter Script is 117000. These scripts are always run from a user connection very similar to MAR Report Scripts, however they do NOT support the MAR Script Tags such as <<COADD>> etc. The script for this class must contain native SQL commands for the Advantage Database Server that will run under a user connection in the ARC Utility. Since these are user based scripts, they do support both links to company data (colink) and global data (gllink).
Tailored Scripts: When the import process needs to be modified for a specific company set of business rules that are different from the standard library process, a tailored script can be created by an AccuBuild Consultant using the Custom SQL Script Utility to make a copy of the library script into the company script table. This process is very similar to the MAR Report Script changes but there are some important differences as noted below:
•Selected Sequences ONLY - Each Sequence number in the Library Script (Class 117000) has a specific function as described previously, so you only want to create sequence numbers in the Tailored Script that need to be modified. For example, the JOBLIST table uses sequence numbers 1020,2020,3020 & 20 as shown above for steps 1 through 4 respectfully. If you need to modify the import process for the JOBLIST Table, then you will only be working with these sequence numbers and NOT the entire script class. Furthermore, you only need to target the specific sequence numbers for the steps that need to be modified.
JobList Example - Let’s say that the company maintains a set of user defined fields for the job number creation date and user ID of the user that added (imported) the job to the JOBLIST. You would probably only need to modify sequence number 20 which has the code for pushing the import data to the JOBLIST table. In this case, sequence numbers 1020,2020, & 3020 can still use the library version.
•Sequence Numbering Rules: The tailored script must use the EXACT same Sequence Numbers as the original library script Sequence Numbers. These sequence numbers are noted as comments in the Library Script for easy reference as shown in the following example for sequence number 3030:
//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
// SEQ 3030 - AccuImporter - pmpersonnel_accuimporter table creation process
//%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Import Audit Files: During the import process, several audit files are created that can be viewed after the import process to view the script processing sequences and the related data tracking tables. All of these files are saved in the User’s AccuBuild Folder for the user that logged in the AccuImporter Utility Program:
•Script Sequence Files: Every Script Process that is run for the import will be logged in a text file to show the actual steps executed by the script. A separate file will be created for each script sequence number and the comments at the top of the file will document the Class, Sequence Number, and whether or not a LIBRARY Script was run or a COMPANY (Tailored Solution) Script was run. This is very useful to make sure any tailored scripts are actually being executed.
File Names - The names of these script sequence files will be formatted as follows: accuimporter_tracker_117000_nnnn.txt where nnnn represents the sequence number of the script:
•Library Script File Example:
•Tailored Script File Example:
Script Testing / Debugging - If any errors are encountered with the script, you can copy ALL of the lines from the script text file and paste into a user connection of the ARC Utility Program to debug or modify the process.
•User Temporary Tables: Any temporary user tables will be created as free tables in the User’s Folder using the code from the Script Sequence files. These tables can be opened with ARC to view the resulting data.
•Global Settings Table: Every time a table is imported, a global settings table will be created in the User Folder which will contain all of the company settings in case these are required by the user script. The table will be named globals_accuimporter. This can be very handy for getting default settings for imported data to automate the field update process using existing company property settings.
•Labor Distributions Codes Table: Every time a table is imported, a labor distribution codes table will be created in the user folder in case these fields are required by the user script. The table will be named labordistcodes_accuimporter. This table is very useful when importing time card worksheet records and the labor distribution codes need to be enforced
For TimeCard Worksheet Record import processes, the employee default payroll setting will be applied including any Labor Distribution settings that may be enabled for the company. These defaults will be applied according to the time card business rules hierarchy. Please refer to the AccuBuild online help for more information:
http://support.accubuildcloud.com/abhelp/businessrules.htm
•Import Audit Table: - During the Import Process, an audit table will be maintained in the User Folder to keep a record of the import processes along with a list of the key field names and field values of the records imported. The file structure for the audit file is listed below:
Import Audit File Name: accuImporter_audit.adt
oImporterRecID // Unique Import Record ID (autoinc)
oUserID // User ID of user that did the import
oImportDateTime // Timestamp when import was done
oImportTypeID // Defines the Import Type which is related to all of the script sequences for that type
oDestinationTableType // 1=User Batch, 2=Company Table, 3=Globals Table (Data Folder), 4=Other
oDestinationTableName // Destination Table Name
oUniqueRecID1 // Key Field Value 1 - String Representation
oUniqueRecID2 // Key Field Value 2 - String Representation
oUniqueRecID3 // Key Field Value 3 - String Representation
oUniqueRecIDFieldName1 // Key Field Name 1
oUniqueRecIDFieldName2 // Key Field Name 2
oUniqueRecIDFieldName3 // Key Field Name 3
oInteger1 // Reserved for Future Use
oInteger2 // Reserved for Future Use
oInteger3 // Reserved for Future Use
This table will help to identify the imported records along with the target destination table information, import date and time, and the user that did the import. The following example shows the audit records that are recorded during the import process for timecard worksheet records:
oDestinationTableName: PMPERSONNEL is the Company Table where these records are written to.
oUniqueRecID1: This field holds the value of the PersonnelID field which is a unique (autoinc) field for the PERSONNEL table.
oUniqueRecIDFieldName1: - This field holds the actual field name (PersonnelID) where the unique value is stored.
•Company File Backups: - When imported records are written directly to a company table, backup processes will be executed to put a copy of the company table in the user folder BEFORE the data is written to the company file. These Backup Tables are free tables which include the data file (adt) and the memo (adm) file when applicable. These backup file names will be cycled with a file name of 1 through 10 so that historical copies (up to 10) will be maintained before the numbers are re-used. The backup file names will include the company table as part of the backup file name which will be named as follows:
oCompanyTableName_AccuImporterBackup_nn.adt Data File
oCompanyTableName_AccuImporterBackup_nn.adm Memo File
Where CompanyTableName is the original file name in the company folder, and nn is the copy number which cycles from 1 to 10.
Important Note: The 3000 Sequence Number Series (3010-3990) supports script to create a list of company tables that should be backed up during the import process. You can update this script to include as many company tables as necessary to make sure all company tables that will be updated by the import process are backed up to the Users Folder before the import is done: The following example shows the company table list that was tailored when importing TimeCard Worksheet Records to the PMPERSONNEL table. This list will make sure that all 3 company tables are backed up before the import process is done, and the import process will be cancelled if the backup process fails:
