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

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  | Date
dateString := '15-MAR-14';
dateValue := ConvertDateString(dateString);

ConvertDateString(dateString : Text[9]) : Date
// local variables
// dateString | Text
// dayValue   | Integer
// months     | Text
// monthValue | Integer
// yearValue  | Integer
// dateValue  | Date
// mPos       | Integer
// monthText  | Text

if strpos(dateString,'-') > 0 then begin
  dateString := CONVERTSTR(dateString,'-',',');
  months := 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec';
  monthText := lowercase(SELECTSTR(2,dateString));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(months,monthText);
  if mPos >0 then begin
    monthValue := (mPos + 3) / 4;
    IF yearValue > 14 THEN
      yearValue := yearValue + 1900
      yearValue := yearValue + 2000;
    dateValue := DMY2DATE(dayValue,monthValue,yearValue);
  end else
    error('No valid month given: ' + monthText);

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,dec

Position := 1;
Length := STRLEN(DateText);
ReadCharacter(' ',DateText,Position,Length);
// begin changes
IF STRPOS(DateText,'-') > 0 THEN BEGIN
  DateText := CONVERTSTR(DateText,'-',',');
  monthText := lowercase(SELECTSTR(2,DateText));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(Text022,monthText);
  IF (mPos > 0) THEN BEGIN
    monthValue := (mPos + 3) / 4;
    // optional: add a leading 0, if needed; simple use format(monthValue) should also work
    monthText := PADSTR('',2 - STRLEN(FORMAT(monthValue)),'0') + FORMAT(monthValue);
    DateText := SELECTSTR(1,DateText) + '-' + monthText + '-' + SELECTSTR(3,DateText);
    error('No valid month given: ' + monthText);
// end changes
IF NOT FindText(PartOfText,DateText,Position,Length) THEN


Process decimal values with different regional settings

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 language
  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


nav 2013 web client limitations & possibilities

the nav web client is the second client to connect to dynamics nav. it was introduced with nav 2013. the main purpose is for outdoor activities. but there are some limitations.

  • the menu structure is reduced to the minimum.
  • there are no possibilities to customize that client.
  • not all pages can be reached by default.

but there are possibilities to pimp the web client.

  • to view any page in the webclient you only need the number of the page,
    e.g. http://localhost:8080/List.aspx?page=16
  • the search window:
    the search window works like the search box in the windows client. every page is so reachable.
    to enable the search window, goto folder C:\Program Files\Microsoft Dynamics NAV\70\Web Client, there open the file web.config in admin mode. search for “<add key=”ShowPageSearch” value=”false”/>”. change the value to true.
    restart the nav webclient in the iis admin.
  • addins for usage with the webclient, goto:
  • for special customization (only for web developer pros):
    add/change the settings in section <httpHandlers> in the web.config
and what else (like george clooney says)?
develope your own client app using nav webservices.

what else?

Dynamics Connector for NAV vs. 3.4 is released

Hi all,

Dynamics Connector for NAV vs. 3.4 (V3 CU4) was released on Dec./10/2014. The new Dynamics Connector provides an integration between Microsoft Dynamics CRM 2013 or CRM 2015 and Microsoft Dynamics NAV 2009 R2, NAV 2013, NAV 2013 R2 or NAV 2015. For integration of CRM 2011 use Connector V3 (Download section).

System Requirements:
.NET Framework 4.5.2
For Microsoft Dynamics NAV 2009 R2 KB 2597312 is needed.
For Microsoft Dynamics NAV 2009 R2 KB 2721512 is needed.

You can download the Connector from here.

Table Permissions for Limited Users

With Nav 2013 a new License Model was introduced. Especially the new Limited User License is a bit hard to understand. First of all: full read access is permitted. But … write access is very strong limited. It’s said, that within a write transaction 3 tables are allowed to access, But … it’s not described, how to do that. That is not easy to handle, especially for developers. 😦  But ok. It would be interesting, how the Microsoft Developers themselves handle that limitation.
What does that mean at the end? When writing your own code, always check your list of used tables. A maximum of only 3 tables, you use, might not be listed in “Appendix A”, within your code snippet!
At least there are a couple of tables, that can be used anyway. Following is the original text out from the “Dynamics ERP Perpetual License Guide”.

Limited User licenses for Microsoft Dynamics NAV 2013 provide users full read, but limited write capabilities to all of the licensed solution functionality through any and all modes of access including the Windows Client. The Limited User license is designed to give customers a lower-cost alternative for extending ERP to users who only need to perform the following tasks:

  • “Read access” to data contained in the ERP solution through any client accessing the application via the Microsoft Dynamics NAV Application Programming Interface (API) including but not limited to the User Interface Web Services for Microsoft Dynamics NAV
  • “Write access” via the Timesheet functionality
  • “Write access” through any client accessing the ERP solution via the Microsoft Dynamics NAV API to a maximum of 3 table objects in any range number with the following exceptions:
    • limited users are not allowed to write to any of the following tables: General Ledger (table 17), Permission Set (table 2000000004), Permission (table 2000000005) or Access Control (table 2000000053) and
    • any table described in Appendix A as “Limited User Included Tables” do not count towards the 3 table objects

