NAS Failes to initialize with error “‘Já’ is not an option.”

Posted on by
1

Just got a request from a client because the Job Queue NAS is not working.  Stopped working after a service restart.  Looking through Event Viewer I see the following error.

'Já' is not an option.

The existing options are:

No, Yes

In the %APPDATA% folder for the service user I find a file called NaviBP.xml.  This file includes a list of breakpoints from the last debugging session.  It seems that the debugger writes this file and uses the Icelandic ‘Já’ instead of the required English ‘Yes’.

<?xml version="1.0" ?>
<BreakpointList>
  <Object Type="Codeunit" ID="10010883" Name="E. Com Send-Receive Utilities">
      <Breakpoint>
      <TriggerName>CreateSendInvoiceRequest</TriggerName>
      <CodeNo>8</CodeNo>
      <TriggerLine>32</TriggerLine>
      <Enabled>Já</Enabled>
    </Breakpoint>
  </Object>
</BreakpointList>

After changing the <Enabled>Já</Enabled> to <Enabled>Yes</Enabled> the NAS should start normally.  If those breakpoints are no longer needed it is fine to just remove the file.

 

Client Temporary Path

Posted on by
1

In one of my solutions I create a lot of Excel and PDF documents.  All these documents are stored in BLOB fields and then downloaded to the client computer temporary folder and opened for the user.

Every time I use the ClientTempFileName function in Codeunit 419 a file is being created in the client computer temporary folder and that file is not deleted until the Role Tailored Client is closed.

Since the user is creating temporary files his whole workday I decided that a single instance codeunit would be a better way to store information about the client and server temporary file paths.  I created codeunit 50060 and two functions; GetClientTempPath and GetServerTempPath.

OBJECT Codeunit 50060 Application Temp Path Mgt.
{
  OBJECT-PROPERTIES
  {
    Date=27.03.12;
    Time=16:11:00;
    Modified=Yes;
    Version List=Dynamics.is;
  }
  PROPERTIES
  {
    SingleInstance=Yes;
    OnRun=BEGIN
          END;

  }
  CODE
  {
    VAR
      ThreeTierMgt@1200050000 : Codeunit 419;
      ClientTempPath@1200050001 : Text[1024];
      ServerTempPath@1200050002 : Text[1024];

    PROCEDURE GetClientTempPath@1200050000() : Text[1024];
    BEGIN
      IF ClientTempPath = '' THEN
        ClientTempPath := ThreeTierMgt.Path(ThreeTierMgt.ClientTempFileName('',''));
      EXIT(ClientTempPath);
    END;

    PROCEDURE GetServerTempPath@1200050001() : Text[1024];
    BEGIN
      IF ServerTempPath = '' THEN
        ServerTempPath := ThreeTierMgt.Path(ThreeTierMgt.ServerTempFileName('',''));
      EXIT(ServerTempPath);
    END;

    BEGIN
    END.
  }
}

The Source is here, Application Temporary Path

 

Automatic EMail from Change Log

Posted on by
1

I have been asked for a solution that can send an email when a customer is changed.  I have a few objects created and a codeunit that is executed by the Job Queue or manually from a page or a form.  First the Change Log needed to be activated.

Then after installing the attached object and adding page or from 77170 to the menu suite I can setup an email address for each table and each trigger.

Attached is the needed code, Change Log EMailer

Testing your Dynamics NAV Web Service

Posted on by
2

I am building a web service for one of my clients and another company is using this web service for an aspx web site.  I realized that I needed to test my web service before I can deliver it to that company.  So, I created a test codeunit for the job.

First I downloaded the universal XML import/export tool from Mibuso.  Then I added a function to the table 60000 XML Buffer that is in the above tool.

Read(VAR DOMDoc : Automation "'Microsoft XML, v6.0'.DOMDocument")
DELETEALL;
DOMNode := DOMDoc.documentElement;
Import2(DOMNode,1);
IF FINDFIRST THEN;

Next I create a read function in my test codeunit for every function in the web service.  Here is an example.

