Tag: Role Tailored Client

Posted on

Run a Table in NAV

One of the things we developers and consultants miss the most from the “Classic Times” is the ability to run a table to edit the data. Sure we can run a table from the Developement Environment but we are not always working with direct access to the SQL database.

My solution is to have a Page running on the object table (Table2000000001). From the page I can start Pages, Reports, Codeunits and XML Ports directly with a simple line of code, but to start a Table is more complex.

The first solution was to use HYPERLINK on the result from the GETURL function. That works fine in a local environment, but when you have installed multiple Dynamics NAV versions or running Dynamics NAV from a ClickOnce installation things start to break.

To fix this I stop using HYPERLINK and start the Dynamics NAV client with arguments to run a table. First step is to find the current client path. This path can be the usual System Drive path but it can also be a User Application Path if using ClickOnce. The function to locate the client path uses DotNet.

[code] PROCEDURE GetClientPath@1100408003() : Text;
VAR
ClientAssembly@1100408001 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Reflection.Assembly" RUNONCLIENT;
ClientPath@1100408000 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.Path" RUNONCLIENT;
BEGIN
ClientAssembly := ClientAssembly.GetExecutingAssembly;
EXIT(ClientPath.GetDirectoryName(ClientAssembly.Location));
END;[/code]

And based on the client Path I look for the ClientConfiguration.config file and start the client with the GETURL results.

[code] LOCAL PROCEDURE ViewRecords@1100408000();
VAR
AddinMgt@1100408000 : Codeunit 10000207;
PathHelper@1100408008 : DotNet "’mscorlib’.System.IO.Path";
ClientFileHelper@1100408007 : DotNet "’mscorlib’.System.IO.File" RUNONCLIENT;
ClientProcess@1100408005 : DotNet "’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Diagnostics.Process" RUNONCLIENT;
ClientProcessWindowStyle@1100408004 : DotNet "’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Diagnostics.ProcessWindowStyle" RUNONCLIENT;
ClientProcessStartInfo@1100408003 : DotNet "’System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Diagnostics.ProcessStartInfo" RUNONCLIENT;
StartCommand@1100408002 : Text;
ArgumentList@1100408006 : Text;
ClientPath@1100408001 : Text;
ClientConfigurationPath@1100408009 : Text;
BEGIN
ClientPath := AddinMgt.GetClientPath;
ClientConfigurationPath := PathHelper.Combine(ClientPath,’ClientUserSettings.config’);
IF ClientFileHelper.Exists(ClientConfigurationPath) THEN
ArgumentList := STRSUBSTNO(‘-settings:"%1" ‘,ClientConfigurationPath);
ArgumentList += GETURL(CLIENTTYPE::Windows, COMPANYNAME, OBJECTTYPE::Table, ID);
ClientPath := PathHelper.Combine(ClientPath,’Microsoft.Dynamics.Nav.Client.exe’);
ClientProcessStartInfo := ClientProcessStartInfo.ProcessStartInfo(ClientPath);
ClientProcessStartInfo.Arguments := ArgumentList;
ClientProcessStartInfo.WindowStyle := ClientProcessWindowStyle.Normal;
ClientProcess := ClientProcess.Start(ClientProcessStartInfo);
END;[/code]

To repeat my last comment, just use DotNet 🙂

Posted on

Selection Filter to Clipboard

I have now seen three posts about using Excel to help create a Pipe Filter.  The latest one from Mohana is similar to the way I normally do this.  His post is a followup from the original video from Kerry Rosvold and an alternative from Mark Brummel.

If this is something you frequently need to do, why don’t use NAV and skip the Excel part ?

Lets imagine how that could look like.

CustomerList

We add a button the ribbon, select what ever we like from the customer list and click the button.

Clipboard

Now it just a matter of using the filter. Paste it where ever you need it.

FilteredCustomerList

And this is with just a few lines of code.

CustomerListModification

Page21 Delta file

Posted on

Using Views and Linked Tables

There are always some cases where you would like to have external data available to Dynamics NAV.  In cases where the data is available to the SQL Server hosting the Dynamics NAV database it is easy to create a view in the NAV database and a linked table in the Dynamics NAV.  The process is;

