Import File Layout:
Header
Field | Reference | Data Type | Tab | Example | Description |
filetype | Required | | | SRWO | (File Type Code) |
linetype | Required | | | H | (Header Record) |
clnnum | Required [*] ** | Numeric | Header | | Client No |
ordnum | Required [*] ** | String (20) | Header | | Order No |
invnum | Required (I) | String (20) | Header | | Invoice No |
orddte | Required (I) | Date | Header | yyyy-mm-dd | Order Date |
dscrpt | Required (I) | String (75) | Header | | Order Description |
status | Required (I) ** (1) | Numeric | Header | 6-Quote; 7-Work Order; 8-Completed; 9-Route; 10-Contact; { 5-Void } | Order Status |
invtyp | Required (I) ** | Numeric | Header | | Order Type |
schdte | Required (I) | Date | Dispatch | yyyy-mm-dd | Scheduled Date |
jobnum | ** | Numeric | Header | | Job No |
lckedt | | Numeric | Header | 0=No; 1=Yes | Lock Edit |
shpnte | | Text | Header | | Work Order Note |
ntetxt | | Text | Header | | General Note |
dscavl | | Numeric (8,4) | Footer | 25 | Discount Available Percent |
depost | (2) | Numeric (8,4) | Footer | | Deposit Amount |
locnum | ** | Numeric | Location | | Location No |
ctcnme | (3) | String (50) | Location | | Location Contact |
phnnum | (3) | String (14) | Location | | Location Phone |
addrs1 | (3) | String (50) | Location | | Location Address 1 |
addrs2 | (3) | String (50) | Location | | Location Address 2 |
ctynme | (3) | String (50) | Location | | Location City |
state_ | (3) | String (2) | Location | | Location State |
zipcde | (3) | String (10) | Location | | Location Zip |
srvgeo | ** | Numeric | Location | | Service Area |
crsstr | | String (50) | Location | | Cross Street |
maploc | | String (10) | Location | | Map Location |
rutnum | (4) ** | Numeric | Location | | Service Route |
usrdf1 | | String (50) | Location | | User Def 1 |
usrdf2 | | String (50) | Location | | User Def 2 |
clldte | | Date | Dispatch | yyyy-mm-dd | Call Date |
clltim | | Time | Dispatch | hh:mm:ss | Call Time |
dspdte | | Date | Dispatch | yyyy-mm-dd | Dispatch Date |
dsptim | | Time | Dispatch | hh:mm:ss | Dispatch Time |
schtim | | Time | Dispatch | hh:mm:ss | Scheduled Time |
schhrs | | Numeric (5,2) | Dispatch | | Estimated Hours |
strdte | | Date | Dispatch | yyyy-mm-dd | Start Date |
strtim | | Time | Dispatch | hh:mm:ss | Start Time |
findte | | Date | Dispatch | yyyy-mm-dd | Complete Date |
fintim | | Time | Dispatch | hh:mm:ss | Complete Time |
acthrs | | Numeric (5,2) | Dispatch | | Actual Hours |
priort | ** | Numeric | Dispatch | [1-8] | Priority Level |
empnum | ** | Numeric | Dispatch | | Technician |
invdte | | Date | Billing | yyyy-mm-dd | Invoice Date |
duedte | | Date | Billing | yyyy-mm-dd | Due Date |
dscdte | | Date | Billing | yyyy-mm-dd | Discount Date |
refnum | | String (20) | Billing | | Reference No |
invsrc | ** | Numeric | Billing | | Invoice Source |
pchord | | String (20) | Billing | | Client PO No |
exmnum | | String (30) | Billing | | Resale No |
slspsn | ** | Numeric | Billing | | Salesperson |
taxdst | ** | Numeric | Billing | 0=Default from Client; -1=Default from Client Location | Tax District |
pmttyp | ** | Numeric | Billing | 1-Cash; 2-Check; 3-Credit Card; 4-Bill; 5-Insurance; 6-Other | Payment Type |
crdnum | (5) | String (64) | Billing | | Ref 1/ Credit Card No |
crdnme | | String (30) | Billing | | Ref 2/Credit Card Name |
expdte | | Date | Billing | yyyy-mm-dd | Ref Date / Card Exp Date |
constd | (6) | Date | Billing | yyyy-mm-dd | Route Contract Start Date |
conend | (6) | Date | Billing | yyyy-mm-dd | Route Contract Exp Date |
bildte | (6)(7) | Date | Billing | yyyy-mm-dd | Route Next Bill Date |
bilcyc | (6)(7) ** | Numeric | Billing | 1-Weekly; 2-Bi-weekly; 3-Monthly; 4-Bi-monthly; 5-Quarterly; 6-Semi-annually; 7-Annually | Route Billing Cycle |
taxmrt | | decimal | Invoice Details | | Taxable Markup % |
txadmk | | decimal | Invoice Details | | Taxable Markup $ |
taxovr | | decimal | Invoice Details | | Taxable Billing Amount |
taxlck | | Numeric | Invoice Details | 0=No;1=Yes | Taxable Locked |
ntxmrt | | Numeric (10,2) | Invoice Details | | Non-Taxable Markup % |
ntadmk | | Numeric (10,2) | Invoice Details | | Non-Taxable Markup $ |
ntxovr | | Numeric (10,2) | Invoice Details | | Non-Taxable Billing Amount |
ntxlck | | Numeric | Invoice Details | 0=No;1=Yes | Non-Taxable Locked |
plcnum | | String (30) | Insurance | | Policy No |
plcnme | | String (50) | Insurance | | Policy Holder |
plcad1 | | String (50) | Insurance | | Policy Address 1 |
plcad2 | | String (50) | Insurance | | Policy Address 2 |
plccty | | String (60) | Insurance | | Policy City,State, Zip |
plcphn | | String (30) | Insurance | | Policy Phone |
plctyp | | String (50) | Insurance | | Policy Type |
dedabl | | Numeric (10,2) | Insurance | | Policy Deductible Amt |
cmpnme | | String (50) | Insurance | | Policy Company Name |
adjnme | | String (50) | Insurance | | Policy Adjuster Name |
adjad1 | | String (50) | Insurance | | Policy Adjuster Address 1 |
adjad2 | | String (50) | Insurance | | Policy Adjuster Address 2 |
adjcty | | String (60) | Insurance | | Policy Adjuster City, State, Zip |
adjphn | | String (14) | Insurance | | Policy Adjuster Phone |
Required (I) - required for Inserts only
[*] Key Fields - the combination of theses fields must be unique
** Validated Field - value must exist in the respective lookup table in Sage 100 Contractor
(1) Updating to 'Void' status is only allowed when an order already exists in Sage and has a status of 6, 7 or 10
(2) Deposit Amount - creates a credit invoice
(3) If null or left out of header mapping the field will be derived from the [locnum] and if the [locnum] is blank then it will be derived from the [clientno]
(4) Service Route is only available when the Order Status is [9-Route]
(5) Field Encryption - this field is encrypted by default, in order to import a value into this field, Encryption must be turned OFF
a) Go To 7-1 Company Information
b) Click Options, then Database Security Encryption
c) Select "OFF"
(6) Field is required when the Order Status [9-Route]
(7) Field is required when the Order Status is [10-Contract]
Invoice Details
filetype | Required | | | SRWO | (File Type Code) |
linetype | Required | | | D | (Detail Record) |
clnnum | Required [*] ** | Numeric | Header | | Client No |
ordnum | Required [*] | String (20) | Header | 12345 | Order No |
linnum | [*] (1) | Numeric | Invoice Details | 1 | Line No |
asmnum | ** (2) | Numeric | Invoice Details | | Assembly No |
asmchk | ** (3) | String (1) | Invoice Details | L=Assembly; R=Assembly Labor; P=Part; A=Assembly; or Blank | Assembly Checkmark |
prtnum | ** (2) | Numeric | Invoice Details | | Part No |
dscrpt | Required (4) | String (75) | Invoice Details | | Item Description |
untdsc | (4) | String (10) | Invoice Details | | Unit of Measure |
prtqty | | Numeric (8,4) | Invoice Details | | Quantity |
prtprc | (4) | Numeric (9,6) | Invoice Details | | Price |
tktnum | | String (20) | Invoice Details | | Ticket No |
curbll | | Numeric (10,2) | Invoice Details | | Current Billed Amount |
csttyp | (4) ** | Numeric | Invoice Details | 1-Material; 2-Labor; 3-Equipment; 4-Subcontract; 5-Other; 6-Udf-6; 7-Udf-7; 8-Udf-8; 9-Udf-9 | Cost Type |
slstax | ** | String (1) | Invoice Details | Y-Yes; N-No (Default = 'No') | Taxable |
actnum | ** | Numeric | Invoice Details | | Account |
subact | ** | Numeric | Invoice Details | | Subaccount |
invloc | ** | Numeric | Invoice Details | 0 = Default Part Location | Inventory Location |
sernum | | String (30) | Invoice Details | | Serial Number |
sitloc | | String (50) | Invoice Details | | Site Location |
eqpnum | ** | Numeric | Invoice Details | | Equipment |
usrdf1 | | String (50) | Invoice Details | | User Defined |
shpnte | | Text | Invoice Details | | Shop Notes |
ntetxt | | Text | Invoice Details | | Owner Notes |
[*] Key Fields - the combination of theses fields must be unique
** Validated Field - value must exist in the respective lookup table in Sage 100 Contractor
(1) Null/Empty = line no will be automatically derived; Specified line no must be unique based on the Key pair
(2) Assembly No must be blank if Part No is provided; Part No must be blank if Assembly No is provided
(3) Only P or A are allowed if an Assembly No is provided
(4) Null/Empty = If null or excluded from mapping and a Part No is entered, the field value will default from the Part No record
Dispatch Schedule
filetype | Required | | | SRWO | (File Type Code) |
linetype | Required | | | DS | (Detail Record) |
clnnum | Required [*] | String (20) | Header | 12345 | Client No |
ordnum | Required [*] | String (20) | Header | 12345 | Order No |
linnum | [*] (1) | Numeric | Dispatch | 1 | Line No |
empnum | Required [*] (2) ** | Numeric | Dispatch | | Employee No |
eqpnum | Required (2) ** | Numeric | Dispatch | | Equipment No |
vndnum | Required (2) ** | Numeric | Dispatch | | Vendor No |
priort | Required ** | Numeric | Dispatch | [1-8] | Priority |
schdte | | Date | Dispatch | yyyy-mm-dd | Scheduled Date |
schstr | | Time | Dispatch | hh:mm:ss | Scheduled Start |
schfin | | Time | Dispatch | hh:mm:ss | Scheduled Finish |
esthrs | | decimal (5,2) | Dispatch | | Scheduled Hours |
tvltim | | String (4) | Dispatch | | Travel Time |
actstr | | Time | Dispatch | hh:mm:ss | Actual Start Time |
findte | | Date | Dispatch | yyyy-mm-dd | Actual Finish Date |
actfin | | Time | Dispatch | hh:mm:ss | Actual Finish Time |
acthrs | | decimal (5,2) | Dispatch | | Actual Hours |
bildte | | Date | Dispatch | yyyy-mm-dd | Billed Date |
usrdf1 | | String (50) | Dispatch | | User Defined |
[*] Key Fields - the combination of theses fields must be unique
** Validated Field - value must exist in the respective lookup table in Sage 100 Contractor
(1) Blank = line no will be automatically derived; If specified line no must be unique based on the Key pair
(2) Either an employee, equipment or vendor number must be specified
File Example:
!SRWO,H,clnnum,ordnum,invnum,orddte,dscrpt,status,invtyp,schdte,jobnum,lckedt,shpnte,ntetxt,dscavl,depost,locnum,ctcnme,phnnum,addrs1,addrs2,ctynme,state_,zipcde,srvgeo,crsstr,maploc,rutnum,usrdf1,usrdf2,clldte,clltim,dspdte,dsptim,schtim,schhrs,strdte,strtim,findte,fintim,acthrs,priort,empnum,invdte,duedte,dscdte,refnum,invsrc,pchord,exmnum,slspsn,taxdst,pmttyp,crdnum,crdnme,expdte,constd,conend,bildte,bilcyc,taxmrt,txadmk,taxovr,taxlck,ntxmrt,ntadmk,ntxovr,ntxlck,plcnum,plcnme,plcad1,plcad2,plccty,plcphn,plctyp,dedabl,cmpnme,adjnme,adjad1,adjad2,adjcty,adjphn
!SRWO,D,clnnum,ordnum,linnum,asmnum,asmchk,prtnum,dscrpt,untdsc,prtqty,prtprc,tktnum,curbll,csttyp,slstax,actnum,subact,invloc,sernum,sitloc,eqpnum,usrdf1,shpnte,ntetxt
!SRWO,DS,clnnum,ordnum,linnum,empnum,eqpnum,vndnum,priort,schdte,schstr,schfin,esthrs,tvltim,actstr,findte,actfin,acthrs,bildte,usrdf1
SRWO,H,123,"InvABC","2","5/20/2017","Test Order",7,1,"5/20/2017",,,"","",,,,"Jim Ray","777-888-9595","GE Corp Services","45 Willow Park Ln","Torrence","CA","92254",,"","",,"","","","","","","",,"","","","",,,,"","","","",,"","",,,,"","","","","","",,,,125,1,100,50,,,"","","","","","","",,"","","","","",""
SRWO,D,123,"InvABC",1,,"",,"test line 1","ea",1,250.00,"",,,"N",4010,,,"","",,"","","notes line 1"
SRWO,D,123,"InvABC",2,,"",,"test line 2","ea",3,50.00,"",,,"N",4010,,,"","",,"","","notes line 2"