-

A short while ago I informed you about an important hotfix for Navision 4.0 SP3 and 5.0 SP1.

At one of my larger customers in Germany we implemented this build. It is really working great and solves a lot of problems caused by the Dynamics cursors introduced in build 26410.

However we have managed to find two strange issues with it.

1.       Adding SQL indexes NAV forgets a filter on Sell-to Cutomer No. in a Select Statement:

We found a bug where NAV creates a wrong select statement which is missing a filter. This happens when you open the Sales History from a Customer Card (we did not check opening Sales History from a  Sales Order). Attached you will find the result from Client Monitor where you can see that Sell-to Customer No. is filtered but the select is missing the where clause.

If this happens the query is of course very slow and reading a huge amount of data.

Additionally the form 7171 Sales History does not open in foreground but is sent to the background. To the user it looks like the form was trying to open and is immediately closed again. This does only happen if the forms are in fullscreen.

2.       Contact List open/scrolling is very slow:

When you open the Contact Card and open the Contact List (F5) the form opens very slow. It is using the index “SORTING(Company Name,Company No.,Type,Name)” but SQL Server does perform a clustered table scan instead (the index exists). An Index Hint has solved that. This did not happen in 26410.

We have reported these issues to Microsoft both direct and via the local NAV partner.

I will keep you informed about the progress.

/Mark

For this blog entry I would like to refer to Eric Wauters (Waldo) blog entry.

One thing he describes about NAV 2009 SP1 is the possibility to start pages from the object designer, simply by clicking the run button.

This saves you the trouble of running Dynamicsnav:////runpage?page=70000 from the windows run menu or the internet explorer.

BUT.

Let's see what happens add a record to the table, which page 70000 is based on...

And run the page again...

The new data is not showing....

WHY?

For this to understand we have to go back to the statement

Dynamicsnav:////runpage?page=70000 

Please note that it has 4 forward slashes. When I first got demo't this the reason they gave was they did not know why.

Reason is simple. Between the first two slashes you can enter the companyname and servername.

The online help says this about it:

When you run the  button the hyperlink opens the default settings from the CustomSettings.Config file

This MSDN link explains how the file works.

The company seems to be remembered from the last opened company.

Conclusion: The run button is a nice to have but does not make the Runpage obsolete...

Enjoy

Interaction Log entries are a very powerfull feature of Dynamics NAV. From everywhere in NAV you can add an interaction log entry if you print a report and from withing the CRM part of NAV you can register all other interactions like emails, phonecalls, mailings etc.

Interactions can be related to a path of multiple to-do's that can be sinchronised with Outlook.

Technicaly the interaction log entry table is clustered by an entry no. Like al Entry tables in NAV this handled manualy by C/AL code. This means that every time someone creates a new interaction log entry, the last record in the table is locked, thus isolating all transactions creating a new record.

SQL Server can also maintain the entry number for you. This is built in technilogy called Identity Insert.

To activate this feature for a NAV table change the property Auto Increment of the field.

Every table can have one field with this feature. This can be (part of) the primairy key or not.

If you enable this field you will encounter two typical issues. The first issue you will notice is that if you create a new entry the value of the autoincrement field has to be set to zero. The other issue is that if an error occurs during the transaction, the entry number will be skipped, leading to gaps in the numbering.

If the latter is not a functional issue then the first can be solved by rewriting some code. For Interaction log entries this is just two places. I will let you figure out those places yourself.

This was also a great tip for me. I saw someone do it when I was onsite at an enduser.

Everyone who works with the key's in NAV tables hassled with this, I am sure.

When you hit F6 on a key you get the field list. This is only usefull when you add a key on one field. What you want is to go to the form that allowes you to add more than one keyfield.

This (seems to be) able to achieve with shift+F2.

It has been almost two years since I presented a successfull session at Directions USA about migrating from the Navision Native (C/Side or Classic) database to SQL Server. This session was repeated in EMEA half a year later.

You can download the slides here.

It was only recently that I found another thing you have to realise when you migrate. It was related to table 49 Invoice Post. Buffer.

Some of you might know that it is used in both codeunit 80 and 90 to combine G/L entries by (VAT) posting groups and some other stuff. This is done by doing a trick in the uniqueness of the records. If you want to add a criteria you must first add the field to the table and then to the keys.

A classic example is to add the posting description to the G/L Entries.

A native database can have up to 20 fields in the primairy key. However, the SQL database only allows up to 16 key fields in the clustered index. If you try to add more you will get this message:

Even though this table wil never be populated on SQL Server NAV maintains the table definition there.

As far as I know there is no solution for this other than reducing the number of fields.

Anyone?

Enjoy.