This function allows a user to import a pre-defined, comma separated ASCII file containing information needed to create new records and relationships between Clients, Client Sites, Client Site Equipment, Jobs, Cost Codes and Billing Items in the database. When the import function is run, the system will create a new Client Site along with one or more Client Site Equipment records referencing a newly created Job/Cost Code/Billing Item and Client combination based on the specified Client Id and Job Id used for cloning an existing relationship.
If any errors occur during the import (i.e. invalid Client, Job or other referenced table values) the entire import process will abort and an error report will be generated specifying what problems were experienced during the import. The user can then re-attempt the import after the errors are corrected.
When this function is run, SAM Pro will open a browser window so that you may select the appropriate .cvs file for importing your client site data.
Registry Option to Specify Directory for CSV Site Import Files
Id: sys-start-Sclntsteimprt
Setting Key: CSVDirectory Setting Value: path (specify directory to place CSV files)
The first row of the .csv file MUST be a header row, and contain the pertinent title information, as shown in the following table. A ‘starter’ spreadsheet may be obtained from your support rep that has the correct header row data.
The .csv file must contain a valid Client Id and Job Id (per row of data) that the system can use as a model for cloning. Any other fields from the Client Site record (parent only) may be specified in the file layout. Any specified field values that reference a table other than the Client Site must be valid values in the database. For example, if the Account Manager Id “Tim” is specified in the file layout then “Tim” must exist in the Account Manager table prior to importing the file.
Any fields that are required by the system, in your customization file, or via required field registry setting must be specified in the template or in the spreadsheet.
See Excel for Imports for suggestions on dealing with Excel formatting issues.
Imported data must be formatted as follows (you may need to scroll down to view the table):
NOTE on Phone Numbers (Version 8.0 and above)
In previous releases, phone numbers had to conform to the SAM Pro (###)###-#### format to import correctly. In this release, if the .CSV phone fields contain any of the following formats they will be saved to SAM Pro correctly: ###-###-#### OR (###)###-#### OR ##########
|
Column Title |
Column # (in Excel) |
Notes and Formatting |
|
Orig Client Id |
A |
Client Id must exist in database |
|
Orig Job Template Id |
B |
Job Id must exist in database |
|
New Job Id |
C |
Id must NOT exist in database; 15 character maximum |
|
Job Name |
D |
40 character maximum; required |
|
New Site Id |
E |
Id must NOT exist in database; 15 character maximum |
|
Site Name |
F |
40 character maximum; required |
|
Site Alpha Id |
G |
20 character maximum |
|
Date Opened |
H |
YYYY-MM-DD; defaults to today if blank |
|
Date Closed |
I |
YYYY-MM-DD; defaults to blank |
|
Template |
J |
Y or N (default is N, not a template) |
|
Ship Address 1 |
K |
Site Shipping Address – 50 character maximum per field Ship Address 1 is required |
|
Ship Address 2 |
L |
|
|
Ship Address 3 |
M |
|
|
Ship Address 4 |
N |
|
|
City |
O |
30 character maximum; required |
|
State |
P |
Valid, 2-cha State or Province abbreviation; required |
|
Zip |
Q |
5 digit zip, or zip plus 4 (44024-0001); required |
|
Voice Phone |
R |
(###)###-#### ;required |
|
Fax Phone |
S |
(###)###-#### |
|
Acct Manager Id |
T |
Valid Id must exist in database if entered |
|
Sales Rep Id |
U |
Valid Id must exist in database if entered |
|
Service Manager |
V |
Valid Id must exist in database ; required |
|
Dispatcher |
W |
Valid Id must exist in database if entered |
|
Branch Id |
X |
Valid Id must exist in database if entered |
|
Service Zone |
Y |
Valid Id must exist in database; required |
|
AR Terms Id |
Z |
Valid Id must exist in database if entered |
|
Store # |
AA |
10 character maximum |
|
Call Script Id |
AB |
Valid Id must exist in database if entered |
|
Latitude |
AC |
#####.###### |
|
Longitude |
AD |
#####.###### |
|
Status |
AE |
Active, Inactive, Mail List or Credit Hold (default Active) |
|
Email Address |
AF |
Valid email; 128 character maximum per field. |
|
Email Fax |
AG |
|
|
Email Pager |
AH |
|
|
Email Invoices |
AI |
This field is obsolete |
|
Market Source Id |
AJ |
Valid Id must exist in database; required |
|
Price Code |
AK |
Valid Id must exist in database if entered |
|
Time Zone ID |
AL |
Time Zone Id must exist in database if entered |
|
Mat Price Level |
AM |
Single digit, 0-9 |
|
Technician Id |
AN |
Tech Id must exist in database if entered |
|
SC1 First Name |
AO |
Site Contact 1 fields – all are 20 character maximum |
|
SC1 Last Name |
AP |
|
|
SC1 Title |
AQ |
|
|
SC1 Salutation |
AR |
|
|
SC1 Phone |
AS |
(###)###-#### |
|
SC1 Ext. |
AT |
4 character maximum |
|
SC1 Cell Phone |
AU |
(###)###-#### |
|
SC1 Fax Phone |
AV |
(###)###-#### |
|
SC1 Email |
AW |
Valid email; 128 character maximum |
|
SC2 First Name |
AX |
Client Site Contact 2 fields – all are 20 character maximum |
|
SC2 Last Name |
AY |
|
|
SC2 Title |
AZ |
|
|
SC2 Salutation |
BA |
|
|
SC2 Phone |
BB |
(###)###-#### |
|
SC2 Ext. |
BC |
4 character maximum |
|
SC2 Cell Phone |
BD |
(###)###-#### |
|
SC2 Fax Phone |
BE |
(###)###-#### |
|
SC2 Email |
BF |
Valid email; 128 character maximum |
|
Dispatch Note |
BG |
40 character maximum. |
|
Agreement No |
BH |
15 character maximum. |
|
Agreement PO |
BI |
20 character maximum. |
|
Service Hours |
BJ |
10 character maximum. |
|
Sub Not to Exceed |
BK |
Decimal; cannot exceed ######.## |
|
PO Required |
BL |
Defaults to N; other valid option Y |
|
Client Not to Exceed |
BK |
Decimal; cannot exceed ######.## |
|
Gross Margin Id |
BN |
Valid Id must exist in database if entered |
|
Workorder Comments |
BO |
400 character maximum. |
|
Billing Comments |
BP |
1000 character maximum. |
|
Shipping Instructions |
BQ |
For COP; 240 character maximum |
|
Bill of Ladding Instructions |
BR |
For COP; 180 character maximum |
|
Location Id |
BS |
For COP; valid Id must exist in database if entered. |
|
GL Entity Id |
BT |
For COP; valid Id must exist in database if entered. |
|
Tax Group Id |
BU |
Valid Id must exist in database; used by COP and Quotes (if Job / WO not specified). |
|
Min. Ship Value |
BV |
###### (6 numeric characters, no decimal point); COP |
|
Ship Via Id |
BW |
For COP; valid Id must exist in database if entered. |
|
Proforma Table Id |
BX |
For COP; valid Id must exist in database if entered. |
|
Freight |
BY |
Prepaid is default, only other allowable values are Prepay&Add and Collect. For COP. |
|
C of A Reg. |
BZ |
Defaults to None; other valid options are W/Shpmnt and W/Shpmnt + Fax. For COP. |
|
Report Id |
CA |
Default Quote Report (RQTEFRM or RQTEFRM1) |
|
Quote Notes |
CB |
320 character maximum. |
|
Quote Small Print |
CC |
2000 character maximum. |
|
Web Profile Id |
CD |
CRMAnywhere / CSRAnywhere; a valid Id must exist in database if this field is completed. |
|
Custom Value 1 |
CE |
For CRMAnywhere / CSRAnywhere. If you selected a Web Profile Id, these must be completed as specified in the Profile. |
|
Custom Value 2 |
CF |
|
|
Custom Value 3 |
CG |
|
|
Custom Value 4 |
CH |
|
|
Custom Value 5 |
CI |
|
|
User Field 1 |
CJ |
Client Site User Fields; maximum 30 characters per field. |
|
User Field 2 |
CK |
|
|
User Field 3 |
CL |
|
|
User Field 4 |
CM |
|
|
User Field 5 |
CN |
|
|
Directions to Site |
CO |
160 character maximum. |