3 Easy Methods to move company data between NAV databases

Standard

If there is something that changes in NAV every few releases, is how we backup and restore data (be that full backup, application data, companies data) between NAV databases.

Recently I was asked how is this done in NAV 2016.

The answer is simple and is applicable to both NAV 2015 and NAV 2016:

  • with 2 powershell cmdlets
  • via Pages 9901 (for Export) and Page 9900 (for Import)
  • using C/AL functions EXPORTDATA/IMPORTDATA.

Let’s assume the following case. A bug was reported by a customer. You want to have access to his production environment, but many times that’s not possible. One way is to do a SQL backup of customer’s database. That assumes you will have at least backup rights on their database server. Many times that’s not possible. But you probably have access to NAV.

Let’s try to export company data with powershell cmdlets:

  • Launch Dynamics 2016 Administration Shell
  • Run the following cmdlet:NAV-ExportData
    • DatabaseName : look in the NAV Administration console for the database name associated with your instance
    • FilePath: it is expected a file with extension “navdata”
  • Copy navdata file onto your local machine and run the following cmdlet to restore the company data in your other environment (be that development, or QA …)NAV-ImportData
    • Similarly to the previous step, DatabaseName can be extracted from the NAV Administration console and FilePath is a local file with a “navdata” extension
    • The cmdlet is confirming that we want to overwrite the company “…” on the local server “.\ServerName”
    • After confirmation of the overwrite, the importing starts, but we get an error. The cmdlet reports that there is a difference in a table definition. In the example above, on the destination database, one of the tables had a few new fields, just because the destination environment was a development copy of customer’s database.
    • To get around this error you need to have similar objects on the destination and the source. So I saved the latest changes on the target database (exported to a fob) calling it LatestChanges.fob. Then I sync’d objects from customer production with target database. I re-run again Import-NAVData and this time it run without issues.
    • ImportSuccesful
    • After a succesful import of company data you can restore your code from LatestChanges.fob
  • The easiest way though, to restore a company data, is to use Page 9901 (for Export)on the Source and Page 9900 (for Import) on the Target.

To export/import data via C/AL code check EXPORTDATA and IMPORTDATA functions:

Msdn EXPORTDATA and Msdn IMPORTDATA

For a sample of how to use these 2 functions, check the code in pages 9900, 9901 in W1.

Original post here.

 

Advertisements

Dimensions in NAV 2016.. – design overview

Standard

As stated here, beyond the setup and usage of dimensions, developing with NAV requires a better grasp of dimension’s internal design.

I’ll be diving shortly into the dimension related concepts: dimension set entries (Table 480 Dimension Set Entry), search trees (Table 481 Dimension Set Tree Node), dimension management (Codeunit 408 DimensionManagement).

The following use case will create a sales invoice, and add dimensions. We will inspect the main tables and the code in codeunit 408 DimensionManagement to understand what happens. But first, let’s setup some dimensions and their values:

  1. Open Page 536 “Dimension” and create a new dimension (DEPARTMENT), this will create a record in Table 348 “Dimension”
  2. On the same Page, in the Navigate FastTasb, launch “Dimension Values”. This will open Page 537 “Dimension Values” where you can create a few dimension values for this new dimension: these new values will be stored in Table 349 “Dimension Value”
  3. Repeat steps 1 and 2 for two more dimensions AREA and SALESPERSON.
  4. Assign Global Dimensions in General Ledger Setup (Open General Ledger Setup Page and launch “Change Global Dimensions …” action) to Department and Area. This will fill up General Ledger Setup fields: “Global Dimension 1 Code” and “Global Dimension 2 Code”. NAV will assign “Shortcut Dimension 1 Code” and “Shortcut Dimension 2 Code” with these two global dimensions.

GLS

Note: If you’ve updated the Global Dimensions you’ll be warned that the system will run an update on all posted entries to update the dimensions. After the update restart RTC, otherwise .

At the end of the 4 steps this is how the affected tables look like:

Table 348 “Dimension”:

T348

Table 349 “Dimension Value”:

DimVal

 

Let’s create now a Sales Invoice and note how the dimensions are handled.

On the Lines Fast Tab on the Sales Invoice add the columns for the newly picked dimensions: DEPARTMENT Code and AREA Code.

Assign AREA Code field the value “EAST”:

 

SL_1

Validating the “Department Code” involves updating the “Dimension Set ID” for the sales line. The core of this operation happens in Codeunit 408 DimensionManagement:

