Has anybody had any experience with renaming a single G/L Account number?
How viable is this and what is the best why of performing this? We currently are running on Version 4 of NAV but with a tailored package for the Construction Industry. The SQL server is 2005.
I have written a VB program to look through all tables for a company that is selected within the program. It then returns the number of iterations that occur for each table for the G/L account:
| ‘Company Name$169$0’ |
51129 |
| ‘Company Name$Job Ledger Entry’ |
38688 |
| ‘Company Name$365$0’ |
23063 |
| ‘Company Name$17$3’ |
19150 |
| ‘Company Name$17$1’ |
15389 |
| ‘Company Name$Analysis View Entry’ |
3193 |
| ‘Company Name$G_L Entry’ |
3140 |
| ‘Company Name$Sales Invoice Line’ |
2566 |
| ‘Company Name$17$4’ |
316 |
| ‘Company Name$Sales Cr_Memo Line’ |
246 |
| ‘Company Name$17$0’ |
163 |
| ‘Company Name$37$0’ |
26 |
| ‘Company Name$17$2’ |
25 |
| ‘Company Name$General Posting Setup’ |
4 |
| ‘Company Name$Default Dimension’ |
2 |
| ‘Company Name$Ledger Entry Dimension’ |
1 |
| ‘Company Name$G_L Account’ |
1 |
| ‘Company Name$Dimension Value’ |
1 |
The offending G/L Account is the only sales account within the system and has one Dimension cost code that points to it. From what I can see all the tables identified are core Navision but I am not sure what the $ numbered tables are. They look like temporary holding tables.
Our current supplier is bing a bit vague on the whole request so I am posting here to see if I can get a little further.
Thanks in advance.
I have made several G/L accounts renaming and until know I never had such problems. Just go to chart of accounts and rename G/L account. Dynamics NAV should handle the rest of work. You can try it first in a backup database. The only problem you might get is related to customized parts in Navision because it could be created tables by missing relations to Chart of accounts.
I understand its easy to rename in the chart of accounts, are you saying that it will update all tables with the new value (Except as you stated any customized ones?)
We are going to attempt this soon but are making sure our understaning is correct before proceding on a backup company in test. Is this because it is a field that updates all child related fields throughout the system?
Just renaming G/L account it will be enough. It will update all other tables. Customized tables it will also updated if they are corrected. Once I got an error on a customer that was related to a missing relation in a custom table. If custom tables are correct it also should be updated.
Are you serious? This is one of the most basic features of Navision that every partner MUST know about. I wonder if they have not been following Navision development rules when they wrote tehir code and are now concerned that something will break.
PS the $ tables are sift fields for Navision DO NOT TOUCH THEM, in fact NEVER touch any Navision tables direct in SQL.
PPS in your case, the only technical difficulty in renaming is to get your AUditors to agree to it, once they are on board its simple.
Sounds good so far, we are in talks with the auditors currently so I dont see that as a problem. Thanks for the update on the sift tables I will ignore these.
The only time I deal directly with the SQL tables is for reporting purposes as I use asp querys to return data on our in house reporting web server.
I understand the point about the partner should know but they do seem concerned and they do not have an answer and are laying the ground work for blame firmly in our laps if it goes wrong.
I have just changed the Sales G/L Account code, However I now have the following tables with the old number:
| ‘Company Name$169$0’ |
41183 |
| ‘Company Name$Job Ledger Entry’ |
17238 |
| ‘Company Name$365$0’ |
10545 |
| ‘Company Name$17$3’ |
6783 |
| ‘Company Name$17$1’ |
5811 |
| ‘Company Name$17$0’ |
58 |
| ‘Company Name$17$2’ |
25 |
So if I ignore the sift tables (What are they and what impact does this have?) this just leaves me with the Job Ledger Entry table that still has the old G/L account number on. It is found in the ‘Job Posting Group’ Field and all entries in the No. field have correctly changed so the G/L Account no is now correct.
What impact does Job posting group have for me?
Instead of summing all entries Navision maintains intermediary table with sums to improve performance. Those values are stored in SFIT tables.
To clean values just optimize tables and unnecessary values to be deleted.
Regarding Job Ledger Entries you take a look because if there isn’t any changes it should be correctly changed. Can you send a screenshot with a lookup from NAV of incorrect values in Job Ledger Entries ?
Sorry to sound like an idiot but how do I clean and optimise?
I have attached a screenshot of the entrys on our test system, no. has the corrected G/L Account number for test purposes, but the Job Posting Group has got 131001 in. Which was the old G/L Account number. Am I getting myself confused with another entry in the system that is not a G/L account?
I think it’s strange Job Posting group change value it should be the same. Compare to previous values from from backup database.
To optimize tables go to File → Database → Information → Optimize.
Select all tables and then select Optimize.
I have checked the data and these entrys were as they are now except for the No. column contained 131001 as the G/L account.
For some reason the ‘Job Posting Group’ number was not changed, I take it that the core navision product would change this field as well if it matched a changed entry within the chart of accounts. If this is the case is there anything that I can check to see if this is working?
Sorry, but I’m lost know.
You have renamed G/L account and everything is correct. You didn’t changed posting groups code so it wasn’t changed. To me seems that everything is correct.
The Job Posting Group field in the Job Ledger Entry table has a conditional table relationship, and it seems that those are not changed when you do a rename. Looks like you will need to modify that field manually.
The other tables (the ones formatted as “$firstnumber$secondnumber”) are all SIFT tables. Just because there is an occurrance of your old value, does not mean that this is a bad thing. NAV keeps bucket values in those SIFT tables, and calculates them on the fly. Remaining bucket records that refer to the old value should all add up to zero.
What you can do is optimize the base tables (as suggested before), which are referred to by the ‘firstnumber’. The SIFT table called ‘Company Name$17$0’ for instance, is a SIFT table for NAV table number 17, which is the General Ledger Entry table. Select that table from the tables list in Database Information, and you optimize it. This process should rebuild the SIFT tables that are related to that table, which now should not include any references to the old value.
There are also products out there that do maintenance on SQL Server, and they include routines to clean up those so called ‘empty SIFT records’. You can also upgrade your exe’s to 5.0 SP1 or higher, which implements SIFT in indexed views instead of SIFT tables.
hi, can you help me? i’ve an error when rename “No.” in table 15 (is not defined in the G/L Account table)
