nav add-ins

How to work with big item descriptions

9 Comments

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=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;

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

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

13 Comments

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 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”.

cheers

Dotnet Events are not shown

Leave a comment

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.

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.


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.

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}

1 Comment

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.

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

Leave a comment

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

Leave a comment

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 fieldsLogin SID | Username | Role ID | Role Name// global variablesUsername | Text | 100adLib | Automation | 'ActiveDirectoryLib'.AccountInfo// the codeWindows 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

Leave a comment

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.

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.

Error, when creating an instance of a .net class

Leave a comment

Following error can occur, when creating an instance of a .net class in C/AL:

Reasons:

  • If the assembly is used on client side (property RunOnClient=True), then the assembly was not copied to the local Add-ins Folder of the RTC Client. To copy the assembly into this folder you need in most cases Admin rights. So, check that first.
  • If the assembly is used on server side (property RunOnClient=False), then there might be a version conflict between the assembly file located in the Add-ins Folder of the Nav Service and the used version in the C/AL Code. That can easy occur after installing Updates. So update the code and restart the nav service.
  • Missing System permission: That means, that the assembly was copied from the web or from a computer outside the local network to a target computer/server with windows server OS. Then, the assembly is automatically blocked by the system.

Navgate – a new Development Environment for Dynamics NAV

Leave a comment

hi all,

good news on the front for development environments.

First some rumours about the current Dev.env.: Maybe there will be a RTC Version of the Nav Dev.env. in the future. This means: complete new surface (like VS ?), shorter runtime when starting/testing nav objects, better integration with the test tool.

The real good news has a name: NavGate
Navgate from idyn (Developers of the OM-Tools) is a new editor for c/al and would be a big, big step in useability. It looks like Visual Studio, it feels like Visual Studio. There are some many features in navgate, that i miss in the current dev.env. since years.

It is not very probable, that microsoft changes the programming language from c/al to c# (in the near future), although it would be a real good idea. But for the meantime, this new editor will be a great help for developers.

Links:

best regards
Jonathan