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;



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


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:



Check windows client’s execution mode at runtime

If you want to check if the windows client is run in config mode at runtime, then there is an easy method to do that:

// variablesEnv : DotNet : System.Environment.'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'cmdArgs : DotNet : System.Array.'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'// codecmdArgs := Env.GetCommandLineArgs;IF LOWERCASE(FORMAT(cmdArgs.GetValue(1))) = '-configure' THEN  // do something

Method GetCommandLineArgs gives back a string array. In the sample it’s expected, that the -configure parameter is the first parameter. To check all parameters you can do with that code:

FOR i := 0 TO cmdArgs.Length - 1 DO  IF LOWERCASE(FORMAT(cmdArgs.GetValue(i))) = '-configure' THENMESSAGE('Config mode');

There is an alternative method, also working, but much longer and with low performance. 😉

// variablescmdLine : Text; // codecmdLine := LOWERCASE(GetCmdLine);IF (STRPOS(cmdLine, '-configure') <> 0) OR (STRPOS(cmdLine, '/configure') <> 0) THEN  Message('Config mode');PROCEDURE GetCmdLine : Text;// variablesFileMgt : Codeunit 419CurrProc : DotNet "'System, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Diagnostics.Process"MgmtObjSearcher : DotNet "'System.Management, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.System.Management.ManagementObjectSearcher"MgmtObjColl : DotNet "'System.Management, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.System.Management.ManagementObjectCollection"MgmtObj : DotNet "'System.Management, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'.System.Management.ManagementObject"Enum : DotNet "'mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.IDictionaryEnumerator"cmdLine : Text;// codeCurrProc := CurrProc.GetCurrentProcess;MgmtObjSearcher := MgmtObjSearcher.ManagementObjectSearcher(  STRSUBSTNO('SELECT CommandLine FROM Win32_Process WHERE ProcessId = %1', CurrProc.Id));MgmtObjColl := MgmtObjSearcher.Get;Enum := MgmtObjColl.GetEnumerator;WHILE Enum.MoveNext DO BEGIN  MgmtObj := Enum.Current;  cmdLine += STRSUBSTNO(' %1', MgmtObj.Item('CommandLine'));END;EXIT(cmdLine);


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.



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.


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.


How to display sum value in factbox on base of multiple line selection

Typical issue is to show a calculated value in a factbox on base of the currently selected line in a list page or the lines in a subpage. Is it possible to get a calculated value on base of more than one selected line? Let’s check out.

Let’s say we want to display the sum of field “Credit Limit” in the customer list when selecting multiple lines. So first we edit page 9082 “Customer Statistics FactBox”. There we add a global variable SumCredLimit (decimal) to the page, then we add a new line in page with sourceexpr = SumCredLimit.

Now we add a new global function SetSumCredLimit.

Save and close the page. Now we edit page 21 customer list. There we rename page part “Customer Statistics FactBox” to CustStatFbx. That’s needed, so that we can reach the custom functions in page “Customer Statistics FactBox” from page “customer list”.

After that edit the page code (F9). Goto trigger OnAfterGetCurrRecord, there add local variable Cust (record|Customer). Add following code to trigger:

Save and close the page. Now run the Nav windows client and goto customer list.
The result:

You see, the new value in the factbox shows the sum of the Credit Limit values of the 3 selected lines.


Simple Read/Write Excel Data

In a Nav forum there was a question, how to read data from an excel document, change the data in Nav, write data back to the excel document without changing the existing cell formatting. With table ExcelBuffer and the helper assemblies delivered with Nav this cannot be done, because the Write functions e.g. SetCellValueText work with decorators.

So i developed assembly XlsLib. With that assembly read and write actions from an excel document are done very easy. The provided methods are very simple. You can read the cell values, the basic number formats and save it to table Excel Buffer. When writing only the value as is is written back to the cell. No further formatting is read or written.That can also be used, when you work with templates.

Main functions:

XlsWorksheet.GetValue(rowId, colId, value, type):
– Get a value from cell rowId/colId,
– given value is of type text,
– type is of datatype integer, can be 0-3 according option field NumberFormat in table ExcelBuffer, Type=4 means Unknown

XlsWorksheet.SetValue(rowId, colId, value):
– Sets a value in cell rowId/colId. value can be of any type.

Here is a sample code:

TestXlsLib()// variables//  XlsWorkbook DotNet XlsLib.Workbook.'XlsLib, Version=, Culture=neutral, PublicKeyToken=38613a2311532c9a'// XlsWorksheet DotNet XlsLib.Worksheet.'XlsLib, Version=, Culture=neutral, PublicKeyToken=38613a2311532c9a'// value Text// type Integer// open excel workbookXlsWorkbook := XlsWorkbook.Workbook('c:\temp\test.xlsx');// select sheet 'Sheet2'XlsWorksheet := XlsWorksheet.Worksheet(XlsWorkbook,'Sheet2');// read cell values from row=1, column=1-3XlsWorksheet.GetValue(1, 1, value, type); // e.g. 1234XlsWorksheet.GetValue(1, 2, value, type); // e.g. 10.10.1990XlsWorksheet.GetValue(1, 3, value, type); // e.g. 99.12// Write some valuesXlsWorksheet.SetValue(1, 1, 5432);  // an integer value againXlsWorksheet.SetValue(1, 2, 010175D); // again a date valueXlsWorksheet.SetValue(1, 3, 34.45); // a decimal value// read values again to check changesXlsWorksheet.GetValue(1, 1, value, type);XlsWorksheet.GetValue(1, 2, value, type);XlsWorksheet.GetValue(1, 3, value, type);// Save: Save to loaded file filename//XlsWorkbook.Save();// SaveAs: create a copy, save result to new fileXlsWorkbook.SaveAs('c:\temp\test2.xlsx');// Close Excel App !!XlsWorkbook.Close();// if an error occurs ...IF XlsWorkbook.Error <> '' THEN  MESSAGE(XlsWorkbook.Error);// release memoryclear(XlsWorkbook);

You can download XlsLib here.

Hint: When downloading that file, it can be, that you have troubles using it because of security issues, e.g. dll is not visible in list of assemblies, you get an error message like “Cannot open xlslib, because one or more dependencies are not met”, methods are not visible or something else. then start windows explorer, goto folder were you copied the lib files, view file properties. if you see a notice at the bottom of the file properties window like “file is blocked because downloaded from internet, grant access?”, then click on “grant access”.