Convert SETSELECTIONFILTER to SETFILTER

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.

sc1

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

sc2

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:

sc3

// local variables
SalesInvHeader | Record | Sales Invoice Header
noFilter | 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 records
IF SalesInvHeader.FINDFIRST THEN BEGIN
  REPEAT
    IF noFilter  '' THEN
      noFilter := noFilter + '|';
    noFilter := noFilter + SalesInvHeader."No."; // create filter expr.
  UNTIL SalesInvHeader.NEXT = 0;
  CLEAR(SalesInvHeader);
  SalesInvHeader.SETFILTER("No.",noFilter); // create the filter
END;
REPORT.RUNMODAL(206,TRUE,FALSE,SalesInvHeader);

As result we get:

sc4

Cheers

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

Time driven actions with .Net Class Timer

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:

sc10

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 variable

MESSAGE('Timer started');
IF EXISTS('c:\temp\sample.txt') THEN
  ERASE('c:\temp\sample.txt'); // delete the file, if it already exists
TextFile.CREATE('c:\temp\sample.txt'); // TextFile is a FILE variable
TextFile.TEXTMODE := TRUE;

Timer := Timer.Timer(); // create a Timer instance
Timer.Interval := 3000; // i.e. 3 secs (unit is ms)
Timer.Enabled := TRUE;
Timer.Start(); // starts the timer

Timer::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 times
IF Counter > 10 THEN BEGIN
  Timer.Enabled := FALSE;
  Timer.Stop(); // stops the timer
  Timer.Close();
  CLEAR(Timer);
  TextFile.CLOSE;
END;

Result:

sc11

cheers

Error: Could not load the selected type library

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.

sc6

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

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.

sc5

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 | Database
database.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

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 | Text
senderMailAddress | Text
idx | 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 instance
olMailItem := olApp.CreateItem(olItemType.olMailItem);  // creates a new outlook mail message

// find the selected outlook profile and set it as sender mailAddress
olAccountList := olApp.Session.Accounts;
idx := 1;
REPEAT
  olAccount := olAccountList.Item(idx);
  IF LOWERCASE(olAccount.SmtpAddress) = LOWERCASE(senderMailAddress) THEN
    olMailItem.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 attach
olMailItem.Attachments.Add(fileName,olAttachmentType.olByValue,1,fileName);
olMailItem.Display(TRUE); // Display the outlook window

cheers