CU480_1

Looking into table 480 and 481 this is the current situation in our exercise:

480_481_1

NAV has created an entry in the table 480 Dimension Set Entry for the Dimension/Dimension Value pair AREA=EAST and assigned it an ID = 3254.

Additionally, NAV created a search tree in which at this point we only have a node(the root) for the just started dimension combination with one dimension (AREA=EAST).

Lets assign now a new dimension, the Department code to SALES:

SalesLine_Department

Let’s check what happened in Table 480 and 481:

480_481_2

 

In Table 480 “Dimension Set Entry” NAV inserted two more lines (2nd and 3rd) and assigned to both Dimension Set ID = 3255. This is to identify the 2-tuple AREA=EAST and DEPARTMENT =SALES uniquely with ID = 3255. I do expect that, if I add a new dimension that would be assigned an ID=3256 and we will have 3 new records with that ID.

In the left side we have the trees.

The middle one (0,5039;3254) was generated in the previous step when we assigned the first dimension (AREA=EAST); As part of assigning a second dimension (DEPARTMENT=SALES) the system generated two more trees:

  • first one is a tree with a single node for DEPATMENT=SALES
  • second tree has two nodes: as root we have the node with Dimension Set ID=1 (DEPARTMENT=SALES) and as branch from this root we have a node with Dimension Value= 5839 and Dimension Set ID = 3255 which we can see in Table 480 is the node for AREA=EAST.

Tree_2_Dims

Let’s fill up a third dimension and check if our expectations were right:

SL_3

By inspecting the two tables (480 and 481) we notice:

480_481_3

  • 3 new records in 480 to identify the 3-tuple (AREA=EAST,DEPARTMENT=SALES,SALESPERSON=SAM). All three records have been assigned Dimension Set ID = 3256.
  • In table 481, NAV created a 4th search tree, beside the 3 existing ones

4th_TREE

In code, if we want to update the “Dimension Set ID” we would do something similar to the code in ValidateShortcutDimValues function in Codeunit 408 “DimensionManagement”

UpdateDimSetID

If you need to get a combined Dimension Set ID based on two existing Dimension Set IDs you can use the function GetCombinedDimensionSetID to do it in one shot:

MultipleDimSets

To understand the benefit between old way of managing dimensions (Pre-2013) and the new way, check archerpoint article.

Thanks for reading, sharing commenting … Much appreciated!

Original article here.

Setup and How-Tos re: Dimensions in NAV2016..

Standard

Recently I’ve been asked to explain the concept of dimension in NAV and while I managed to come up quickly with an answer I wanted to spend some time researching the topic so that next time I need to offer a similar explanation my answer will sound more academic, plus I’ll have a link to refer to 🙂

According to msdn, “A dimension is data that you can add to an entry as a kind of marker so that the program can group entries with similar characteristics and easily retrieve these groups for analysis purposes.”.

A few characteristics of dimensions:

  • can be applied to documents and journals
  • dimensions have dimension values, for example we can set a dimension AREA with 3 dimension values: Europe, America, Africa.
  • used to create extracts of financial statements for statistics and analysis. In creating these statistics we can use more than one dimension, for example we can look at sales by the following dimensions AREA and DEPARTMENT.

There are two types of dimensions I want to cover here: global dimensions and shortcut dimensions. Additionally, there are budget dimensions that can be defined at the moment when you generate a new budget.

Both, global dimensions as well as shortcut dimensions can be set up for the company in the General Ledger Setup. There are maximum 2 global dimensions and maximum 8 shortcut dimensions. NAV defines the first two shortcut dimensions the same with the global dimensions.

Global dimensions are available for input across  the system in tables like 15 “G/L Account”, 18 Customer, 23 Vendor or in ledger entries tables like: 17 “G/L Entries”, 21 “Cust. Ledger Entries” and 25 “Vend. Ledger Entries” (Fields 23,24). Because Global Dimensions are being part of the core system they can be used in filtering ledger entries, filtering in the reports, account schedules or batch jobs.

Shortcut dimensions(set up initially in the General Ledger Setup) will be available at the document level, or at the journal lines level. On each line of the document(sale or purchase) or journal line add the column with a name(code) that was designated in general ledger setup as shortcut dimension and assign to it one of the available dimension values. E.g. if I have defined Global Dimension 1 as DEPARTMENT, I’m expecting to have in the General Ledger Setup the “Shortcut Dimension 1 Code” as DEPARTMENT and when I create a Purchase Invoice, on the lines, to have available a field “Department Code”.

To create dimensions, use Page Dimensions and enter or modify dimensions.

To create values for each dimensions, on the same page go to Navigate Tab, and launch action “Dimension Values”. To assign default values for dimensions, on the same Page (Dimensions) use action “Account Type Default Dim.”. If you dont’ want to specify a default dimension value code, but you want to have it on each ledger entry, set the value of “Value Posting” field to “Code Mandatory”. This way you ensure that no General Journal Line or document will be posted without a value for the specific dimension.

In a future blog I will dive into the technical design of dimensions covering notions such as dimension sets, dimension set entries, search trees and a few notes on performance improvements compared to older dimension design.

Thank you for reading, sharing, commenting … Much appreciated!

Original blog here.

NAV 2017 – How To Start With Application Testing In 5 Easy Steps

Standard

Thanks for landing here!

Microsoft has developed a series of automated tests that allow developers to test various features of the standard application before releasing new functionality in production.
NAV Developers can extended Microsoft’s work to include testing of their work.

  1. To test the standard application, the developer needs to install the Test Toolkit as delivered with each build by Microsoft. The Test Toolkit can be found in the ‘TestToolkit’ folder of the build.

E.g.

1

Contents of the TestToolkit folder:

  • CALTestRunner.fob – contains objects to support the application testing, including tables (e.g. CAL Test Suite, CAL Test Line, CAL Test Codeunit); codeunits (e.g. CAL Test Runner), and pages (e.g. CAL Test Tool).
  • CALTestLibraries.fob – contains libraries used in the actual tests, such as the “Assert” codeunit or the “Library – Random” codeunit
  • CALTestCodeunits.fob – the actual tests Microsoft designed to test different sections of the standard application

2.  To run standard tests, run Page 130401 “CAL Test Tool”.

3. Develop your own tests:

  • create a test suite (create record in table 130400 “CAL TestSuite”)
  • create groups, test codeunits and individual tests. You can generate them via a codeunit like below:

2

For test codeunits increase “Level” and the “Line Type” to Codeunit, for individual tests increase again “Level” and change the “Line Type” to Function.

4. Create a test codeunit and give it the ID you specified in the previous step.

Change the codeunit subtype to Test to make it testable codeunit.

3

5. To create individual tests, create a function and change FunctionType to Test:

4

Depending on the code you’re testing you might need to specify your UI Handlers in HandlerFunctions property.

Please visit msdn for so much more info.

Happy testing!

Thank you for reading, sharing, following, commenting …

Original post here.

NAV 2017 – Delivering custom code as extension

Standard

We can still deliver our custom code as a fob in NAV 2017 as most VARs will continue to do so in the foreseeable future, however it seems to me that ISVs will embrace faster the new method. While in my first blog on extensions I was focused on explaining the basics of the technical side of creating an extension, in this blog I will delve into delivering a functional requirement as an extension.

I will go through an exercise of adding a new field: “Created By” Code 20 (a valid user from User table)  to Sales documents.

If you’re planning to follow this exercise please review “Setting up your working space” from my previous blog.

I will make this new field available on:

  • Table 36 “Sales Header”
  • Table 112 “Sales Invoice Header”
  • Table 114 “Sales Cr. Memo Header”
  • Page 43 “Sales Invoices”
  • Page 44 “Sales Credit Memo”
  • Page 132 “Posted Sales Invoices”
  • Page 134 “Posted Sales Credit Memo”.

The field will be passed on and made visible on

  • Page 20 “G/L Entries” and
  • Page 25 “Cust. Ledger Entries”

when we drill down on different tables exposed via Navigate action on the posted document.

At this moment, delta files for reports (standard report modifications) cannot be part of an extension. Please visit Extension Packages Capability Support Matrix for updates on this topic.

Therefore my attempt to modify the report 1306 to add the new field in the layout and report failed:

ext21

Delivering modifications to reports must be done in a different way. One alternative is to add a new action on the Posted Sales Invoice and point to a copy of report 1306 (modified to include our field). Therefore our custom code won’t be extension-only based.

This link contains a fob with all the modifications done to a Demo Database 2017 CU9.

The steps to create and publish the extension are very similar to the steps in my previous blog located here.

Let’s test the changes by pointing our Service tier to the target database and publish there the new extension.

