Tag: Role Tailored Client

Posted on

Record Links in NAV 2009 R2

I made a function that collects all the record links from the documents related to a customer ledger entry or a vendor ledger entry.  I then display the list or record links and offer the user the change to open the links or email them.

This all worked just fine in my Classic Client but in the Role Tailored Client the page gives me a metadata error.  I asked Microsoft and got an answer from Lars Lohndorf-Larsen.

I have been in contact with development and they say that this is unfortunately a restriction in NAV2009. It is not possible to use “Record Link” as Source Table on a page.
“Record Link” is a special table which gets (re)built dynamically. A page expects a normal table and will implicitly compile the underlying table. But this particular table does not have usual metadata that the page expects, so it fails.
In NAV2013 it works because this table was redesigned.

This means that I will have to use the Record Link table as a global variable instead of using it as a source table.  That is what I will do.

Posted on

Use WSDL and dotnet interop in the Role Tailored Client

I have been working with the dotnet interop and consuming soap web services.  I wrote about a dotnet interop based way to communicate with soap web services in a previous blog but now I want to demonstrate another way that requires just a few lines of code.

I would like to find a web service that is open so everyone can do their own testing.  I found a web service that will look up a country by an IP address.

The first step is to start Microsoft Visual Studio and create a new project.  The language that you choose is not important.

I delete Class1.cs.  Then go to Project menu and Add Service Reference.  Click Advanced… and Add Web Reference…  Look in my previous blog for more details.

I paste the web service URL in to Visual Studio and select a name for the web reference.

I go to Project and GeoIPService Properties to make sure that I am using .NET Framework 3.5 and I choose to create my own key to sign the class.  I also like to add details to the Assembly Information…

I go to Signing and create a new key file with my own password.

Then I save the project and select Build and Build GeoIPService.  In the solution folder I will now find the GeoIPService.dll file.  This file I copy to my Classic Client Add-ins folder.  I start the Classic Client and create a new Codeunit.  I will then add a dotnet global variable with my new class.  I create a variable both for GeoIPService and for GeoIP.

I the need to choose if I would like the service to be run on the client or on the server.  If I choose to run on the client then I need to copy the class file to Role Tailored Client Add-ins folder for every user.  If I choose to run on server I copy the class file to the service Add-ins folder.  I selected to run on server since I do not have to supply my domain user name or any certificates to the service.

The code in the Codeunit is as follows

[code]
GeoIPService := GeoIPService.GeoIPService;
GeoIPService.Url := ‘http://www.webservicex.net/geoipservice.asmx’;
GeoIP := GeoIPService.GetGeoIP(‘93.95.74.198’);
MESSAGE(
‘Return Code: %1\IP Address %2\Country Name: %3\Country Code: %4’,
GeoIP.ReturnCode,
GeoIP.IP,
GeoIP.CountryName,
GeoIP.CountryCode);[/code]

I then start the codeunit in my Role Tailored Client with the Run Object Page that Microsoft supplied in their blog.

Attached is the codeunit, the class library and the Run Object Page from Microsoft.

GeoIPService

Posted on

NAV Timer DotNet Addin

Freddy supplied the code to build a timer to be used in pages in the Role Tailored Client.  His blog entry about the timer is here.

Some of the comments show that developers have had the same problem that I ran into.  I change the code just a little bit.  Freddy’s code is

[code lang=”csharp”]public override string Value
{
get
{
return base.Value;
}
set
{
base.Value = value;
if (!int.TryParse(value, out interval))
{
interval = 0;
}
interval = interval * 100;
if (timer != null && timer.Interval != interval)
{
timer.Interval = interval;
count = 0;
if (interval == 0)
timer.Stop();
else
timer.Start();
}
}
}
[/code]

I moved the line “timer.Interval = interval;” down four lines.

[code lang=”csharp”]public override string Value
{
get
{
return base.Value;
}
set
{
base.Value = value;
if (!int.TryParse(value, out interval))
{
interval = 0;
}
interval = interval * 100;
if (timer != null && timer.Interval != interval)
{
count = 0;
if (interval == 0)
timer.Stop();
else
timer.Interval = interval;
timer.Start();
}
}
}
[/code]

and the problem was solved.

NAV Timer Add-in

Posted on

A DotNet Interop Soap Web Request

I am currently working on a solution that requires a Dynamics NAV client to communicate with Dynamics NAV web service.  This I have done before with the classic client and have used automation objects for the job.  Now I wanted to do this with dotnet only objects in the Role Tailored Client.  Took some time to put all things together but here it is.  This version is running the request from the client.

