Wiki

Case Status
Log In

Wiki

 
Data Conversion Guidelines
  • RSS Feed

Last modified on 2/1/2019 3:19 PM by User.

Tags:

Data Conversion Guidelines

Data Guidelines

When converting data over to OASIS, the following criteria must be met:

Customer Data

Customer data must have the following format:

Field Requirements Example
Customer ID REQUIRED, NO DUPLICATES! (Max 32 Characters); background ID OASIS uses CUST123
Customer Name REQUIRED! (Max 255 Characters) Customer 123
Display ID REQUIRED (Max 32 Characters); ID visible to the user (usually matches CustomerID) CUST123
Customer Role Optional, Must match a role you have set at Customers->File->Configuration->Roles Tab Distributor
Sold To/Ship To/Bill To Addresses
Name Optional, Ship/Bill/Sold To can be identical Customer 123
Line 1 Optional, Ship/Bill/Sold To can be identical 123 Main St.
Line 2 Optional, Ship/Bill/Sold To can be identical Suite 1
City Optional, Ship/Bill/Sold To can be identical Cabot
State Two Letters, Optional, Ship/Bill/Sold To can be identical AR
Zip Optional, Ship/Bill/Sold To can be identical 72023
Country Three Letters, Optional, Ship/Bill/Sold To can be identical USA
County Optional, Ship/Bill/Sold To can be identical Lonoke
Phone Optional, may have duplicate/additional entries 555-555-5555
Fax Optional, may have duplicate/additional entries 555-555-5555
Email Optional, may have duplicate/additional entries person@cust123.com
Website Optional, may have duplicate/additional entries www.cust123.com
Assigned Sales Account Optional (use OASIS user ID) KH
Parent Company Optional, unique Customer ID of the company that you want to assign to CUST456
Invoice Term Text Optional All values are in USD
Tax Status Optional, Either U (Unknown), T (Taxable), or E (Exempt) T
Tax ID Optional, use the ID of a Tax Account listed in Configuration->Tax Accounts AR Local

NOTE:

  • If your spreadsheet does not have Customer IDs, we will ask you to add them in. Customer IDs are crucial for OASIS to link transactions together, and while using names will work in some cases, IDs are much more compact (useful for reporting).
  • Be sure when entering IDs to keep them as short as possible, but also unique. Keep in mind that you will be using these IDs to enter customer information in Orders/Quotes, so understanding them is key. Some ideas for IDs:
    • Try a combination of characters from the name: My Test Customer -> MTC
    • Add City/ST abbreviations for customers with multiple branches: Cabot, AR -> MTC-CAB; Little Rock -> MTC-LR

Manufacturer Data

Field Requirements Example
Manufacturer ID REQUIRED, NO DUPLICATES! (Max 32 Characters) MYMFG
Manufacturer Name REQUIRED! (Max 255 Characters) My Manufacturer
Display ID REQUIRED (Max 32 Characters); ID visible to the user (usually matches CustomerID) MYMFG
MailTo/ShipTo/RemitTo  
Mail Line 1 Optional, Mail/Ship/Remit To can be identical 123 Main St.
Mail Line 2 Optional, Mail/Ship/Remit To can be identical Suite 1
Mail City Optional, Mail/Ship/Remit To can be identical Cabot
Mail State Two Letters, Optional, Mail/Ship/Remit To can be identical AR
Mail Zip Optional, Mail/Ship/Remit To can be identical 72023
Mail County Optional, Mail/Ship/Remit To can be identical Lonoke
Mail Country Three Letters, Optional, Mail/Ship/Remit To can be identical USA
Phone Optional, may have duplicate/additional entries 555-555-5555
Fax Optional, may have duplicate/additional entries 555-555-5555
Email Optional, may have duplicate/additional entries person@mymfg.com
Website Optional, may have duplicate/additional entries www.mymfg.com
Allowance Amount Optional (avoid currency formatting) 1000
Allowance Weight Optional  (avoid specific weight labels) 100
Standard Commission Optional (numbers only, no percent formatting) 10
Std Overage Split Optional (numbers only, no percent formatting)

75

NOTE: Just like Customers, Manufacturer Data must have IDs. Manufacturer IDs are used commonly by Quoters/CSRs when entering line items, so be sure they are easy to remember and understand (also, shorter is better).

Customer Accounts

Customer accounts are where you can link a customer to a manufacturer. Be sure when adding customer accounts to your spreadsheet that the data is formatted correctly (see bottom of help sheet for details).

Field Requirements

Example

Manufacturer ID REQUIRED! MYMFG
Customer ID REQUIRED! CUST123
Account Number REQUIRED! 123456

Representative Data

