BigText.ADDTEXT loop is slow in RTC

I had a code where I was combining several XML documents into one and used a BigText variable for the job.  At design time everything looked fine but when I executed the code in the Role Tailored Client everything was slow.  I sent this issue to Microsoft and the product team has responded.

It is true that AddText used in a loop is slower in RTC than in CC. The reason is the underlying .Net string type. As mentioned in the immutability section in: http://msdn.microsoft.com/en-us/library/system.string.aspx#Immutability the creation of many immutable strings can be very time consuming. Since the backing field in NAV is a string we have the same problem – and it would be vastly inefficient in all other scenarios to create a StringBuilder for each BigText variable – and you only save instantiating strings if you do more than one concatenation.

This leaves us the following options:
–          If partner is adding texts – consider using .Net Interop and use the StringBuilder class – and document the changed behavior of BigText
–          Refactor code to avoid hundreds / thousands of small additions (e.g. by building the total big text in chunks of e.g. 1000 – but will have to look at perf of that)
–          Create a new feature for a StringBuilder class in A/L (would possibly have to work for the other text types as well?)

  • I doubt that this would be prioritized in Sicily

I know that this is an unwanted side effect of using .Net strings – but that is also what buys us Unicode etc., so I would prefer that we document this new behavior.

So I rewrote this bit and am now using OutStream instead.  Just wanted you to know.

Code fix for Table 36 – Sales Header

Just sent Microsoft a suggestion on Microsoft Connect.

We where trying to import data into the Sales Header table and stopped on an error. The old code is

[code]
Payment Terms Code – OnValidate()
IF ("Payment Terms Code" <> ”) AND ("Document Date" <> 0D) THEN BEGIN
PaymentTerms.GET("Payment Terms Code");
IF (("Document Type" IN ["Document Type"::"Return Order","Document Type"::"Credit Memo"]) AND
NOT PaymentTerms."Calc. Pmt. Disc. on Cr. Memos")
THEN BEGIN
VALIDATE("Due Date","Document Date");
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END ELSE BEGIN
"Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
"Pmt. Discount Date" := CALCDATE(PaymentTerms."Discount Date Calculation","Document Date");
IF NOT UpdateDocumentDate THEN
VALIDATE("Payment Discount %",PaymentTerms."Discount %")
END;
END ELSE BEGIN
VALIDATE("Due Date","Document Date");
IF NOT UpdateDocumentDate THEN BEGIN
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END;
END;
IF xRec."Payment Terms Code" = "Prepmt. Payment Terms Code" THEN BEGIN
IF xRec."Prepayment Due Date" = 0D THEN
"Prepayment Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
VALIDATE("Prepmt. Payment Terms Code","Payment Terms Code");
END;[/code]

and I suggeste a change to the fourth last line

[code]
Payment Terms Code – OnValidate()
IF ("Payment Terms Code" <> ”) AND ("Document Date" <> 0D) THEN BEGIN
PaymentTerms.GET("Payment Terms Code");
IF (("Document Type" IN ["Document Type"::"Return Order","Document Type"::"Credit Memo"]) AND
NOT PaymentTerms."Calc. Pmt. Disc. on Cr. Memos")
THEN BEGIN
VALIDATE("Due Date","Document Date");
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END ELSE BEGIN
"Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
"Pmt. Discount Date" := CALCDATE(PaymentTerms."Discount Date Calculation","Document Date");
IF NOT UpdateDocumentDate THEN
VALIDATE("Payment Discount %",PaymentTerms."Discount %")
END;
END ELSE BEGIN
VALIDATE("Due Date","Document Date");
IF NOT UpdateDocumentDate THEN BEGIN
VALIDATE("Pmt. Discount Date",0D);
VALIDATE("Payment Discount %",0);
END;
END;
IF (xRec."Payment Terms Code" = "Prepmt. Payment Terms Code") AND (xRec."Payment Terms Code" <> ”) THEN BEGIN
IF xRec."Prepayment Due Date" = 0D THEN
"Prepayment Due Date" := CALCDATE(PaymentTerms."Due Date Calculation","Document Date");
VALIDATE("Prepmt. Payment Terms Code","Payment Terms Code");
END;[/code]

