nav 2009

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

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.

Encrypt/Decrypt strings in C/AL

8 Comments

Following can be used, if you need a encryption/decryption function in NAV. In this quite simple sample there is shown the usage of the TripleDES encryption algorithm. The base are the cryptograhic function in namespace System.Security.Cryptography of the .net framework.

// local variablesTripleDESEncDotNetSystem.Security.Cryptography.TripleDES.'mscorlib, Version=4.0.0.0, culture=neutral, PublicKeyToken=b77a5c561934e089'cStreamDotNetSystem.Security.Cryptography.CryptoStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cModeDotNetSystem.Security.Cryptography.CryptoStreamMode.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cEncDotNetSystem.Security.Cryptography.ICryptoTransform.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'TripleDESDecDotNetSystem.Security.Cryptography.TripleDES.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cDecDotNetSystem.Security.Cryptography.ICryptoTransform.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'cKeyDotNetSystem.Byte.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'initVectorDotNetSystem.Byte.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'fStreamDotNetSystem.IO.FileStream.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'fModeDotNetSystem.IO.FileMode.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'sWriterDotNetSystem.IO.StreamWriter.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'sReaderDotNetSystem.IO.StreamReader.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'sDataText 1024FileNameText 1024fileFile// the codesData := 'Here is some data to encrypt.';FileName := 'c:\temp\CText.txt';// encrypt partfMode := fMode.OpenOrCreate;fStream := fStream.FileStream(FileName,fMode);TripleDESEnc := TripleDESEnc.Create('TripleDES');// create encryptor and internally a random key and a random IVcEnc := TripleDESEnc.CreateEncryptor;cMode := cMode.Write;cStream := cStream.CryptoStream(fStream,cEnc,cMode);sWriter := sWriter.StreamWriter(cStream);sWriter.WriteLine(sData);sWriter.Close;cStream.Close;fStream.Close;file.OPEN(FileName);file.READ(sData);file.CLOSE;// display encrypted stringMESSAGE('Encrypted: ' + sData);// decryptfMode := fMode.OpenOrCreate;fStream := fStream.FileStream(FileName, fMode);TripleDESDec := TripleDESDec.Create;// reuse the key/iv-pair, created abovecDec := TripleDESDec.CreateDecryptor(TripleDESEnc.Key,TripleDESEnc.IV);cMode := cMode.Read;cStream := cStream.CryptoStream(fStream,cDec,cMode);sReader := sReader.StreamReader(cStream);sData := sReader.ReadLine;sReader.Close;cStream.Close;fStream.Close;MESSAGE('Decrypted: ' + sData);

In the sample the same Key/IV pair was used for encryption and decryption, automatically created by method CreateEncryptor. To use your own Key/IV pair use method CreateEncryptor(byte[] key, byte[] iv) or set the Key and IV property of the encryptor/decryptor.

...CreateByteArray(cKey, 'abcdefghijklmnoqprstuvwx'); // length: 24CreateByteArray(initVector, 'abcdefgh'); // length: 8TripleDESEnc.Key(cKey);TripleDESEnc.IV(initVector);cEnc := TripleDESEnc.CreateEncryptor;...CreateByteArray(VAR ByteArray : DotNet "System.Array";InputString : Text)// local variables// idxInteger// ArrayTypeDotNetSystem.Type.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'// ByteValueByte// StrLengthIntegerStrLength := strlen(InputString);ArrayType := ArrayType.GetType('System.Byte',FALSE);ByteArray := ByteArray.CreateInstance(ArrayType,StrLength);FOR idx := 1 TO StrLength DO BEGIN  ByteValue := InputString[idx];  ByteArray.SetValue(ByteValue,idx - 1);END;

Links:
http://msdn.microsoft.com/de-de/library/z565ef9x(v=vs.110).aspx

Sql Statements in C/AL – Part 2 : Create View

Leave a comment

The following code can be used to create a sql view in a given, external database.