APPENDIX A – Limited User Included Tables
The following tables do not count towards the maximum of 3 permitted for Limited Users in Microsoft
Dynamics NAV. Last updated 20 November 2012 (11 tables added)

Table Table Name Table Table Name
43 Purch. Comment Line 5123 Inter. Log Entry Comment Line
44 Sales Comment Line 5125 Purch. Comment Line Archive
51 User Time Register 5126 Sales Comment Line Archive
97 Comment Line 5150 Integration Page
309 No. Series Line 5151 Integration Record
336 Tracking Specification 5199 Attendee
337 Reservation Entry 5648 FA Allocation Dimension
355 Dimension Ledger Entry 5766 Warehouse Activity Header
356 Journal Line Dimension 5772 Registered Whse. Activity Hdr.
357 Document Dimension 5773 Registered Whse. Activity Line
358 Production Document Dimension 5814 Inventory Period
359 Posted Document Dimension 6550 Whse. Item Tracking Line
361 G/L Budget Dimension 7135 Item Budget Dimension
389 Service Contract Dimension 7310 Warehouse Journal Batch
405 Change Log Entry 7311 Warehouse Journal Line
455 Approval Line Comment 7312 Warehouse Entry
480 Dimension Set Entry 7313 Warehouse Register
481 Dimension Set Tree Node 7318 Posted Whse. Receipt Header
952 Time Sheet Detail 7319 Posted Whse. Receipt Line
953 Time Sheet Comment Line 7320 Warehouse Shipment Header
5050 Contact 7321 Warehouse Shipment Line
5051 Contact Alt. Address 7322 Posted Whse. Shipment Header
5052 Contact Alt. Addr. Date Range 7323 Posted Whse. Shipment Line
5054 Contact Business Relation 7324 Whse. Put-away Request
5061 Rlshp. Mgt. Comment Line 7325 Whse. Pick Request
5062 Attachment 7326 Whse. Worksheet Line
5065 Interaction Log Entry 7331 Whse. Internal Put-away Header
5072 Campaign Entry 7332 Whse. Internal Put-away Line
5075 Logged Segment 7354 Bin
5078 Segment History 2000000067 User Default Style Sheet
5080 To-do 2000000068 Record Link
5106 Document Dimension Archive 2000000073 User Personalization
5107 Sales Header Archive 2000000075 User Metadata
5108 Sales Line Archive 2000000080 Page Data Personalization
5109 Purchase Header Archive 2000000111 Session Event
5110 Purchase Line Archive

Source: Page 6 and Appendix A in Dynamics ERP Perpetual Licensing Guide.

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

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.

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

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

create fct.:
Usage of automations in nav 2013:
Usage of automations in nav 2009:

Sql Statements in C/AL – Part 1 : Select

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 variables
ADOConnection	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Connection	
ADOCommand	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Command	
ADOParameter	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Parameter	
ADORecSet	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Recordset	
adoField	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Field	
connString	  Text		1024
activeConnection  Variant		
salesLineStr	  Text		1024
idx		  Integer		
adoValue	  Variant		
adoType		  Integer		

// the code
// replace the server ip and the ext. databasename with your values
connString := 'Driver={SQL Server};Server=;Database=Cronus600';
// convert ADO Connection to Variant
activeConnection := ADOConnection;

ADOCommand.ActiveConnection := activeConnection;
ADOCommand.CommandType := 1; // type is sql statement
// sample statement: read first record from table sales line
ADOCommand.CommandText := 'select top 1 * from [CRONUS AG$Sales Line]';
ADOCommand.CommandTimeout := 100;

ADORecSet := ADOCommand.Execute;
ADORecSet.MoveFirst;  // goto first record
FOR 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 converted
    adoValue := adoField.Value
    adoValue := GetFieldValue(adoField);
  IF (adoField.Type  13) THEN  // ado type 13 = Unknown
    // gets the value with index idx of the resulting record
    salesLineStr := salesLineStr + FORMAT(adoValue);
  salesLineStr := salesLineStr + ';';


GetFieldValue(adoField : 'Microsoft ActiveX Data Objects 2.8 Library'.Field) : Text
// local variables
// ADOStream	  Automation	'Microsoft ActiveX Data Objects 2.8 Library'.Stream	
// RetVal	  Text		
ADOStream.Position:= 0;
RetVal:= ADOStream.ReadText;

helpful links: