APPLIES TO RELEASE 1807NN. Columns may be slightly different for older releases
The attached Excel import template includes two sample customers to show sample data goes in each field plus additional information. A PDF is included to provide more information.
- You may copy/paste your data into the provided template and remove the sample customers if easier. Or you may copy Row 1 into your spreadsheet for column headers then ensure your information lines up.
- Do NOT delete or add columns as these are set up to import the correct date
- Do NOT combine data that is shown in two cells into one. Example: There are separate cells for the First and Last name.
- DO add missing information. It's much easier and faster to clean up your contacts here.
- DO delete blank rows.
- The columns highlighted in YELLOW require data entry and can NOT be left blank. The other columns only need to have data entered if desired for your import.
- The import can be in a CSV or Excel file format.
1> Column A: Facility ID - Go to Preferences > Company and locate your Facility ID. Enter this into the import spreadsheet and repeat it to the last record you'll import
2> Column D: - Customer ID (In some releases this may be Column C - Header is CUSTID). The last step in this section is critical that it is done.
- Go to Preferences -> Misc -> Numbering in Crate Pro 6 . In the 'Next Customer Starting Number' select a starting number higher than shown in that field and enter it into your spreadsheet. Increase each customer record sequentially.
- Do not duplicate any number that already exists in Crate Pro 6. Do not duplicate any number in your spreadsheet.
- Go to Preferences -> Misc -> Numbering in Crate Pro 6 . In the 'Next Customer Starting Number' enter a number higher than the last customer number in your spreadsheet. Duplicating any number will cause a customer to be over-written.
3> Column E: Customer - This is the company name of your customer. Enter it as you would want it to appear on Cut Lists, Quotes, Invoices, Work Orders, and other reports.
4> Column R : Active - Enter a '1' in this field for all active customers so they're available to select in designs and projects. If the customer is not active you can leave this field blank. The customer's information will be added to your customer list in Crate Pro 6 but will not be selectable when you create designs or projects. Within CP6 you can mark any customer active or inactive as needed.
OPTIONAL FIELDS: It's recommended to complete additional information prior to importing. Data in the other columns is not required but you may wish to enter more information. Please review this additional information.
- Column C: External ID - If your customer has a unique customer ID that is used in another system, this can be extremely helpful when field matching information from Crate Pro 6 to your external system
- Column H: State - This could be Province, District, or other information that would apply
- Column M: Email - ensure that only 1 email address is entered here. Additional email addresses can be added in the Notes field or in Other Locations in the customer record.
- Column Q: Type - Used to specify if the customer is Commercial or Retail.
- Column S & T: Taxable & Sales Tax Rate - In Preferences > Pricingyou can enter a default tax or VAT to be added to customer quotes and invoices in Projects. If your rate should be applied you will enter a '1' in this cell for each customer is applies to.
- In column T you can enter a different sales tax or vat rate for specific customers which will override your company default. Leave this field blank to use your company default
- Leave this column blank to use your company default settings
- Column U & V: Markups: Some customers may receive discounts or markups on the selling price which is in addition to the company markups you set in Preferences > Pricing. In the Preferences module, you set your company baseline markups over costs. Your markups can apply to material and labor OR either one alone. These fields in the Customer records will add onto or discount the markup set in Preferences.
- Leave blank if this customer will use pricing as set in Preferences
- values can be a positive or negative number but are always shown as a fraction
- Column W: This optional field is if the customer's company uses a billing code that they ask that you put in your Invoices. CP6 is not an accounting program but the feature to send quotes to your customers exists. The ability to send invoices from CP6 is to have a consistent format between quotes and invoicing and to make the process faster. You will need to export the information or create regular invoices in your own accounting system
- Delete the sample rows prior to import.
- In Customer module select Import/ Export at top of window
- Select either ALL or THIS ONE to proceed to import screen (There is no difference)
- Select 'Import' to proceed to the Interim Import Table
- Select 'Customers' to proceed to the customer import ( If you have additional addresses for your customers, such as Pick Up, Ship To, or Final destinations, you may also import their information as well. This is not addressed in this Solution Article)
- If you already have customers in your Crate Pro database, you may want to select to 'BackUp Database'. It has occurred where the Customer ID in the spreadsheet duplicates an existing Customer ID. You will overwrite the existing customer in your Crate Pro 6 if this occurs and lose their history.
- Select 'Get Interim Records' and select your prepared spreadsheet. (If your spreadsheets has multiple tabs you will select the tab your data is on)
- The Source and Target fields will line up if you did not modify the column order as shown in the sample template. Change the field order if you modified the template. Check the box to 'Don't import first record' if you did not delete the row with the column headers..
- Say OK then Import. Scroll through the table view to confirm data is correct and in the correct fields, then select 'Import Records'. If any fields are red or yellow or have ?, correct that data. When all fields are correct, select 'Import These Records'
- When the import is complete you should 'Clear Interim Records'. This clears the table data so if you import more customers or the Locations table, you will not import duplicated existing data.
Finishing Up - COMPLETE THESE STEPS
Customers must have a Unique ID that is used internally by the system. To ensure that if you add a customer by clicking on NEW in the Customer module, go to Preferences -> Misc -> Numbering in your Crate Pro 6 database and ensure the 'Next Customer Number' is GREATER than the highest number used in import.