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 🙂

 

Advertisements

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).

NAV Upgrade Notes – Turning Comments into Notes using .NET

Standard

Upgrading a NAV AddOn is not solely merging code and upgrading the database through all NAV versions in between source and target. It involves analysis and prototyping of present AddOn features into features existent in the latest NAV versions. We often ask ourselves how can we replace feature X in our AddOn hosted on a NAV 2009 installation with the new feature Y in NAV 2017?

Sometimes the answer is Yes: we can merge AddOn functionality with existent features in standard. The benefit is major.

Take Comment Line table for example. Almost any AddOn has a storage place for AddOn entities to record comments or notes.

Why keep them recorded in a table in ISV’s range when we could take advantage of an existent standard table perfect for this purpose? Table Record Link was introduced in NAV 2013 and can keep track of notes and links.

The short demo below assumes the existence of:

  • a table “Generic Entity”
  • a table “Generic Entity Comment”
  • a card page “Generic Entity Card”
  • a list page “Generic Entity Comments”
  •  codeunit “Upgrade Notes 1”

To generate notes for each comment in table “Generic Entity Comment” run the codeunit 90003″Upgrade Note 1″.

The text itself resides in a Text field in “Generic Entity Comment”, but “Record Link” table has a BLOB field for recording the text. Therefore I needed to move text from “Generic Entity Comment”Comment field into a BLOB field in “Record Link”.

text2blob

I wanted to use .NET here not only for exercising my rusty .NET muscles, but recording Danish characters in a BLOB proved to be a non-trivial job. So I used MemoryStream class with BinaryWriter which comes equipped with character encoding capabilities.

For a simple demo follow steps 1-4 below:

capture

Code used is available for download here.

Building a NAV server performance baseline using Logman and Perfmon Windows utilities

Standard

More and more NAV servers are being deployed to the cloud, yet a large number of NAV customers are keeping their NAV installations on premise (either on a physical server or a VM). Those that choose cloud(and if you didn’t please read this) get the benefit of cloud specific tools for measuring the hardware and database performance.

Unless you have a specialized tool, to monitor the performance of your server you need to establish a baseline. During server’s deployment, administrators can  still tweak (especially if it’s a VM or part of a hyper-v infrastructure) the server hardware until the server reaches a satisfactory performance level.

Regular recordings of server’s performance can uncover problems in their early stages, sometimes long before they become obvious in production.

Windows Server 2008 and later server versions as well as Windows 8/8.1/10 come with two easy to use tools for measuring (and scheduling) the performance of the system: perfmon and logman.

With perfmon we get a graphical interface in which we can manually create our alerts or counters, run them and analyze the results.

With logman we can manage the counters from command line.

On my system I created two counters, one for measuring SQL server counters, the other was targeted at the hardware performance:perfmon

Double-clicking on HardwareBaseline we can manage the counters:

hardware

To create these two counters I ran the following script:

install-counters

To start and stop the counters, run:

start-counters

Or manually, in Performance Monitor, right click on the counter and choose Start or Stop.

A few “logman” command switches I use:

-b and -e switches to allow the performance counters to run in a specific time period.

-v switch to add a timestamp for when the output file is created

-f specifies the file format used for collecting data

After a few minutes the performance counters graph will look like this:

graph

With a Task Scheduler entry you can control when to start and stop the performance counters collection.

As for the analysis of collected data, there are lots of places online where you can find valuable information on counter’s results interpretation.

Download the package with hardware and SQL counters and  a sample script from here.

A  baseline case study

One of the processes that is pressing all resources on a NAV server instance in our solution is the running of a report that posts sales invoices for all customers for a specific Due Date. I’ll run the report and record and discuss the counters recorded.

With the installation of MS Dynamics server you get out of the box a few counters NAV specific:

nav-specific-counters I created a new data collector with the following counters:

my-nav-counters

In Performance Monitor right click on your new Data Collector Set and Start.

Next I went to RTC and ran the report.