OBJECT Codeunit 50027 IC Addon Inbox WebService
{
  OBJECT-PROPERTIES
  {
    Date=09.04.14;
    Time=17:28:02;
    Modified=Yes;
    Version List=IC7.10;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {

    PROCEDURE LoadTransaction@1100408001(FromPartnerCode@1100408004 : Code[20];FromRespCenterCode@1100408005 : Code[10];ToPartnerCode@1100408006 : Code[20];ToRespCenterCode@1100408007 : Code[10];Transaction@1100408000 : BigText;PDFInvoice@1100408001 : BigText;PDFDetails@1100408002 : BigText;XMLInvoice@1100408003 : BigText;VAR ResponseMessage@1100408009 : Text) Success : Boolean;
    VAR
      Loader@1000000000 : Codeunit 50019;
      TransactionStream@1000000014 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      PDFInvoiceStream@1000000016 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      PDFDetailsStream@1000000015 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      XMLInvoiceStream@1000000013 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
    BEGIN
      TransactionStream := TransactionStream.MemoryStream;
      PDFInvoiceStream := PDFInvoiceStream.MemoryStream;
      PDFDetailsStream := PDFDetailsStream.MemoryStream;
      XMLInvoiceStream := XMLInvoiceStream.MemoryStream;
      IF Transaction.LENGTH > 0 THEN
        Transaction.WRITE(TransactionStream);
      IF PDFInvoice.LENGTH > 0 THEN
        PDFInvoice.WRITE(PDFInvoiceStream);
      IF PDFDetails.LENGTH > 0 THEN
        PDFDetails.WRITE(PDFDetailsStream);
      IF XMLInvoice.LENGTH > 0 THEN
        XMLInvoice.WRITE(XMLInvoiceStream);

      Loader.SetProperties(
        FromPartnerCode,
        FromRespCenterCode,
        ToPartnerCode,
        ToRespCenterCode,
        TransactionStream,
        PDFInvoiceStream,
        PDFDetailsStream,
        XMLInvoiceStream,
        Transaction.LENGTH > 0,
        PDFInvoice.LENGTH > 0,
        PDFDetails.LENGTH > 0,
        XMLInvoice.LENGTH > 0);

      IF Loader.RUN THEN
        EXIT(TRUE)
      ELSE BEGIN
        ResponseMessage := GETLASTERRORTEXT;
        EXIT(FALSE);
      END;
    END;

    BEGIN
    END.
  }
}

OBJECT Codeunit 50028 IC Addon Web Service Client
{
  OBJECT-PROPERTIES
  {
    Date=08.03.15;
    Time=16:01:05;
    Modified=Yes;
    Version List=IC7.10.0432;
  }
  PROPERTIES
  {
    OnRun=BEGIN
          END;

  }
  CODE
  {
    VAR
      Text001@1000000019 : TextConst 'ENU=Succesfully delivered;ISL=Sending hepnaÐist';
      Text003@1100408001 : TextConst 'ENU=Error: %1\%2;ISL=St”Ðuvilla: %1\%2';
      Credential@1000000015 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.NetworkCredential";
      HttpWebRequest@1000000014 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.HttpWebRequest";
      HttpWebResponse@1000000013 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebResponse";
      HttpWebException@1000000017 : DotNet "'System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Net.WebException";
      MemoryStream@1000000012 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      XMLRequestDoc@1000000011 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      XMLResponseDoc@1000000010 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
      XMLProsInstr@1000000009 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlProcessingInstruction";
      XMLElement1@1000000008 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
      XMLElement2@1000000007 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
      XMLElement3@1000000006 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlElement";
      XMLNode4@1000000005 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
      XMLNsMgr@1000000004 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNamespaceManager";
      Bytes@1000000003 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      String@1000000002 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.String";
      Convert@1000000001 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
      ServerFile@1000000000 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File";
      NAVWebRequest@1000000018 : DotNet "'NAVWebRequest, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f53f0925d26e1382'.NAVWebRequest.NAVWebRequest";
      RespCenter@1100408000 : Record 5714;
      CompanyInfo@1100408002 : Record 79;
      Log@1000000016 : Record 50009;
      FileMgt@1100408003 : Codeunit 419;
      WebServiceName@1100408008 : Text[1024];
      InStr@1100408005 : InStream;
      Text006@1100408007 : TextConst 'ENU=Export;ISL=Flytja £t';
      Text009@1100408006 : TextConst 'ENU=All Files (*.*)|*.*;ISL=Allar skr r (*.*)|*.*';

    PROCEDURE SendToPartner@1100408000(ICOutboxTrans@1100408000 : Record 414;ICPartner@1100408001 : Record 413;FileName@1100408002 : Text[250]);
    BEGIN
      ICPartner.TESTFIELD("Inbox Details");
      WebServiceName := FindWebServiceName(ICPartner."Inbox Details");

      WITH ICOutboxTrans DO BEGIN
        CALCFIELDS("PDF Document","XML Document","Details Document");

        IF "Responsibility Center" <> '' THEN BEGIN
          RespCenter.GET("Responsibility Center");
          RespCenter.TESTFIELD("IC Partner Code");
          CompanyInfo."IC Partner Code" := RespCenter."IC Partner Code";
        END ELSE BEGIN
          CompanyInfo.GET;
          CompanyInfo.TESTFIELD("IC Partner Code");
        END;

      END;

      SendTransactionToPartnerDotNet(ICOutboxTrans,ICPartner,FileName)
    END;

    LOCAL PROCEDURE FindWebServiceName@1100408002(URL@1100408000 : Text[1024]) WebServiceName : Text[1024];
    VAR
      i@1100408001 : Integer;
    BEGIN
      FOR i := 1 TO STRLEN(URL) DO
        IF COPYSTR(URL,i,1) = '/' THEN
          WebServiceName := COPYSTR(URL,i + 1);
    END;

    LOCAL PROCEDURE SendTransactionToPartnerDotNet@1100408003(ICOutboxTrans@1100408000 : Record 414;ICPartner@1100408001 : Record 413;FileName@1100408002 : Text[250]);
    VAR
      TempFile@1000000001 : File;
      TempFileName@1000000000 : Text[250];
      WebServiceUserID@1000000003 : Text[1024];
      OutStr@1000000002 : OutStream;
    BEGIN
      WITH ICOutboxTrans DO BEGIN

        Log.GET("Transaction No.");
        Log."Delivered Date and Time" := CURRENTDATETIME;
        Log."Delivered by User ID" := USERID;

        XMLRequestDoc := XMLResponseDoc.XmlDocument;
        XMLProsInstr := XMLRequestDoc.CreateProcessingInstruction('xml','version="1.0" encoding="utf-8"');
        XMLRequestDoc.AppendChild(XMLProsInstr);

        XMLElement1 := XMLRequestDoc.CreateElement('soap','Envelope','http://schemas.xmlsoap.org/soap/envelope/');
        XMLElement1.SetAttribute('xmlns:xsi','http://www.w3.org/2001/XMLSchema-instance');
        XMLElement1.SetAttribute('xmlns:xsd','http://www.w3.org/2001/XMLSchema');

        XMLElement2 := XMLRequestDoc.CreateElement('soap','Body', 'http://schemas.xmlsoap.org/soap/envelope/');
        XMLElement3 := XMLRequestDoc.CreateElement('LoadTransaction');
        XMLElement3.SetAttribute('xmlns',STRSUBSTNO('urn:microsoft-dynamics-schemas/codeunit/%1',WebServiceName));

        XMLNode4 := XMLRequestDoc.CreateElement('fromPartnerCode');
        XMLNode4.InnerText := CompanyInfo."IC Partner Code";
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('fromRespCenterCode');
        IF ICPartner."Send Resp. Center Code" THEN
          XMLNode4.InnerText := "Responsibility Center";
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('toPartnerCode');
        XMLNode4.InnerText := "IC Partner Code";
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('toRespCenterCode');
        XMLNode4.InnerText := "IC Partner Resp. Center";

        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('transaction');
        XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(FileName));
        XMLElement3.AppendChild(XMLNode4);
        TempFile.OPEN(FileName);
        TempFile.CREATEINSTREAM(InStr);
        Log.Transaction.CREATEOUTSTREAM(OutStr);
        COPYSTREAM(OutStr,InStr);
        TempFile.CLOSE;
        ServerFile.Delete(FileName);

        XMLNode4 := XMLRequestDoc.CreateElement('pDFInvoice');
        IF "PDF Document".HASVALUE THEN BEGIN
          "PDF Document".CREATEINSTREAM(InStr);
          TempFileName := FileMgt.ServerTempFileName('pdf');
          TempFile.CREATE(TempFileName);
          TempFile.CREATEOUTSTREAM(OutStr);
          COPYSTREAM(OutStr,InStr);
          TempFile.CLOSE;
          XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(TempFileName));
          ServerFile.Delete(TempFileName);
        END;
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('pDFDetails');
        IF "Details Document".HASVALUE THEN BEGIN
          "Details Document".CREATEINSTREAM(InStr);
          TempFileName := FileMgt.ServerTempFileName('pdf');
          TempFile.CREATE(TempFileName);
          TempFile.CREATEOUTSTREAM(OutStr);
          COPYSTREAM(OutStr,InStr);
          TempFile.CLOSE;
          XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(TempFileName));
          ServerFile.Delete(TempFileName);
        END;
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('xMLInvoice');
        IF "XML Document".HASVALUE THEN BEGIN
          "XML Document".CREATEINSTREAM(InStr);
          TempFileName := FileMgt.ServerTempFileName('xml');
          TempFile.CREATE(TempFileName);
          TempFile.CREATEOUTSTREAM(OutStr);
          COPYSTREAM(OutStr,InStr);
          TempFile.CLOSE;
          XMLNode4.InnerText := Convert.ToBase64String(ServerFile.ReadAllBytes(TempFileName));
          ServerFile.Delete(TempFileName);
        END;
        XMLElement3.AppendChild(XMLNode4);

        XMLNode4 := XMLRequestDoc.CreateElement('responseMessage');
        XMLElement3.AppendChild(XMLNode4);
        XMLElement2.AppendChild(XMLElement3);
        XMLElement1.AppendChild(XMLElement2);
        XMLRequestDoc.AppendChild(XMLElement1);

        HttpWebRequest := HttpWebRequest.Create(ICPartner."Inbox Details");
        HttpWebRequest.Timeout := 30000;
        WebServiceUserID := ICPartner.GetUserID;
        IF WebServiceUserID = '' THEN
          HttpWebRequest.UseDefaultCredentials(TRUE)
        ELSE BEGIN
          HttpWebRequest.UseDefaultCredentials(FALSE);
          Credential := Credential.NetworkCredential;
          Credential.UserName := WebServiceUserID;
          Credential.Password := ICPartner.GetPassword;
          Credential.Domain := ICPartner.GetDomain;
          HttpWebRequest.Credentials := Credential;
        END;
        HttpWebRequest.Method := 'POST';
        HttpWebRequest.ContentType := 'text/xml; charset=utf-8';
        HttpWebRequest.Accept := 'text/xml';
        HttpWebRequest.Headers.Add('SOAPAction','LoadTransaction');
        MemoryStream := HttpWebRequest.GetRequestStream;
        XMLRequestDoc.Save(MemoryStream);
        MemoryStream.Flush;
        MemoryStream.Close;

        NAVWebRequest := NAVWebRequest.NAVWebRequest;
        IF NOT NAVWebRequest.doRequest(HttpWebRequest,HttpWebException,HttpWebResponse) THEN BEGIN
          Log.Delivered := FALSE;
          Log.SetMessage(HttpWebException.Message);
          Log.MODIFY;
          COMMIT;
          ERROR(Text003,HttpWebException.Status.ToString,HttpWebException.Message);
        END;

        MemoryStream := HttpWebResponse.GetResponseStream;
        XMLResponseDoc := XMLResponseDoc.XmlDocument;
        XMLResponseDoc.Load(MemoryStream);
        MemoryStream.Flush;
        MemoryStream.Close;

        XMLNsMgr := XMLNsMgr.XmlNamespaceManager(XMLResponseDoc.NameTable);
        XMLNsMgr.AddNamespace('urn',STRSUBSTNO('urn:microsoft-dynamics-schemas/codeunit/%1',WebServiceName));
        XMLNode4 := XMLResponseDoc.SelectSingleNode('//urn:return_value',XMLNsMgr);

        IF UPPERCASE(XMLNode4.InnerText) = 'FALSE' THEN BEGIN
          XMLNode4 :=  XMLResponseDoc.SelectSingleNode('//urn:responseMessage',XMLNsMgr);
          Log.Delivered := FALSE;
          Log.SetMessage(XMLNode4.InnerText);
          Log.MODIFY;
          COMMIT;
          ERROR(XMLNode4.InnerText);
        END;

        Log.Delivered := TRUE;
        Log.SetMessage(Text001);
        Log.MODIFY;
        COMMIT;

      END;
    END;

    EVENT XMLResponseDoc@1000000010::NodeInserting@93(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeInserted@94(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeRemoving@95(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeRemoved@96(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeChanging@97(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLResponseDoc@1000000010::NodeChanged@98(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeInserting@93(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeInserted@94(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeRemoving@95(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeRemoved@96(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeChanging@97(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    EVENT XMLRequestDoc@1000000011::NodeChanged@98(sender@1000000001 : Variant;e@1000000000 : DotNet "'System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeChangedEventArgs");
    BEGIN
    END;

    BEGIN
    END.
  }
}


OBJECT Codeunit 50019 IC Addon Load Transaction
{
  OBJECT-PROPERTIES
  {
    Date=02.05.14;
    Time=10:12:10;
    Modified=Yes;
    Version List=IC7.10;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            LoadTransaction;
          END;

  }
  CODE
  {
    VAR
      Text001@1100408000 : TextConst 'ENU=IC Partner Code %1 not found;ISL=Mf. f‚lagak¢ti %1 finnst ekki';
      Text002@1100408001 : TextConst 'ENU=Responsibility Center Code mismatch, %1 <> %2;ISL=µbyrgÐast”Ðvark¢ti stemmir ekki, %1 <> %2';
      Convert@1100408004 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
      DocumentFile@1100408003 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File";
      Bytes@1100408010 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Array";
      MemoryStream@1100408008 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
      FileMgt@1100408009 : Codeunit 419;
      TempFile@1100408007 : File;
      InStr@1000000011 : InStream;
      OutStr@1000000010 : OutStream;
      TempFileName@1100408006 : Text[1024];
      Text003@1100408011 : TextConst 'ENU=No data received;ISL=Engin g”gn m¢ttekin';
      Text004@1100408012 : TextConst 'ENU=Transaction no. %1 is already imported;ISL=F‘rsla nr. %1 er çegar innflutt';
      Text005@1000000000 : TextConst 'ENU=From Partner Code Error in Transaction, %1 <> %2;ISL=Fr  mf. f‚lagak¢ta villa ¡ f‘rslu, %1 <> %2';
      Text006@1000000001 : TextConst 'ENU=To Partner Code Error in Transaction, %1 <> %2;ISL=Til mf. f‚lagak¢ta villa ¡ f‘rslu, %1 <> %2';
      FromPartnerCode@1000000009 : Code[20];
      FromRespCenterCode@1000000008 : Code[10];
      ToPartnerCode@1000000007 : Code[20];
      ToRespCenterCode@1000000006 : Code[10];
      Transaction@1000000005 : BigText;
      PDFInvoice@1000000004 : BigText;
      PDFDetails@1000000003 : BigText;
      XMLInvoice@1000000002 : BigText;

    LOCAL PROCEDURE LoadTransaction@1100408001();
    VAR
      ICPartner@1100408010 : Record 413;
      TempBlob@1100408024 : TEMPORARY Record 99008535;
      TempICOutboxTrans@1100408020 : TEMPORARY Record 414;
      TempICOutBoxJnlLine@1100408019 : TEMPORARY Record 415;
      TempICIOBoxJnlDim@1100408018 : TEMPORARY Record 423;
      TempICOutBoxSalesHdr@1100408017 : TEMPORARY Record 426;
      TempICOutBoxSalesLine@1100408016 : TEMPORARY Record 427;
      TempICOutBoxPurchHdr@1100408015 : TEMPORARY Record 428;
      TempICOutBoxPurchLine@1100408014 : TEMPORARY Record 429;
      TempICDocDim@1100408013 : TEMPORARY Record 442;
      ICInboxTransaction@1100408022 : Record 418;
      ICInboxTransaction2@1100408032 : Record 418;
      ICInboxJnlLine@1100408030 : Record 419;
      ICInboxSalesHdr@1100408029 : Record 434;
      ICInboxSalesLine@1100408028 : Record 435;
      ICInboxPurchHdr@1100408027 : Record 436;
      ICInboxPurchLine@1100408026 : Record 437;
      ICInboxJnlLineDim@1100408025 : Record 423;
      ICInboxDocDim@1100408023 : Record 442;
      HandledICInboxTransaction@1000000000 : Record 420;
      ICInboxOutboxMgt@1100408021 : Codeunit 427;
      FromICPartnerCode@1100408012 : Code[20];
      ToICPartnerCode@1100408011 : Code[20];
      ICOutboxExportXML@1100408008 : XMLport 12;
      NewTableID@1100408031 : Integer;
    BEGIN
      IF NOT ICPartner.GET(FromPartnerCode) THEN
        ERROR(Text001,FromPartnerCode);

      IF ICPartner."Responsibility Center" <> FromRespCenterCode THEN
        ERROR(Text002,ICPartner."Responsibility Center",FromRespCenterCode);

      IF NOT ICPartner.GET(ToPartnerCode) THEN
        ERROR(Text001,ToPartnerCode);

      IF ICPartner."Responsibility Center" <> ToRespCenterCode THEN
        ERROR(Text002,ICPartner."Responsibility Center",ToRespCenterCode);

      IF Transaction.LENGTH > 0 THEN BEGIN
        Bytes := Convert.FromBase64String(Transaction);
        MemoryStream := MemoryStream.MemoryStream(Bytes);
        TempBlob.Blob.CREATEOUTSTREAM(OutStr);
        MemoryStream.WriteTo(OutStr);
        TempBlob.Blob.CREATEINSTREAM(InStr);

        ICOutboxExportXML.SETSOURCE(InStr);
        ICOutboxExportXML.IMPORT;
        ICOutboxExportXML.GetICOutboxTrans(TempICOutboxTrans);
        ICOutboxExportXML.GetICOutBoxJnlLine(TempICOutBoxJnlLine);
        ICOutboxExportXML.GetICIOBoxJnlDim(TempICIOBoxJnlDim);
        ICOutboxExportXML.GetICOutBoxSalesHdr(TempICOutBoxSalesHdr);
        ICOutboxExportXML.GetICOutBoxSalesLine(TempICOutBoxSalesLine);
        ICOutboxExportXML.GetICOutBoxPurchHdr(TempICOutBoxPurchHdr);
        ICOutboxExportXML.GetICOutBoxPurchLine(TempICOutBoxPurchLine);
        ICOutboxExportXML.GetICSalesDocDim(TempICDocDim);
        ICOutboxExportXML.GetICSalesDocLineDim(TempICDocDim);
        ICOutboxExportXML.GetICPurchDocDim(TempICDocDim);
        ICOutboxExportXML.GetICPurchDocLineDim(TempICDocDim);
        FromICPartnerCode := ICOutboxExportXML.GetFromICPartnerCode;
        ToICPartnerCode := ICOutboxExportXML.GetToICPartnerCode;

        TempICOutBoxSalesHdr.MODIFYALL("Responsibility Center",FromRespCenterCode);
        TempICOutBoxSalesHdr.MODIFYALL("IC Partner Resp. Center",ToRespCenterCode);
        TempICOutBoxPurchHdr.MODIFYALL("Responsibility Center",FromRespCenterCode);
        TempICOutBoxPurchHdr.MODIFYALL("IC Partner Resp. Center",ToRespCenterCode);

        IF FromICPartnerCode <> FromPartnerCode THEN
          ERROR(Text005,FromICPartnerCode,FromPartnerCode);

        IF ToICPartnerCode <> ToPartnerCode THEN
          ERROR(Text006,ToICPartnerCode,ToPartnerCode);

        ICInboxTransaction2.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
        ICInboxTransaction2.SETRANGE("IC Partner Code",FromICPartnerCode);
        ICInboxTransaction2.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
        IF ICInboxTransaction2.FINDFIRST THEN
          ERROR(Text004,TempICOutboxTrans."Transaction No.");

        HandledICInboxTransaction.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
        HandledICInboxTransaction.SETRANGE("IC Partner Code",FromICPartnerCode);
        HandledICInboxTransaction.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
        IF HandledICInboxTransaction.FINDFIRST THEN
          ERROR(Text004,TempICOutboxTrans."Transaction No.");

        IF TempICOutboxTrans.FIND('-') THEN BEGIN
          ICInboxOutboxMgt.OutboxTransToInbox(TempICOutboxTrans,ICInboxTransaction,FromICPartnerCode);

          TempICOutBoxJnlLine.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxJnlLine.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxJnlLine.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxJnlLine.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxJnlLineToInbox(ICInboxTransaction,TempICOutBoxJnlLine,ICInboxJnlLine);
              TempICIOBoxJnlDim.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
              TempICIOBoxJnlDim.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
              TempICIOBoxJnlDim.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
              TempICIOBoxJnlDim.SETRANGE("Line No.",ICInboxJnlLine."Line No.");
              IF TempICIOBoxJnlDim.FIND('-') THEN
                REPEAT
                  ICInboxOutboxMgt.OutboxJnlLineDimToInbox(
                    ICInboxJnlLine,TempICIOBoxJnlDim,ICInboxJnlLineDim,DATABASE::"IC Inbox Jnl. Line");
                UNTIL TempICIOBoxJnlDim.NEXT = 0;
            UNTIL TempICOutBoxJnlLine.NEXT = 0;

          TempICOutBoxSalesHdr.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxSalesHdr.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxSalesHdr.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxSalesHdr.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxSalesHdrToInbox(ICInboxTransaction,TempICOutBoxSalesHdr,ICInboxPurchHdr);
            UNTIL TempICOutBoxSalesHdr.NEXT = 0;

          TempICOutBoxSalesLine.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxSalesLine.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxSalesLine.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxSalesLine.FIND('-') THEN BEGIN
            REPEAT
              ICInboxOutboxMgt.OutboxSalesLineToInbox(ICInboxTransaction,TempICOutBoxSalesLine,ICInboxPurchLine);
            UNTIL TempICOutBoxSalesLine.NEXT = 0;
            ICInboxPurchLine.SETRANGE("IC Transaction No.",ICInboxPurchHdr."IC Transaction No.");
            ICInboxPurchLine.SETRANGE("IC Partner Code",ICInboxPurchHdr."IC Partner Code");
            ICInboxPurchLine.SETRANGE("Transaction Source",ICInboxPurchHdr."Transaction Source");
            ICInboxPurchLine.SETRANGE("VAT Base Amount",-0.5,0.5);
            ICInboxPurchLine.CALCSUMS("Amount Including VAT");
            ICInboxPurchHdr."Payable Rounding Amount" := ICInboxPurchLine."Amount Including VAT";
            ICInboxPurchLine.SETRANGE("VAT Base Amount");
            ICInboxPurchLine.CALCSUMS("Amount Including VAT","VAT Base Amount");
            ICInboxPurchHdr."Payable Amount" := ICInboxPurchLine."Amount Including VAT";
            ICInboxPurchHdr."Line Extension Amount" := ICInboxPurchLine."VAT Base Amount" - ICInboxPurchHdr."Payable Rounding Amount";
            ICInboxPurchHdr."Tax Exclusive Amount" := ICInboxPurchHdr."Line Extension Amount";
            ICInboxPurchHdr."Tax Inclusive Amount" := ICInboxPurchLine."Amount Including VAT" - ICInboxPurchHdr."Payable Rounding Amount";
            ICInboxPurchHdr."Tax Amount" :=  ICInboxPurchHdr."Tax Inclusive Amount" - ICInboxPurchHdr."Tax Exclusive Amount";
            ICInboxPurchHdr.MODIFY;
          END;

          TempICOutBoxPurchHdr.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxPurchHdr.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxPurchHdr.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxPurchHdr.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxPurchHdrToInbox(ICInboxTransaction,TempICOutBoxPurchHdr,ICInboxSalesHdr);
            UNTIL TempICOutBoxPurchHdr.NEXT = 0;

          TempICOutBoxPurchLine.SETRANGE("IC Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICOutBoxPurchLine.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICOutBoxPurchLine.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICOutBoxPurchLine.FIND('-') THEN
            REPEAT
              ICInboxOutboxMgt.OutboxPurchLineToInbox(ICInboxTransaction,TempICOutBoxPurchLine,ICInboxSalesLine);
            UNTIL TempICOutBoxPurchLine.NEXT = 0;

          TempICDocDim.SETRANGE("Transaction No.",TempICOutboxTrans."Transaction No.");
          TempICDocDim.SETRANGE("IC Partner Code",TempICOutboxTrans."IC Partner Code");
          TempICDocDim.SETRANGE("Transaction Source",TempICOutboxTrans."Transaction Source");
          IF TempICDocDim.FIND('-') THEN
            REPEAT
              CASE TempICDocDim."Table ID" OF
                DATABASE::"IC Outbox Sales Header": NewTableID := DATABASE::"IC Inbox Purchase Header";
                DATABASE::"IC Outbox Sales Line": NewTableID := DATABASE::"IC Inbox Purchase Line";
                DATABASE::"IC Outbox Purchase Header": NewTableID := DATABASE::"IC Inbox Sales Header";
                DATABASE::"IC Outbox Purchase Line": NewTableID := DATABASE::"IC Inbox Sales Line";
              END;
              ICInboxOutboxMgt.OutboxDocDimToInbox(
                TempICDocDim,ICInboxDocDim,NewTableID,FromICPartnerCode,ICInboxTransaction."Transaction Source");
            UNTIL TempICDocDim.NEXT = 0;
        END;

        ICInboxTransaction."Responsibility Center" := ToRespCenterCode;
        ICInboxTransaction."IC Partner Resp. Center" := FromRespCenterCode;

        IF XMLInvoice.LENGTH > 0 THEN BEGIN
          Bytes := Convert.FromBase64String(XMLInvoice);
          MemoryStream := MemoryStream.MemoryStream(Bytes);
          ICInboxTransaction."XML Document".CREATEOUTSTREAM(OutStr);
          MemoryStream.WriteTo(OutStr);
        END;

        IF PDFInvoice.LENGTH > 0 THEN BEGIN
          Bytes := Convert.FromBase64String(PDFInvoice);
          MemoryStream := MemoryStream.MemoryStream(Bytes);
          ICInboxTransaction."PDF Document".CREATEOUTSTREAM(OutStr);
          MemoryStream.WriteTo(OutStr);
        END;

        IF PDFDetails.LENGTH > 0 THEN BEGIN
          Bytes := Convert.FromBase64String(PDFDetails);
          MemoryStream := MemoryStream.MemoryStream(Bytes);
          ICInboxTransaction."Details Document".CREATEOUTSTREAM(OutStr);
          MemoryStream.WriteTo(OutStr);
        END;

        ICInboxTransaction.MODIFY;
      END ELSE
        ERROR(Text003);
    END;

    PROCEDURE SetProperties@1000000000(VAR SetFromPartnerCode@1000000007 : Code[20];VAR SetFromRespCenterCode@1000000006 : Code[10];VAR SetToPartnerCode@1000000005 : Code[20];VAR SetToRespCenterCode@1000000004 : Code[10];VAR SetTransaction@1000000003 : InStream;VAR SetPDFInvoice@1000000002 : InStream;VAR SetPDFDetails@1000000001 : InStream;VAR SetXMLInvoice@1000000000 : InStream;TransactionHasValue@1000000011 : Boolean;PDFInvoiceHasValue@1000000010 : Boolean;PDFDetailsHasValue@1000000009 : Boolean;XMLInvoiceHasValue@1000000008 : Boolean);
    BEGIN
      FromPartnerCode := SetFromPartnerCode;
      FromRespCenterCode := SetFromRespCenterCode;
      ToPartnerCode := SetToPartnerCode;
      ToRespCenterCode := SetToRespCenterCode;
      IF TransactionHasValue THEN
        Transaction.READ(SetTransaction);
      IF PDFInvoiceHasValue THEN
        PDFInvoice.READ(SetPDFInvoice);
      IF PDFDetailsHasValue THEN
        PDFDetails.READ(SetPDFDetails);
      IF XMLInvoiceHasValue THEN
        XMLInvoice.READ(SetXMLInvoice);
    END;

    BEGIN
    END.
  }
}

 

Posted on

DropDown in RTC

In the table designer the fields that appear in the drop down list are defined.  For example the Customer table.

I found a feedback in Microsoft Connect and added a few comments to that.  What I would like the drop down window to be able to do is;

  • remember the last position and default to that one
  • allow multi select that will result in a filter

I would love this to be applied to both pages and the report “Show results:” area.

 

Posted on

NAV 2013 and ClickOnce Install

I have installed NAV 2013 on a Windows 2008 Server.  Everything is working fine and it is time to create the OneClick installation source.  I followed the documentation from Microsoft.  Make sure that when you install NAV 2013 to select ClickOnce Installer Tools.

I first found that the SDK needed for the mage.exe command is not available on the server.  I downloaded and installed Microsoft Windows SDK for Windows 7 and .NET Framework 4 from Microsoft.

I created the folder C:\inetpub\wwwroot\NAV2013Beta as the source for deployment and the folder C:\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles for the application.  I created a read only share for the folder NAV2013Beta.  I copied everything from the folder C:\Program Files (x86)\Microsoft Dynamics NAV\70\RoleTailored Client to this folder.

I found the folder C:\ProgramData\Microsoft\Microsoft Dynamics NAV\70 and copied the ClientUserSettings.config file to my C:\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles folder.  First I made sure that the server was not set to localhost but to the network server name.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <!--
      Name of the machine hosting the Microsoft Dynamics Nav Server to
      be connected to.
    -->
    <add key="Server" value="nav2013.dynamics.is"/>

I found the folder C:\Program Files (x86)\Microsoft Dynamics NAV\70\ClickOnce Installer Tools\TemplateFiles and copied all files to my C:\inetpub\wwwroot\NAV2013Beta folder, selecting to merge the Deployment folder.

Next I opened Command Prompt and typed

set PATH=%PATH%;C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\NETFX 4.0 Tools;C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin
cd "\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles"
mage.exe -Update Microsoft.Dynamics.Nav.Client.exe.manifest -FromDirectory .\

I got the following errors

Warning MSB3112: Two or more assemblies have the same identity 'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35, ProcessorArchitecture=msil'.
Warning MSB3182: File name 'C:\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles\Add-ins\CodeViewer\EditorComponents\Microsoft.VisualStudio.UI.Text.Wpf.KeyProcessor.Implementation.dll' exceeds 100 characters.

Microsoft.Dynamics.Nav.Client.exe.manifest successfully updated. However, some errors were encountered.

Decided to remove the Development Client and only install the Role Tailored Client and restarted the process and got a better result.

Microsoft.Dynamics.Nav.Client.exe.manifest successfully updated

Continuing in the command prompt

cd ..
mage.exe -Update Microsoft.Dynamics.Nav.Client.application -AppManifest \\nav2013\NAV2013Beta\Deployment\ApplicationFiles\Microsoft.Dynamics.Nav.Client.exe.manifest -AppCodeBase \\nav2013\nav2013beta\Deployment\ApplicationFiles\Microsoft.Dynamics.Nav.Client.exe.manifest
mage.exe

I change the Name to identify the installation

I changed the description

and I needed to change the Start Location

Again, I saved the file selecting not to sign it.

In my \\nav2013\NAV2013Beta folder I used Word to create the Software License Terms for my company and saved is as Rich Text Format as PartnerSolutionLicense.rtf.

Then I opened the NAVClientInstallation.html file in Notepad and removed the TODO both for the text and for the link.

Next I tried the installation by browsing to the folder \\nav2013\NAV2013Beta on a Windows 2008 Remote Desktop Server and double clicking on NAVClientInstallation.html file.  After a few click I had my NAV 2013 Beta up and running.

Posted on

Client Temporary Path

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.
[code htmlscript=”false”]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.
}
}[/code]
The Source is here, Application Temporary Path

 

Posted on

Data Visualization Control not found

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.

Posted on

Using OpenXML to create an Excel Document

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

 

 

Posted on

WinHTTP and RTC Client

I have been using the automation ‘Microsoft XML, v6.0’.XMLHTTP to communicate with web services and web sites.  I have been experiencing a problem with this automation when running in Role Tailored Client.  The solution has been to use the automation ‘Microsoft XML, v6.0’.ServerXMLHTTP when running in the service tier.
[code htmlscript=”false”]IF ISSERVICETIER THEN BEGIN
IF ISCLEAR(WinHTTPServer) THEN
CREATE(WinHTTPServer,TRUE,FALSE);
WinHTTPServer.open(‘GET’,URL,FALSE);
WinHTTPServer.send(”);

IF WinHTTPServer.status <> 200 THEN
ERROR(Text007,WinHTTPServer.status,WinHTTPServer.statusText);

DOMDocument.load(WinHTTPServer.responseXML);
CLEAR(WinHTTPServer);
END ELSE BEGIN
IF ISCLEAR(WinHTTP) THEN
CREATE(WinHTTP,TRUE,FALSE);
WinHTTP.open(‘GET’,URL,FALSE);
WinHTTP.send(”);

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

DOMDocument.load(WinHTTP.responseXML);
CLEAR(WinHTTP);
END;[/code]
Where Error string Text007 is “Status error %1 %2”.