c/al

Convert SETSELECTIONFILTER to SETFILTER

1 Comment

If you want to print more than one document in a document list at one stroke – e.g. sales invoices – you would select 2 or more records and then start printing.

But, … in the request page only the last selected document no. or, also possible, no number is set.

Internally in most cases a setselectionfilter command is applied to get the selected records. But whats going on, when selecting the records? each record is marked. With setselectionfilter we can filter the selected record for the setfilter expression.
To print all selected records in one stroke you can do following. Add a new action to the action list of the list page and add following code to the new action trigger:

// local variablesSalesInvHeader | Record | Sales Invoice HeadernoFilter | Text// the code - OnAction()CurrPage.SETSELECTIONFILTER(SalesInvHeader); // fetch the marks// internally property Marked is set to true at the selected records// the loop will fetch only these recordsIF SalesInvHeader.FINDFIRST THEN BEGIN  REPEATIF noFilter  '' THEN  noFilter := noFilter + '|';noFilter := noFilter + SalesInvHeader."No."; // create filter expr.  UNTIL SalesInvHeader.NEXT = 0;  CLEAR(SalesInvHeader);  SalesInvHeader.SETFILTER("No.",noFilter); // create the filterEND;REPORT.RUNMODAL(206,TRUE,FALSE,SalesInvHeader);

As result we get:

Cheers

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

Time driven actions with .Net Class Timer

5 Comments

Till Nav 2009 if time driven actions were needed, it was done using automation NTimer.dll (NavTimer). With Nav 2013 and newer Versions Microsoft recommends to avoid usage of automations. As a result many of the common used automations, shipped with Nav 2009 and earlier, disappeared. There are few descriptions, how to solve common issues formerly solved with automations. So, let’s have a look at the Timer issue.

The .Net Framework contains class System.Timers.Timer in assembly System.dll. To use and test it, create a new codeunit and add some global variables:

Variable Timer is of subtype System.Timers.Timer. We set properties RunOnClient to false, but WithEvents to true. Only one of these properties can be set to true at one time, both together is not allowed. But ok, we need the Triggers (in .Net called Events). With RunOnClient=false, the code runs on the server … and that’s ok. Setting WithEvents to true we get automatically all embedded .Net Events written in the C/AL code, in that case Timer::Elapsed(sender : Variant;e : DotNet “System.Timers.ElapsedEventArgs”) and Timer::Disposed(sender : Variant;e : DotNet “System.EventArgs”). We only use the first one.

In the sample we want create a file and write lines into the file, step by step, every 3 seconds one line. We use a Counter for a break condition.

OnRun()Counter := 0; // Counter is an Integer variableMESSAGE('Timer started');IF EXISTS('c:\temp\sample.txt') THEN  ERASE('c:\temp\sample.txt'); // delete the file, if it already existsTextFile.CREATE('c:\temp\sample.txt'); // TextFile is a FILE variableTextFile.TEXTMODE := TRUE;Timer := Timer.Timer(); // create a Timer instanceTimer.Interval := 3000; // i.e. 3 secs (unit is ms)Timer.Enabled := TRUE;Timer.Start(); // starts the timerTimer::Elapsed(sender : Variant;e : DotNet "System.Timers.ElapsedEventArgs")Counter := Counter + 1;TextFile.WRITE('line ' + FORMAT(Counter) + ', ' + FORMAT(TODAY) + ' ' + FORMAT(TIME));// stop timer after trigger Elapsed was called 10 timesIF Counter > 10 THEN BEGIN  Timer.Enabled := FALSE;  Timer.Stop(); // stops the timer  Timer.Close();  CLEAR(Timer);  TextFile.CLOSE;END;

Result:

cheers

Error: Could not load the selected type library

1 Comment

This is a common error, which can be caused by following reasons:

  • an automation is missing
  • the automation was missing, then installed on the system, but the nav object was not re-compiled after installation
  • a different version of the automation is expected by the nav object.

If this error occurs, check where the variable is defined (local or global variables list). After you found the variable, you will see, that the value in field Subtype is like “Unknown Automation Server.Application”. When exporting the Nav object as text file, you would find a subtype value like that: Class1@1000000010 : Automation “{D1233675-2BBA-49DD-AD90-1680A404EAD5} 1.0:{DB3E185E-C123-46C2-9C62-F4A4E81E0B8F}:’FunnyAutomation’.Class1”. If you don’t know exactly what you are missing, this strange looking subtype value can help you for research.

Next step is – and that is the importand one – remove the value in field subtype! Do not click on the Assist button first and choose the automation without removing the value. It won’t work! So, first remove the value, then click on the Assist button and search for the missing automation.

