In previous blog posts, we have learned how two ways to get fixed asset purchases into Acumatica ERP. The first was entering them manually and the other was to create the fixed asset records from purchases. Both of these methods work and are extremely efficient when entering a few fixed assets. In some situations, your company may purchase many fixed assets at the same time which would make manual entry a long laborious process. Using the built-in function of Integration Services in Acumatica ERP, one can quickly create a routine that will import multiple fixed assets into the system using data from an Excel spreadsheet.
The first step is to create your Fixed Asset template in Excel. Create your spreadsheet using the following required fields as column headings.
- Asset Class
- Property Type
- Asset Type
- Receipt Date
- Original Acquisition Cost
- Department
- Receipt Date 2
- Fixed Asset Account
- Fixed Asset Accrual Account
- Accumulated Depreciation Account
- Gain Account
- Loss Account
- Book
- Depreciate From
Save your template. You can name it Fixed Asset 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 drop-down 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 toolbar. Check the Active check box next to the Fixed Asset data load object and hit the Save button.
In the Source Fields grid, click the Fill Schema Fields button on the toolbar 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 Manage Fixed Assets menu location.
Use the selector in the Provider field to select the newly created Fixed Asset 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 drop-down 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 drop-down 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 drop-down 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 Fixed Assets. 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 Fixed Asset Upload scenario.
Click the Prepare button on the toolbar to bring the file into the process grid.
Click the Import button in the toolbar 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 toolbar 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 biotech, 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.
