Plataan

Import .fbk undocumented feature

Recently I was giving training about the upgrade toolkit and the developer's toolkit and I noticed that not many people know about this undocumented feature of Dynamics NAV.

Sometimes, especially when doing upgrades in Dynamics NAV, you need to import a lot of objects into a database. Normally you then first export the objects into a .fob or .txt file and then you import them into the new database.

Did you know that there's another way to do this?

If you have an .fbk file of a database (backup file), then you can also use this .fbk file to import into another database.

If you import a .fbk file (File – Import, while the object designer is opened), then Dynamics NAV will look for all of the objects that are in the .fbk file, and will try to import those objects. Next Dynamics NAV will open the Import Worksheet, showing the objects in the .fbk file. Here you can decide what needs to happen with the objects during import (skip, replace, merge…) and when you click on OK the objects are imported, just as if you import a .fob file.

Nice, isn't it!

Requisition Worksheet

Recently I found out a little trick about the Requisition Worksheet in Dynamics NAV.

For some reason the worksheet was not calculating for Items that were not setup with a SKU (Stock Keeping Unit). When we made SKU's for the items, then the calculation worked properly, but without a SKU it did not calculate anything. This seemed very strange and after a little research I found a workaround.

The workaround is to use the location code in the manufacturing setup. Even if not running manufacturing, this part of the manufacturing setup is available.

In the workaround, you have to decide a "replenishment location". This replenishment location is setup using the "Component at Location" field at the Manufacturing Setup. (Manufacturing | Setup | Manufacturing Setup | Planning Tab | Component at Location field)

Once a location is selected in MFG setup, when calculate plan is selected from the requisition worksheet the system will create a Purchase Order for items that are not setup with a SKU.

We were working on a Dynamics NAV 5.0 SP1 German version, but I think this workaround may also work in other and earlier versions of Dynamics NAV.

Dynamics NAV 5 SP1 BE released

This is to inform you that Service Pack 1 for Dynamics NAV 5.0 has finally been released for Belgium. (And other GRP2 countries). You can download it on PartnerSource.

Why is this so interesting?

Well besides numerous bug fixes and new functionality there has been put a lot of effort in performance optimalisation towards Sql Server.

First of all, the famous SIFT-tables have been replaced by indexed views:

Microsoft Dynamics NAV now uses "Indexed Views" to maintain SIFT totals. Indexed views are a standard SQL Server feature. An indexed view is similar to a normal SQL Server view except that the contents have been materialized (computed and stored) to speed up the retrieval of data.

Microsoft Dynamics NAV creates one indexed view for each SIFT key that is enabled. When you create a SIFT key for a table, you must set the MaintainSIFTIndex property for that key to Yes to enable the SIFT key and create the indexed view. Once SQL Server has created the indexed view, it maintains the contents of the view whenever any changes are made to the base table. If you set the MaintainSIFTIndex property for that key to No, SQL Server drops the indexed view and stops maintaining the totals.

Second of all, Dynamics NAV will make use of Bulk Inserts:

Microsoft Dynamics NAV automatically buffers inserts in order to send them to SQL Server in one go.

By using bulk inserts, Microsoft Dynamics NAV reduces the number of server calls thereby improving performance. It also improves scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the amount of time that tables are locked; especially tables that contain SIFT indexes.

So the Dynamics NAV 5 SP1 seems to promise some interesting performance boosts when working on Sql Server 2005.

What’s the difference between using @ and # when opening a dialog window?

This is a question I almost always get when giving the C/SIDE Introduction and/or C/SIDE Solution Development trainings so why not make a little blog item about it?

This is the syntax of the OPEN function for a dialog:

Dialog.OPEN(String [, Variable1], ...)