GetFarmerTankEntryAverageYW(FarmerID : Integer;MinYear : Integer;MaxYear : Integer;MinWeekNo : Integer;MaxWeekNo : Integer)
GetSetup;
CREATE(XMLDoc,TRUE,FALSE);

XMLProsInstr := XMLDoc.createProcessingInstruction('xml','version="1.0" encoding="utf-8"');
XMLDoc.appendChild(XMLProsInstr);

CreateEnvelope(XMLElement1);
XMLElement2 := XMLDoc.createElement('soap:Body');
XMLElement3 := XMLDoc.createElement('GetFarmerTankEntryAverageYW');
XMLElement3.setAttribute('xmlns','urn:microsoft-dynamics-schemas/codeunit/RMWeb');
CreateElement(XMLElement3, 'farmerID', FORMAT(FarmerID,0,9), '', '');
CreateElement(XMLElement3, 'minYear', FORMAT(MinYear,0,9), '', '');
CreateElement(XMLElement3, 'maxYear', FORMAT(MaxYear,0,9), '', '');
CreateElement(XMLElement3, 'minWeekNo', FORMAT(MinWeekNo,0,9), '', '');
CreateElement(XMLElement3, 'maxWeekNo', FORMAT(MaxWeekNo,0,9), '', '');
CreateElement(XMLElement3, 'tankEntryXML', '', '', '');
XMLElement2.appendChild(XMLElement3);
XMLElement1.appendChild(XMLElement2);
XMLDoc.appendChild(XMLElement1);

WinHTTP.open('POST',ServiceURL,FALSE,UserName,Password);
WinHTTP.setRequestHeader('Content-Type','text/xml; charset=utf-8');
WinHTTP.setRequestHeader('SOAPAction','GetFarmerTankEntryAverageYW');
WinHTTP.send(XMLDoc);

IF WinHTTP.status <> 200 THEN
  ERROR(Text003,WinHTTP.status,WinHTTP.statusText);

XMLResponseDoc.load(WinHTTP.responseXML);
DisplayDocument(XMLResponseDoc);

This will use the XML Buffer to read the response document and display the result.  The Text Constant Text003 contains

ENU=Status error %1 %2;ISL=Stöðuvilla %1 %2

and the four functions used here contain

DisplayDocument(VAR XMLDoc : Automation "'Microsoft XML, v6.0'.DOMDocument")
XMLBuffer.Read(XMLDoc);
COMMIT;
FORM.RUNMODAL(FORM::"XML Buffer");

CreateEnvelope(VAR InElement : Automation "'Microsoft XML, v6.0'.IXMLDOMElement")
InElement := XMLRequestDoc.createElement('soap:Envelope');
InElement.setAttribute('xmlns:soap','http://schemas.xmlsoap.org/soap/envelope/');
InElement.setAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
InElement.setAttribute('xmlns:xsd','http://www.w3.org/2001/XMLSchema');

