One of the most difficult decisions that has to be made during a new system implementation is how much and what kind of data to migrate from your old system to the new. How many years do we bring over? Do we bring over detailed or summarized data? Is it reconciled? We will have access to old system for historical reporting purposes? These are all questions that are posed and answers that are debated during the process of making the decision.
There are, however, a few data decisions where there can be no debate. You have to bring over your Chart of Accounts. You may choose to take the opportunity to restructure your Chart of Accounts or to get rid of inactive accounts but you have to have your Chart of Accounts. The other data requirements are your Employees, Vendors and Customers. There still remains the option of do you add these records via manual entry or do you upload them. Using the manual entry method can provide the opportunity for training, since in the future, you will be adding these types of records one or just a few at a time.
In Acumatica ERP, it is possible to create customized imports that allow you import data records for just about any master table in the database. In this blog post, we are going to see how to create an Import Scenario to import Customer master records into the database.
The first step is to create your Customer template in Excel. Create your spreadsheet using the following required fields as column headings.
- Customer ID
- Customer Name
- Phone
- Address Line 1
- Address Line 2
- City
- Country
- State
- Postal Code
- Customer Class
- Save your template. You can name it Customer template.
The next step is to create the provider. Open the providers form by going to System > Integration > Manage > Data Providers. In the Name field, name your Provider. Use the selector in the Provider Type field to select Excel Provider from the dropdown list. Save your new Provider.
Attach your template to the Provider using the Files button in the top right hand corner of the screen. Press the Files button and then the Plus sign (+) to reveal the Upload File screen. Select the Browse button and navigate to the location of saved Excel file and press Upload.
Your file will be attached to your Provider. Press the Save button to update your Provider.
Now you need to select your Source Object by clicking on the Schema tab. In the Source Objects grid, click on the Fill Schema Objects button on the tool bar. Check the Active check box next to the Vendor data load object and hit the Save button.
In the Source Fields grid, click the Fill Schema Fields button on the tool bar and click the Save button to save your changes. The column headings from your Excel template will appear in the Source Fields grid.
The next step is to create the Import Scenario. Navigate to the Import Scenarios by going to System > Integration > Process > Import Scenario. Create a new Import Scenario by entering a description in the Name field. Use the selector in the Screen Name field to navigate to the Finance>Accounts Receivable>Work Area>Manage>Customers menu location.
Use the selector in the Provider field to select the newly created Vendor Provider.
Use the selector in the Provider Object field to select the sheet in the Excel workbook where the data resides that you will upload. Click the Save button to save your changes. Next you will need to create the mappings between the fields in your Excel spreadsheet and the Acumatica ERP fields.
On the Mapping tab, click the new line (+ sign) to add a new line to the mapping grid. In the Target Object field, use the dropdown arrow to find and select the target (the name of the functional object of the graph the Acumatica ERP form is based on. The functional object can be the Summary area, the Details or any of the tabs or boxes available on the form). Use the dropdown arrow in the Field/Action Name field to look up and select the name of the field (or action) of the functional object selected in the Target Object column. Make no changes to the Commit field. In the Source Field/Value field use the dropdown arrow to select the field in the Excel spreadsheet to be imported to generate the value for the internal field. Repeat this for all columns in your spreadsheet to complete the mappings.
When all of the fields from the Excel spreadsheet have been mapped to fields in Acumatica, the last line in the mapping must be a line that saves the record before moving on to process the next line in the spreadsheet. In the Target Object field, select Vendor Summary. In the Field/Action Name select <Action: Save>.
Now you are ready to upload the file and prepare it to be imported into the database. Navigate to Import by Scenario. Click on System > Integration > Process > Import by Scenario. In the Name field, use the selector to select the Vendor upload scenario.
Click the Prepare button on the tool bar to bring the file into the process grid.
Click the Import button in the tool bar to import the records into the database. When the records are imported successfully, you will see a green check mark next to the Get File button on the tool bar and check marks in the Process field of each record as shown below.
BRETT MARTIN | Business Software Consultant
Brett has over 15 years of experience in accounting holding positions of Controller, Assistant Controller, and Accounting Manager in various industries including federal government contracting, not for profit bio-tech, real estate, motion picture distribution, and motion picture production. In addition, Brett has 10 years of experience implementing accounting systems such as Acumatica, Microsoft Dynamics NAV, JAMIS Prime ERP, JAMIS Financials, Deltek Costpoint, and Deltek GCS Premier. Prior to KTL, he worked as a Senior Business Consultant for JAMIS Software Corporation implementing JAMIS Prime ERP and JAMIS Financials. Brett has studied accounting and computer information systems at Morehouse and Montgomery Colleges.