// local variablesADOConnection  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.ConnectionADOCommand  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.CommandADORecSet  Automation 'Microsoft ActiveX Data Objects 2.8 Library'.RecordsetconnString  Text 1024activeConnection  VariantviewName  Text 100// the code// replace the server ip and the ext. databasename with your valuesconnString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';CREATE(ADOConnection);ADOConnection.ConnectionString(connString);ADOConnection.Open;// convert ADO Connection to VariantactiveConnection := ADOConnection;viewName := 'CRONUS$Item View';CREATE(ADOCommand);ADOCommand.ActiveConnection := activeConnection;ADOCommand.CommandType := 1;ADOCommand.CommandText :=  'SELECT * FROM INFORMATION_SCHEMA.TABLES where ' +  '(TABLE_TYPE = ''VIEW'') and (TABLE_NAME =''' + viewName + ''')';ADORecSet := ADOCommand.Execute;IF ADORecSet.EOF = TRUE THEN BEGIN  ADOCommand.CommandText := 'CREATE VIEW [' + viewName + ']' +'AS SELECT No_, Description FROM dbo.[CRONUS$Item]' +'WHERE No_ like ''100%''';  ADOCommand.Execute;  message('view created');END ELSE  MESSAGE('view already exists');ADORecSet.Close;ADOConnection.Close;CLEARALL;

follow also posting “Sql Statements in C/AL – Part 1 : Select”.

cheers

Convert date string containing a month text value to a date value

1 Comment

Assuming you want to enter date values in the RTC client like 15-MAR-14. that won’t work. date values are only accepted with syntax like 15.03, 15.03.14, etc. But it can be that users prefer the above syntax. so then you can use a text field in your page and convert internally the value into a date value. Following function converts that kind of date string to a date value.

// local variables// dateString | Text// dateValue  | DatedateString := '15-MAR-14';dateValue := ConvertDateString(dateString);MESSAGE(FORMAT(dateValue,0,'<day,2>.<month,2>.<year4>'));ConvertDateString(dateString : Text[9]) : Date// local variables// dateString | Text// dayValue   | Integer// months | Text// monthValue | Integer// yearValue  | Integer// dateValue  | Date// mPos   | Integer// monthText  | Textif strpos(dateString,'-') > 0 then begin  dateString := CONVERTSTR(dateString,'-',',');  EVALUATE(dayValue,SELECTSTR(1,dateString));  months := 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec';  monthText := lowercase(SELECTSTR(2,dateString));  if strlen(monthText) <> 3 thenerror('Invalid month expression: ' + monthText);  mPos := STRPOS(months,monthText);  if mPos >0 then beginmonthValue := (mPos + 3) / 4;EVALUATE(yearValue,SELECTSTR(3,dateString));IF yearValue > 14 THEN  yearValue := yearValue + 1900ELSE  yearValue := yearValue + 2000;dateValue := DMY2DATE(dayValue,monthValue,yearValue);exit(dateValue);  end elseerror('No valid month given: ' + monthText);end;exit(0D);

to use the standard functionality of date validation and conversion it’s needed to change function MakeDateText in codeunit 1:

MakeDateText(VAR DateText : Text[250]) : Integer// additional local variables// mPos | Integer// monthText | Text | 10// monthValue | Integer// Text Constant: Text022 | jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,decPosition := 1;Length := STRLEN(DateText);ReadCharacter(' ',DateText,Position,Length); // begin changesIF STRPOS(DateText,'-') > 0 THEN BEGIN  DateText := CONVERTSTR(DateText,'-',',');  monthText := lowercase(SELECTSTR(2,DateText));  if strlen(monthText) <> 3 thenerror('Invalid month expression: ' + monthText);  mPos := STRPOS(Text022,monthText);  IF (mPos > 0) THEN BEGINmonthValue := (mPos + 3) / 4;// optional: add a leading 0, if needed; simple use format(monthValue) should also workmonthText := PADSTR('',2 - STRLEN(FORMAT(monthValue)),'0') + FORMAT(monthValue);DateText := SELECTSTR(1,DateText) + '-' + monthText + '-' + SELECTSTR(3,DateText);  END ELSEerror('No valid month given: ' + monthText);END;// end changes IF NOT FindText(PartOfText,DateText,Position,Length) THEN...

cheers

Process decimal values with different regional settings

Leave a comment

When importing/processing decimal values, the decimal separator is . or , according to the regional setting. Assuming you want to import decimal values with US Standard regional setting, then the separator is a point (.).

To process such decimal values, you can work with the NAV Command GLOBALLANGUAGE. That is the in NAV at runtime used language.

impValue := 0.25;impValStr := format(impValue);// check for GER and FR languageif (GLOBALLANGUAGE = 1031) OR (GLOBALLANGUAGE = 1036) then  impValStr := Convertstr(impValStr,'.'','); // replaces the point by a comma