CreateElement(VAR InElement : Automation "'Microsoft XML, v6.0'.IXMLDOMElement";InNodeName : Text[50];InNodeValue : Text[250];InAttribu
TempElement := XMLRequestDoc.createElement(InNodeName);
TempElement.nodeTypedValue(InNodeValue);
IF InAttributeName <> '' THEN
  TempElement.setAttribute(InAttributeName,InAttributeValue);
InElement.appendChild(TempElement);

GetSetup()
ServiceURL := 'http://gunnar.dynamics.is:7047/DynamicsNAV/WS/CRONUS/Codeunit/WebService';
UserName := '<Domain\User>';
Password := '<Password>';
IF ISCLEAR(WinHTTP) THEN
  CREATE(WinHTTP,TRUE,FALSE);
IF ISCLEAR(XMLResponseDoc) THEN
  CREATE(XMLResponseDoc,TRUE,FALSE);

Data Visualization Control not found

Posted on by
1

Just created a chart for a customer and added it to the customized role center.  The customer got a permission error when opening the client as there is no permission to read the table Chart no. 2000000078.  Added read permission to that table to the user role and the client started.  The next error was that the data visualization control was missing on the client machine.

A quick search pointed me to this download link.  After installing and restarting the client everything is working as it should be.

Ommit Zero in Calculation Formula

Posted on by
1

There are a few calculation methods available in for a flowfield.  This is a great feature in NAV to aggregate amounts from ledger entries.  I would like Microsoft to add an option to the flowfield declaration.  I would like to be able to omit zeros when using methods; Average, Exist, Count, Max and Min.  In a normal SQL it is possible to use NULL to archive this result but not in NAV.

The solution that I am using now is to create a boolean field for each of the amount fields that indicate if the amount is zero.  Then I have to add that boolean field to the sum-index enabled key and filter on that field.

Microsoft, please add this option to your upcoming releases of Dynamics NAV.  You can vote for this here.

 

 

Job Queue stops when lock time out occurs

Posted on by
Reply

Running NAS with Job Queue will start a timer to process Job Queue Entries every two seconds. In the original code the Timer is disabled before checking the Job Queue Entries and then enabled again after the process. If NAS will not be able to read the Job Queue Entry then the function will exit without enabling the Timer and nothing will be processed.

The original code in Codeunit 448 is

HandleRequest()
JobQueueSetup.GET;
IF NOT JobQueueSetup."Job Queue Active" THEN
  EXIT;

NavTimer.Enabled := FALSE;

ThisSessionIsActive := UpdateJobQueueSession(JobQueueEntry,FALSE);

...

CleanUpJobQueue;
COMMIT;

NavTimer.Enabled := TRUE;

I suggest that you will find a suitable period of time where everything in the Queue should be processed and change the behaviour. Do not disable the timer, just change the interval.  Here I change the interval to five minutes.

The replacement code would then be

HandleRequest()
JobQueueSetup.GET;
IF NOT JobQueueSetup."Job Queue Active" THEN
EXIT;

NavTimer.Enabled := FALSE;
NavTimer.Interval := 5 * 60 * 1000; // 5 min
NavTimer.Enabled := TRUE;

ThisSessionIsActive := UpdateJobQueueSession(JobQueueEntry,FALSE);

...

CleanUpJobQueue;
COMMIT;

NavTimer.Enabled := FALSE;
NavTimer.Interval := 2 * 1000; // 2 sec.
NavTimer.Enabled := TRUE;

 

Geometric mean

Posted on by
2

In one of my recent projects I needed to calculate a geometric mean for a group of numbers.  The Geometric mean formula is generally, if the numbers are x_1,\ldots,x_n, the geometric mean G satisfies G = \sqrt[n]{x_1 x_2 \cdots x_n},

The problem appeared when I needed to multiply numbers and got a overflow on the decimal data type in Dynamics NAV.

I produced two solutions to this problem; first for Classic Client using Excel and secondly for Role Tailored Client using Add-in.  Using Excel for this job is slow but the Add-in works great.

In Excel I add values to a column and then use the built in function GEOMEAN(‘<Range>’) to calculate the value.  This works for large values.

The GeoMean Class Add-in I created in Visual Studio in VB.NET

Public Class GeoMeanClass
    Dim TotalValue As Double
    Dim NoOfValues As Integer
    Public Sub ResetValue()
        TotalValue = 1
        NoOfValues = 0
    End Sub
    Public Sub AddValue(ByVal Value As Decimal)
        If Value = 0 Then Exit Sub
        TotalValue = TotalValue * Value
        NoOfValues = NoOfValues + 1
    End Sub
    Public ReadOnly Property NoOfStoredValues() As Integer
        Get
            Return NoOfValues
        End Get
    End Property
    Public ReadOnly Property GetGeoMean() As Decimal
        Get
            If NoOfValues = 0 Then
                Return 0
            Else
                Return CDec(TotalValue ^ (1 / NoOfValues))
            End If
        End Get
    End Property
End Class

Attached are the Add-in and the codeunits needed to calculate Geometric mean.

GeoMean

 

Using OpenXML to create an Excel Document

Posted on by
1

In one of my projects I needed to create a complex Excel document from Dynamics NAV.  I did this by using COM automation in the same way that Microsoft is doing in the Excel Buffer table no. 370.  The problem is that this is dead slow.  It could take up to two minutes to create a single Excel document on the Role Tailored Client.

I saw in Kauffmann’s blog that it is possible to use OpenXML on the server side to create the Excel document.  Great blog and a big help.  The same Excel document is now created in under two seconds.

As I started the batch to create the OpenXML Excel documents I received an error: “hexadecimal value 0x1F, is an invalid character”.  I was inserting an invalid character into the XML.  I did a quick search and found a solution, I needed to filter the data through a white list.  Here is my AddCell function:

    LOCAL PROCEDURE AddCell@16(CurrentRow@1200050001 : Integer;CurrentCol@1200050000 : Integer;Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : BigText;IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];Attention@1200050002 : Boolean;Alignment@1200050006 : 'General,Left,Right,Center');
    VAR
      XlColor@1200050008 : DotNet "'ClosedXML, Version=0.64.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";
      CellValueAsText@1200050007 : Text[1024];
      CommentLine@1200050003 : Text[1024];
      CommentPosition@1200050004 : Integer;
      CommentLength@1200050005 : Integer;
    BEGIN
      IF CurrentRow = 0 THEN
        GlobalRowNo := GlobalRowNo + 1
      ELSE
        GlobalRowNo := CurrentRow;
      IF CurrentCol = 0 THEN
        GlobalColumnNo := GlobalColumnNo + 1
      ELSE
        GlobalColumnNo := CurrentCol;

      //Comments are not yet supported by ClosedXML
      //CommentLength := CommentText.LENGTH;
      //IF CommentLength > 0 THEN BEGIN
      //  CommentPosition := 1;
      //  WHILE CommentPosition < CommentLength DO BEGIN
      //    CommentPosition := CommentPosition  + CommentText.GETSUBTEXT(CommentLine,CommentPosition,MAXSTRLEN(CommentLine));
      //    XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Comment.AddText := CommentLine;
      //  END;
      //END;

      XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Bold := IsBold;
      XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Italic := IsItalics;
      IF IsUnderline THEN
        XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.SetBottomBorder :=
          GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.BottomBorder,'Continuous');

      XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.NumberFormat.SetFormat := NumFormat;

      IF Attention THEN BEGIN
        XlColor := XlColor.FromName('Red');
        XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.SetFontColor := XlColor;
      END;

      IF IsFormula THEN
        XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).FormulaA1 := Value
      ELSE
        CASE TRUE OF
          Value.ISTEXT,Value.ISCHAR,Value.ISCODE :
            BEGIN
              CellValueAsText := FORMAT(Value);
              CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);
              XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := CellValueAsText;
            END;
          ELSE IF FORMAT(Value) <> '0' THEN
            XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := Value;
        END;

      CASE Alignment OF
        Alignment::General:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'General');
        Alignment::Center:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Center');
        Alignment::Right:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Right');
        Alignment::Left:
          XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
            GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Left');
      END;
    END;

