How to use CAPTIONCLASSTRANSLATE in Dynamics NAV

Standard

Not long ago I stumbled upon a question on the popular NAV forum dynamicsuser.net. The question was “What is the use of CAPTIONCLASSTRANSLATE command in NAV?”.
There was only one answer coming from one of the most influential experts in the NAV world, Luc Van Vugt, but his answer intrigued me even more. As msdn was not very helpful, I started digging into this function use.

First, I wanted to see where is used in standard NAV 2017 in the Cronus database. To find all occurrences of CAPTIONCLASSTRANSLATE in standard NAV I used the Object Manager produced by idyn , a NAV AddOn for managing C/AL code. For my readers that do not have access to this development tool, export all objects to a text file and search in the file for “CAPTIONCLASSTRANSLATE”.

std-nav-use

First occurrence was in report 13, VAT register:

rep13

The report is using the command CAPTIONCLASSTRANSLATE to populate the value of a control(VATEntryClosedCaption) with the caption of “VAT Entry”.Closed field.

Similarly, CAPTIONCLASSTRANSLATE is used in report 22 “No. Series Check”, report 35 “Document Entries”, report 122 “Reminder – Test”, report 123 “Finance Charge Memo – Test”, report 1403 “Bank Account Register”, report 5900 “Service Order”.

A more intriguing way of using the command CAPTIONCLASSTRANSLATE is found in the other objects  as following:

employeelistreport

As we can see above, CAPTIONCLASSTRANSLATE receives a string parameter consisting of three sub-strings separated by two commas.

CAPTIONCLASSTRANSLATE(‘i,j,k’)

By investigating the code in Codeunit 42 “CaptionManagement” and Codeunit 57 “Document Totals” I could draw the following conclusions:

If i = 1 then CAPTIONCLASSTRANSLATE will output Dimension codes.

If j = 1 then CAPTIONCLASSTRANSLATE will output Global Dimensions code.

If k = 1 (i=1,j=1) then CAPTIONCLASSTRANSLATE will output “Global Dimension 1 Code”

If k = 2 (i=1,j=1) then CAPTIONCLASSTRANSLATE will output “Global Dimension 2 Code”

If i = 1 and j = 2 then CAPTIONCLASSTRANSLATE will output Shortcut Dimension Codes(for k=1..8).

dimensioncodes

For printing field captions that have (LCY) in caption use i = 101.

For example to display Amount(LCY) use CAPTIONCLASSTRANSLATE like below:

lcy

To display captions of amount fields that contain the text “Incl. VAT” or “Excl. VAT” use CAPTIONCLASSTRANSLATE with i =2 and j = 0 (to display Excl. VAT) or j = 1 (to display Incl. VAT) as in the examples below:

vat

All examples of CAPTIONCLASSTRANSLATE found in NAV standard were in reports and used to display captions in the table headers or group totals in report layouts.

 

 

 

Day-to-day NAV: Upgrade codeunits

Standard

Starting with NAV 2015 there is an easier way to migrate data between old and new structures of NAV tables.

From msdn excerpt, the upgrade codeunits role is to provide “the logic for migrating existing data in the business data table from the old format to the new format after schema synchronization”.

When we operate a change to the structure of a table the system will detect there are discrepancies between what we just saved through the development environment and what is current in SQL Server.

When we save the object we can switch between three options available for “Synchronize schema”. By default the value is “Now – with validation”.  If we choose “Later” the object is saved but no schema synchronization is performed. If we choose “Force”, the object is saved and the new structure is operated at the SQL Server  level with potential data loss.

If we choose “Now – with validation”, the system will attempt to synchronize schema and because there are structural changes it will check the existence of an upgrade codeunit that can perform the changes without data loss. If such an upgrade unit does not exist we get the error above.

To see the upgrade codeunit in action, I will:

  1. Create a table
  2. Populate it
  3. Change its structure:
    • change the ID# of a field
    • change the data type of a field
    • insert new field in the freed ID#
  4. Confirm schema sync error
  5. Create upgrade codeunit
  6. Attempt to save the object with Schema Synchronization “Now – with validation”
  7. Start the upgrade process
  8. Check upgrade output

Let’s create a table, populate it with some data and attempt to change its structure.

1. Create a table similar to the one below:

1-table

2. Create a codeunit to populate the newly created table:

codeunit-populate

   3. Let’s change the structure of the table as following:

  • ID 6 Status (Option) -> ID 20 Status (Option) [Update1]
  • ID 4 Field_3 (Integer) -> ID 4 Field_3(Code10) [Update2]
  • ID 6 Description (Text30) (new field) [Update3]

Check the current status of the table pre-upgrade in Management Studio

