Problem with Drill down

pruebasExpruebasEx Member Posts: 51
I have a field in a table that sum another (I put the calcformula in the property of calcformula of the field). Then I show in a form.

In my database it works, but in the database of the customer not. The objects are the same in bouth case (form with same filter, same calcformula...etc)

In my database.
ybpqk3adrf80.png
f2rxld4ofk09.png
kjaqo2zgvrgv.png
psgoz9mwleqv.png


Customer
r4t6tmsup5gp.png
3wza1mzk6yty.png



In customer when you click on drill down of this field, you can see the values but, drill down field is still zero ( 0 ).
I repeat, the objects of two databases are THE SAME.

Thanks.

Best Answer

  • pruebasExpruebasEx Member Posts: 51
    Answer ✓
    I do not know if anyone said anything similar, but the solution to this problem was to remove the key that was being used by the SUMINDEX.

    1-Disable de check of the Key.
    2- Compile
    3- Enable check again.
    4- Compile.


    When I try to use a new variable with the value of the bad SUMINDEX, I got an error that seems to be a bug or an inconsistence with SQL. The error was about the option NOEXPAND from SQL query.
«1

Answers

  • krikikriki Member, Moderator Posts: 9,094
    If you are using a version before NAV5SP1 on SQL, it might be that the SIFT-data is corrupt. (SIFT-data is updated through SQL-triggers on the table).
    Export the object. Then remove the SIFT key that is used (or all if you don't know). And then import the object again.
    If I remember correctly, the problem might popup if you run a script that deletes the 0-value records in the SIFT-table.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • pruebasExpruebasEx Member Posts: 51
    kriki wrote: »
    If you are using a version before NAV5SP1 on SQL, it might be that the SIFT-data is corrupt. (SIFT-data is updated through SQL-triggers on the table).
    Export the object. Then remove the SIFT key that is used (or all if you don't know). And then import the object again.
    If I remember correctly, the problem might popup if you run a script that deletes the 0-value records in the SIFT-table.

    It's a 2009 R2 versión....

    Versión 6.0 R2 (6.00.32012)
  • vaprogvaprog Member Posts: 1,116
    Looks lie a matrix form. How are the matrix values calculated? CALCFIELDS, CALCSUMS, any other way? IF CALCFIELDS, use the table to verify, the flowfield's value, otherwise use the debugger to verify, the calculation algorithm does what you expect.
  • pruebasExpruebasEx Member Posts: 51
    In the form I call a variable(MyAmount) of another table (not the same I referred in the form ).

    The properties of Myamount are

    Data Type -> Decimal
    Enabled -> YES
    Init Value -> Undefined
    Field Class -> FlowField
    CalcFormula -> Sum("Ticket Header"."Total Amount" WHERE (H=Field(Time Filter), Date=(Date Filter), Code = Field(Code), store=Filter()No)))

    ...
    ...
    ...

    I repeat, In my database It works, In the customer not. I realized they are the same objects, same code....So I dont understand....
  • pruebasExpruebasEx Member Posts: 51
    edited 2017-08-14
    vaprog wrote: »
    Looks lie a matrix form. How are the matrix values calculated? CALCFIELDS, CALCSUMS, any other way? IF CALCFIELDS, use the table to verify, the flowfield's value, otherwise use the debugger to verify, the calculation algorithm does what you expect.

    In the matrix I have a CALCFIELDS of variable MyAmount (MyAmount has a calcformula set)
  • pruebasExpruebasEx Member Posts: 51
    I still have no answer for this...
  • vaprogvaprog Member Posts: 1,116
    So, what did you investigate?

    Is it definitely a problem with the CALCFIELDS or is the problem somewhere else in the code.
    Did you try CALCFIELDS directly on the table, in a page with SourceTable = that table? What does the default drilldown page show (Property DrillDownPageID, no Code in the OnDrillDown trigger)?

    Did you try to delete metadata for all objects involved and recompile them (table beeing summed, table containing the FlowField, Pages, Codeunits, ...). I have seen cases where a recompile did not correct an issiue, without manually deleting object metadata beforehand.

    Do you use the same build in both databases? If not, try the other one.

    Don't rely on others presenting you with a solution. Narrow down the real cause. Report back what you have done, what your results were, and what you concluded from it.
  • pruebasExpruebasEx Member Posts: 51
    edited 2017-08-17
    vaprog wrote: »
    So, what did you investigate?

    Is it definitely a problem with the CALCFIELDS or is the problem somewhere else in the code.
    Did you try CALCFIELDS directly on the table, in a page with SourceTable = that table? What does the default drilldown page show (Property DrillDownPageID, no Code in the OnDrillDown trigger)?

    Did you try to delete metadata for all objects involved and recompile them (table beeing summed, table containing the FlowField, Pages, Codeunits, ...). I have seen cases where a recompile did not correct an issiue, without manually deleting object metadata beforehand.

    Do you use the same build in both databases? If not, try the other one.

    Don't rely on others presenting you with a solution. Narrow down the real cause. Report back what you have done, what your results were, and what you concluded from it.

    I have tried all the things you propose, CALCFIELDS directly on the table, see if is default drilldown, or even the DrillDownTrigger.

    But the only thing I cant try at the moment is delete metadata (how can i do that? I am connected to the customer so, I cant delete any object.)

    I think is a problema with the CALCFORMULA/FLOWFIELD but...I cant test anything....or find anything strange...
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Check if your customer database is case sensitive. If it is and your is not then it might be that you have some filters which use different case that the data in the database
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    Check if your customer database is case sensitive. If it is and your is not then it might be that you have some filters which use different case that the data in the database

    Both have same filters. Exactly. And they are same object. I import on the customer database the objects of my database.
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    edited 2017-08-17
    This might be data related, not necessarily the code.

    Have you checked the collation on customer's database ? Is it case sensitive? Is is the same as at yours?

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    This might be data related, not necessarily the code.

    Have you checked the collation on customer's database ?

    Slawek

    How can I check that?
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    In NAV: File -> Database -> Alter, then Collation tab. You will see the database collation there, and two check boxes "Case Sensitive" and "Accent Sensitive"
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • vaprogvaprog Member Posts: 1,116
    pruebasEx wrote: »
    I have tried all the things you propose, CALCFIELDS directly on the table, see if is default drilldown, or even the DrillDownTrigger.
    And the value on the page was zero, and the drilldown showed records which do not add up to zero?, Or did it not show any records, even though there aught to be some?
    This is important stuff, because either the sum is incorrect, or the selected records beieng summed are incorrect, or the filter is incorrect ...

    By the way, did you check the SQL Table's and Views integrity? Consult the DBA in charge, if you don't know how, or don't have access.

    On 2009 Classic you may also try File > Database > Information > Table > select the target table of the flowfield, then Check (you may skip relations) and Optimize.
    pruebasEx wrote: »
    But the only thing I cant try at the moment is delete metadata (how can i do that? I am connected to the customer so, I cant delete any object.)
    Don't you have a test database at the customer's site? Is the failing code life? The customer should grant you time to fix it.

    You may try to export the objects as text, import them from text and then compile them. This might make the deletion of metadata redundant.

    Also try restarting the service. Many strange things have disappeared this way.

    Meta data tables are
    2000000071 Object Metadata
    For Pages there are many more, but i think that's irrelevant here.
    pruebasEx wrote: »
    I think is a problema with the CALCFORMULA/FLOWFIELD but...I cant test anything....or find anything strange...

    Did you investigate the executed query on SQL used to execute the CALCFIELDS?

    You can test by changing little bits, e.g. adding or deleting flowfilters. You can also delete and or (re-)create sumindex fields.

    None of these goes with some interruption for other users.

  • pruebasExpruebasEx Member Posts: 51
    In NAV: File -> Database -> Alter, then Collation tab. You will see the database collation there, and two check boxes "Case Sensitive" and "Accent Sensitive"

    Ok Thanks.

    Same collation. Accent Sensitive (YES) and Case Sensitive (NO) in both
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    Do you have a custom code in OnDrillDown trigger?
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    Do you have a custom code in OnDrillDown trigger?

    No...triggers are empty too...
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    The lasting thing to blame is usually the SQL Server, especially since NAV 5 SP1 it is no longer the SQL trigger on the table responsible for maintaining the SIFT data used to calculate flowfields

    Try to edit keys on the table with the source data (the table referred in the flowfield definition), find all the indexes which could be used by your flowfield, and untick MaintainSIFTIndex on every one of them. This will force NAV to calculate values by summing up the actual records from the table, rather than summing up records from associated indexed views.

    It may be a stupid question but have you tried to copy the data from the drill down details form and sum them up in Excel to ensure they don't add up to 0?

    Your screenshots doesn't show the headers on the drill down form - double check that what you are displaying on the drill down is what you actually summing up in your flowfield definition.

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • David_SingletonDavid_Singleton Member Posts: 5,479
    There isn't enough information here.
    Firstly what is the code that calculates the numbers

    Then you need to put the data in Excel and add it up and show that.

    You are just hiding too much information to be able to get good help. From the screen shots these could be two completely different pieces of information.
    David Singleton
  • pruebasExpruebasEx Member Posts: 51

    Try to edit keys on the table with the source data (the table referred in the flowfield definition), find all the indexes which could be used by your flowfield, and untick MaintainSIFTIndex on every one of them. This will force NAV to calculate values by summing up the actual records from the table, rather than summing up records from associated indexed views.
    |
    --> I'm going to try this.


    It may be a stupid question but have you tried to copy the data from the drill down details form and sum them up in Excel to ensure they don't add up to 0?

    Your screenshots doesn't show the headers on the drill down form - double check that what you are displaying on the drill down is what you actually summing up in your flowfield definition.

    Slawek

    We have to remember that In my database everything works ok, and the objects are the same...so obviouly yes, what I'm displaying on drill down is what I'm summing up
    (and the same for the data sum is not Zero.)


    Thanks for your help!
  • pruebasExpruebasEx Member Posts: 51
    There isn't enough information here.
    Firstly what is the code that calculates the numbers

    Then you need to put the data in Excel and add it up and show that.

    You are just hiding too much information to be able to get good help. From the screen shots these could be two completely different pieces of information.

    Ok I'll try to update the post!

    Thanks!
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    pruebasEx wrote: »
    We have to remember that In my database everything works ok
    Well, personally I'd rather not assumed that. It may only looks like it is working OK in your DEV db. But of course it is your database and your choice..

    Slawek
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    pruebasEx wrote: »
    We have to remember that In my database everything works ok
    Well, personally I'd rather not assumed that. It may only looks like it is working OK in your DEV db.

    Slawek

    Yes of course!.

    I only assume that I have same objects with same code, same object properties, same formulas etc... Thats why I thought that...
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    By assuming that something works OK you are preventing yourself from checking all possible places where things can go wrong.

    As I've mentioned in my previous post the SQL server is usually the very very very last thing to blame for data errors or inconsistencies in NAV. Having said that there were some problems with indexed views (the technology used behind NAV SIFT from NAV 5 SP1 onwards) on the SQL Server. There was a bug in some SQL versions (2008, 2008R2 and 2012) which caused incorrect result an indexed view was queried. You can read the details here

    The KB article mentions the NOEXPAND query hint, but this hint is used by NAV when querying indexed views. On the other hand the error was manifesting itself only when the base tables have been updated - so straight after loading an object with a key having a SIFT defined on it, or after disabling and re-enabling the SIFT index (both operations aer rebuild underlying indexed views) the returned results would be OK.

    The article also mentions a problem with indexed view based on two tables - which is never the case in NAV - but it does not contain information if indexed views based on a single table have been affected. My guess is that single table based indexed views were probably not affected - therefore NAV was not affected by this bug.

    Anyway - the problem is quite old and your customer should have its SQL Server patched long time ago (in case of SQL2012 the Service Pack 2 included the fix, but before SP2 was released the fix was available in Cumulative Updates)

    Slawek

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    kriki wrote: »
    If you are using a version before NAV5SP1 on SQL, it might be that the SIFT-data is corrupt. (SIFT-data is updated through SQL-triggers on the table).
    Export the object. Then remove the SIFT key that is used (or all if you don't know). And then import the object again.
    If I remember correctly, the problem might popup if you run a script that deletes the 0-value records in the SIFT-table.

    I up this again to check if it's possible test this.

    How to know if it's a SIFT KEY??
    Where is the SIFT table?

    If I understood correctly, steps are...
    1- Export
    2- Remove SIFT keys
    3-Import??
    4-Script 0-values??
  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    You still have not posted your C/AL code, or object definitions, but insisting on checking places which are VERY unlikely to be the source of error.

    Run SELECT @ VERSION in SSMS (use two @ chars, no space between @ and VERSION, Mibuso doesn't display two @ signs for some reason), this will give you your SQL server version and build number. Compare that with the list of SQL Server versions listed in the KB article, and you will know if your NAV installation is potentially affected or not.

    If it is not don't bother checking results by querying SQL views. Checking results against VSIFT views is tricky, especially when you don't have the experience. You need to know which one to use, and you need get your predicates correct. They need to be exact equivalent to filters set in NAV to get the same results. They need to be exact equivalent to what NAV sets, not to what you think NAV sets on the record var.

    If you still insist on checking against values stored in SQL views then the SELECT SUM(SUM$MyVarible) FROM MyTable$VSIFT$1 ... is pretty much it, if your table is called MyTable, the field used to add up values is called MyVarible, and it is defined on the second key on your table. Then you need to add your filters. Time='1754-01-01 12:00:00.0' is an equivalent of SETRANGE(Time, 120000T) in C/AL, Date>='2016-12-01 00:00:00.0' AND Date<='2016-12-02 00:00:00.0' is equivalent of SETRANGE(Date, 01122016D, 02122016D), and so on. The Cod and Shop field filters are pretty straight forward.

    By the way - you've written in your previous post that you are on version 2009 R2 build 321012 (which is 2009 R2 RTM version), therefore what kriki said about possible SIFT data corruption does not apply to your installation.


    Slawek

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    edited 2017-08-29
    By assuming that something works OK you are preventing yourself from checking all possible places where things can go wrong.

    As I've mentioned in my previous post the SQL server is usually the very very very last thing to blame for data errors or inconsistencies in NAV. Having said that there were some problems with indexed views (the technology used behind NAV SIFT from NAV 5 SP1 onwards) on the SQL Server. There was a bug in some SQL versions (2008, 2008R2 and 2012) which caused incorrect result an indexed view was queried. You can read the details here

    The KB article mentions the NOEXPAND query hint, but this hint is used by NAV when querying indexed views. On the other hand the error was manifesting itself only when the base tables have been updated - so straight after loading an object with a key having a SIFT defined on it, or after disabling and re-enabling the SIFT index (both operations aer rebuild underlying indexed views) the returned results would be OK.

    The article also mentions a problem with indexed view based on two tables - which is never the case in NAV - but it does not contain information if indexed views based on a single table have been affected. My guess is that single table based indexed views were probably not affected - therefore NAV was not affected by this bug.

    Anyway - the problem is quite old and your customer should have its SQL Server patched long time ago (in case of SQL2012 the Service Pack 2 included the fix, but before SP2 was released the fix was available in Cumulative Updates)

    Slawek


    URL FIXED
  • pruebasExpruebasEx Member Posts: 51
    I don't know why, when I edit a message then it disappears

    I search for the SQL version of the customer and compare to our version.

    Our version SQL
    vtul4qadgtff.png


    Customer's version
    rsqlclkrpx18.png


    How it's possible our SQL works if it's older than customer's version??

  • Slawek_GuzekSlawek_Guzek Member Posts: 1,690
    pruebasEx wrote: »
    How it's possible our SQL works if it's older than customer's version??
    Easy explanation - it is possible if the SQL has nothing to do with it, and the error is somewhere else.
    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • pruebasExpruebasEx Member Posts: 51
    pruebasEx wrote: »
    How it's possible our SQL works if it's older than customer's version??
    Easy explanation - it is possible if the SQL has nothing to do with it, and the error is somewhere else.

    Ok. So, SQL discarded...?

    I update the post with a bit of information.
Sign In or Register to comment.