If you do not find the missing automation, then look for the setup file and install the automation. After that restart the CC Client and start this fix procedure from the beginning. If you don’t know, which automation is missing (maybe an old code, a code of a different developer, …), then start with a research, ask your colleagues, your PM, do a little web search or find an alternative solution.

If all is fine, the automation is installed and the Unknown… value was removed, find and choose the automation after clicking on the Assist button and select the needed embedded class of the automation. After that re-compile the Nav Object and test the whole thing.

cheers

Session List in NAV 2009

1 Comment

The Session List in Nav 2013 gives an overview over the active sessions. This overview shows session details for all active sessions. This page is mainly used for debugging purposes, but also useful, if there are problems with sessions or if too many user sessions are open, etc.

Till NAV2009R2 the current sessions can be displayed in classic client under File–>Database–>Information. There select the “Sessions” tab and click the assist button right to the sessions counter. But sometimes it’s more helpful to have a dictinct (and maybe customisable) form to display the current (user) sessions like in Nav 2013. It can easily be created.

Create a new form with SourceTable “Session” with fields “Connection ID”, “User ID”, “Login Type”, “Login Date”, “Login Time”, “Host Name”, “Application Name”, “Database Name”, “Idle Time”.
Add a filter for field “Database Name” with the value of the current database to trigger OnOpenForm.

Form - OnOpenForm()// local variables// database | Record | Databasedatabase.SETRANGE("My Database",TRUE);database.FINDFIRST;SETRANGE("Database Name",database."Database Name");

You can download the form from here.

Send Outlook Mail with different Sender Address

4 Comments

For sending outlook mails one can use CU 397 and it works fine, if it’s ok to use the standard outlook profile as sender address (“From”). If you want to use a different sender address, then this is not possible.

To get that possibility let’s have a look at the in CU 397 used .net assemblies. There we have especially assembly Microsoft.Dynamics.Nav.Integration.Office. For most cases a nice little thing. But it delivers no possibility to set/change the sender address. So what to do?

Assembly Microsoft.Dynamics.Nav.Integration.Office.dll references (internally) the assembly Microsoft.Office.Interop.Outlook. So let’s have a more precise look on THAT assembly. There we have the typical from COM to .Net converted assembly with strange looking classes like Microsoft.Office.Interop.Outlook.ApplicationClass, etc. But … this class ApplicationClass contains a property Session and further a property Accounts, what means the outlook user profiles. Voila! We have, what we want, the access to the solution. Accounts.item(index) gives us one distinct account, which has the property SmtpAddress, means the mail address of an outlook account. This mail address we will compare with the given sender mail address for sending the mail. What else do we need? The possibility to assign the sender address. So let’s check the mail message class Microsoft.Office.Interop.Outlook.MailItem. There we have property SendUsingAccount to set/assign the sender account. Ok then, let’s do it …

//local variables:olApp DotNet Microsoft.Office.Interop.Outlook.ApplicationClass.'Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'olMailItem DotNet Microsoft.Office.Interop.Outlook.MailItem.'Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'olItemType DotNet Microsoft.Office.Interop.Outlook.OlItemType.'Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'olAccountList DotNet Microsoft.Office.Interop.Outlook.Accounts.'Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'olAccount DotNet Microsoft.Office.Interop.Outlook.Account.'Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'olAttachmentType DotNet Microsoft.Office.Interop.Outlook.OlAttachmentType.'Microsoft.Office.Interop.Outlook, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'fileName | TextsenderMailAddress | Textidx | Integer

Let’s say, we select the sender address from table “company information”.

senderMailAddress := CompInfo."E-Mail" // e.g. 'sender@test.com';olApp := olApp.ApplicationClass; // creates the outlook instanceolMailItem := olApp.CreateItem(olItemType.olMailItem);  // creates a new outlook mail message// find the selected outlook profile and set it as sender mailAddressolAccountList := olApp.Session.Accounts;idx := 1;REPEAT  olAccount := olAccountList.Item(idx);  IF LOWERCASE(olAccount.SmtpAddress) = LOWERCASE(senderMailAddress) THENolMailItem.SendUsingAccount := olAccount;  idx += 1;UNTIL idx > olAccountList.Count;olMailItem.Subject := 'subject text';olMailItem."To" := 'receiver@test.com';olMailItem.Body := 'This is the message.';fileName := 'c:\temp\test.docx'; // optional: file to attacholMailItem.Attachments.Add(fileName,olAttachmentType.olByValue,1,fileName);olMailItem.Display(TRUE); // Display the outlook window

cheers

Extend CU 6224: Read xml node values and attributes

1 Comment

Xml documents can be created/processed via XmlPorts. An other opportunity is the usage of Codeunit 6224. It’s used for complex xml handling. This CU only delivers some create and find functions, but no read/get functions. Following functions extend CU 6224 with read functions.

First a sample for the usage of the new functions. This sample shows, how to load a xml document given as string and read some of the node attributes.

// local variables// XmlMgmtCodeunitXML DOM Management// xmlDocAutomation'Microsoft XML, v3.0'.DOMDocument60// rootNodeAutomation'Microsoft XML, v3.0'.IXMLDOMNode// childNodeAutomation'Microsoft XML, v3.0'.IXMLDOMNode// addressText30// prioInteger// createdAtDateCREATE(xmlDoc);xmlDoc.loadXML('<mail priority="1" createdAt="26/5/2014"><from address="from@test.com"/>' +'<to name="to@test.com"/><state value="1"/></mail>');rootNode := xmlDoc.selectSingleNode('mail');prio := XmlMgmt.GetAttributeValueAsInt(rootNode,'priority');createdAt := XmlMgmt.GetAttributeValueAsDate(rootNode,'createdAt');childNode := rootNode.firstChild;address := XmlMgmt.GetAttributeValueAsText(childNode,'address');MESSAGE('mail: ' + FORMAT(prio) + ',' + FORMAT(createdAt) + ',' + address);CLEAR(rootNode);CLEAR(childNode);CLEAR(xmlDoc);

The new functions:

// Read an attribute value from a given node, e.g. from <data att1="1"/> gets value 1GetAttributeValueAsText(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Text// attributeNodeLoc | Automation | 'Microsoft XML, v3.0'.IXMLDOMNodeSetNormalCase;IF ISCLEAR(xmlNodePar) THEN  EXIT('Param. xmlNodePar must not be null');IF attributeNamePar = '' THEN  EXIT('Param. attributeNamePar must not be empty');attributeNodeLoc := xmlNodePar.attributes.getNamedItem(attributeNamePar);IF ISCLEAR(attributeNodeLoc) THEN  EXIT('Attribute ' + attributeNamePar + ' not found');IF STRLEN(attributeNodeLoc.text) > 0 THEN  EXIT(attributeNodeLoc.text);EXIT('');GetAttributeValueAsInt(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Integer// attrValLoc | Text// returnValueLoc | IntegerattrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);IF attrValLoc <> '' THEN  IF NOT EVALUATE(returnValueLoc, attrValLoc) THENEXIT(-1);EXIT(returnValueLoc);GetAttributeValueAsDec(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Decimal// attrValLoc | Text// returnValueLoc | DecimalattrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);IF attrValLoc <> '' THEN  IF NOT EVALUATE(returnValueLoc, attrValLoc) THENEXIT(-1);EXIT(returnValueLoc);GetAttributeValueAsDate(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Date// attrValLoc | Text// returnValueLoc | DateattrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);returnValueLoc := 0D;IF attrValLoc <> '' THEN  IF NOT EVALUATE(returnValueLoc, attrValLoc) THEN;EXIT(returnValueLoc);GetAttributeValueAsBool(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Boolean// attrValLoc | Text// returnValueLoc | BooleanattrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);returnValueLoc := FALSE;IF attrValLoc <> '' THEN BEGIN  IF NOT EVALUATE(returnValueLoc, attrValLoc) THENERROR('No bool value given');  EXIT(FALSE);END;EXIT(returnValueLoc);// Following is needed to read a node value, e.g. from <data>1</data> gets value 1GetNodeValueAsText(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode") : Text[1024]IF ISCLEAR(xmlNodePar) THEN  EXIT('Param. xmlNode must not be null');IF STRLEN(xmlNodePar.text) > 0 THEN  EXIT(xmlNodePar.text);EXIT('');// Return a node value as Integer valueGetNodeValueAsInt(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode") : Integer// attrValLoc | Text// returnValueLoc | IntegerxmlNodeValueLoc := GetNodeValueAsText(xmlNodePar);returnValueLoc := 0;IF xmlNodeValueLoc <> '' THEN  IF NOT EVALUATE(returnValueLoc, xmlNodeValueLoc) THENERROR('No int value given');EXIT(returnValueLoc);

Instead of Xml vs. 3 you can also use Xml vs. 6, e.g. xmlNode : ‘Microsoft XML, v6.0’.IXMLDOMNode.

Export captions with Nav 2009

Leave a comment

Sometimes there can be a need to export the captions of a nav object, maybe for translation purposes. For that kind of task, there is no nice possibility in NAV. i found a fine solution for this task on mibuso.com. Thanks to Duikmeester.
With the attached report you can export the captions of a given table. An export file “export.txt” containing all or a part of the captions (filtered by field no.) is created. The report can also be printed directly.

Request Form: TableRequest Form: Fields
Resulting PreviewResulting Export file

The original post.
The report Nav2009-Export-Captions for download.