Performance compare tests – Part 2

another test scenario:

same preconditions like in last post.
1. db: orig. nav 2017 demo db
2. db: to nav 2017 converted nav 2013 demo db

Now developed a recordload test with g/l entries. wanted to know the record load time per entry.

orig. nav 2017 demo db:
(total no. of entries) | (total load time) | (load time per entry)
run 1: 3186 | 282 | 0,088512
run 2: 3186 | 265 | 0,083176
run 3: 3186 | 250 | 0,078468

converted nav 2013 demo db:
(total no. of entries) | (total load time) | (load time per entry)
run 1: 2842 | 171 | 0,060169
run 2: 2842 | 156 | 0,054891
run 3: 2842 | 141 | 0,049613

the test code:
// dt / DateTime
// timePerLoad / Decimal
// noOfEntries / Integer
// totalTime / Decimal
noOfEntries := IterateGLEntries;
totalTime := CURRENTDATETIME – dt;
timePerLoad := totalTime / noOfEntries;
MESSAGE(FORMAT(noOfEntries) + ‘ | ‘ + FORMAT(totalTime) + ‘ | ‘ + FORMAT(ROUND(timePerLoad,0.000001)));

IterateGLEntries() : Integer
glEntry2.GET(glEntry.”Entry No.”);
UNTIL glEntry.NEXT = 0;

also interesting …


Performance compare tests between Nav 2013 and Nav 2017

There were some discussions about the newer Nav versions performance. As for my own experience, i can say Nav 2017/2018 releases are quite slow.

To get a better feeling about that i did the following:

I converted Nav 2013 Cronus db (build 46056) to Nav 2017 (build 13682) by simply run the convert job in the dev. env. nothing more. then created a nav service for the converted nav2013-db.

test scenario: i wanted to test the load time for the item list page. in many huge database there can be 1000s and 1000s of items. it’s a very important page, very often loaded per day by many people. so high peformance would be a good thing. in both databases are the same no. of items: 145. in both pages are the same columns/fields in the same order shown. i have the same nav system, same binaries, but different db. so the situation is quite ok for a valid comparison.

next step: i wrote a simple test cu for loading the item-list page.

[Test] TestItemList()
// ItemList / TestPage / item list
// dt / DateTime

i started the test cu with the simple test function and measured the time:
1. run: 270ms
2. run: 230ms
3. run: 170ms

now copied that test cu to the nav2017 db, ran the same test, measured the time:
1. run: 380ms
2. run: 320ms
3. run: 300ms

WOW!!!!!! simple test, the numbers tell the story!
What’s about other main list pages (customers, vendors, ledger entries and so on)? What about performance of posting processes? What would i measure, if i continue that kind of tests? i think better not measure …

Is that really a progress??? What is the advantage of new features, if the performance lowers and lowers from release to release ???

i think here is a todo for the nav microsoft team.




Tables Changes from Nav 2013 to Nav 2017 – first shot

For those who would like to have release notes when upgrading from nav 2013 to nav 2017 i have developed following table comparison.

New in Nav 2017:

60Document Sending Profile
61Electronic Document Format
62Record Export Buffer
130Incoming Document
131Incoming Documents Setup
132Incoming Document Approver
133Incoming Document Attachment
134Posted Docs. With No Inc. Buf.
135Acc. Sched. KPI Web Srv. Setup
136Acc. Sched. KPI Web Srv. Line
137Inc. Doc. Attachment Overview
197Acc. Sched. KPI Buffer
249VAT Registration Log
326Tax Setup
477Report Inbox
487Business Chart User Setup
570G/L Account Category
710Activity Log
777Online Bank Acc. Link
851Cortana Intelligence
870Social Listening Setup
871Social Listening Search Topic
980Payment Registration Setup
981Payment Registration Buffer
983Document Search Result
1034Job Planning Line – Calendar
1060Payment Service Setup
1062Payment Reporting Argument
1150Report Totals Buffer
1200Bank Export/Import Setup
1205Credit Transfer Register
1206Credit Transfer Entry
1209Credit Trans Re-export History
1213Data Exchange Type
1214Intermediate Data Import
1220Data Exch.
1221Data Exch. Field
1222Data Exch. Def
1223Data Exch. Column Def
1224Data Exch. Mapping
1225Data Exch. Field Mapping
1227Data Exch. Line Def
1231Positive Pay Entry
1232Positive Pay Entry Detail
1235XML Buffer
1237Transformation Rule
1240Positive Pay Header
1241Positive Pay Detail
1242Positive Pay Footer
1248Ledger Entry Matching Buffer
1249Bank Stmt Multiple Match Line
1250Bank Statement Matching Buffer
1251Text-to-Account Mapping
1252Bank Pmt. Appl. Rule
1259Bank Data Conv. Bank
1260Bank Data Conv. Service Setup
1261Service Password
1265Data Exch. Field Mapping Buf.
1270OCR Service Setup
1271OCR Service Document Template
1275Doc. Exch. Service Setup
1280Bank Clearing Standard
1281Bank Data Conversion Pmt. Type
1284Outstanding Bank Transaction
1293Payment Application Proposal
1294Applied Payment Entry
1295Posted Payment Recon. Hdr
1296Posted Payment Recon. Line
1299Payment Matching Details
1300Mini Customer Template
1301Item Template
1302Dimensions Template
1303Mini Vendor Template
1304Sales Price and Line Disc Buff
1306User Preference
1307O365 Device Setup Instructions
1308O365 Getting Started Page Data
1309O365 Getting Started
1310Chart Definition
1311Last Used Chart
1312Trial Balance Setup
1313Activities Cue
1319Sales by Cust. Grp.Chart Setup
1400Service Connection
1430Role Center Notifications
1500Workflow Buffer
1502Workflow Step
1504Workflow Step Instance
1505Workflow – Table Relation
1506Workflow Table Relation Value
1507Workflow Step Buffer
1508Workflow Category
1509WF Event/Response Combination
1511Notification Entry
1512Notification Setup
1513Notification Schedule
1514Sent Notification Entry
1515Dynamic Request Page Entity
1516Dynamic Request Page Field
1518My Notifications
1520Workflow Event
1521Workflow Response
1522Workflow Event Queue
1523Workflow Step Argument
1524Workflow Rule
1525Workflow – Record Change
1526Workflow Record Change Archive
1530Workflow Step Instance Archive
1540Workflow User Group
1541Workflow User Group Member
1550Restricted Record
1600Office Add-in Context
1601Office Add-in Setup
1602Exchange Object
1606Office Invoice
1610Office Add-in
1612Office Admin. Credentials
1615Office Job Journal
1620Office Document Selection
1625Office Contact Associations
1638Invoiced Booking Item
1650Curr. Exch. Rate Update Setup
1660Payroll Setup
1661Import G/L Transaction
1662Payroll Import Buffer
1700Deferral Template
1701Deferral Header
1702Deferral Line
1703Deferral Post. Buffer
1704Posted Deferral Header
1705Posted Deferral Line
1800Data Migrator Registration
1801Data Migration Entity
1802Assisted Company Setup Status
1803Assisted Setup
1804Approval Workflow Wizard
1805Encrypted Key/Value
1806Data Migration Setup
1807Assisted Setup Log
1810Assisted Setup Icons
1900Cancelled Document
2000Time Series Buffer
2001Time Series Forecast
2002Azure Machine Learning Usage
2100Sales Document Icon
2103O365 Sales Document
2105O365 Payment History Buffer
2110O365 Sales Initial Setup
2118O365 Email Setup
5127Deferral Header Archive
5128Deferral Line Archive
5324Exchange Service Setup
5329CRM Redirect
5330CRM Connection Setup
5331CRM Integration Record
5332Coupling Record Buffer
5333Coupling Field Buffer
5334CRM Option Mapping
5335Integration Table Mapping
5336Integration Field Mapping
5337Temp Integration Field Mapping
5338Integration Synch. Job
5339Integration Synch. Job Errors
5340CRM Systemuser
5341CRM Account
5342CRM Contact
5343CRM Opportunity
5344CRM Post
5345CRM Transactioncurrency
5346CRM Pricelevel
5347CRM Productpricelevel
5348CRM Product
5349CRM Incident
5350CRM Incidentresolution
5351CRM Quote
5352CRM Quotedetail
5353CRM Salesorder
5354CRM Salesorderdetail
5355CRM Invoice
5356CRM Invoicedetail
5357CRM Contract
5359CRM Team
5360CRM Customeraddress
5361CRM Uom
5362CRM Uomschedule
5363CRM Organization
5364CRM Businessunit
5365CRM Discount
5366CRM Discounttype
5367CRM Account Statistics
5368CRM NAV Connection
5370CRM Synch. Job Status Cue
6300Azure AD App Setup
6301Power BI Report Configuration
6302Power BI Report Buffer
6303Azure AD Mgt. Setup
6700Exchange Sync
6701Exchange Contact
6702Booking Sync
6703Booking Service
6704Booking Mailbox
6705Booking Staff
6706Booking Service Mapping
6707Booking Item
6710Tenant Web Service OData
6711Tenant Web Service Columns
6712Tenant Web Service Filter
6721Booking Mgr. Setup
7500Item Attribute
7501Item Attribute Value
7502Item Attribute Translation
7503Item Attr. Value Translation
7504Item Attribute Value Selection
7505Item Attribute Value Mapping
7506Filter Item Attributes Buffer
8400Record Set Definition
8401Record Set Tree
8402Record Set Buffer
8628Config. Field Mapping
8630Config. Media Buffer
8631Config. Table Processing Rule
8632Config. Record For Processing
8650DataExch-RapidStart Buffer
9000User Group
9001User Group Member
9002User Group Access Control
9003User Group Permission Set
9005User Plan
9006Plan Permission Set
9007User Group Plan
9008User Login
9042Team Member Cue
9062User Security Status
9063Relationship Mgmt. Cue
9069O365 Sales Cue
9070Accounting Services Cue
9153My Account
9154My Job
9155My Time Sheets
9170Profile Resource Import/Export
9178Application Area Setup
9179Application Area Buffer
9190Terms And Conditions
9191Terms And Conditions State
9192Pending Company Rename
9400Media Repository
9500Email Item
9600XML Schema
9610XML Schema Element
9611XML Schema Restriction
9612Referenced XML Schema
9650Custom Report Layout
9651Report Layout Selection
9656Report Layout Update Log
9657Custom Report Selection
9701Cue Setup
9800Table Permission Buffer
9900Web Service Aggregate
130400CAL Test Suite
130401CAL Test Line
130402CAL Test Codeunit
130403CAL Test Enabled Codeunit
130404CAL Test Method
130405CAL Test Result
130406CAL Test Coverage Map
2000000114Document Service
2000000150NAV App Object Metadata
2000000151NAV App Tenant App
2000000152NAV App Data Archive
2000000153NAV App Installed App
2000000160NAV App
2000000161NAV App Dependencies
2000000162NAV App Capabilities
2000000163NAV App Object Prerequisites
2000000165Tenant Permission Set
2000000166Tenant Permission
2000000168Tenant Web Service
2000000169NAV App Tenant Add-In
2000000170Configuration Package File
2000000175Scheduled Task
2000000180Media Set
2000000183Tenant Media Set
2000000184Tenant Media
2000000185Tenant Media Thumbnails
2000000189Tenant License State
2000000190Entitlement Set
2000000194Webhook Notification
2000000195Membership Entitlement
2000000196Object Options
2000000197Token Cache
2000000198Page Documentation
2000000199Webhook Subscription


