Phantom (ghost) flow-field balance in G/L acct under SQL Svr

KAdamsInCo

We are taking a client live on Monday with Dynamics – NAV 4.0 and have run into an interesting problem I need help with. I have a ‘ghost’ Actual Journal balance in an account, where on the G/L -> Chart of Accounts the Actual total is $1,591.86, but if you drill down to see the G/L entries, there are none. The entries were deleted because the G/L upload was done wrong, but this balance remains. Simply deleting the account and re-adding it does not work because this dollar amount is in the roll-up total, and remains there even if the actual G/L account is removed.

What is really interesting is that this is using MS SQL Server 2005, and when we do a Navision backup and restore it to a native Navision DB, the ghost entry is not there. The client has reasons to run under SQL Server, so we need to figure out where this bogus information is coming from. I have heard that the way the SIFT / flow-field data is stored in SQL Server is radically different from native Navision as other table structures are built and maintained in order to provide ‘like’ functionality. This ghost data might be in one of these tables, but I have no idea how to identify where it is, and what records to delete to properly remove this entry.

This is a production database where payroll and other work has been done, so I don’t want to remove all Actual Journal entries. I have to leave everything else intact but be able to get rid of this fake $1,591.86 ‘balance’ in the 16100 G/L account.

Any ideas on where I would look to resolve this issue? :-k

Thanks!

Keaton

ara3n

Click on File->database->information->table. Fine table G/L Entry. Click on Optimize.

That will solve it.

ara3n

The reason you have the phantom amount is because, somebody went through sql and started changing the values directly from SQL.

[-X [-X [-X [-X [-X [-X

DenSter

It might also be because someone manually deleted lines from within NAV, using a date filter. It can happen that a line with a closing date was left in there, making it look like the balance doesn't add up.

Optimizing the table should fix the issue.

ara3n

I heard of the closing date issue only if you renamed the gl account.

KAdamsInCo

OK,

So I ran an optimize on all tables, and the phantom balance is still there! Any more suggestions? I guess I'll open up a support incident with Microsoft to see what they have to say about it.

Thanks for your responses,

Keaton

zarryn

If there are values left in the sift bucket tables, a restore to a new SQL database should resolve the issue.

KAdamsInCo

Is there any way to rebuild the SIFT data without doing a restore?

Thanks again, the information has been helpful,

Keaton

ara3n

run the sift table from sql and delete all the records.

The sift table look like this

Company Name$table ID$Key No.


So for gl entry table it would

Cronus$17$0
Cronus$17$1
Cronus$17$2

Depending on how many keys you with sumindex values in them.

David_Singleton

KAdamsInCo wrote:

We are taking a client live on Monday

You need to sit with your client and rethink that. This is not a quick fix, and you could be getting your client into a situation that will be probelmatic for some time.

KAdamsInCo wrote:

The entries were deleted because the G/L upload was done wrong,

You can't just "delete the entries" most important, as Rashed asked, did you do this in Navision, or directly in SQL. If from SQL then you really need to go back and restore an older database. If from Navision directly, then look at completing the process, but even then, it was a big mistake that is going to come back and bite you many more times in the future.

KAdamsInCo wrote:

I have heard that the way the SIFT / flow-field data is stored in SQL Server is radically different from native Navision as other table structures are built and maintained in order to provide ‘like’ functionality.

It sounds like you are new to SQL with Navision. "I have heard" really will not be a confidence builder for you client.

I know that you don't want to hear this, and you are looking for a quick fix to get your client live, but you may find that getting it right, is better than getting it on time.

I am not sure how you did the data conversion, but lets hope you have a documented process, in which case, you can just redo the whole thing from the beginning. If on the other hand you have been just building up the database as you go a long, there really could be problems.

Anyway, I am pretty sure I am going to be blasted for try to help, but ah well getting kicked is all a part of trying to help.

DenSter

ara3n wrote:

run the sift table from sql and delete all the records.

I will not reply the way I want to reply here, but I will say that this is not good advice. Someone who clearly does not know their way around NAV on SQL Server should never EVER be messing around in those tables.

Table optimize should rebuild the SIFT values, so if the problem persists, it will come back even if they did delete those records.

ara3n

It happened at one of the clients. They had deleted the analysis view Entry table from sql.

I told them the same thing and the problem was solved.


I agree that information shouldn't be given to everybody. And what he should do is do a navision backup and create a new sql database and restore.
This has been already suggested, but he doesn't want to do it or that's not an option.

David_Singleton

In this particular case, |I think the decision has been made to go live Monday, and thus there is no time to fix the problem, so they are looking for a quick fix that will hide the problem long enough to try and do a real fix.

Its damn hard to decide a/ Do I help this person be specifically answering their question, or b/ tell it to them straight and hope they will listen.

I think the customer is screwed no matter what, the hood is up, the spanners are out, and all those "useless" pipes and hoses that really don't do anything have been removed, and even if we wanted to put them back, 2 of them are broken, one is now full of sand, and the others we really don't know where they go. Lets just get the motor started.

We all know that there is no way that this problem is going to get fixed in one day. The only solution is to abort the go-live. but is that going to happen?

When I left Navision I figured that I might get 2 or 3 years of consulting work, helping to fix up disasters like this. Then I would need to find a new line of business. Well that was quite some years ago, and still there is room to make a living

I agree that its not good to give advise if the person does not know how to use that advise, but he is going to do it no matter what, so probably its best that he has at least some vague idea of what he should do rather than just remove more bits from the engine that he really thinks are not necessary.

Lets just hope that in a week or so when things are really falling apart, they have the sense to get in an expert to fix the mess.

kriki

KAdamsInCo wrote:

Is there any way to rebuild the SIFT data without doing a restore?

Make a .fob of the table.
Then for all indexes : property MaintSIFTIndex=No and save the object.
Navision will then delete all the SIFT-tables in SQL of that table.
After that, import the .fob you just saved. Navision will re-create the SIFT-tables from scratch.

Phenno

kriki wrote:

KAdamsInCo wrote:

Is there any way to rebuild the SIFT data without doing a restore?

Make a .fob of the table.
Then for all indexes : property MaintSIFTIndex=No and save the object.
Navision will then delete all the SIFT-tables in SQL of that table.
After that, import the .fob you just saved. Navision will re-create the SIFT-tables from scratch.

I agree with kine's solution but something other bothers me...
If you are planning to go Live at Monday, why you had postings in production db already? Why didn't you have test db?
It seems to me that you tried to create a production DB from test DB and if True than you had to delete not only G/L entries but a lot more and if you deleted G/L entries through SQL (as we all supposed) than you deleted other data through SQL too.

If all of this (mine speculations, ofcourse) is true than you have a big problem.

Phenno

Ofcourse, there are situation where you are posting some start balance values, though, they usually come after a while.

In addition, here was the similar problem (and same solution):
http://www.mibuso.com/forum/viewtopic.p ... ft+bug+sql

kriki

[Topic moved from Navision forum to SQL General forum]