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:

 

// variables
tableType, Option : CompInfo,PayTerm
result : 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;

cheers

 

Advertisements

How to: Get record count for all tables

In newer Nav versions the tables overview in development environment/database information has gone. So how to get that list back ?

Let’s got to SSMS (Sql Server Management Studio).
There select the database, for which you want that information.
Right click on database -> Reports -> Standard Reports -> Disk usage by Top Tables

enter image description here

There we are ! 😉

 

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 code
using 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:

autom1    autom2

cheers

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 | InStream
CALCFIELDS("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=1.0.0.0, 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;

itse-1

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

itse-2

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.

itse-3

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.

itse-4

The new page 50001:

itse-5

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 BEGIN
    Item."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()
CurrPage.ItemSearchResultLines.PAGE.SetData(SearchString);

itse-6

The new Item Search Page with a search result.

You can download the TextEdit Control here.

Followup:
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.

cheers

 

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 https://www.doogal.co.uk/PostcodeDownloads.php
// 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.

cheers

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.exe
Framework Version: v4.0.30319
Description: 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=4.0.0.0, 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=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]](Microsoft.Dynamics.Nav.Client.CallServerMethod`1)
 at Microsoft.Dynamics.Nav.Client.ServerInvocationHandler.CallServer[[System.__Canon, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]](Microsoft.Dynamics.Nav.Client.CallServerMethod`1)
 at Microsoft.Dynamics.Nav.Client.ServiceConnection.CallServer[[System.__Canon, mscorlib, Version=4.0.0.0, 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[])

additional:

Name of faulty app: Microsoft.Dynamics.Nav.Client.exe, Version: 7.0.46056.0, time code: 0x57475c32
Name of faulty module: KERNELBASE.dll, Version: 10.0.10586.306, time code: 0x571afb9a
error code: 0xe0434352
error offset: 0x000bdae8
ID of faulty process: 0x292c
Start 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, …).

cheers

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 : Integer
StartPos := 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 : Integer
WHILE i  0 THEN BEGIN
    SubString := COPYSTR(String,1,EndPos-1);
    String := DELSTR(String,1,EndPos);
  END ELSE
    SubString := String;
END;
EXIT(SubString);

To test that new function we use that:

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

cheers