Sage 100 Contractor 11-2 Work Order / Invoices / Credits
Overview:
Import Work Order / Invoices / Credits directly into Sage 100 Contractor. Simply download your work order / invoices / credits into Excel, review and apply work order information and import directly into Sage 100 Contractor 11-2 Work Order / Invoices / Credits.
Are you tired of manually keying in Work Order / Invoices / Credits one by one into Sage 100 Contractor? Would you rather download your work order / invoices / credits, fill in the blanks in Excel and import the details directly into Sage 100 Contractor? The following integration template allows you to do just that. It mimics every feature and function of the 11-2 Work Order / Invoices / Credits data entry form.
Key Features:
- Download Work Order / Invoices / Credits into Excel
- Edit key work order information in Excel
- Bulk import work order / invoices / credits directly into Sage 100 Contractor
- Supports all native features and functions of the 11-2 Work Order / Invoices / Credits Entry Form
Import Template Download:
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"