Removed from table list:

452Approval Setup
453Approval Code
464Approval Templates
465Additional Approvers
470Job Queue
824DO Payment Connection Details
825DO Payment Connection Setup
826DO Payment Setup
827DO Payment Credit Card
828DO Payment Credit Card Number
829DO Payment Trans. Log Entry
830DO Payment Card Type
5150Integration Page
2000000203Database Key Groups



How to: Get field values from dynamically loaded tables

If you need field values from different tables at runtime within the same function, then there is a nice solution:


// variablestableType, Option : CompInfo,PayTermresult : ARRAY [5] OF Variant OnRun()LoadDynamicallyFieldValues(tableType::CompInfo, result);MESSAGE(FORMAT(result[1])+', '+FORMAT(result[2])+', '+FORMAT(result[3]));LoadDynamicallyFieldValues(tableType::PayTerm, result);MESSAGE(FORMAT(result[1])+', '+FORMAT(result[2])+', '+FORMAT(result[3]));LoadDynamicallyFieldValues(tableType : 'CompInfo,PayTerm';VAR result : ARRAY [5] OF Variant)CASE tableType of tableType::CompInfo: begin  compInfo.GET;  recID := compInfo.RECORDID;  GetFieldValue(recID,'Name',result[1]);  GetFieldValue(recID,'Address',result[2]);  GetFieldValue(recID,'City',result[3]); END; tableType::PayTerm: BEGIN  payTerm.FINDFIRST;  recID := payTerm.RECORDID;  GetFieldValue(recID,'Code',result[2]);  GetFieldValue(recID,'Discount %',result[3]);  GetFieldValue(recID,'Description',result[1]); END;END;GetFieldValue(recID : RecordID;fieldName : Text;VAR fieldValue : Variant)recRef.GET(recID);field.SETRANGE(FieldName,fieldName);field.SETRANGE(TableNo,recID.TABLENO);IF field.FINDFIRST THEN BEGIN fieldRef := recRef.FIELD(field."No."); fieldValue := fieldRef.VALUE;END;



Get next object version number

Every developer has sometimes the issue: What is my next version number for the new object ? If there is a couple of developers working in the some database, you’ll need a kind of a version control.

I’ve developed a page, which calculates the next version number for a defined version prefix. The version sytax is: <PREFIX><Main No.>.<2-digit Sub No.>.

Let’s start with a list of pages with different version list, version ARCH1.00 to ARCH1.06. It does not matter, if the version list of an object contains more than one value.

After running the page set the version prefix, here ARCH, and push action “Get next version”. We get then the new version: ARCH1.07.

You can download the page here.


Export Nav Objects by Code

Exporting locked nav objects can be a problem when importung in target database. it’s not that easy to unlock them in the target database. So for that you can export nav objects by code and check Lock status before exporting.

Create a new report, add dataitem Object. set report to processingonly.

select dataitem Object, set property ReqFilterFields to Type,ID.
set request page: add field Path (Text).

add following code to trigger OnOpenPage:

Object.SETRANGE(Type,Object.Type::Table);Path := 'c:\temp';

add following code to report trigger OnPreReport():

finsql := 'C:\Program Files (x86)\Microsoft Dynamics NAV\100\RoleTailored Client\finsql.exe';IF NOT FILE.EXISTS(finsql) THEN  ERROR('finsql not found');

additional add that code to trigger Object – OnAfterGetRecord():

IF Object.Locked THEN BEGIN  Message(FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+' is locked.');  // alternatively unlock object, then try again.  // Object.Locked := FALSE;  // Object.MODIFY;end ELSE begin  arguments := 'command=exportobjects,file=%1,servername=%2,database=%3,filter="Type=%4;ID=%5",ntauthentication=1';  arguments := STRSUBSTNO(arguments,Path+'\'+FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+'.fob','localhost','Cronus',Object.Type,Object.ID);  Process.Start(finsql,arguments);  result := result + FORMAT(Object.Type)+'-'+FORMAT(Object.ID)+'\';END;

to trigger Object – OnPostDataItem():


Global Variables:

Process DotNet System.Diagnostics.Process.'System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'arguments Textfinsql TextPath Textresult Text


Import with Rapid Start Services

The Rapid Start Services are an often used tool in Dynamics Nav to export and import data. In that posting i show how the Import works using new feature Item Attributes.

In that sample i want to import a new Item Attribute Value and a new Item Attribute Value Mapping. Item Attribute Values are the concrete values, which an Item Attribute can have, e.g. Item Attribute “Color” can have values red, green, blue. These values can now be added as attributes to a concrete item using table item attribute value mapping.

We start with a new Config. Package. So goto /Departments/Administration/Application Setup/RapidStart Services/Configuration Packages or simply write “Configuration Packages” into the search box. There create a new Config. Package and set ITEMATT as code value and “Item Attributes” as package name. Now we add some lines to the Tables list: 7501 and 7505.

Now select Filters in subpage menu Table.
Add a filter for the first line (table 7501). Here we want to filter for Item Attribute with ID 1 (Color): FieldID = 1,Field Filter = 1.
For the 2. line (table 7505) set following filter:

Now export the filtered table data to excel using action “Export to Excel” from the main menu Actions. Open the created excel document. So we get the needed format to import some data. Now we change the data. Remove all data lines from table 1 in the excel document except line 1. Now change the values for ID and Value as follows:

In the 2. table change the value for “Item Attribute Value ID”:

Save the changed document to your documents folder, close Excel. Now we can import the new data. For that click on “Import from Excel”, No. of Package records changes to 1. If you click on the no., you can check import errors in the winow Config. Package records. The imported data is now temporarily saved. To save the data to the target tables, click on “Apply Package” action.

Now let’s check, if the data was imported.

Goto items list and click on action Attributes in menu Action. All right, the data is correct. You see in the second line Attribute Farbe (= Color) and the new value Brown, right the attribute value list with the new value.


How to work with big item descriptions

Sometimes it’s needed to save very long descriptions, but in Nav text fields can have only 250 characters. Additional you may want to search in these long text values. You can add a couple of these text fields to save long texts or save the text in text files and add them to the item. But what about searching? Not that easy.
An other option to save long texts is the usage of blob fields. For that option i developed a solution.

First add a new field “Description 3” to table Item, type BLOB, subtype Memo.
Then edit page Item Card, add a global variable Desc3Txt of type text with no length. Add the variable as new field to the item card, Editable=False, MultiLine=Yes.
Add following code to trigger OnAfterGetRecord in item card page:

// InStr | InStreamCALCFIELDS("Description 3");IF "Description 3".HASVALUE THEN BEGIN  "Description 3".CREATEINSTREAM(InStr);  InStr.READ(Desc3Txt);END;

Add to trigger Desc3Txt – OnAssistEdit()

// OutStr | OutStream // EditCtrl | DotNet | Archer.TextEdit.'Archer.TextEdit, Version=, Culture=neutral, PublicKeyToken=1465b259ee2284cb' CLEAR(EditCtrl);EditCtrl := EditCtrl.TextEdit;EditCtrl.Load(Desc3Txt);EditCtrl.ShowDialog;Desc3Txt := EditCtrl.Save;EditCtrl.Close;CLEAR(EditCtrl);"Description 3".CREATEOUTSTREAM(OutStr);OutStr.WRITE(Desc3Txt);MODIFY;CurrPage.UPDATE;

Page item card with new multiline text field “Description 3” and Assist Button.

Clicking on Assist Button starts the TextEdit Control and loads the current text. After changing the text and closing the text control, you are asked, if you want to change the text.

Now we need the opportunity to search within the new text/blob field. For that we need a new page Item Search. That new field cannot be searched using the standard search function.

Create a new page with objectid 50000, name Item Search. Add a group under the contentarea, add a global text variable SearchString, add a new field under the group with SearchString as SourceExpr. Add another group, add a new line with type part. Later we set the value for property PagePartID.

To show the search result we need another page: type listpart, objectid 50001, name Item Search Result. As source of the new page we need a new table: objectid 50000, name Item Search Result.

The new page 50001:

Properties: Editable=False, SourceTable=50001, SourceTableTemporary=Yes.

Now add global function SetData(SearchFilter : Text) to the new page. Add following code to the new function:

// local variables// Item, Record, Item // ItemSearchResultLine, Record, Item Search Result // InStr, InStream // Desc3Txt, Text // LineNo, Integer DELETEALL;LineNo := 10;Item.FINDSET;REPEAT  Item.CALCFIELDS("Description 3");  IF Item."Description 3".HASVALUE THEN BEGINItem."Description 3".CREATEINSTREAM(InStr);InStr.READTEXT(Desc3Txt);IF STRPOS(LOWERCASE(Desc3Txt),LOWERCASE(SearchFilter)) > 0 THEN BEGIN  "Line No." := LineNo;  "Item No." := Item."No.";  Description := Item.Description;  "Description 2" := Item."Description 2";  "Description 3" := COPYSTR(Desc3Txt,1,250); // first 250 chars  INSERT(FALSE);  LineNo += 10;END;  END;UNTIL Item.NEXT = 0;CurrPage.UPDATE(FALSE);

Now you can set the property PagePartID in the part line in page 50000 to 50001.

For calling the search function we need a Search button in page 50000.
Add following code to trigger Search – OnAction()

The new Item Search Page with a search result.

You can download the TextEdit Control here.

You could simplify the solution by:
* Search page: Use only page 50001, add a second group at the top with field SearchString. So page 50000 is not needed.
* Page Item Card: remove the textedit control and the according code, set field Desc3Txt to editable, add the code to fill the blob field “description 3” using outstream to trigger Desc3Txt-OnValidate.



Error when opening Whse. Shipment page

Got following error after creating a Whse. shipment from a sales order in Nav 2017 when opening the Whse. Shipment from page “Whse. Shipment Lines”:

The record that you tried to open is not available. The page will close or show the next record.

The first strange thing is, the message is an info box, not an error message. After analysing the data in the sales order and tables Whse. Shipment Header and Whse. Shipment Line no error found. So what’s up? Also debugging did not really help, the debugger did not jump to the line, where the info box is created.

In the end i found out, that the reason for the strange behaviour was following: I created a new location for some tests, but forgot to add my user to the whse. employees, but that was not shown. The causal reason in the code: When opening page 7335 Whse. Shipment from page “Whse. Shipment Lines” following code is called in OnOpenPage trigger:

ErrorIfUserIsNotWhseEmployee() // from table 7320 Whse. Shipment HeaderIF USERID = '' THEN BEGIN  WhseEmployee.SETRANGE("User ID",USERID);  IF WhseEmployee.ISEMPTY THEN   ERROR(Text002,USERID);END;

Nice code, but what happens, if WhseEmployee.ISEMPTY is not empty? No error message is shown, but the info message from above with senseless text, which has nothing to do with the original error. Here it’s not checked, if there is a whse. employee created for the current user and the given location of the sales order. Thx microsoft.

What’s needed to correct the behaviour? Change above code to:

IF USERID = '' THEN BEGIN  WhseEmployee.SETRANGE("User ID",USERID);  WhseEmployee.SETRANGE("Location Code","Location Code");  IF WhseEmployee.ISEMPTY THENERROR(Text002,USERID);  END;

You should also change Text002 to “You must first set up user %1 as a warehouse employee for location %2.”.