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

 

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=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a'
// XlsWorksheet DotNet XlsLib.Worksheet.'XlsLib, Version=1.0.0.0, Culture=neutral, PublicKeyToken=38613a2311532c9a'
// value Text
// type Integer

// open excel workbook
XlsWorkbook := XlsWorkbook.Workbook('c:\temp\test.xlsx');
// select sheet 'Sheet2'
XlsWorksheet := XlsWorksheet.Worksheet(XlsWorkbook,'Sheet2');

// read cell values from row=1, column=1-3
XlsWorksheet.GetValue(1, 1, value, type); // e.g. 1234
XlsWorksheet.GetValue(1, 2, value, type); // e.g. 10.10.1990
XlsWorksheet.GetValue(1, 3, value, type); // e.g. 99.12

// Write some values
XlsWorksheet.SetValue(1, 1, 5432);  // an integer value again
XlsWorksheet.SetValue(1, 2, 010175D); // again a date value
XlsWorksheet.SetValue(1, 3, 34.45); // a decimal value

// read values again to check changes
XlsWorksheet.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 file
XlsWorkbook.SaveAs('c:\temp\test2.xlsx');
// Close Excel App !!
XlsWorkbook.Close();

// if an error occurs ...
IF XlsWorkbook.Error <> '' THEN
  MESSAGE(XlsWorkbook.Error);

// release memory
clear(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”.

cheers

Dotnet Events are not shown

There are 2 scenarios, where embedded events of .net (dotnet) assemblies can/should be displayed in the c/al code … or maybe not.

Scenario 1:
You use a control-addin like the PingPong Control-Addin (Microsoft.Dynamics.Nav.Client.PingPong). For usage it’s needed that a control-addin is listed in the Control-Addin Page. If so, you can select it from the list, wenn you set a value in property ControlAddin of Page’s field. If the control-addin has embedded events, they should be displayed immediately at the end of the fields triggers in the c/al code.

DisplayEvents3

If not, then

  • the assembly has no events
  • the eventhandling in the assembly was developed not correctly
  • the from the Control-Addin referenced assembly Microsoft.Dynamics.Framework.UI.Extensibility has the wrong version! This assembly is referenced by every Control-Addin. Microsoft ships with each version (build, CU) mostly (always?) new build versions of all the Nav assemblies.

 

Scenario 2:

You declare a global variable of type dotnet and use a .net assembly, which has embedded events. These events are only shown, if you set the variable’s property “WithEvents” to Yes.

DisplayEvents2
If the property is set to No (default), the events are not shown in the c/al code.
if you have set it to true, after that to false, the events stay … for the moment. Compile, re-edit the object and the events in the c/al code are gone.

DisplayEvents1

P.S.: The rumour that the WithEvents property is obsolete, is wrong! It is used in global variables of type dotnet  and is mandatory for the usage of embedded windows events. It was also used in old COM/OCX Components. That one is obsolete in newer Nav versions.

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.

aut-serv-err

Solution:

Search the web (e.g. google.com, microsoft.com) 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.

cheers

Build or buy solutions

i read an quite interesting, but obviously hidden commercial, posting about build or buy a solution for EDI. it’s right, that this is often not an easy decision, if you need a new module.

one point is missing in that posting: if you buy a so called ready-to-use solution, in most cases, it is not ready-to-use. you should reckon at least 30% of the budget for customization. The custom solution can be the cheaper solution, if there is no buy-solution on the market or the available solutions are only usable with a huge amount of customizations. For managing a custom project you could read this blog posting.

Active Directory queries from C/AL

I wanted to get a list of all NAV users with displayname and roles per user in a report. The data base is table “Windows Access Control”. One field should show the displayname of each user. The displayname can be read from hidden table “Windows Object”, field Name. To embed that value in the report i tried to import the field with different methods: by code and by a second, linked dataitem in the report. The result was the same in both cases: the read call (internally an AD query) of the displayname (field Name) is really very, very slow! Although AD queries are quite slow in general (the technique behind is slow), this simple report lasted over 20 minutes and longer. That is not slow, that is really a hoax. Maybe that’s a bug in Nav 2009, maybe the implementation is not very good. So i searched for an alternative solution.

In the end i developed a little automation dll to get the displayname of each user with a given domain account (field “Login ID”). With that automation the report rendering speed was quite acceptable.

This automation, i called it ActiveDirectoryLib, delivers 2 methods:
* GetDisplayName(string userName) : string
* GetUserName(string loginSID) : string

// the displayed report fields
Login SID | Username | Role ID | Role Name

// global variables
Username | Text | 100
adLib | Automation | 'ActiveDirectoryLib'.AccountInfo

// the code
Windows Access Control - OnPreDataItem()
CREATE(adLib);

Windows Access Control - OnAfterGetRecord()
CALCFIELDS("Login ID","Role Name");
Username := adLib.GetDisplayName("Login ID");
IF Username = '' THEN
  Username := "Login ID";

Windows Access Control - OnPostDataItem()
CLEAR(adLib);

You can download the file here.

cheers

Debugging Control Add-ins

If an error occurs, when running a page, where a custom control add-in was added, then it’s often not easy to find the error reason. Typical error message: Exception has been thrown by the target of an invocation.

An expert method to analyse add-ins is using Visual Studio.
For that run visual studio and goto menu “debug”, menu item “attach to process …”. in the following dialog select Microsoft.Dynamics.Nav.Client.exe.

sc11.png-550x0

Then start the nav windows client (RTC), open the page, which contains the field with the control-addin and start the according action. After that, the error is shown in visual studio at the exact position, where the error occurs (debug mode).
In most cases there is for sure an unhandled exception. Edit the c# source file, fix the code and add at this position a better exception handling to get a better speaking exception message.

Also helpful for analysing purposes is to develope a logging mechanism, e.g. write eventlog entries.