First let’s install the extension, by opening page 2500 “Extension Management”:

ext22

Let’s open Sales Invoice page and create a new invoice. The “Created By” field should be in the first FastTab and auto-populated with the logged in user:

ext23

After posting the invoice we notice that the new field is part of the “Posted Sales Invoice” as well:

ext24

Next we want to check the existence of this new field in page “General Ledger Entries” and page “Cust. Ledger Entries”. In the “Posted Sales Invoice” page, in the Actions tab, click on Navigate, and check the 2 pages:

Ext25

ext26

At the core of this simple exercise is the codeunit 50000 in which I subscribed in a few instances to standard events like OnAfterInsertEvent:

 ext28

Thank you for reading, sharing, commenting, liking, following 🙂

 

How To : 5 Easy Steps to generate your First NAV Extension

Standard

Thank you for landing here.

It’s quite common nowadays to hear NAV people talking about Dynamics 365, AppStore, extensions, new AL language and the Visual Studio Code.

So extension is a concept that sooner or later NAV developers need to grasp.

In this blog I will create one simple extension. I have seen a few demos on the web, but I had  a few issues following them. I will highlight these issues as I go.

I encourage everyone to visit msdn page on this topic.

1. Setting up your working space

On my development machine I installed NAV 2017 CU 9. I will be working with 3 databases which initially will be restored from your NAV DVD, from the folder below:

SQLDemoDatabase\CommonAppData\Microsoft\Microsoft Dynamics NAV\100\Database
  • restore via SQL Server Management Studio the Demo database as Demo Database NAV (10-0) (we won’t touch this – it will be our ORIGINAL DB)
  • restore via SQL Server Management Studio the Demo database as TestExtension (we will make modifications on this database – it will be our MODIFIED DB)
  • restore via SQL Server Management Studio the Demo database as ApplyExtension (we will install our extension here – it will be our TARGET DB)
  • create a NAV service(I named mine TestExtension) pointing to the database you will be working with in the development environment, in our case is TestExtension
  • created 3 folders on your local drive: ORIGINAL, MODIFIED, DELTA

2. Create extension content

I added field 50000 “Planet No.” – Integer on table 14 Location.

I added this new field to Page 15, “Location List”.

I created a new codeunit, 50000, TestExtension, with 2 empty and local functions:

Extension1

And these are the 3 objects:

Ext2

3. Populate local folders: ORIGINAL,MODIFIED and DELTA:

a. Populate ORIGINAL:

Point Service tier towards Demo Database NAV (10-0) and from Dev. environment export all objects (or just Table 14 and Page 15 if you want a faster text file processing) as Original.txt. As this file contains a concatenation of all objects we will need to split the big file into smaller ones (one per object). In the ORIGINAL folder create a Split folder. From the Dynamics NAV 2017 Administration Shell run:

Split-NAVApplicationObjectFile -Source .\original\*.txt -Destination .\original\split\

And this is the end-result:

ext3

b. Populate MODIFIED

Point Service tier towards TestExtension and from Dev. environment export all objects (or just Table 14 and Page 15 if you want a faster text file processing) as Modified.txt.
For the same reason as in the previous step split big Modified file:

 Split-NAVApplicationObjectFile -Source .\Modified\*.txt -Destination .\Modified\split\

c. Populate DELTA

To create Delta files run:

Compare-NAVApplicationObject -OriginalPath .\ORIGINAL\SPLIT\ -ModifiedPath .\MODIFIED\Split\ -DeltaPath .\DELTA\
Processed 4965 objects:
 Inserted 1 objects
 Deleted 0 objects
 Changed 2 objects
 Identical 4963 objects
 Failed 0 objects

Have a look at the composition of a Delta file:

ext4

4. Create NAVX file (extension file)

From the Microsoft Dynamics NAV 2017 Administration Shell run:

 New-NAVAppManifest -Name "FirstExtension" -Publisher "SVIRLAN.com" -Version "1.0.0.0" | New-NAVAppPackage -Path FirstExtension.navx -SourcePath .\DELTA

5. Publish extension

To publish the new extension in the ApplyExtension database, point your service tier towards the ApplyExtension database and from the Microsoft Dynamics NAV 2017 Administration Shell run:

 Publish-NAVApp -Path .\FirstExtension.navx -ServerInstance TestExtension -SkipVerification

Open page 2500 “Extension Management” and click Install on our new extension:

ext5