SQL Native Client Version

I wrote a solution to log blocking in NAV with the help of Microsoft SQL Native Client.  I wanted to be able to use this solution for a client but then ran into a problem with the SQL Native Client.  There are three versions out there and the connection string must include the version installed on the client machine.

The first step is to find the “Program Files” folder
[code] LOCAL PROCEDURE GetProgramFilesPath@1200050010() ProgramFilesPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
ProgramFilesPath := SystemEnviroment.Item(‘ProgramW6432’);
IF ProgramFilesPath = ” THEN
ProgramFilesPath := SystemEnviroment.Item(‘ProgramFiles’);
END;[/code]and the System32 folder[code] LOCAL PROCEDURE GetSystemRootPath@1100408000() SystemRootPath : Text[1024];
VAR
EnviormentPath@1200050000 : Code[50];
BEGIN
IF ISCLEAR(SystemShell) THEN
CREATE(SystemShell);
EnviormentPath := ‘PROCESS’;
SystemEnviroment := SystemShell.Environment(EnviormentPath);
SystemRootPath := SystemEnviroment.Item(‘SystemRoot’) + ‘\System32’;
END;[/code]

Then I use the EXISTS function to check for the client version.

[code] LOCAL PROCEDURE OpenConnection@1200050006(ConnectAsUserID@1200050003 : Text[30];ConnactAsPassword@1200050002 : Text[30]);
VAR
MyServer@1200050001 : Record 2000000047;
MyDatabase@1200050000 : Record 2000000048;
ClientVersion@1100408000 : Text[30];
ProgramFilesPath@1100408001 : Text[50];
SystemRootPath@1100408002 : Text[50];
BEGIN
IF ISCLEAR(ADOConnection) THEN
IF NOT CREATE(ADOConnection) THEN
ERROR(Text001);

IF ISCLEAR(ADORecordset) THEN
CREATE(ADORecordset);

IF ISCLEAR(ADOStream) THEN
CREATE(ADOStream);

IF ADOConnection.State = 1 THEN
EXIT;

IF ISSERVICETIER THEN BEGIN

IF ISCLEAR(DomDoc) THEN
CREATE(DomDoc);

DomDoc.load(APPLICATIONPATH + ‘CustomSettings.config’);
DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseServer”]’);
MyServerName := DomNode.attributes.item(1).text;