Field Requirements Example
Representative ID REQUIRED, NO DUPLICATES! (Max 32 Characters) AGENT1
Representative Name REQUIRED! (Max 255 Characters) Agent 1
Mail Name Optional, may be split into Mail/Bill if needed Agent 1
Mail Line 1 Optional, may be split into Mail/Bill if needed 123 Main St.
Mail Line 2 Optional, may be split into Mail/Bill if needed Suite 1
Mail City Optional, may be split into Mail/Bill if needed Cabot
Mail State Two Letters, Optional, may be split into Mail/Bill if needed AR
Mail Zip Optional, may be split into Mail/Bill if needed 72023
Mail Country Three Letters, Optional, may be split into Mail/Bill if needed USA
Mail County Optional, may be split into Mail/Bill if needed Lonoke
Phone Optional, may have duplicate/additional entries 555-555-5555
Fax Optional, may have duplicate/additional entries 555-555-5555
Website Optional, may have duplicate/additional entries www.cust123.com

Contacts

When converting contacts, they may be either intermingled with customer data, or as their own spreadsheet.

Field Requirements Example
Name REQUIRED! May be split into First/Middle/Last columns John Doe
Position Optional Sales
CustomerID
ManufacturerID
RepresentativeID
REQUIRED.  One of the fields Customer, Manufacturer or Representative ID is required to map this contact to an existing customer, manufacturer or representative. CUST123
MYMFG
AGENT1
Manufacturer ID Optional (If MFG Contact) MYMFG
Representative ID Optional (If Representative Contact) AGENT1
Phone Optional, may have duplicate/additional entries 555-555-5555
Email Optional, may have duplicate/additional entries john@abc.com
Mobile Optional, may have duplicate/additional entries 555-555-5555
Fax Optional, may have duplicate/additional entries 555-555-5555

Formatting

Data must be in spreadsheet format, with columns forming common data elements, and rows signifying separate transactions (see below). Spreadsheet may be an Excel document or CSV file.

Customer ID Customer Name Role City ST
CUST1 Customer ABC Distributor Cabot AR
ENG1 Engineer 123 Engineer Little Rock AR
ARCH1 Architect XYZ Architect Conway AR

NOTE: Data in any other format will be rejected due to technical limitations (unless specified otherwise)

When using the optional entries, have each in their own columns. 

When formatting data, you may use a single spreadsheet, or multiple spreadsheets if needed. When merging data into one spreadsheet, you can use one of the following templates:

I want my data on a single spreadsheet (Customers/Manufacturers/Reps, with contacts)

Your data will need a Type column, so you can specify if the line is for a customer/mfg/rep, etc. This will double as your "Role" for your customers as well. If you have multiple contacts for one customer/mfg, be sure to list the data twice (at least the ID, Name, and Type). See example below:

ID Name Type Contact ...
CUST1 Customer 123 Distributor Joe Brown  
CUSTABC Customer ABC Distributor John Doe  
CUSTABC Customer ABC Distributor Jane Doe  
ARCH1 Architect XYZ Architect Bob Green  
MYMFG My Manufacturer Manufacturer Gary Smith  
MFG999 Manufactuer 999 Manufacturer Amy Doe  

Download: OASISData1.xls

I want my data on multiple spreadsheets (Customers, Manufacturers, Reps all separate) with contacts mixed in

Your data will be clearer to read this way. Just like above, you will need to list your information twice if you have more than one contact at a company (at least the ID, Name, and Type). See example below:

ID Name Type Contact City ...
CUST1 Customer 123 Distributor Joe Brown Cabot  
CUSTABC Customer ABC Distributor John Doe Conway  
CUSTABC Customer ABC Distributor Jane Doe Conway  
ARCH1 Architect XYZ Architect Bob Green Sherwood  
CONT-CAB Contractor 777 - Cabot Contractor Gary Smith Cabot  
CONT-LR Contractor 777 - Little Rock Contractor Amy Doe Little Rock  
ID Name Contact Std Commission ...
MYMFG My Manufacturer Joe Brown 5  
MFG999 Manufacturer 999 John Doe 7  
AWEMFG Awesome Manufactuer Amy Doe 12  

You may want to download the attached spreadsheet as a template, which contains all the columns that have been listed in the examples above.

Download: OASISData2.xls

I want my data on multiple spreadsheets, contacts included

When converting contacts separately, be careful to make sure you link the Contacts back to the company they are associated with. If they have no company, then leave all three ID fields blank.

Customer ID Manufacturer ID Representative ID Contact Name Phone ...
CUST123     Jane Doe 555-555-5555  
CUST123     John Doe 555-555-5555  
CONT-LR     Joe Brown 555-555-5555  
CONT-CAB     Gray Smith 555-555-5555  
  MYMFG   Bill Brown 555-555-5555  
  MYMFG   Amy Smith 555-555-5555  
  MFG999   Mary White 555-555-5555  
    AGENT1 Frank Red 555-555-5555  

Download: OASISData3.xls

I have extra data I need to import

For certain items, we can convert them as long as you follow one of the templates below:

Customer Accounts: OASISCustomerAccounts.xls

Inventory (In-stock parts only): OASISInventory.xls

For items outside these, please call Ingen Software.