NAV 2013 R2 | Keys, Keygroups and SetCurrentKey

With NAV 2013, the Classic Client and the Native Navision database were discontinued.

One of the possibilities that arised was improving the SQL story in NAV.

This was done first by implementing SETAUTOCALCFIELDS and the possibility to do CALCSUMS and FlowFields without specifying a SumIndexField. This is released in NAV2013.

In NAV2013 R2 the story continues

Keys

In the Native database the structure how Navision maintained Keys was perceived as revolutionairy and a main part in the success of the Application and its architecture.

Within a Key one could specify SumIndexFields. These are decimals that then would be totalled in the background without writing code.

Everything in NAV like G/L, Inventory and Jobs is based on that principle. You will not find any Application code in NAV that totals entries.

To be able to sort something a Key was required in the Native database. A maximum of 40 keys were allowed. This is different in SQL Server.

SQL Server can sort without having an Index (the name for Key in SQL Server). Therefore with the Native database being discontinued the possibility opened to change the Key story in NAV.

Therefore with the introduction of NAVA 2013 R2 it is no longer required to define a Key on a table to sort data.

It is now possible to sort on any column in the UI.

And it is also possible to sort on any field from AL code.

SetCurrentKey no longer requires a Key to be created in the table.

KeyGroups

Another change that has been imlemented is the discontunation of KeyGroups.

After internal debate and investigation Microsoft decided that the active use of the feature was not enough to justify the effort of moving the feature from Classic Client to PowerShell.

Do you agree with this?

Advertisements

5 thoughts on “NAV 2013 R2 | Keys, Keygroups and SetCurrentKey

  1. Keys
    It would seem that not having to define keys as strictly as before is an advantage, until I hit a problem with a flowfield in NAV2013 recently. It took me hours to investigate that the cause of gradual performance decline on a Page was associated to a flowfield; it was filtering on fields not part of any key in the source table. Once I added this key, the problem was solved immediately.

    KeyGroups
    I have never needed to use KeyGroups for any NAV system over the last 11 years. I guess this was introduced to in the early days to help save of space and related performance?

    Like

    1. As usual, performance tuning changes with each version of NAV. The things I’ve taught people in my classes before will change. We’ll go from over indexed to under indexed. Also covering indexes will be more important.

      Like

  2. Also interesting is I exported table 18 to text from an unmodified 2013R2 NA database and exported the same table from a 2013R2 NA database with EFT functionality installed. Using the old (As in original from Navision) compare tool to compare these 2 files It’s interesting to note that one of the changes shown is the added key Search E-Mail with the setting KeyGroups=EFT. Nowhere to be found in the object itself in design mode is this setting. Yet it shows in my text file. In fact there are several entries in an unmodified table 18 for KeyGroups=SearchCol. Export this table to text, open with Notepad, search for KeyGroups and there they are.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s