DomNode := DomDoc.selectSingleNode(‘//appSettings/add[@key=”DatabaseName”]’);
MyDatabaseName := DomNode.attributes.item(1).text;

END ELSE BEGIN
MyServer.SETRANGE("My Server",TRUE);
MyServer.FINDFIRST;
MyServerName := MyServer."Server Name";

MyDatabase.SETRANGE("My Database",TRUE);
MyDatabase.FINDFIRST;
MyDatabaseName := MyDatabase."Database Name";
END;

ProgramFilesPath := GetProgramFilesPath;

CASE TRUE OF
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\90\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\100\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(ProgramFilesPath + ‘\Microsoft SQL Server\110\SDK\Include\sqlncli.h’):
ClientVersion := ‘SQLNCLI11’;
ELSE
BEGIN
SystemRootPath := GetSystemRootPath;
CASE TRUE OF
EXISTS(SystemRootPath + ‘\sqlncli.dll’):
ClientVersion := ‘SQLNCLI’;
EXISTS(SystemRootPath + ‘\sqlncli10.dll’):
ClientVersion := ‘SQLNCLI10’;
EXISTS(SystemRootPath + ‘\sqlncli11.dll’):
ClientVersion := ‘SQLNCLI11’;
ELSE
ERROR(Text002);
END;
END;
END;

IF ConnectAsUserID <> ” THEN
ADOConnection.Open(
STRSUBSTNO(‘Provider=%5;Server=%1;Database=%2;Uid=%3;Pwd=%4;’,
MyServerName,MyDatabaseName,ConnectAsUserID,ConnactAsPassword,ClientVersion))
ELSE
ADOConnection.Open(
STRSUBSTNO(‘Provider=%3;Server=%1;Database=%2;Trusted_Connection=yes;’,
MyServerName,MyDatabaseName,ClientVersion));

ADOConnection.CommandTimeout(0);
END;[/code]

where Text002@1100408002 : TextConst ‘ENU=Microsoft SQL Native Client not found;ISL=Microsoft SQL Native Client finnst ekki.’;

No execute permission on CodeUnit with ID 8700

I am using web services in a company that does not have the license to use the Mobile Document Dispatcher.  In Codeunit 1 and trigger CompanyClose there are functions to stop listeners.

[code]

CompanyClose()
EmployeePortalApplSrv.StopQueue;
MobDocDispatcher.Stop;
BizTalkApplnSrvStartup.StopTcpListen;
IF GUIALLOWED THEN
LogInEnd;
[/code]

This is causing problems in the web service so I changed the code to

[code]
IF NOT ISSERVICETIER THEN BEGIN
EmployeePortalApplSrv.StopQueue;
MobDocDispatcher.Stop;
BizTalkApplnSrvStartup.StopTcpListen;
END;
IF GUIALLOWED THEN
LogInEnd;[/code]

and the error is history.

UPLOAD and DOWNLOAD size limit

Always something new.  I have bin using the Record Links to store pointers to scanned files, created pdf files all other files that the user would like to link to a record and store centrally.  I have the option to import the file to a BLOB field and also to a separate tables in a different database.

What I do next is to install a single aspx page to an internal web server and the URL in the Record Links table points me to this page with parameters that define where to get the centrally stored file.  This aspx page can fetch the file from the database or through web services from NAV BLOB field.

My first version of this was to have everything executed on the client side.  I did not see that working for NAV 2013 where we can have users that authenticate outside the windows domain.  The same is for NAV 2009 R2 where the Role Tailored Client is connecting to the service tier via WAN.

So I moved the functionality to the service tier and wanted to use UPLOAD and DOWNLOAD to transfer the files to and from the client.  Here I hit a wall.  There is a size limit to the files that I can upload and download.

I therefore looked at the code I had created earlier and modified it a little bit.

[code]PROCEDURE UploadClientFile@1100408005(FileName@1100408000 : Text[1024]) ServerFileName : Text[1024];
VAR
Document@1100408011 : BigText;
ServerConvert@1100408010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert";
ServerBase64File@1100408009 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
ServerDocumentFile@1100408008 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
ClientConvert@1100408007 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert" RUNONCLIENT;
ClientFile@1100408006 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ServerFileStream@1100408005 : File;
ServerBase64FileName@1100408004 : Text[1024];
OutStr@1100408002 : OutStream;
BEGIN
Document.ADDTEXT(ClientConvert.ToBase64String(ClientFile.ReadAllBytes(FileName)));
ServerBase64FileName := ThreeTier.ServerTempFileName(”,”);
ServerFileStream.WRITEMODE(TRUE);
ServerFileStream.CREATE(ServerBase64FileName);
ServerFileStream.CREATEOUTSTREAM(OutStr);
Document.WRITE(OutStr);
ServerFileStream.CLOSE;

ServerFileName := ThreeTier.ServerTempFileName(”,”);

ServerDocumentFile.WriteAllBytes(
ServerFileName,
ServerConvert.FromBase64String(
ServerBase64File.ReadAllText(ServerBase64FileName)));

ServerBase64File.Delete(ServerBase64FileName);
END;

PROCEDURE DownloadServerFile@1100408017(FileName@1100408000 : Text[1024]) ClientFileName : Text[1024];
VAR
Document@1100408002 : BigText;
ServerFile@1100408003 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File";
ServerConvert@1100408001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert";
ClientBase64File@1100408006 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ClientStreamWriter@1100408007 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.StreamWriter" RUNONCLIENT;
ClientConvert@1100408010 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Convert" RUNONCLIENT;
ClientFile@1100408009 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
ClientPath@1100408011 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.Path" RUNONCLIENT;
ClientBase64FileName@1100408008 : Text[1024];
SubString@1100408004 : Text[1024];
Pos@1100408005 : Integer;
BEGIN
Document.ADDTEXT(ServerConvert.ToBase64String(ServerFile.ReadAllBytes(FileName)));
ClientBase64FileName := ThreeTier.ClientTempFileName(”,’b64′);
ClientStreamWriter := ClientBase64File.CreateText(ClientBase64FileName);
Pos := 1;
WHILE Pos < Document.LENGTH DO BEGIN
Pos := Pos + Document.GETSUBTEXT(SubString,Pos,MAXSTRLEN(SubString));
ClientStreamWriter.Write(SubString);
END;
ClientStreamWriter.Close;

ClientFileName :=
ClientPath.GetTempPath +
SigningTools.RemovePath(FileName);

ClientFile.WriteAllBytes(
ClientFileName,
ClientConvert.FromBase64String(
ClientBase64File.ReadAllText(ClientBase64FileName)));
END;
[/code]

The problem is that this method is not fast enough. Perhaps someone reading this has a faster solution.  I convert the file to base64 and use BigText variable to move it from and to the service tier.  This will support large files but it is slow.

Copy Record Link between databases

In my current upgrade process I needed to copy the Record Link table from one database to another.  I can use a copy-paste method for small number of records but in this case I needed something else.

A Dataport or a XML Port was the way I choose to go.  There is one problem though.  the Record Link table has a field for the Record ID and this field is not a field type that can be used in dataports or xml ports.

I created two new global variables, one text variable for the record position and one integer variable for the table number.  And to help me with the conversion I needed to have two more global variables; RecID  and RecRef.

RecordLinkVariables

Now for the code.  To convert my Record ID field to a record position and a table number

RecordLinksBeforeExport

and to convert from the table number and position to a Record ID

RecordLinksAfterImport

Client Beep

In the Classic Client we had the C/AL command BEEP to sound an alert to the user.  This function is not supported in the Role Tailored Client.

Here we have a solution in .NET.

[code]IF ISSERVICETIER THEN
Console.Beep
ELSE
BEEP(1000,300);[/code]

Where Console is a RunOnClient DotNet variable ‘mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Console

Customer Statistics FactBox

The Page 9082 is not displaying correct information.  One of the fact boxes for sales is the Customer Statistics factbox page 9082.

origpage9082

The last amount, “Sales YTD (LCY):” is in fact the total sales for the customer.  I wanted to change this and use 12 months sales instead of YTD sales.  I changed the caption according to that and changed the code.

[code]
OnAfterGetRecord()

IF CurrentDate <> WORKDATE THEN BEGIN
CurrentDate := WORKDATE;
END;

SETRANGE("Date Filter",0D,CurrentDate);
CALCFIELDS(
Balance,"Balance (LCY)","Balance Due","Balance Due (LCY)",
"Outstanding Orders (LCY)","Shipped Not Invoiced (LCY)");

TotalAmountLCY := "Balance (LCY)" + "Outstanding Orders (LCY)" + "Shipped Not Invoiced (LCY)" + "Outstanding Invoices (LCY)";

SETRANGE("Date Filter",CALCDATE(”,CurrentDate),CurrentDate); //#01-

CALCFIELDS(
"Sales (LCY)","Profit (LCY)","Inv. Discounts (LCY)","Inv. Amounts (LCY)","Pmt. Discounts (LCY)",
"Pmt. Disc. Tolerance (LCY)","Pmt. Tolerance (LCY)",
"Fin. Charge Memo Amounts (LCY)","Cr. Memo Amounts (LCY)","Payments (LCY)",
"Reminder Amounts (LCY)","Refunds (LCY)","Other Amounts (LCY)");
SETRANGE("Date Filter",0D,CurrentDate);[/code]

where the line

SETRANGE(“Date Filter”,CALCDATE(‘<-12M>’,CurrentDate),CurrentDate); //#01-

is added.

CurrReport.CREATETOTALS and the Role Tailored Client

You can say; “that won’t work”, and you are correct.  It will not work.

Some years ago I wrote a batch that loops through customer ledger entries and creates a total. Based on that total I wanted to create a claim and send it to the local bank for collection.  This batch has been working perfectly for many years but when I put this system to use for a client running the role tailored client this batch failed.

When I first looked at the batch looking for a different behaviour between the role tailored client and the classic client I saw nothing.  Decided that I would take a closer look next Monday.  When I woke up on Saturday morning and not really thinking about this the solution hit me.  There is a CurrReport.CREATETOTALS in the batch.  And that is ignored in the role tailored client.

So I changed the code. In OnPreDataItem trigger the code is now

[code]
IF ISSERVICETIER THEN BEGIN
TotalCustomerAmount := 0;
TotalCustomerEntries := 0;
END ELSE
CurrReport.CREATETOTALS(TotalCustomerAmount,TotalCustomerEntries);[/code]

and in the OnAfterGetRecord trigger the code is

[code]
IF ISSERVICETIER THEN BEGIN
TotalCustomerEntries := TotalCustomerEntries + 1;
TotalCustomerAmount := TotalCustomerAmount + "Remaining Amt. (LCY)";
END ELSE BEGIN
TotalCustomerEntries := 1;
TotalCustomerAmount := "Remaining Amt. (LCY)";
END;[/code]

I guess the lesson is; don’t use CurrReport.CREATETOTALS in your batches.

Customer warnings in sales

In the sales process NAV check for both credit limits and overdue balance on the customer. This is a needed process to inform the salesperson before posting the order or the invoice. However, the problem is that the warning is displayed every time the salesperson creates or modifies a sales line.

When a salesperson has agreed to sell this customer why should we continue to give him warnings that he has already approved?

I solved this by creating a new table to log warnings.  If a salesperson opts to continue the sales process despite the warning an entry is created in the log table.  Next time the system checks for a warning it will read the log table to see if a warning of that type has already been given to that salesperson.

The changes needed to implement this are minimal.

In Form and Page 343

  • move the local variable ExitValue from the ShowWarning function to Globals.
  • Add a code to the ShowWarning function to set ExitValue to zero before the warning logic.
  • Add a global function GetExitValue of type integer that will return the value of ExitValue

Then add functions to Codeunit 312 to check for log entry and to create a new log entry.

[code]
SalesHeaderCheck(SalesHeader : Record "Sales Header")
IF CustCheckCreditLimit.SalesHeaderShowWarning(SalesHeader) THEN BEGIN
//#01-
IF ExistsInWarningLog(
SalesHeader."Document Type",
SalesHeader."No.",
SalesHeader."Bill-to Customer No.",
CustCheckCreditLimit.GetExitValue) THEN EXIT;
//#01+
OK := CustCheckCreditLimit.RUNMODAL = ACTION::Yes;
//#01-
IF OK THEN BEGIN
AddToWarningLog(
SalesHeader."Document Type",
SalesHeader."No.",
SalesHeader."Bill-to Customer No.",
SalesHeader."Sell-to Customer No.",
SalesHeader."Posting Date",
CustCheckCreditLimit.GetExitValue);
CLEAR(CustCheckCreditLimit);
END ELSE
// IF NOT OK THEN
//#01+
ERROR(Text000);
END;[/code]
[code]

#01-()

ExistsInWarningLog(DocumentType : Option;DocumentNo : Code[20];BillToCustomerNo : Code[20];WarningType : Integer) : Boolean
WITH WarningLog DO BEGIN
SETCURRENTKEY("Document Type","No.","Bill-to Customer No.","Warning Type","User ID");
SETRANGE("Document Type",DocumentType);
SETRANGE("No.",DocumentNo);
SETRANGE("Bill-to Customer No.",BillToCustomerNo);
SETRANGE("Warning Type",WarningType);
SETRANGE("User ID",USERID);
EXIT(NOT ISEMPTY);
END;

AddToWarningLog(DocumentType : Option;DocumentNo : Code[20];BillToCustomerNo : Code[20];SellToCustomerNo : Code[20];PostingDate : Date;
WITH WarningLog DO BEGIN
INIT;
"Entry No." := 0;
"Date and Time" := CURRENTDATETIME;
"Bill-to Customer No." := BillToCustomerNo;
"Document Type" := DocumentType;
"Sell-to Customer No." := SellToCustomerNo;
"No." := DocumentNo;
"Warning Type" := WarningType;
"Posting Date" := PostingDate;
"User ID" := USERID;
INSERT;
END;

#01+()
[/code]

I created a new role with read permission to the warning table and assigned that role to administrative users. I added indirect read and indirect add to a general role that gives a sales person permission to create a sales invoice.

Warning Log Objects