Prepare for Report Transformation

On of the bigger tasks when upgrading to the Role Tailored Client is the report transformation.  In big database there are houndreds of reports.  Some of them might just be idle reports that are never used.  This blog from ArcherPoint that is based on a original post from Mark Brummel got me thinking.  In 2009 Microsoft added to the client the possibility to execute a function with ID 120 in codeunit 1 that only works for the Classic Client.

I mixed these together by adding the code to codeunit 1 in the same way as Mark Brummel but creating a report log table instead of a report print count table.

Here the Entry No. field automatically increments the numbers in the database.  I also changed the property DataPerCompany for the table to No.

The single instance codeunit is also a simple one.

The next step might just be to create a report for Excel Pivot table like I did with ledger tables.

Report Printing Log

Send multiple base 64 encoded files to a single Web Service

Sometimes a little more flexibility is needed than a single XML Port in a web service function.  Then it is possible to send multiple files to a single web service.  I am working on an enhancement for the inter-company posting feature in Dynamics NAV.

To add to the standard functionality I want to be able to use web services to deliver an invoice from one company to another.  I send two XML files and two PDF files to web service.

In the Classic Client I use then ‘CG Request Client’.Base64 to encode the files into a XML node.

[code]
XMLElement3.appendChild(XMLNode);
XMLNode := XMLRequestDoc.createElement(‘pDFInvoice’);
IF "PDF Document".HASVALUE THEN BEGIN
PDFInvoiceFileName := FileMgt.ClientTempFileName(”,’pdf’);
ICOutboxTrans."PDF Document".EXPORT(PDFInvoiceFileName,FALSE);
Base64.Encode(PDFInvoiceFileName,XMLNode);
ERASE(PDFInvoiceFileName);
END;
XMLElement3.appendChild(XMLNode);[/code]

in the Role Tailored Client I use dotnet interop

[code]
XMLNode4 := XMLRequestDoc.CreateElement(‘pDFInvoice’);
IF "PDF Document".HASVALUE THEN BEGIN
"PDF Document".CREATEINSTREAM(InStr);
DOWNLOADFROMSTREAM(InStr,Text006,Path,Text009,PDFInvoiceFileName);
XMLNode4.InnerText := Convert.ToBase64String(ClientFile.ReadAllBytes(PDFInvoiceFileName));
ClientFile.Delete(PDFInvoiceFileName);
END;
XMLElement3.AppendChild(XMLNode4);[/code]

On the service part it is possible to write the file to a BLOB without using a temporary file.

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

A message from Dan Brown, General Manager Dynamics NAV

This is a message to you all from Dan Brown General Manager, Dynamics NAV:

Hi, everyone.

Over the past several months the NAV team has worked hard getting ready to release Microsoft Dynamics NAV 2013. We’ve run hundreds of thousands of performance-, stress-, unit- and regression-tests daily. We’ve monitored the comments you’ve made on our first-ever public Beta of NAV and incorporated the feedback as much as possible. And, we’ve worked with partners bringing several customers live on NAV 2013. All of this has been to ensure that the product is of the highest possible quality before we ship. We’re looking forward to getting the RTM version in your hands as soon as possible!

Microsoft Dynamics NAV 2013 is probably the biggest launch of the product ever. It concludes the transition from the classic client/server 2-tier proprietary architecture developed in the 1990’s to a state-of-the-art, 4-tier Microsoft architecture capable of rendering multiple clients and facilitating multiple modes of integration. It also means that Microsoft Dynamics NAV now is a full-blown member of the Microsoft server family and adheres to all Microsoft standards in terms of security, reliability and scalability. Finally, it signals the beginning of a new era of “NAV in the cloud,” opening up an array of new opportunities using NAV and integrating it with Microsoft and non-Microsoft products.

With Microsoft Dynamics NAV 2013 coming out this fall, the NAV TechDays conference is a great opportunity for everybody in the NAV developer community to learn more about and get ready for the release. All the sessions at NAV TechDays are technical and long enough to allow the speakers to go into enough detail for the developers in the audience to understand what the features in the product are about and how to use them. Since NAV TechDays is a conference for developers by developers with deep technical content, we are sending some of our best developers who designed and wrote the code to attend and speak at the conference. If you have questions about your favorite feature, you will have an opportunity to give your feedback on Microsoft Dynamics NAV 2013 and provide input on what you would like to see in future releases.

I hope to see you all at NAV TechDays in Antwerp, Belgium on September 27th.

-Dan

____________________________________
Daniel C. Brown
General Manager, Dynamics NAV

Accessing your non english data from the MS SQL server

If you are like me, located in a non-English speaking country and would like users to be able to use other tools then the Dynamics NAV clients to access the company data you will find that all the meta data in the database is in English.  This means that you will have to translate the fields and sometimes the data to your language.

The problems are:

  • Field captions are unavailable in the MS SQL database
  • Option values are shown as number
  • Boolean is shown as number
  • Global dimension do not have the correct caption
  • Time is shown as DateTime
  • Not easy to see the difference between normal date and closing date

The solution that I am using is to create a separate database on the same database server and create localized views in that database.  What you get with the solution is:

  • A date table that can be used to show all properties of a given date
  • A import of the option value captions
  • A selection of tables to make accessible
  • A batch job to create the localized SQL view for each table

Here is a list of the fields in the date table