You can Un-install the extension(via action on Page 2500) and you can Un-publish it via “Un-Publish” powershell command.

Facts:

Let’s have a look at what we’ve got by applying our extension to the ApplyExtension database:

The Locations page contains the new field:ext6

But when looking at the objects we dont see the codeunit 50000, the new fields nor the changes in the Version List:

ext8

ext9

Moreover, trying to add the field 50000 in the Location table prompts this system error:

ext7

This makes sense!

The code in table14 and Page 15 remained as delivered by Microsoft, and the extension fields are somehow managed internally via system objects like tables 2000000150 – 2000000163.

Issues encountered:

1. First error I encountered was: “The package contains changes to the database schema that are not handled in upgrade code.”

I needed a codeunit to subscribe to standard published events:
That’s the reason I have codeunit 50000 and the 2 functions:
OnNavAppUpgradePerDatabase()
OnNavAppUpgradePerCompany()

PS C:\docs\blogs\Extensions> New-NAVAppManifest -Name "Proseware SmartStuff" -Publisher "Proseware, Inc." -Version "1.5.0.12" | New-NAVAppPackage
 -Path MyExtension.navx -SourcePath DELTA
 New-NAVAppPackage : The package contains changes to the database schema that are not handled in upgrade code.
 At line:1 char:100
 + ... 1.5.0.12" | New-NAVAppPackage -Path MyExtension.navx -SourcePath DELT ...
 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : InvalidArgument: (Microsoft.Dynam...ewNavAppPackage:NewNavAppPackage) [New-NAVAppPackage], InvalidOperationExcepti
 on
 + FullyQualifiedErrorId : Microsoft.Dynamics.Nav.Apps.Tools.NewNavAppPackage

2. Second issue happened when running the Publish-NAVApp command: “Access is denied. You need to be a member of the local Administrators group on the server to run this cmdlet.”

Need to run powershell as Admin:

PS C:\docs\blogs\Extensions> Publish-NAVApp -Path .\FirstExtension.navx -ServerInstance TestExtension -SkipVerification
 Publish-NAVApp : Access is denied. You need to be a member of the local Administrators group on the server to run this cmdlet.
 At line:1 char:1
 + Publish-NAVApp -Path .\FirstExtension.navx -ServerInstance TestExtens ...
 + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 + CategoryInfo : NotSpecified: (:) [Publish-NAVApp], NavCommandException
 + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$TestExtension,Microsoft.Dynamics.Nav.Apps.Management.Cmdlets.PublishNavApp

3. In case any of the powershell cmdlets is not recognized in your environment run:

PS C:\docs\blogs\Extensions> Import-Module “C:\Program Files (x86)\Microsoft Dynamics NAV\100\RoleTailored Client\Micro
soft.Dynamics.Nav.Model.Tools.psd1” –force

Thank you for reading, sharing, commenting, liking, following 🙂

 

NAV Upgrade Notes – First essential step for a successful NAV upgrade

Standard

The first step in my last NAV upgrade was to back up customized tables. Although having database backups throughout your upgrade process is a no brainer, my focus in this post is backing up locally tables that contain non-standard fields. Having the heavy customized tables readily available in the same database, somewhere in the range 50000-99999 it can prove useful.

For example, let’s say the table 21 Customer Ledger Entry in my customer NAV 2009 database has a few non-standard fields in the range 50k or some other range. My plan is to have an exact copy of this table with ID 50000, table that I can carry forward until the upgrade process reaches NAV 2017.

Having the original data allows me to:

  •  re-construct any field that might have gotten lost or overriden during the upgrade (through a Forced sync without an upgrade codeunit or some other faulty process) or
  • perform conversion validation like comparing the balance for each customer in the original table(now saved in the 50k range) with the value from the upgraded table.

To backup the table I start with opening the original table (21), copy all fields, create a new table in the 50k range (60007) and paste all the fields. In the new table we don’t need any business rules, therefore we can select all code lines and delete them.

I would do the same for all tables whose data I want to back up.

If your backup set includes like mine, hundreds of tables, I would create a new table(50000) where I am planning to keep all original-backed up table pairs. For example, I can have pairs like table 21 – table 60007 in the new table:

List Of Tables

Next we need to populate the new table 60007 with original data.

Create a new codeunit and traverse table 50000. For each row execute the following function:

CopyData

This method copies the value of “Normal” fields from the source table(21) into each correspondent field from target table(60007).