struct-pre-upgrade

4. If trying to save the object with Schema synchronization “Now – with validation” after [Update1] and [Update2] we get this error:

error1

If we try to save the object after [Update1],[Update2] and [Update3]

we get this error:

error2

Notice how the system reports only the last update for field 6, I assume because the Update1 is not committed.

5. Let’s now create the upgrade codeunit:

Start by setting up the Subtype property of the codeunit to “Upgrade”.

The codeunit consists of two functions:

  • A function that will back up the data from our table into a temporary table. [My temporary table is pretty much a copy of the original table. But if you’re not changing a lot of fields in your process, I suggest having in your temp table just the fields in the primary key and the fields that are affected.]. Set the FunctionType property of this function to TableSyncSetup.

tablesyncsetupfnA function that will populate the new structure with the data from the temporary backup table … something like a “forced” schema synchronization with data restore from a backup. Set the FunctionType property of this function to UpgradePerCompany

restoredata

6. Save the object (compile option for schema synchronization Now – with validation). No errors should be encountered at this point if the upgrade codeunit was created.

7. Start the upgrade from the development environment (Tools-Data Upgrade – Start …) or if you prefer powershell, run in Administration Shell: Start-NAVDataUpgrade [InstanceName]

8. Check the content of the table:

afterupgrade

I used upgrade codeunits in day-to-day tasks(recently changing the data type of a field from integer to code), and in upgrade projects when existent fields have been relocated to different IDs.

When the Data Upgrade processes is suspended you can identify potential errors in your upgrade codeunit by running a powershell command: Get-NAVDataUpgrade [InstanceName] -ErrorOnly

But it’s not possible to step through the codeunit with the debugger … or, to be more precise, I couldn’t find how.

Sample code here.

NAV 2017: Wizard pages now supported in Web Client

Standard

As of October 2016, NAV 2017 is the latest version of the popular ERP system. To complement the brand new features, NAV team worked on enhancing current features.

A list of the features not supported in NAV 2016 is located here.  Among them, NavigatePage was not supported in NAV 2016 Web Client, but it is now supported in NAV 2017 Web Client.

Let’s delve into creating a simple (yet fit for starting your own) NavigatePage and check it in the Web Client. Additionally, I will suggest a few cases when a NavigatePage is a good candidate to collect data from users and perform some processing in one screen.

The NavigatePage assumes the existence of the following objects (included in the demo):

  • Table 90001 “Generic Entity” with the following fields:
    • field_1 (Code10)
    • field_2(Code10)
    • field_3(Code10)

PK: field_1,field_2,field_3

  • Table 90002 “Generic Entity Comment” with the following fields:
    • field_1 (Code10)
    • field_2(Code10)
    • field_3(Code10)
    • Entry No.(integer)
    • Comment(Text80)

PK: field_1,field_2,field_3,Entry No.

  • Page 90001 “Generic Entity Card”
    • An action to launch the NavigatePage will be located on this page

NavigatePage details:

navigatepage

And the actions:

navigatepageactions

Note: To make the actions appear as buttons on the toolbar and the groups as tabs set each action’s InFooterBar property to Yes.

This simple NavigatePage will collect data(the comment) from the user in Step1 and when the user clicks Next will insert a comment and make visible Step2.

When user clicks Next, the system will:

  • validate the fields in Step1
  • Hide the fields in Step1 group
  • Perform Wizard action (generate the comment)
  • Show Step2 group

When the user clicks on Finish the page closes.

A few snapshots of the NavigatePage as it appears on the Web Client:

genericentitycardpage

Launching Comment – Wizard action:

genericentitycommentwizzard

Click on Next will move the wizard into Step 2:

wizzardsummarystep

Standard NAV has a few NavigatePage pages. Check them out:

  • 5077 -> Create Interact
  • 5097 -> Create To-do
  • 5126 -> Create opportunity

If you have processes in which the users need to go through a few pages to enter data and create different entities, then NavigatePages are a great candidate for improving user efficiency and experience.

I used recently NavigatePages in two instances. Once, when I needed an unified screen to allow users to attach files (word docs, excel files, pdfs) in a Property Management AddOn. The documents were collected and attached to an email sent to the owner at month-end.

Another example was more recent when, with one NavigatePage, I was able to create records similar to a fixed asset, collect mandatory data for a purchase invoice and generate the purchase invoice for that record. Of course you need methods that will take care of generating the purchase invoice, its lines, general ledgers, vendor ledgers, dimensions. Moreover, assertiveness is needed to not end up with half-performed processes … But it’s possible to run your processes through a wizard page with an overall improved user experience and efficiency.

Sample code available here.

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.