Additional you can use the Command WINDOWSLANGUAGE. This command returns the regional system setting (the language of the OS). In common with that command you can handle every case.

for language codes follow
http://technet.microsoft.com/en-us/library/dd346950.aspx

links:
http://msdn.microsoft.com/en-us/library/dd338772.aspx
http://msdn.microsoft.com/en-us/library/dd301095.aspx

You cannot create an Automation object ‘name of autom.’ on Microsoft Dynamics NAV Server. You must create it on a client computer.

2 Comments

This is an error message, when trying to create an instance of an automation on a nav server vs. >= 2013. It’s only possible to create the automation instance as client instance. Creating automation instances on nav server is allowed till nav 2009.

solution:
use create(<name_of_autom.>,false,true) instead of create(<name_of_autom.>).

remarks:
you need a installed version of the automation on each client computer.

links:
create fct.: http://msdn.microsoft.com/en-us/library/dd355255(v=nav.70).aspx
Usage of automations in nav 2013: http://msdn.microsoft.com/en-us/library/ee909565(v=nav.70).aspx
Usage of automations in nav 2009: http://msdn.microsoft.com/en-us/library/ee909565(v=nav.60).aspx

Sql Statements in C/AL – Part 1 : Select

2 Comments

There is no possibility to read data from an external database with standard c/al. Best way to do that till NAV version 2009 is using ADO. With Nav vs. 2013 upwards you can use .Net classes.
The following code can be used to read data from a given, external database.

// local variablesADOConnection  Automation'Microsoft ActiveX Data Objects 2.8 Library'.ConnectionADOCommand  Automation'Microsoft ActiveX Data Objects 2.8 Library'.CommandADOParameter  Automation'Microsoft ActiveX Data Objects 2.8 Library'.ParameterADORecSet  Automation'Microsoft ActiveX Data Objects 2.8 Library'.RecordsetadoField  Automation'Microsoft ActiveX Data Objects 2.8 Library'.FieldconnString  Text1024activeConnection  VariantsalesLineStr  Text1024idx  IntegeradoValue  VariantadoType  Integer// the code// replace the server ip and the ext. databasename with your valuesReadFromExternalDatabase()connString := 'Driver={SQL Server};Server=127.0.0.1;Database=Cronus600';CREATE(ADOConnection);ADOConnection.ConnectionString(connString);ADOConnection.Open;// convert ADO Connection to VariantactiveConnection := ADOConnection;CREATE(ADOCommand);ADOCommand.ActiveConnection := activeConnection;ADOCommand.CommandType := 1; // type is sql statement// sample statement: read first record from table sales lineADOCommand.CommandText := 'select top 1 * from [CRONUS AG$Sales Line]';ADOCommand.CommandTimeout := 100;CREATE(ADORecSet);ADORecSet := ADOCommand.Execute;ADORecSet.MoveFirst;  // goto first recordFOR idx := 1 TO ADORecSet.Fields.Count - 1 DO BEGIN  adoField := ADORecSet.Fields.Item(idx);  adoType := adoField.Type;  IF adoType  131 THEN  // ado type numeric must be convertedadoValue := adoField.Value  ELSEadoValue := GetFieldValue(adoField);  IF (adoField.Type  13) THEN  // ado type 13 = Unknown// gets the value with index idx of the resulting recordsalesLineStr := salesLineStr + FORMAT(adoValue);  salesLineStr := salesLineStr + ';';END;MESSAGE(salesLineStr);ADORecSet.Close;ADOConnection.Close;CLEARALL;GetFieldValue(adoField : 'Microsoft ActiveX Data Objects 2.8 Library'.Field) : Text// local variables// ADOStream  Automation'Microsoft ActiveX Data Objects 2.8 Library'.Stream// RetVal  TextCREATE(ADOStream);ADOStream.Open;ADOStream.WriteText(adoField.Value);ADOStream.Position:= 0;RetVal:= ADOStream.ReadText;ADOStream.Close;exit(RetVal);

helpful links:
http://www.w3schools.com/asp/ado_ref_connection.asp
http://www.w3schools.com/asp/ado_ref_command.asp
http://www.w3schools.com/asp/prop_comm_commandtype.asp#commandtypeenum