After the report finished I came back to Performance monitor and stopped the Data Collector Set.

reporton-counters

Microsoft Dynamics NAV\.NET CLR\% Time  in GC:

If RAM is insufficient for MS Dynamics NAV Server instance, you might see a spike in the “% Time in GC” – which measures the .NET memory garbage collection activity. My process shows a maximum of 7% nd an average of a bit over 2%  – numbers that do not show that NAV is looking for more RAM.

Microsoft Dynamics NAV\% Primary key cache hit rate:

This counter should be above 90% – otherwise your cache might be too small because either your cache settings are set too low or the cache is shared between tenants and might need to be increased. I my case is above 99%:

cachehits

% Calculated fields cache hit rate  averages 63% which means that in 63% of the cases when we launched a CALCFIELDS command we hit the cache – decent number!

calcfieldsjpg

# Open Connections is 5, and refers to the number of open connections made from the service tier to the database hosted on SQL Server. You might be interested in the counter “# Active Sessions” which keeps track of the number of concurrent connections  the service tier manages.

openconn

The rest of the counters give numbers of rows, which might or might not be too relevant considering that in time the number of rows increases.

Having a baseline and comparing regular performance counters log against it, is not just a proactive measure in order to keep a NAV server healthy. It is fairly easy to use and cheap (logman and perfmon are both builtin Windows), qualities that appeal to NAV customers and Dynamics VARs.

 

 

 

 

An asynchronous processing solution for MS Dynamics NAV

Standard

From the earliest to the newest NAV versions, long-running tasks are usually accompanied by a progress bar or windows with multiple progress bars. It’s developer’s preferred choice to inform the user with the progress of the launched task – an obvious choice to an hourglass cursor. There are plenty of business processes in any NAV database that do heavy processing. If we look into a posting task, NAV generates Add-On specific ledger entries, standard specific ledger entries, creates invoices and lines, adjacent records and so on. Therefore these tasks  can take a while and can keep the user session busy.

asyncThis blog is about a solution, my boss and I designed and used it on a few target processes, to allow users to immediately regain control of their session after the launch of a long-running task, while assigning the processing to a NAV background session. The results of the processing are recorded and easily retrievable by the calling page automatically by using PingPong Control AddIn.

PingPong info: msdnGunnar’s and Olof Simren ‘s blog.

How does our solution get implemented?

A table with entities to be processed named in the code sample “Generic Entity”. At the minimum the table should have PK fields. Our sample has three fields that are part of the PK: Field_1, Field_2, Field_3.

asynchronous-processing-in-nav

A card page for “Generic Entity” table named “Generic Entity Card” whose only action will launch the async process.

A factbox “Queue Summary Factbox” used on the “Generic Entity Card” to reflect the status of the last queued record processed. The status gets updated by using a PingPong Control AddIn.

A new table “Processing Queue”. At the minimum add enough fields to be able to locate later the record(“Generic Entity”) that launched the process. Additionally, an Option field, “Status” with at least 4 values: Queued, Processing, Error and Success and a Text field “Error Message” to record the errors that prevented the process to finish successfully.

A new codeunit “Process Queue” (whose Rec is “Posting Queue” ). In the Run function of this codeunit wrap the processing in a TryFunction to be able to rollback if errors are encountered.

If wanted you can add more fields to the “Processing Queue”. In our environment we have “Document No.” of the resulting Posted Sales Invoice and “Elapsed Time” to record the duration of the task. You could also enable Lookup on the factbox field to open a list page for Processing Queue table.

If you want to try our solution download it from here.

NAV 2016 – Applying a new NAV Cumulative Update using Powershell cmdlets

Standard

I’ve done a few C/AL code merging for Dynamics NAV in the past, and while I don’t mind working with BeyondCompare for a few hours every now and then, the task is repetitive and prone to errors so I was searching for a cleaner and faster approach. A few days ago my boss told me about (NAV) Powershell commands and that I should give them a try. Why not?

