Tools

Field Mapping--Customers & Prospects

Author: Reference Number: AA-01339 Created: 2011-03-01 09:35 Last Updated: 2016-09-14 18:16

0 Rating/ Voters

To import your Customer list create a spreadsheet with these column names (see the Column Name column on the left).

Column Name	OneSource Field Type	Field Lenght or Numeric Range	Sample Value	Default Value	Notes
CustomerNumber	Text	15	A12345 123456789		* REQUIRED * No duplicate values allowed. Each customer number must be unique. Cannot have any customer numbers with “or * characters.
CompanyName	Text	50	XYZ Music Company		* Required *
Salutation	Text	50	Mr. Ms. Dr.
FirstName	Text	50	John
LastName	Text	Text	Doe
ContactedNameCombined	Text	N/A	John Doe		* This field does NOT exist in OneSource * However, if your existing accounting application only provides a way to export contact names information into one field, this column can be used to store this information. In this case, our import technician will run an algorithm to attempt to automatically parse the proper data into the proper fields in OneSource. PLEASE NOTE: This algorithm is not 100% accurate. If possible, manually parsing the data into the appropriate field/column will provide a more accurate final result.
ContactTitle	Text	50	President
AddressLine1	Text	50	12345 Somewhere Street		Highly Recommended
AddressLine2	Text	50
City	Text	50	Anaheim		Highly Recommended
State/Province	Text	25	Ca		Highly Recommended
Region	Text	50	Southwest
Zip/Postal Code	Text	20	92860		Highly Recommende *** Currently 75, but soon to be changed to 20.
City/State/ZipCombined	Text	N/A			* This field does NOT exist in OneSource * However, if your existing accounting application only provides a way to export city, state, zip code information into one field, this column can be used to store this information. In this case, our import technician will run an algorithm to attempt to automatically parse the proper data into the proper fields in OneSource. PLEASE NOTE: This algorithm is not 100% accurate. If possible, manually parsing the data into the appropriate field/column will provide a more accurate final result.
Country	Text	20	USA
Phone	Text	20	8662044997		It is much easier to search by phone # if the data is entered without any dashes or parenthesis. A number entered 1234567890 will typically be formatted as such in OneSource: (123) 456-7890.
Extension	Text	10	345
Fax	Text	20	8664290690
TypeOfCustomer	Text	50	Customer		Highly Recommended
SubTypeOfCustomer	Text	50	Piano's
Status	Text	50	Happy
AddressType	Text	50	Both		* SPECIFIC VALUES REQUIRED * Must be one of these exact values: Bill To, Ship To, Both, Quote (Actual Field Name: Customer Record Type) Note If Customer X has more than 1 address, such as a Billing Address and a Ship To Address (or even multiple Ship To Addresses) each additional address needs to be put on the list as a separate record, with the AddressType set to Ship To and the related Bill To Address Customer Number put in the BillToCustomerNumber field below.
BillToCustomerNumber	Text	15			* VERY IMPORTANT * If the record has a SINGLE set of Ship To fields that are different than the Bill To (which would be the address fields above) – then please make use the various Ship To fields below. If, on the other hand, several of the records you are importing are really Ship To Address and need to be LINKED to a particular BILL TO Address record, please hand enter the actual Bill To Customer Number (which will typically be the Customer Number of the particular record that you want to link the Ship To record to).
IsShipToAddressSameAsBillTo	Yes/No		Yes/No True/False -1/0	No	* VERY IMPORTANT * If value is YES here, then the Address Type (above) will be set to “Both” (meaning that the Bill To Address will be Both the Bill To and Ship To Address – and both will be maintained with the same address. All Ship To address fields below will be ignored and not imported. If value is NO here, then all Ship To fields will be imported into an ADDITIONAL customer record with the Address Type value being set to Ship To and the associated Bill To Customer Number will be set to the same as the Customer Number for the Bill To address fields.
AccountRepresentative	Text	50	Jane Smith
Salesperson	Text	50	abc1235		* Related OneSource Primary Key value required here * The Salesperson is actually setup on the User Security form. This value MUST be the actual username setup in the User Security form. While the Full Name will be displayed, the actual data that is entered and linked to the User Security form is unique user name setup in OneSource.
ProductsServices	Text	255	Pianos, Guitars,
CompanyEmail	Text	100	sales@xyzmusic.com
Website	Text	100	http://www.xy.com/
SalesTaxCode	Text	10	CA-OC		Required This is the Code that your existing system uses to identify a particular Sales Tax rate/code.
SalesTaxCodeRate	Percent	Single	.04		Required (However, if the prep has been completed on your OneSource Company Data file, and you have set up the various 'Tax Codes' and the associated rates, at least one for every Sales Tax Code found in your exported Customer list, then this rate will be not need to be in your exported Customer list). Example value: .04 = 4%
TaxExempt	Yes/No		Yes, No True, False -1, 0		Required (if you have any) Is the Customer Tax Exempt?
TaxExemptNumber	Tex	50	E90001
FederalTaxID	Text	50	94-030909
CustomPricingTemplateID	Number	Long Integer	1		* NEW VALUES MAY BE REQUIRED * If you are planning to make use of Custom Pricing Templates on the GoLive date, and would like the Customer records being imported to link to certain Custom Pricing Templates, you will need to create Custom Pricing Templates in your OneSource Pilot.mdb database file (in advance) and then manually the appropriate BuyingGroupID (which will have been auto created and stored in the tblBuyingGroups table when you make the templates in OneSource).
DefaultPricingMethod	Text	30	Discount	Discount	* SPECIFIC VALUES REQUIRED * If you plan to make use of the Default Pricing Method feature, one of the following values must be entered here: Discount, Code Discount, Markup, Multiplier, Matrix by Code, Matrix by Qty, Matrix by Choice, First, Markup by Divisor
DefaultDiscountPercent	Number	Single	.10	0%	.10 = 10%
DefaultMarkupPercent	Number	Single	.10	0%	.10 = 10%
DefaultMultiplierPercent	Number	Single	.10	0%	.10 = 10%
DefaultPriceCode	Text	10	A
PaymentTerms	Text	20	2% 10 Net 30		Required. Enter the payment terms that you want for each customer - exactly the way they will read when you set up the payment terms later on in OneSource.
CreditLimit	Number	Currency	$5,000	0	Recommended.
CreditPastDueDays	Number	LOng Integer	30	30	Recommended. How many days does this customer have before their Invoices are considered Past Due?
COD	Yes/No		Yes, No True, False-1, 0	No	Is this Customer on COD terms? (Cash on Delivery)
CreditHold	Yes/No		No	No	Is this Customer on Credit Hold?
AdminCreditHold	Yes/No		No	No	Is this Customer on Admin Credit Hold? (Should all printing of Invoices, Picking Tickets, Packing Slips, etc be halted unless authorized by a manager?)
ReceivableNotes	Memo	65,535
FinanceCharges	Yes/No		Yes, No True, False -1, 0		Enter Yes for each customer that you want the system to calculate Finance Charges.
ShippedVia	50		UPS
FreightNotes	Memo
SalesNotes	Memo				This text is displayed in a special POP UP form when a Sales Order is created (when option turned on).
SourceOfContact	Text	50	Referral
DateAdded	Date		12/1/2005 12/1/05 12-1-2005		We will add this value.
DateEdited	Date		12/1/2005 12/1/05 12-1-2005
AdditionalInternalNotes	Memo				This field is a good place to concatenate values from other fields being imported that is valuable to you but where no applicable fields exist in OneSource.