how to globally update posting periods via sql script in NAV 5.0

A new client was hit by ransomware about a month ago, and their IT director had left shortly before that. The NAV environment was then reconstructed. The client had called me in for a SQL DBA type role, not as a NAV specialist. But now I am being asked to solve NAV specific problems and I am trying to get up to speed as quickly as possible.

I have been told that there was a sql script that ran automatically to update posting periods at the end of the month, but it was lost after the ransomware hit, and only the IT director knew the details about it. There are several hundred users and about 15 companies. If I don’t get this done for them today, then the ERP manager will have to do it manually early tomorrow, which he says is a very tedious task.

It seems to me that this should be a simple SQL update against the date columns in a NAV table. What table corresponds to users / posting periods? Or is there another way to do this automatically.


I think I have figured out what is required for this - I can run the following sql:

update [thecompanydatabase$User Setup] set [Allow Posting From] = DATEADD (MONTH, 1, [Allow Posting From]), [Allow Posting To] = DATEADD (MONTH, 1, [Allow Posting To])

and repeat for each company.

It seems pretty simple, but I want to make sure there are no gotchas, other related tables to update.

I have worked with a number of other ERP systems, and I am new to NAV, but I am surprised that this functionality is not in the interface. Also, it is custom by user type, for example “Accounting” users have a longer posting window

Normally, allowed posting period would be controlled by the General Ledger Setup, and only a limited number of users have specific settings - that’s the reason for not creating a special job to update all users.

In general, it is not recommended to update any tables directly by SQL queries. First of all, because a table can have C/AL triggers that will not be executed this way, and the update can cause data inconsistency. On the other hand, the only trigger in the User Setup table verifies correctness of the entered date, so this update should not cause any issues. But it is always safer to run this kind of updates via C/AL code.

I don’t know details of NAV 5.0 server archtecture, but in newer versions NAV server tier has its own cache apart from SQL. If a record being updated via SQL, is cached on NAV side, cache will not be refreshed - NAV just does not know that the record gets stale. Not sure though, if it’s applicable to version 5.

The function is not in the interface because this is not the way to do it… If you do not have the right Dynamics NAV 5.0 expericence I would strongly advice you to find someone whom does. You can really F**K-up the system if you don’t know what you are doing. Accounting Systems is not just “anotther application installed on a computer”… without it the company would not survive for long.

And as Alexander says… NO accounting database modifications should be done from outside NAV…Especially not on an old NAV solution like 5.0.