Delete FA records

seckpin

Background:
NAV 4 SP3, on SQL
In use since year 2007
Number of FA Cards > 700K (and don't even ask about the number of FA Ledger Entries with all the Depreciation postings)

The sheer volume of the FA Cards and transactions are making the system extremely slow whenever the users access the FA module. Running the depreciation calculation and posting it takes ages. Even just opening up the FA List takes a long time to load.

We have come up with some customisation to cut down the number of new FA Cards to be created in future. The problem now is that without cleaning up the old data, the performance will not improve.

Our vendor's suggestion is to delete away the FA Cards and all related transactions (which of course include everything such as FA Ledger Entries, G/L Entries, VAT Entries, etc.). I raised my concerns in possible database integrity issue and also the repercussion, if any, on the gaps in Entry No. in those tables. No matter how much we test the system after deletion, there may still be a possibility of missing out on something and the problem only surfaces after a few months/years down the road. (Oh yes, i am rather risk averse)

However, our vendor is very confident that this can be done without any problems down the road. They claimed that they had in fact done data deletion for another customer before too. I guess our company just have to take their words for it...

I hope all gurus here can provide some valuable input on this matter. Is it advisable to do so? If yes, anything we should look out for?

Thanks in advance!

matttrax

Make sure your partner ARCHIVES the data, not deletes it. Then it's just an easy import back into the system if something is mistakenly removed. You can also create new FA Ledger / General Ledger entries to keep your balances the same.

For example, say you have 100,000 G/L Entries to archive, you put those in another database. Then (preferably grouped by Account No., Posting Date) you create new entries for the sum of those amounts.

We personally do this every year for one company. Finance still needs the numbers in the same database, and an easy way to reference them, but it grows at such a fast rate that that they like to keep the size down by archiving anything more than three years old. If done correctly there is no issue. Just remember developers aren't end-users or accountants (usually), so it's up to you to make sure everything worked the way you wanted it to.

seckpin

I do understand that archiving the data after a few years are peformed by a lot of companies. However, we are not talking about archiving the full G/L and all related records. We are thinking of removing the FA and associated records only. This means that transactions originated from AP, AR, Bank, Inventory, G/L... will stay the same, but only FA related data is deleted.

I am just wondering if it is safe to do so. For instance, if in the G/L, a particular month of depreciation takes up G/L Entry No. from 1001 to 1500, and we delete these away, then we will see a gap in the G/L Entry No. (1000 then to 1501). Of course, not to forget there are other related tables too such as G/L Register, VAT Entries, etc.

jordi79

Hi,

In the standard 4SP3, fixed assets that are disposed can be deleted. However, the FA ledger entries still remain. The FA ledger entries of deleted fixed assets all will have BLANK FA No.

Just give it a try on a standard db, and see if this is what you want.

By the way, if the assets are inactive, you should mark them as BLOCK, as this will make the calc. depr batch jobs faster.
Also, it may be advisable to run the batch job in smaller chunks. E.g. filtered by a certain FA posting group each time. Because, if you are running for all assets, and on the last asset, if there in a error, all created entries are rolled back.
Also, if you see that your batch job is stuck with some message that NAV is calculating records in the record, with a incrementing entry no. consider adding keys to improve the performance of the batch job.