Field No. Field Name Data Type Length
1 Date Date
2 Date Name Text 30
3 Year Integer
4 Week Integer
5 Month Integer
6 Month Name Text 20
7 Day of Week Integer
8 Day Name Text 20
9 Day of Month Integer
10 Closing Date Boolean
11 SQL Month Name Text 20
12 SQL Day Name Text 20
13 Quarter Integer
14 Year Month Integer
15 Year Month Name Text 20
16 Month Year  Name Text 20
17 Quarter Name Text 20
18 VAT Period Integer
19 VAT Period Name Text 20
20 Sorting Date Integer
21 HRMS Integer Start Integer
22 HRMS Integer End Integer
23 Day of Year Integer
24 Day of Half Year Integer
25 Day of Quarter Integer
26 Day of Accounting Integer
27 Half Years Integer
28 Half Year of Year Integer
29 Is Holiday Boolean
30 Is Working Day Boolean
31 Month of Half Year Integer
32 Month of Quarter Integer
33 Month of Year Integer
34 Quarters of Half Year Integer
35 Quarters of Year Integer
36 Week of Year Integer
37 Is Week Day Boolean
41 Half Year Name Text 20
42 Week Name Text 20
102 Fiscal Day Date
103 Fiscal Year Integer
104 Fiscal Week Integer
105 Fiscal Month Integer
107 Fiscal Day of Week Integer
109 Fiscal Day of Month Integer
113 Fiscal Quarter Integer
123 Fiscal Day of Year Integer
124 Fiscal Day of Half Year Integer
125 Fiscal Day of Quarter Integer
127 Fiscal Half Years Integer
128 Fiscal Half Year of Year Integer
131 Fiscal Month of Half Year Integer
132 Fiscal Month of Quarter Integer
133 Fiscal Month of Year Integer
134 Fiscal Quarters of Half Year Integer
135 Fiscal Quarters of Year Integer
136 Fiscal Week of Half Year Integer
137 Fiscal Week of Month Integer
138 Fiscal Week of Quarter Integer
139 Fiscal Week of Year Integer
140 Fiscal Quarter Name Text 20
141 Fiscal Half Year Name Text 20
142 Fiscal Week Name Text 20
143 Fiscal Month Name Text 20

Lets take the G/L Entry table as an example. In the MS SQL the field names are in English and the data not readable for the normal user.

and for example the [Document Type] will show as numbers.  Here is an example of the output of my tool to create a localized view for this table.

Executing this will give me a view in my database that I can use to fetch localized data from the G/L Entry table with all the above problems solved.

By using the additional date table as dimension in OLAP or as join in a SQL query I can easily find all aspects of the “Posting Date” in the G/L Entry table and group entries accordingly.

Enable Inventory Adjustment from Job Queue

Today I posted a new feedback to Microsoft.  My experience is that companies that use inventory should execute Inventory Adjustment with the Job Queue every night.  To be able to do this a change is required to the OpenWindow trigger in codeunit 5895.

The old code is

[code]OpenWindow()
Window.OPEN(
Text000 +
‘#1########################\\’ +
Text001 +
Text003 +
Text004 +
Text005 +
Text006);
WindowIsOpen := TRUE;

UpDateWindow(NewWindowAdjmtLevel : Integer;NewWindowItem : Code[20];NewWindowAdjust : Text[20];NewWindowFWLevel : Integer;NewWindowEntr
WindowAdjmtLevel := NewWindowAdjmtLevel;
WindowItem := NewWindowItem;
WindowAdjust := NewWindowAdjust;
WindowFWLevel := NewWindowFWLevel;
WindowEntry := NewWindowEntry;

IF IsTimeForUpdate THEN BEGIN
IF NOT WindowIsOpen THEN
OpenWindow;
Window.UPDATE(1,STRSUBSTNO(Text002,AdjmtBuf.FIELDCAPTION("Item No."),WindowItem));
Window.UPDATE(2,WindowAdjmtLevel);
Window.UPDATE(3,WindowAdjust);
Window.UPDATE(4,WindowFWLevel);
Window.UPDATE(5,WindowEntry);
END;[/code]

and the new code is

[code]
OpenWindow()
IF GUIALLOWED THEN BEGIN
Window.OPEN(
Text000 +
‘#1########################\\’ +
Text001 +
Text003 +
Text004 +
Text005 +
Text006);
WindowIsOpen := TRUE;
END;
END;

UpDateWindow(NewWindowAdjmtLevel : Integer;NewWindowItem : Code[20];NewWindowAdjust : Text[20];NewWindowFWLevel : Integer;NewWindowEntr
WindowAdjmtLevel := NewWindowAdjmtLevel;
WindowItem := NewWindowItem;
WindowAdjust := NewWindowAdjust;
WindowFWLevel := NewWindowFWLevel;
WindowEntry := NewWindowEntry;
IF GUIALLOWED THEN BEGIN
IF IsTimeForUpdate THEN BEGIN
IF NOT WindowIsOpen THEN
OpenWindow;
Window.UPDATE(1,STRSUBSTNO(Text002,AdjmtBuf.FIELDCAPTION("Item No."),WindowItem));
Window.UPDATE(2,WindowAdjmtLevel);
Window.UPDATE(3,WindowAdjust);
Window.UPDATE(4,WindowFWLevel);
Window.UPDATE(5,WindowEntry);
END;
END;[/code]

If you agree with this please vote here.  A simple extension to the Job Queue can be found in an earlier post.