Create a normal table in Dynamics NAV and save it.  Make sure that you select to use the same table for all companies.

TableProperties1

Next go to SQL Management Studio and build a CREATE TABLE script for this table.  When you do the CREATE VIEW script you must make sure that all fields are identical and with matching collation. I begin by dropping the table and then building the view.

[code lang=”sql”]USE [NAV 2013 R2 Company]
GO

/****** Object: Table [dbo].[National Register] Script Date: 13.9.2013 09:35:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DROP TABLE [dbo].[National Register]
GO

CREATE VIEW [dbo].[National Register] AS SELECT
[No_] COLLATE Icelandic_100_CS_AS AS [No_]
,[Type]
,[Family No_] COLLATE Icelandic_100_CS_AS AS [Family No_]
,[Name] COLLATE Icelandic_100_CS_AS AS [Name]
,[Sorting] COLLATE Icelandic_100_CS_AS AS [Sorting]
,[Community] COLLATE Icelandic_100_CS_AS AS [Community]
,[Gender]
,[Martial Status]
,[Mate No_] COLLATE Icelandic_100_CS_AS AS [Mate No_]
,[Post Code] COLLATE Icelandic_100_CS_AS AS [Post Code]
,[Address] COLLATE Icelandic_100_CS_AS AS [Address]
,[Guardian No_] COLLATE Icelandic_100_CS_AS AS [Guardian No_]
,[Industry Code] COLLATE Icelandic_100_CS_AS AS [Industry Code]
,[Industry] COLLATE Icelandic_100_CS_AS AS [Industry]
,[Address Code] COLLATE Icelandic_100_CS_AS AS [Address Code]
,[identification No_] COLLATE Icelandic_100_CS_AS AS [identification No_]
,[Community Sorting] COLLATE Icelandic_100_CS_AS AS [Community Sorting]
,[Legal Home Code] COLLATE Icelandic_100_CS_AS AS [Legal Home Code]
,[Blocking Code] COLLATE Icelandic_100_CS_AS AS [Blocking Code]
,[Country] COLLATE Icelandic_100_CS_AS AS [Country]
,[Place of Birth] COLLATE Icelandic_100_CS_AS AS [Place of Birth]
,[Date of Birth]
,[Address Code 2] COLLATE Icelandic_100_CS_AS AS [Address Code 2]
,[Last Local Address] COLLATE Icelandic_100_CS_AS AS [Last Local Address]
,[Proxy No_] COLLATE Icelandic_100_CS_AS AS [Proxy No_]
,[Church] COLLATE Icelandic_100_CS_AS AS [Church]
,[Address to] COLLATE Icelandic_100_CS_AS AS [Address to]
,[Status] COLLATE Icelandic_100_CS_AS AS [Status]
,[Deport Date]
,[Post Address] COLLATE Icelandic_100_CS_AS AS [Post Address]
,[c_o Name] COLLATE Icelandic_100_CS_AS AS [c_o Name]
,[c_o Address] COLLATE Icelandic_100_CS_AS AS [c_o Address]
,[c_o Post Code] COLLATE Icelandic_100_CS_AS AS [c_o Post Code]
,[c_o Post Address] COLLATE Icelandic_100_CS_AS AS [c_o Post Address]
,” COLLATE Icelandic_100_CS_AS AS [VAT No_]
,[Registered By] COLLATE Icelandic_100_CS_AS AS [Registered By]
FROM [NAV 2009 R2 Company].[dbo].[NR Data]
[/code]

Next step is to modify the table properties and set this table to be a linked table.
TableProperties2
Now NAV 2013 R2 has access to all the data in the table. The user running the NAV Service must have read access to the original table.  Make sure that all pages that use this table are read-only as the data can’t be modified through a view.

In NAV 2013 R2 there is an issue that we need to be aware of.  The NAV Server caches the data in these tables so we can have different results in NAV 2013 R2 than expected.  My contacts tell me that this issue has been fixed in the upcoming NAV vNext and I created a Microsoft Connect suggestion to have this fixed in NAV 2013 R2.  Until that solution is available the workaround is to use SELECTLATESTVERSION in you code before using the linked data.  Here is an example function that is reading a plain text password from a linked database and comparing that to an incoming MD5 password.  This issue does not seem to affect pages.  In other words the linked data displayed in pages looks up to date.

 

[code] PROCEDURE ValidateLogin@50011(ChurnNo@50000 : Code[10];MD5Password@50001 : Text[80]) : Boolean;
VAR
ChurnNoRegister@50002 : Record 10017450;
MD5@50003 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Security.Cryptography.MD5";
Encoding@50004 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Text.Encoding";
HashBytes@50005 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Array";
Data@50009 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Byte";
StringBuilder@50008 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Text.StringBuilder";
StringComparer@50006 : DotNet "’mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.StringComparer";
Loop@50007 : Integer;
BEGIN
SELECTLATESTVERSION;
IF NOT ChurnNoRegister.GET(ChurnNo) THEN EXIT(FALSE);
IF ChurnNoRegister."Farmer Web Password" = ” THEN EXIT(FALSE);
MD5 := MD5.Create;
HashBytes := MD5.ComputeHash(Encoding.UTF8.GetBytes(ChurnNoRegister."Farmer Web Password"));
StringBuilder := StringBuilder.StringBuilder;
FOR Loop := 0 TO HashBytes.Length – 1 DO BEGIN
Data := HashBytes.GetValue(Loop);
StringBuilder.Append(Data.ToString(‘x2’));
END;
StringComparer := StringComparer.OrdinalIgnoreCase;
EXIT(StringComparer.Compare(MD5Password,StringBuilder.ToString) = 0);
END;
[/code]

 

Posted on

My Excel Helper to bring opened Excel Documents to front

In one of my projects I use Excel a lot for reporting.  I create the Excel documents with OpenXML and with the help of the Excel Buffer table.  With the click of a button the user creates a beautiful Excel Workbook but when I open the workbook it is displayed in the back of Dynamics NAV client window.

One of the users has been complaining about this and in the process of upgrading from NAV 2009 to NAV 2013 he asked me if this will be fixed.  I of course had to in order to increase the appreciation of the upgrade.

So I looked into this but did not find any .net object to use.  Looked like I had to import user32.dll to do this and that is not possible directly in NAV.  Hence a new class dll was born.

[code language=”csharp”] public static class WindowHelper
{
[System.Runtime.InteropServices.DllImport("user32.dll")]
static extern bool SetForegroundWindow(IntPtr hWnd);

public static void ActivateAppByProcessName(string processName)
{
Process[] p = Process.GetProcessesByName(processName);
if (p.Length > 0)
ActivateAppByHandle(p[0].MainWindowHandle);
}

public static void ActivateAppByPartialProcessName(string processName)
{
Process[] processes = Process.GetProcesses();
foreach (Process process in processes)
{
if (process.MainWindowTitle.Contains(processName))
{
ActivateAppByHandle(process.MainWindowHandle);
}
}
}

public static void ActivateAppByFileName(string fileName)
{
string[] fileNameParts = fileName.Split(new Char[] { ‘.’ });
string windowTitle = "";
for (int i = 0; i < fileNameParts.Length – 1; i++)
{
if (i == 0)
{
windowTitle += fileNameParts[i];
}
else
{
windowTitle += "." + fileNameParts[i];
}
}
ActivateAppByPartialProcessName(windowTitle);
}

static void ActivateAppByHandle(System.IntPtr WindowHandle)
{
SetForegroundWindow(WindowHandle);
}
}[/code]

In the same solution I had a problem converting the Excel Workbook to a PDF file. So I also added that support to this class.

[code language=”csharp”] public static class ExcelHelper
{
public static void ExportPDF(Microsoft.Office.Interop.Excel.Workbook xlWrkBk, string pdfFileName)
{
xlWrkBk.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, pdfFileName);
}
}[/code]

Now I am able to open Excel in front of the NAV client and also able to convert the Excel Workbook to a PDF file.  In Table 370, Excel Buffer, I add a local variable for this class and add a single line in the code.

[code]The changes I make are below[code] PROCEDURE GiveUserControl@3();
VAR
WindowHelper@50000 : DotNet "’UserHelperClass, Version=1.0.0.0, Culture=neutral, PublicKeyToken=22affbe033b077df’.UserHelperClass.WindowHelper" RUNONCLIENT;
BEGIN
IF NOT ISNULL(XlApp) THEN BEGIN
XlApp.Visible := TRUE;
// Dynamics.is – Bring Excel to front –
WindowHelper.ActivateAppByFileName(XlWrkBk.Name);
// Dynamics.is – Bring Excel to front +
XlApp.UserControl := TRUE;
CLEAR(XlApp);
END;
END;
[/code]

And in Codeunit 424 in the bottom of the ExportData trigger

[code]
xlApp.Visible := TRUE;
//Dynamics.is – Bring Excel to Front –
WindowHelper.ActivateAppByFileName(FileMgt.GetFileName(FileName));
//Dynamics.is – Bring Excel to Front +[/code]

The class and then changed objects are attached below.

UserHelperClass  ExcelBufferUpdate

Posted on

The new PingPong add-in for NAV 2013

In NAV 2009 I used a custom control add-in to enable timers in the Role Tailored Client.  NAV 2013 ships with a control add-in that is called PingPong.  In upgrading one of my solution to NAV 2013 I wanted to remove the custom control and introduce the PingPong instead.

PingPongProperties

The control requires a name and as the standard functionality does I use the name PingPong.  The code I had in the – OnControlAddin trigger is now moved to a new trigger, PingPong::Pong.  The new timer does not work the same way the old one does.  The method used is similar to the new method in the Job Queue where the sleep function is used instead of a regular timer.  By executing Currpage.PingPong.Ping(500) a new thread is started that sleeps for 500 milliseconds and then fires the trigger PingPong::Pong.  Hence the add-in name.  When all required code in this trigger has been executed another Ping is required to thow the next ball.

PingPongTriggers

This is all good if the application is only using one timer.  I saw that if I already had one PingPong working then in the subsequent page PingPong did not work.  The good news is that the timer control add-in that I created for NAV 2009 also works in NAV 2013.  The page that I open from the page running PingPong will continue to use the timer add-in that originated from Freddy and I changed a little bit.

Posted on

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.

Posted on

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

Posted on

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.

Posted on

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.

Posted on

Using a .dll proxy for web services

I have now completed my first all-dotnet codeunit.  The codeunit uses a dll file that I created from the web service WDSL.  This makes the programming a lot easier.

This solution has a BLOB fields that stores both incoming and outgoing xml.  When I use a proxy dll I don’t build a xml document and I never handle xml documents.  Again dotnet has a solution.  I use xml serializer from the system.xml object.

[code]LOCAL PROCEDURE SerializeToXMLStream@1100408008(VAR Object@1100408002 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Object" RUNONCLIENT;VAR NavOutstr@1100408004 : OutStream);
VAR
xmlSerializer@1100408000 : DotNet "’System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.Xml.Serialization.XmlSerializer" RUNONCLIENT;
StreamWriter@1100408003 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.StreamWriter" RUNONCLIENT;
File@1100408001 : DotNet "’mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089′.System.IO.File" RUNONCLIENT;
NavInstr@1100408005 : InStream;
BEGIN
IF ISNULL(Object) THEN EXIT;
StreamWriter := StreamWriter.StreamWriter(TempFileName);
xmlSerializer := xmlSerializer.XmlSerializer(Object.GetType);
xmlSerializer.Serialize(StreamWriter,Object);
StreamWriter.Close;
UPLOADINTOSTREAM(”,MagicPath,”,TempFileName,NavInstr);
COPYSTREAM(NavOutstr,NavInstr);
File.Delete(TempFileName);
END;[/code]

I can send the proxy object to this function and will get the xml into the BLOB field.

[code]CurrencyRates := StatementService.GetCurrencyRates(TypeRates,CREATEDATETIME(RatesDate,235959T));

WITH BankAccAction DO BEGIN
"Modified by User ID" := USERID;
"Modification Date and Time" := CURRENTDATETIME;
"Incoming Message".CREATEOUTSTREAM(OutStr);
SerializeToXMLStream(CurrencyRates,OutStr);
MODIFY;
COMMIT;
END;[/code]

This example is executed on the client since I am using certificates and web services security for the communication.