This guide presents the steps required to import Fixed asset records into Business Central, as well as their associated acquisition cost and accumulated depreciation amounts, using a single import template in the Excel Importer app.
Excel Importer - Importing Fixed Assets Information
Menu
Fixed Assets Upload Spreadsheet
In the following steps we will be creating an Excel mapping template to map the columns from data spreadsheet shown above. The spreadsheet contains the Fixed Asset information as well as their cost and accumulated depreciation amounts.
1.1.1 Creating the FA Excel Mapping Template
Navigation in the following steps assume you're logged in to Business Central with the Business Manager role.
Click on the navigation menu item popup Finance
Click on the navigation menu item Fixed Assets
Click on the navigation menu item popup Process
Click on the navigation menu item Import from Excel
Click on the field Select a template to use for the import.
Click on New to start creating a new template
- Fill in the fields in the General tab
Click on the field Code - Enter FAOB. - Click on the field Description - Enter Description.
- In the First Data Row field, enter the first row of data from the Excel File. In this example, data starts on row number 2 - Enter First Data Row.
Next, the columns on the spreadsheets must be mapped. The "Select Fields" function will help you find the fields and easily assign the Excel column id they map to.
Click on the navigation menu item Select Fields
Click on the navigation menu item Edit List
Start mapping all the following fields to the respective column Id from the Excel File.
- Enter A in the Excel Column Id for field "No." - Enter B in Excel Column ID for Field "Description".
- Enter C in the Excel Column Id for field "Depreciation Book Code" - Enter D in the Excel Column Id for field "Depreciation Method".
- Enter E in the Excel Column Id for field "Depreciation Starting Date" - Enter F in the Excel Column Id for field "No. of Depreciation Years".
- Enter G in the Excel Column Id for field "FA Posting Group" - Enter H in the Excel Column Id for field "Main Asset No.".
- Enter I in the Excel Column Id for field "Acquisition Cost" - Enter J in the Excel Column Id for field "Accumulated Depr. (Total)".
- Enter K in the Excel Column Id for field "Last Depreciation Date".
Click OK when done mapping all the possible fields
It is also possible to map field by manually adding new mapping lines, and changing the number in "Processing Order" to control the sorting.
For example, the "Acquisition Date" field should also be mapped to Column E from the spreadsheet. Follow steps below to add this to the mapping list under "Depreciation Starting Date".
Click on the row above which you want to insert the new mapping.
Click on the menu item New Line
Click on the cell Processing Order with the value 0
Enter Processing Order.
Click on the lookup button in the cell No.
Click on Search
Search for "Acquisition" to find the Acquisition Date field.
Click on the link in cell No. with the value _T70253932_F30
Click on the cell Excel Column ID
Enter the Excel column ID containing the Acquisition Date
A confirmation message will warn you that you're mapping the same Excel column to two different fields. Click Yes to continue.
The last two columns on the spreadsheet must be mapped to dimensions.
Select "Dimension" in the field "Type"
Click on the lookup button in the cell No.
Click on the link in cell Code with the value DEPARTMENT
Click on the cell Excel Column ID
Enter Excel Column ID.
Repeat the steps to map column R to the Customer Group dimension.
Click on the lookup button in the cell No.
Click on the link in cell Code with the value CUSTOMERGROUP
Click on the cell Excel Column ID
Enter Excel Column ID. Press the TAB key.
Update fixed assets with new values (YES/NO). Default: NO. Set this to YES if you'd like the import to update existing Fixed Assets. NO will skip any Fixed Assets that already exist.
Balances journal type (FA Journal/General Journal). Default: FA Journal. Enter General Journal if you want the Acquisition Cost and/or Depreciation Amounts to be imported into the FA General Journal (If G/L Integration was enabled in the Depreciation Book)
You're now done creating the template. Click the Back button to close the page. Click Ok on the next screen to close the Excel Import Mapping List".
1.1.2 Importing the Fixed Assets Spreadsheet
We will now import the mapped Excel file using the template we have just created.
If you are importing Fixed Assets balances to the Fixed Asset Journal, please make sure you disable the G/L integration for Acquisition Cost and Depreciation on the Depreciation Book Card.
For this demonstration, we will disable the G/L integration. Please navigate to the depreication book card for depreciation books to be used for the import.
Click on the toggle field G/L Integration - Acq. Cost
Click on the toggle field G/L Integration - Depreciation
Go back to the Fixed Assets page to Import the Fixed assets file.
Click on the navigation menu item popup Finance
Click on the navigation menu item Fixed Assets
Click on the navigation menu item popup Process
Click on the navigation menu item Import from Excel
With template code "FAOB" selected, click to select the Excel Filename
Click on Choose to select the Fixed Assets Excel file from your file explorer.
The worksheet name will default to the first sheet on the Excel file which can be optionally changed. Click OK to start the import.
Click OK to close the confirmation message.
You can verify the imported Fixed Assets.
The Acquisition and Accumulated Depreciation balances have been imported to the Fixed Assets Journal and they can now be posted.
Click to Search for the Fixed Asset Journals page.
Enter Fixed Asset Journal.
Click on Fixed Asset Journals Tasks
The import will insert a line for each Acquisition and Depreciation amount ordered by the Asset number. Verify all the lines if necessary then click Post to post the journal.
Click Yes to post the journal
Click OK to close the confirmation message.
This is the end of the FIxed Asset Import guide. You have now been able to create an Excel Importer template for importing Fixed Asset records as well as their opening balances and related information.
- End -