I liked the idea of running a few Powershell commands that do the bulk of the job, from merging to compiling an entire database with minimal assistance on my side. The less “personal touch” the better. Less time spent on redundant tasks lead to less mistakes, and more time for development and trying new technologies.

And this is where I am now. The process still requires (minimal) developer’s interaction – for updating standard objects (VersionList propery) touched by AddOns objects, comparing a few conflict files and of course, running the commands. Albeit all this, the time I recorded to apply a cumulative update is at the minimum halved. Is there room for further improvement? Definitely, with a few more experiments under the belt and testing, the process can further be refined into a single script run, cutting 4-6 hours task to a few minutes.

The steps below are based on a recent task I worked on: to apply  standard NAV 2016 CU11 on top of a NAV 2016 CU5 database + my employer AddOn.

Pre-requisites:

  • A previous CU demo database (the last one you applied) – let’s call it CU(n-1)
  • The current (the one we want to apply) CU demo database – named here CU(n)
  • The current database based on CU(n-1) and your AddOns
  • A working folder Upgrade with 5 folders in it:
    • ORIGINAL
    • MODIFIED
    • TARGET
    • DELTA (used if you want to compare first the differences)
    • RESULT

Steps:

1. Create objects in text format for all objects of:

  • Demo database CU(n-1) -> in the ORIGINAL folder
  • Demo database CU(n) -> in the MODIFIED folder
  • Current database -> in the TARGET folder

Finsql command=ExportObjects, file=c:\Upgrade\Original\Orginal.txt, servername=myserver, database=Demo CU (n-1)

Finsql command=ExportObjects, file=c:\Upgrade\Modified\Orginal.txt, servername= myserver, database=Demo CU (n)

Finsql command=ExportObjects, file=c:\Upgrade\Target\Orginal.txt, servername= myserver, database=your_current_db_with_addons

2. In the NAV 2016 Development Shell run:

Merge-NAVApplicationObject -TargetPath .\TARGET -OriginalPath .\ORIGINAL -ModifiedPath .\MODIFIED -ResultPath .\RESULT -DateTimeProperty FromModified -ModifiedProperty FromModified -VersionListProperty FromModified

This will populate RESULT folder with txt files and conflict folders.

3. Merge code using BeyondCompare or any other similar tool comparing files from ConflictModified folder against correspondent files from ConflictTarget folder. Copy the merged files(overwrite existent) into the Result folder.

4. In your current database(target db) Development Environment select all standard objects that have been touched by your AddOns

5. Take each object of the list in Development Environment and manually add your AddOn version into the correspondent files from RESULT folder

Note: This is because the -VersionListProperty parameter of Merge_NAVApplicationObject cmdlet does not update the version list with the AddOn signature – need to do it manually for now.

6. Combine all text files from RESULT folder into one:

PS C:\upgrade\result> Join-NAVApplicationObjectFile -Source “*.txt” -Destination “all_objects.txt”

7. Import “all_objects.txt” in the development environment manually or using finsql’s command=importobject” parameter.

8. Compile all objects

More MSDN info here and here.

Useful Powershell commands:

  • Create Delta files

Compare-NAVApplicationObject -OriginalPath .\ORIGINAL -ModifiedPath .\MODIFIED -DeltaPath .\DELTA

  • To apply Delta files:

Update-NAVApplicationObject –DeltaPath .\DELTA -TargetPath .\TARGET\*.txt -ResultPath .\RESULT

  • Join all text files into one:

Join-NAVApplicationObjectFile -Source “*.txt” -Destination “all_objects.txt”

  • Import that single file in NAV

finsql.exe command=importobjects, file=C:\Upgrade\Result\all_objects.txt, servername=myserver, database=”merge_target”

  • Split a big text file into individual text objects

Split-NAVApplicationObjectFile -Source C:\Upgrade\ORIGINAL\*.txt -Destination C:\Upgrade\ORIGINAL\TXT\

More MSDN info on cmdlets here.