mibuso.com
Home› SQL General
Options

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

KAdamsInCoKAdamsInCo Member Posts: 28
edited 2007-05-14 in SQL General
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

Comments

  • Options
    ara3nara3n Member Posts: 9,255
    Click on File->database->information->table. Fine table G/L Entry. Click on Optimize.

    That will solve it.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    ara3nara3n Member Posts: 9,255
    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
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    DenSterDenSter Member Posts: 8,304
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    ara3nara3n Member Posts: 9,255
    I heard of the closing date issue only if you renamed the gl account.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    KAdamsInCoKAdamsInCo Member Posts: 28
    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
  • Options
    zarrynzarryn Member Posts: 29
    If there are values left in the sift bucket tables, a restore to a new SQL database should resolve the issue.
    Zarryn
  • Options
    KAdamsInCoKAdamsInCo Member Posts: 28
    Is there any way to rebuild the SIFT data without doing a restore?

    Thanks again, the information has been helpful,

    Keaton
  • Options
    ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    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. :cry:
    David Singleton
  • Options
    DenSterDenSter Member Posts: 8,304
    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.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    ara3nara3n Member Posts: 9,255
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    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 :mrgreen:

    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.
    David Singleton
  • Options
    krikikriki Member, Moderator Posts: 9,090
    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.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • Options
    PhennoPhenno Member Posts: 630
    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.
  • Options
    PhennoPhenno Member Posts: 630
    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
  • Options
    krikikriki Member, Moderator Posts: 9,090
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

Sign In or Register to comment.