• This string contains the text you want the system to display in the window.
• Use a back slash (\) to start a new line.
• Use pound signs (#) to insert variable values into the string, place the pound signs where you want the system to substitute the variable value.
• If you use @ characters instead of #, the string can be used as an indicator. In this case, use @ characters only for the string, and let the variable be an integer.
• The limits of the indicator are 0 and 9999 - meaning that the integer you use for updating the indicator should have a value within this range.

Example in a Codeunit:

Declare 2 variables:

Then write this code:

Now run the Codeunit, result:

To summarize:

How can I move my database files in SQL Server?

This is a question I got asked a few times the last few months, so why not write a little blog article about it.

An easy way to move user database files to a different location is by detaching, moving and attaching the user database.

(Of course, while you are doing this, people will not be able to work in the database. Make sure they logout of Dynamics NAV correctly.)

This database move can be done in SQL Server Management Studio (SSMS) so you don't have to type any code:

• Start SQL Server Management Studio
• Expand the server instance, expand Databases
• Right-click the database you want to move, and choose "Properties"
• In the Properties window, choose "Files" and write down the current file paths. Click "Cancel"
• Right-click the database again, and choose "Tasks - Detach..."
• Click "OK" in the next window
• Use Windows Explorer to move the data and log files (.mdf and .ldf) to the new location
• Right-click Databases, and choose "Attach..."
• In the "Attach databases" window, click "Add"
• In the "Locate database files" window, browse to the new location and select the .mdf file. Click "OK"
• In the details pane, verify that the new location is listed for both the .mdf and the .ldf file. Click "OK"
• In SQL Server Management Studio, choose "View - Refresh" and verify that your database is listed again under Databases

Alternatively you can back up the database, within Sql Server, and restore it, specifying a different location for the files on the options table or use the MOVE clause in the RESTORE command in Transact-SQL:

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

Key Groups and new functions.

Keygroups have been available in Dynamics NAV for some time now, but since the release of Dynamics NAV 5.0 some new functions can be used to enable and/or disable these key groups via CAL code:

• [Ok :=] KEYGROUPENABLE(GroupName)

  Use this function to enable a key group.

• [Ok :=] KEYGROUPDISABLE(GroupName)

  Use this function to disable a key group.

• [Ok :=] KEYGROUPENABLED(GroupName)

  Use this function to check whether or not a specific key group is enabled.

When you have a look in the design of a table and open the list of Keys of that table you will notice that every Key has a KeyGroup property. You can see this property to specify the predefined key groups that a key belongs to. After you have assigned a key to one or more key groups, you can selectively activate or deactivate the key by enabling and disabling the key groups. In the past this was only possible by going into the Database Key Groups window.

To enable or disable a key group for sorting, click File, Database, Information and the Database Information window appears:

Then click Tables, Key Groups and the Database Key Groups window appears. You can then enable or disable a key group by clicking the appropriate button:

Unfortunately, it is very difficult to get an overview of the keys and tables that are affected when you enable or disable a key group. However, you can look at the design of any table and see the keys that have been defined for that table and whether or not the keys belong to a key group.

Adding a large number of keys to database tables decreases performance. However, by making the keys members of predefined key groups you can have the necessary keys defined and only activate them when they are going to be used.

And as from Dynamics NAV 5.0 we now have the possibility to do this in the CAL code.

This can be useful for reports that use certain keys but that are executed very rarely, for example only once a year. By only enabling these keys when they are needed you can avoid the performance overhead of maintaining them when they are not used.

Remarks:

Be careful with this. When you disable a key, it is no longer available and can also not be used in code. Disabling a key is like deleting it; the difference is that it is still in the list of keys, but disabled.

When working on Sql Server, this means that if that key had any SumIndexField(s) attached to it, the related SIFT tables will also be removed if you disable the key (and recreated when you enable the key).

Enabling a key, means that the index table has to be rebuild, and so this can cause performance issues and locking, especially when there are many records in the table.

This is not to be used as an alternative for not maintaining a key on Sql Server. The MaintainSqlIndex and MaintainSIFTIndex/SiftLevelsToMaintain properties are meant for that purpose.

Plataan hits the news

During the last months Plataan was working hard on the development of Official Microsoft Courseware (MOC) for Microsoft Dynamics Sure Step Implementation Methodology Version 2.

Plataan was granted this assignment by Microsoft Corporation, Redmond US. Plataan was also selected to deliver the train-the-trainer sessions for this courseware both in the US as EMEA.

The Belgian regional televison channel TVL made a newsitem on these assignments, broadcasted during evening news. You can watch it here.