The line

CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);

is used to clean the string that is passed to the XML.

The GetEnumValue function is

    LOCAL PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
    VAR
      Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
    BEGIN
      ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
    END;

When constructing the OpenXML dotnet object I also construct the white characther check object.

XMLWhiteCharCheck := XMLWhiteCharCheck.XMLCharWhiteList;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();

Attached is the Add-in needed on the server side.

XMLCharWhiteList Add-in

 

 

VB.NET NAV Application Server

Posted on by
2

Most of my clients require a running NAV Application Server.  The NAS that is included in NAV 2009 R2 requires a license that is included in most licenses today.  However, there are cases where more than one NAS is needed.  That requires additional NAS licenses.  Where the customer is running NAV 2009 R2 middle tier service this changes.  By running a VB.NET NAV Application Server it is possible to setup multiple services on a single CAL license.  The CAL license is not as expensive as the NAS license.  Here is the solution that I offer.

First, I create a codeunit in NAV

ExecuteCodeunit(CodeunitID : Integer;Log : Boolean) Success : Boolean

IF Log THEN LogEntryNo := InsertLogEntry(5,CodeunitID);
Success := CODEUNIT.RUN(CodeunitID);
IF Log THEN
  UpdateLogEntry(LogEntryNo,Success)
ELSE IF NOT Success THEN BEGIN
  LogEntryNo := InsertLogEntry(5,CodeunitID);
  UpdateLogEntry(LogEntryNo,Success)
