Problem with Drill down
pruebasEx
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.
Customer
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.
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.
Customer
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.
0
Best 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.0
Answers
-
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!0 -
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)0 -
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.0
-
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....0 -
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)0 -
I still have no answer for this...0
-
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.0 -
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...0 -
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 databaseSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
Slawek_Guzek wrote: »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.
0 -
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-030 -
Slawek_Guzek wrote: »This might be data related, not necessarily the code.
Have you checked the collation on customer's database ?
Slawek
How can I check that?0 -
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-030 -
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?I have tried all the things you propose, CALCFIELDS directly on the table, see if is default drilldown, or even the DrillDownTrigger.
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.
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.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.)
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.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.
0 -
Slawek_Guzek wrote: »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 both0 -
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-030 -
Slawek_Guzek wrote: »Do you have a custom code in OnDrillDown trigger?
No...triggers are empty too...0 -
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.
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
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 Singleton0 -
Slawek_Guzek wrote: »
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!
0 -
David_Singleton wrote: »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!1 -
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..We have to remember that In my database everything works ok
SlawekSlawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-031 -
Slawek_Guzek wrote: »
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...0 -
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-030 -
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??0 -
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-031 -
Slawek_Guzek wrote: »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 FIXED0 -
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
Customer's version
How it's possible our SQL works if it's older than customer's version??
0 -
Easy explanation - it is possible if the SQL has nothing to do with it, and the error is somewhere else.How it's possible our SQL works if it's older than customer's version??
Slawek Guzek
Dynamics NAV, MS SQL Server, Wherescape RED;
PRINCE2 Practitioner - License GR657010572SG
GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-030 -
0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions