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;



How to: Create an automation for usage in Navision

The development of a COM component with .Net for usage in Navision needs a special approach. Start with a new C# project, project type Class Library. Set the project to “Make assembly com-visible”. The simple automation provides a string property and a method to show a .net message box.

// a sample codeusing System;using System.Runtime.InteropServices;using System.Windows.Forms;namespace SampleAutom{ // the interface, so that the methods and properties are shown in Nav // needed attributes InterfaceType and Guid [InterfaceType(ComInterfaceType.InterfaceIsDual)] // create a new Guid [Guid("9304DD04-5EF0-498E-893E-CB644CD34656")]  interface IMyInterface {  // set a DispId for each method/property  [DispId(1)]  int MsgBox(string message);  [DispId(2)]  string Title { get; set; } } // class attributes [ClassInterface(ClassInterfaceType.AutoDual)] [Guid("D9E556F3-4D85-45C9-965A-DB3D918528CD")] // implement the interface public class TestCom : IMyInterface {  string _title = "Dynamics Nav";  public TestCom()  { }  // with property Title you can set/read the title of the msgbox  public string Title  {get{ return _title; }  set{ _title = value; }  }// method msgbox returns an integer value according the clicked button  // Yes = 6, No = 7, Cancel = 2  public int MsgBox(string message)  {var result = MessageBox.Show(message, Title,MessageBoxButtons.YesNoCancel, MessageBoxIcon.Information);return (int)result;  } }}

To install the new automation run visual studio in admin mode. after restart and loading the project build/compile the project.

Now start Navision and create a new codeunit. In the OnRun trigger add following code:

// TestCom, Automation, 'SampleAutom'.TestCom // RetVal, Integer CREATE(TestCom);TestCom.Title := '.Net Msgbox run in Navision';RetVal := TestCom.MsgBox('Your message text ...');MESSAGE(FORMAT(RetVal));CLEAR(TestCom);

Now run the codeunit. It results in:



Mass data import

There was an issue with importing all UK post codes from a csv file, about 3m post codes. Importing using rapidstart services (excel import) can cause buffer overflow messages. excel itself has row/size limitations. Increasing MaxNoOfXMLRecordsToSend in config file ClientUserSettings.config from default value 5000 to e.g. 20000 is no problem and can help. Also changing MaxUploadSize in server config file CustomSettings.config is an option (also available via nav service admin console). Better choice for mass data import are dataports (older nav versions) and xmlports.

Another option is to develope a report, which imports the file contents and loops through the lines. quite simple, no memory issues.

create a new report, add following code to trigger OnPreReport():

OnPreReport()// variables PostCode, Record, Post Code file, File fileName, Text, 250 line, Text, 1024 dlg, Dialog idx, Integer txtValue, Text, 100// code PostCode.DELETEALL(FALSE); COMMIT; dlg.OPEN('#1###### #2######'); // show progress dialog idx := 1;// downloaded post code file from as test file, size: 500k, 2.1m lines, some of them contain obsolete post codes fileName := 'c:\temp\England postcodes.csv'; file.WRITEMODE := FALSE; file.TEXTMODE := TRUE; file.OPEN(fileName); file.READ(line); // skip header line WHILE file.READ(line) > 0 DO BEGIN   // skip obsolete post codes: 2. value = No   IF SELECTSTR(2,line) = 'Yes' THEN BEGIN  PostCode.Code := SELECTSTR(1,line); PostCode.VALIDATE(City,GetValue(SELECTSTR(15,line))); PostCode.County := GetValue(SELECTSTR(8,line)); PostCode."Country/Region Code" := 'GB'; PostCode.INSERT; dlg.UPDATE(1,idx); dlg.UPDATE(2,PostCode.Code); idx += 1;   END; END; file.CLOSE; dlg.CLOSE; MESSAGE(FORMAT(idx) + ' post codes imported.');LOCAL GetValue(txtValue : Text[100]) : Text txtValue := DELCHR(txtValue,'<','"'); // remove leading "   txtValue := DELCHR(txtValue,'>','"'); // remove trailing " IF STRLEN(txtValue) > 30 THEN // fields City,County are Text[30]   txtValue := COPYSTR(txtValue,1,30); // cut text, leading 30 chars EXIT(txtValue);

report runs with 1.5m valid lines/records about 5 min.


Error Microsoft.Dynamics.Nav Types.Exceptions NavNCLFieldNotFoundException occured

Quite strange error occured during an upgrade process from Nav 2009 to Nav 2013 after upgrade step 1, when starting Nav 2013 RTC. The RTC simply crashed. The event log entries are the following:

Application: Microsoft.Dynamics.Nav.Client.exeFramework Version: v4.0.30319Description: The process was terminated due to an unhandled exception.Exception Info: Microsoft.Dynamics.Nav.Types.Exceptions.NavNCLFieldNotFoundException
Exception Info: Microsoft.Dynamics.Nav.Types.Exceptions.NavNCLFieldNotFoundException at Microsoft.Dynamics.Nav.Client.ConnectionStateManager.CallServer[[System.__Canon, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]](Microsoft.Dynamics.Nav.Client.CallServerMethod`1, Microsoft.Dynamics.Nav.Client.ServerCallOptions) at Microsoft.Dynamics.Nav.Client.ConnectionStateManager.CallServer[[System.__Canon, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]](Microsoft.Dynamics.Nav.Client.CallServerMethod`1) at Microsoft.Dynamics.Nav.Client.ServerInvocationHandler.CallServer[[System.__Canon, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]](Microsoft.Dynamics.Nav.Client.CallServerMethod`1) at Microsoft.Dynamics.Nav.Client.ServiceConnection.CallServer[[System.__Canon, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]](Microsoft.Dynamics.Nav.Client.CallServerMethod`1) at Microsoft.Dynamics.Nav.Client.ServiceConnection.OpenCompany(System.String) at Microsoft.Dynamics.Nav.Client.Builder.CompanyInitializer.OpenCompany(System.String) at Microsoft.Dynamics.Nav.Client.FormBuilder.BuilderSession.OpenCompany(System.String) at Microsoft.Dynamics.Nav.Client.FormBuilder.BuilderSessionInitializer.InitializeCore() at Microsoft.Dynamics.Nav.Client.FormBuilder.BuilderSessionInitializer.Initialize() at Microsoft.Dynamics.Framework.UI.Builder.Initialize() at Microsoft.Dynamics.Framework.UI.UISession.Initialize() at Microsoft.Dynamics.Nav.Client.WinClient.NavWinFormsClientSession.InitializeUISession() at Microsoft.Dynamics.Framework.UI.Windows.ClientSessionBase.SetupSessions() at Microsoft.Dynamics.Framework.UI.Windows.ClientSessionBase.InitCore() at Microsoft.Dynamics.Nav.Client.WinClient.NavWinFormsClientSession.InitCore() at Microsoft.Dynamics.Framework.UI.ClientSessionCore.Init() at Microsoft.Dynamics.Framework.UI.Windows.ClientSessionBase.Run() at Microsoft.Dynamics.Nav.Client.WinClient.NavClientErrorHandler.ExecuteAndCatchExceptions(System.Func`1) at Microsoft.Dynamics.Nav.Client.WinClient.ExceptionHandler.DoExecute(System.Func`1) at Microsoft.Dynamics.Nav.Client.WinClient.StartWinFormsClient.RunCore() at Microsoft.Dynamics.Nav.Client.WinClient.StartWinFormsClient.Run(Boolean) at Microsoft.Dynamics.Nav.Client.WinClient.StartWinFormsClient.RunAndDispose() at Microsoft.Dynamics.Nav.Client.Program.ExecuteNormally(System.Func`1) at Microsoft.Dynamics.Nav.Client.Program.Main(System.String[])


Name of faulty app: Microsoft.Dynamics.Nav.Client.exe, Version: 7.0.46056.0, time code: 0x57475c32Name of faulty module: KERNELBASE.dll, Version: 10.0.10586.306, time code: 0x571afb9aerror code: 0xe0434352error offset: 0x000bdae8ID of faulty process: 0x292cStart time of faulty app: 0x01d2334b59f3c13d

The error means: There is a default field or function missing in a master data table needed on load process of RTC.

After debugging and comparing with cronus database, it turned out, the reason was a missing default field in master data table “marketing setup”. after adding that field and other missing default fields and functions in master data setup tables  the RTC started correctly.

The main reason was, that the developer did a very sloppy object upgrade, made not the needed comparision step with cronus database to check which fields are missing, also skipped other needed steps.

So if you get that error, either restart object upgrade or check at least all master data tables (setup tables, config tables, document tables, customer, vendor, item, …).


ReplaceString and a new SelectString

With function ConvertStr you can replace substrings in strings. The original substring and the replacement have to have the same length. So what to do, if the length is different? Here is a solution.

ReplaceString(String : Text[250];OrigSubStr : Text[100];ReplSubStr : Text[100]) : Text[250]// StartPos : IntegerStartPos := STRPOS(String,OrigSubStr);WHILE StartPos > 0 DO BEGIN  String := DELSTR(String,StartPos) + ReplSubStr + COPYSTR(String,StartPos + STRLEN(OrigSubStr));  StartPos := STRPOS(String,OrigSubStr);END;EXIT(String);

SelectStr is a nice function to get text values out of a text line, simply giving the number/position of the value within the line, e.g. from ‘this,was,a,cool,thing’. here ‘was’ has position 2, so selectstr(2,line) would give us value ‘was’. SelectStr expects a comma as delimiter. Not so good, if you also have comma letters in the values (e.g. decimal values in europe). Would be fine to have a kind of SelectString function, where you can define the delimiter. hm … let’s try something.

SelectString(Number : Integer;String : Text[250];Delimiter : Char) : Text[100]// i : Integer// EndPos : Integer// SubString : Text[100]i := 1; // i : IntegerWHILE i  0 THEN BEGINSubString := COPYSTR(String,1,EndPos-1);String := DELSTR(String,1,EndPos);  END ELSESubString := String;END;EXIT(SubString);

To test that new function we use that:

// line : Text[250]// i : Integerline := 'ab;cd;ef;cd';for i := 1 to 4 do  MESSAGE(SelectString(i,line,';'));



Convert Tab delimited strings

I had an issue to process a csv file with TAB delimited text lines. The existing code expected text lines with ; as delimiter.

Character TAB has ASCII Code 9.

So i wrote following code:

ConvertTabString(line : Text[250]) : Text[250]ch := 9; // of type Charline := CONVERTSTR(line,FORMAT(ch),';');exit(line);

test code:

// set a line value or load the line from a text file using file.openline := 'ab cd  ef  cd'; line := ConvertTabString(line);MESSAGE(line);result: 'ab;cd;ef;cd'

an interesting thing is, that this code does not work for older nav 2009 builds. maybe a problem with the encoding. for that case i developed a second version.

// text file test.txt contains 1 text line 'ab cd  ef  cd'. with TAB as delimiter.// file : FILE// instr : InStreamfile.OPEN('c:\temp\test.txt');file.CREATEINSTREAM(instr);instr.readtext(line,maxstrlen(line));line := CONVERTSTR(line,FORMAT(ch),';');message(line);file.close;

This can also be used to export the converted lines to a new text file, which can then be imported via dataport/xmlport.


Textual Data Export by Configuration

An often by customers wanted feature is to get a data export to a text/csv file, which can then be edited in excel or text editor.

Yes, you can use Rapidstart Services. But with that feature you can only export data in excel format, not in text format. That’s ok for editing in Excel and reimport the changed data back to NAV. For data exchange scenarios you always need text format. An other point is, that it’s quite often said, Rapidstart Services are not that easy to use for end users and has a couple of bugs. I also read quite often that this feature is not recommended at all. So what else can a customer do? Contact the NAV partner, who then shall develope a new xmlport, report or codeunit to do that job. That’s the usual way.

The german localised version of Dynamics Nav contains a really nice feature, simply called “Data Exports”. You can find it in menu /Departments/Administration/Application Setup/Financial Management/General/Data Exports. This feature is mainly used to export business data for auditing purposes according the GDPdU (Process for data access and testability of digital documents).

With the feature “Data Exports” there is an additional way for these kind of issues, it is possible to export data to csv files without developing! Great thing. So let’s have a look, how we can use that.

First we need at least one definition group. Then we define the according record definition (Button “Record Definitions”).

Here we need values for Code, Description and Export Path. In that case i want to export Sales Orders. “DTD File Name” is a mandatory field. I checked the code. The file is not processed, using different kinds of dtd files does not change anything in the resulting export files. The name of the dtd file is simply written to the file index.xml, which contains the datastructure. To get the thing run we create an empty text file, call it empty.dtd and add only one line of text:


Save the text file and click on Button Import in menu tab “DTD File”, select file empty.dtd and click ok. After that the file is imported and the file’s name is set to column “DTD File Name”.

Now let’s define the table and the fields. For that select the record definition and click on “Record Source”.

In the header area we set table no. to 36 (Sales Header) and the Key No. to 1 (Primary Key). Optional you can set the period field no., here to 19 (Order Date). In Column Table Filter you can set filters, if you do not want to export all records. Here i set the field to “Sales Header: Document Type=Order”, because i only want to export Sales Orders.

In the Fields Area we add the export fields by clicking on the Add Button. The key fields should be in first place. With MoveUp and MoveDown you can change the position of the fields. That’s all.

You can, if you wish, add more tables in the header area, you can set relationships, e.g. between Sales Header and Sales Line. But for now let’s run the thing. First we click Validate and get “The data export record source validated correctly.”, means all is ok.

So, after closing the page “Data Export Records Source” we are back in page “Data Export Records Definitions”. Here we click on Button Export in menu tab “Process”. Report 11015 “Export Business Data” is started:

Here set start and end date. These fields are mandatory and are applied to the period field of the header line, in that case to field “Order Date”. After execution we get some files in the export path, subfolder SALESORDER.

File empty.dtd is simply copied, file index.xml contains the data structure:

and file SalesHeader.txt contains the exported data (csv format):

There we are!
We got a csv export file without any development, only by configuration!

This can, as told before, extended by adding more lines in the header area of page “record source”:

Here you can see 2 header lines “Sales Header” and “Sales Line”, which is indented and has a defined relationship shown in page relationship in the bottom right corner of the above screenshot. In that case you get 2 export files, one per table.

Important: If you are interested in that feature, you could download the german localised version. But … for that you need the according license!
Exporting the nav objects as text export and renumbering them to the 50000s object range is possible, but illegal! So if you like that kind of feature, try to redevelope that functionality. Do not copy these objects to your database, do not reuse the code. If you want to do that, please contact your Nav partner or Microsoft for license clarifcation. You could suggest to add that functionality to the W1 version.

For more details about that feature follow this.


Dynamics Nav – The big picture

Kamil Sacek developed 2008 a nice tool to view and analyse all Nav table relations. It’s a kind of a Where-used-tool, where all the relations are shown. It worked for Nav 5.0. You can download it from here.

I upgraded it to Nav 2009 and Nav 2015, reduced complexity, optimized the layout, added and reordered some fields in the table relations page/form and added needed buttons.

Installation and Usage:

  • Precondition:
    Export all tables from the database as text, save the export e.g. as tabs-60.txt. For that a developer license is needed.
  • How to use:
    1. Import the objects into your DB
    2. Run form/page 66001 Table Relations
    3. Click on button “Update table relations”
    4. Change parameters, if needed. “Fill Table” is preset (relation data is saved in table 66000).
    5. Click the “Process” button
    6. Close the Process form/page.
    7. Now you can browse the relations there. You will see all referred and all referring fields for the selected field.
  • When updating table relations you can optional export the table relation data into a text file for further analyzing or creating a diagram.

Nav 2015 Version:

The “Update table relations” page…

Nav 2009 Version:

The “Update table relations” form …

BPN2.0 for Nav 2015
BPN2.0 for Nav 2009




DECSTR – Decrease integer value in string

INCSTR is a C/AL command, which is often used to increase document numbers, which usually start with some letters followed by a number, e.g. AB-00010.

With INCSTR(‘AB-00010’)  you get then ‘AB-00011’.

In a Nav forum a member asked, if there is a simple way for decreasing such a document no. don’t know, why he needs that, but it’s a nice idea.

So, after a little research and finding some typical looooong c/al code solutions, i developed my own small, very cool solution using .net class Regex:

// loc. variables
//DocNo : Code 20

DocNo := DecStr(‘AB-00010’);

LOCAL DecStr(DocNo : Code[20]) : Code[20]
// loc. variables
//Prefix: Text
//NoString: Text
//Number: Integer
//Regex: DotNet System.Text.RegularExpressions.Regex.’System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089′
//DotNetInt: DotNet System.Int32.’mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089′
//PadLength: Integer

Prefix := Regex.Replace(DocNo,’\d+’,”);
NoString := Regex.Match(DocNo,’\d+’).Value;
Number := DotNetInt.Parse(NoString);
IF Number > 0 THEN BEGIN
Number := Number – 1;
PadLength := STRLEN(DocNo) – STRLEN(Prefix) – STRLEN(FORMAT(Number));
EXIT(Prefix + PADSTR(”,PadLength,’0′) + FORMAT(Number));

ERROR(‘Resulting number would be negative.’);

Results in: AB-00009

the resulting number has the same length.

Additional the links i found:


Cannot create an instance of an Automation Server with CLSID = {GUID}

If you get that kind of error, in most cases that means the automation, which should be registered with that given GUID in your system is missing or misconfigured. In both cases the system does not know the common name of the system. That’s why you get that cryptic message.


Search the web (e.g., to get more information about the automation. The given GUID is unique worldwide! So you’ll get the name for the automation quite easy. The variable name, where the automation is used, is also a hint for the name, also comments in the code.
Then search for the file on the server using windows explorer, where the nav service is installed and where you want to run the nav object (report, table, codeunit, …), if that automation file (dll) is physically installed.

  • If you do not find it, then it’s missing. download it from the vendor or install the missing software, which includes that automation.
  • If you find the file, then it was not registered correctly. In that case you need to run the registry tool regsrv32. First uninstall, then re-install the automation:
    Regsvr32 [/u] [/n] [/i[:cmdline]] <full path to dllname>
    for more details and troubleshooting follow this.

Typical GUIDs according that issue:

  • 000208D5-0000-0000-C000-000000000046: Microsoft.Office.Interop.Excel
  • 248DD896-BB45-11CF-9ABC-0080C7E7B78D: Winsock Control 6.0
  • DB07BCE5-B131-11D3-9219-00002430F8E2: CFront Plus
  • F5078F18-C551-11D3-89B9-0000F81FE221: MS XML 4.0 (or 6.0)

If you can’t find the GUID on the web, then it is probably a custom automation developed by yourself or a vendor years ago.