Dynamics Community Forum Thread Details

Importing fixed assets using T-SQL

Hello Richard,

There's a few questions to unpack above but before getting to that, it is not recommended to try and import assets directly into the table with FA. If the values are imported wrong, it can basically require that the asset be deleted entirely and in the case of the FA00902, getting the correct records populated would be very tricky unless you already know what the exact values should be. Further at a minimum, you would need to import 2 records into the FA00902 for each asset (the FAADD COST\CLEARING distributions) however there can be many more needed (typically an additional 4) if you are importing assets with LTD and YTD depreciation amounts.

EConnect and IM (through the EConnect adapter) can import FA information. .If you need to import multiple books for one asset, then you would import the asset with the FA general destination, and the books with the FABook destination. If they do not have EConnect\ IM there is a built in tool in GP for importing fixed assets (Tools>Utilities>Fixed Assets>Asset Import Export). If you open the tools window and go to the help file (help about this window), all of the field mappings can be found under the 4 related items sections directly from the main help for this window.

If your importing book information, instead of letting GP default in book information for the assets, it is recommended to disable auto add book (go to tools>setup>fixed assets>company, and disable auto add book).

Moving on.

FA00100 <= This is your asset general window. The FAINDEX from this table is what ties all the other tables to the asset and if the FAINDEX is wrong, there will be huge issues including cross linked asset records.

FA00200 <= This is your asset book window. This window contains all of the book related items INCLUDING original life years\days, remaining life years\days, yearly depreciation rate for the current year, ltd depreciation\ ytd depreciation\ net book value\ etc. Any mistake manually importing this table can and will negatively affect the asset for the entire life of the asset. If you import to this table there is a second table that will be covered later that MUST be populated\ updated as well or the asset book record is immediately considered damaged.

FA00400 <= This is your account assignment to an asset. Note there is no value for bookid. This answers one of your additional questions (different accounts per book). An asset can only have one set of accounts, not one set of accounts per book. In addition, unless you are using reporting ledgers only one book can be interfaced to the GL by default. There are ways around it but very special care needs to be taken (this is a feature that once turned on CANNOT be disabled, and functionally creates 3 unique GL ledgers that do not interact with each other at all).

FA00902 <= This is the inquiry>Fixed assets>Financial detail window and keeps track of all distributions for asset\books. This table will be updated for anything you do to an asset\book record including the add. If you manually import fixed assets and do not import records to this table as well, the asset\book is basically damaged as even the add for the asset will create records in this table. If the values imported into this table are not correct (wrong dates\ years,wrong depreciation start\end dates, wrong amounts, etc) you would need to delete the asset\book and restart the book again. At a very minimum, every asset\book will have two records created to this table (cost\clearing), however a new asset could also have 4 additional records if you import with LTD\YTD depreciation. Depending on exactly situation there could also potentially be additional distribution records that would need to be created as well when an asset\book record is created.

There are a bunch of other tables as well however most of these don't auto populate when doing a basic add of an asset, or if they do will just fix themselves (create a default record) when you open the window (like the FA01400).

Again I would HIGHLY recommend using the asset import\export tool in GP or use FA import through EConnect\ integration manager instead of trying to manually import into these tables. in the short term it'll likely prove to be quicker than making sure the asset import through direct table imports is correct and in the long term, it will just be safer. if theres any goofs it may not be until the very end of the assets life that it is caught or if you ever need to reset life on the asset for any reason, any goofs could cause the entire process to go sideways into an unrecoverable state (requiring the asset book records to need to be deleted or the entire asset itself).

Quick Links

Leaderboard