END;

InsertLogEntry(ObjectType : ',,,Report,,Codeunit';ObjectNo : Integer) : Integer
WITH JobQueueLogEntry DO BEGIN
  INIT;
  ID := CREATEGUID;
  "User ID" := USERID;
  "Start Date/Time" := CURRENTDATETIME;
  "Object Type to Run" := ObjectType;
  "Object ID to Run" :=  ObjectNo;
  INSERT(TRUE);
  COMMIT;
  EXIT("Entry No.");
END;

UpdateLogEntry(LogEntryNo : Integer;WasSuccess : Boolean)
WITH JobQueueLogEntry DO BEGIN
  GET(LogEntryNo);
  "End Date/Time" := CURRENTDATETIME;
  IF WasSuccess THEN
    Status := Status::Success
  ELSE BEGIN
    Status := Status::Error;
    SetErrorMessage(COPYSTR(GETLASTERRORTEXT,1,1000));
  END;
  MODIFY;
  COMMIT;
END;

This codeunit uses the Job Queue Log to log the execution.  Next step is to publish this codeunit as a web service in table no. 2000000076.  Default web service name is NAVAppServer.

On the server you install the following files (in my case to C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server)


Next step is to edit the VB.NET NAV Application Server.exe.config file and customize the values.

 <applicationSettings>
        <NAV_Application_Server.My.MySettings>
            <setting name="NAVAppServer" serializeAs="String">
                <value>http://<Middle Tiere Host Name>:7047/DynamicsNAV/WS/<CompanyName>/Codeunit/NAVAppServer</value>
            </setting>
            <setting name="CodeunitID" serializeAs="String">
                <value>81004</value>
            </setting>
            <setting name="LogMode" serializeAs="String">
                <value>False</value>
            </setting>
            <setting name="TimerInterval" serializeAs="String">
                <value>90000</value>
            </setting>
            <setting name="LogFrequency" serializeAs="String">
                <value>15:00:00</value>
            </setting>
            <setting name="SMTPHost" serializeAs="String">
                <value>mail.dynamics.is</value>
            </setting>
            <setting name="FromAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="ToAddress" serializeAs="String">
                <value>gunnar@dynamics.is</value>
            </setting>
            <setting name="RetryTimerInterval" serializeAs="String">
                <value>45000</value>
            </setting>
        </NAV_Application_Server.My.MySettings>
    </applicationSettings>

To install as a service start command prompt in elevated mode and execute installutil.exe command.  The install will prompt for the user to start the service.

C:\Windows\Microsoft.NET\Framework\v2.0.50727>InstallUtil.exe "C:\Program Files(x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe"

Microsoft (R) .NET Framework Installation utility Version 2.0.50727.5420
Copyright (c) Microsoft Corporation.  All rights reserved.

Running a transacted installation.

Beginning the Install phase of the installation.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Installing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog
Installing service VB.NET-NAVAppServer$DEFAULT...
Service VB.NET-NAVAppServer$DEFAULT has been successfully installed.
Creating EventLog source VB.NET-NAVAppServer$DEFAULT in log Application...

The Install phase completed successfully, and the Commit phase is beginning.
See the contents of the log file for the C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe assembly's progress.

The file is located at C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog.
Committing assembly 'C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe'.
Affected parameters are:
   logtoconsole =
   assemblypath = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.exe
   logfile = C:\Program Files (x86)\Dynamics.is\VB.NET NAV Application Server\VB.NET NAV Application Server.InstallLog

The Commit phase completed successfully.

The transacted install has completed.

The last step is to change the service startup and start the service.

The service will create entries in the Application Log.

NAV Web Service Codeunit

The following ZIP files are encrypted.  View the Product page for more information.

VB.NET NAV Application Server DEFAULT Executables

NAV App Server Visual Studio 2008 Project