Tag: MS

Development Tips

TextEncoding Property (XMLports)

Ashwini Tripathi

Specifies the text encoding format to use when you use an XMLport to export or import data as text.

Note

The TextEncoding property is only available when the Format Property (XMLports) of the XMLport is set to Fixed Text or Variable Text.

Values

  • MS-DOS (default)
  • UTF-8
  • UTF-16
  • Windows

Remarks

Text encoding is the process of transforming bytes of data into readable characters for users of a system or program. There are several industry text encoding formats and different systems support different formats. Internally, Microsoft Dynamics NAV uses Unicode encoding. For exporting and importing data with an XMLport, Microsoft Dynamics NAV supports MS-DOS, UTF-8, UTF-16, and Windows encoding formats.

You should set the TextEncoding property to the encoding format that is compatible with the system or program that you will be exporting to or importing from. The following sections describe the available text encoding formats.

Tip

You can also set the TextEncoding property in C/AL code. For example, if your XMLport can import or export different formats based on certain conditions, you can change the encoding on the fly depending on the conditions. For example, you can write code such as the following:

currXMLport.TEXTENCODING := TEXTENCODING::Windows;

Example

The following code example illustrates how you can set the encoding during run time.

TextEncoding

Above screenshot is from XMLport 1220 in the CRONUS International Ltd. demonstration database.

Sample Code:

…

      CASE MyDefinitionTable.”File Encoding” OF

MyDefinitionTable.”File Encoding”::”MS-DOS”:

currXMLport.TEXTENCODING(TEXTENCODING::MSDos);

MyDefinitionTable.”File Encoding”::”UTF-8″:

currXMLport.TEXTENCODING(TEXTENCODING::UTF8);

MyDefinitionTable.”File Encoding”::”UTF-16″:

currXMLport.TEXTENCODING(TEXTENCODING::UTF16);

MyDefinitionTable.”File Encoding”::WINDOWS:

currXMLport.TEXTENCODING(TEXTENCODING::Windows);

…

The table, MyDefinitionTable, (Imaginary Table) has a field, File Encoding, (imaginary Field) that specifies the encoding for this part of an import.

Security, Server

Enhancing Microsoft Dynamics NAV Server Security

Ashwini Tripathi

Microsoft Dynamics NAV Server is a .NET-based Windows Service application that works exclusively with SQL Server databases.

Microsoft Dynamics NAV Server provides an additional layer of security between clients and the database. It leverages the authentication features of the Windows Communications Framework to provide another layer of user authentication and uses impersonation to ensure that business logic is executed in a process that has been instantiated by the user who submitted the request. This means that authorization and logging of user requests are performed on a per-user basis.

Login Account

After you install Microsoft Dynamics NAV Server, the default configuration is for the service to log on using the NT Authority\Network Service account. If Microsoft Dynamics NAV Server and SQL Server are on different computers, then MS recommends that you configure Microsoft Dynamics NAV Server to log on using a dedicated Windows domain user account instead. This account should not be an administrator either in the domain or on any local computer. A dedicated domain user account is considered more secure because no other services and therefore no other users have permissions for this account.

Disk Quotas

Client users can send files to be stored on Microsoft Dynamics NAV Server, so MS recommend that administrators set up disk quotas on all computers running Microsoft Dynamics NAV Server.

This can prevent users from uploading too many files, which can make the server unstable. Disk quotas track and control disk space usage for NTFS volumes, which allows administrators to control the amount of data that each user can store on a specific NTFS volume.

Limiting Port Access

The Microsoft Dynamics NAV Setup program opens a port in the firewall on the computer where you install Microsoft Dynamics NAV Server. By default, this is port 7046.

To improve security, you can consider limiting access to this port to a specific subnet. One way is to use netsh, which is a command-line tool for configuring and monitoring Windows-based computers at a command prompt.

The specific version of this command that you would use is netsh firewall set portopening. For example, the following command limits access to port 7046 to the specified addresses and subnets:

netsh firewall set portopening protocol=TCP port=7046 scope=subnet addresses=LocalSubnet

You can learn more on netsh command here.

Cues, Query

Using Query Object to Calculate the Cue Data

Ashwini Tripathi

Today we will learn to create a query to update Cue data.
SI-Cue-1

Creating a Query for Calculating the Cue Data
First, we will create a query object to calculate the number of open sales invoices from table 21 Cust. Ledger Entry.
Create a query for calculating the Cue data as below:
SI-Cue-2
Save the query.

Adding the Table Field for the Cue Data
Next we will add a field to the table Sales Invoice Cue (create new table) for holding the Cue data.
SI-Cue-3

SI-Cue-4
We will add a global function that returns the total amount of sales invoices for the current month from the query object that we created above procedure.

To add C/AL code to the table calculate the Cue data
Add a global function that is called CalcSalesThisMonthAmount as follows:

On the View menu, choose C/AL Globals.

On the Functions tab, in the Name column, enter CalcSalesThisMonthAmount.

Select the new function, and then in the View menu, select Properties.

Set the Local property to No.

In the C/AL Globals window, select the new function, and then choose Locals.

On the Return Value tab, set Name field to Amount and the Return Type field to Decimal.

On the Variables tab, add two variables as shown in the following table:

Name DataType Subtype
CustLedgerEntry Record Cust. Ledger Entry
CustLedgerEntrySales Query Cust. Ledg. Entry Sales

In C/AL code, add the following code on the CalcSalesThisMonthAmount function:

CustLedgerEntrySales.SETRANGE(Document_Type,CustLedgerEntry.”Document Type”::Invoice);

CustLedgerEntrySales.SETRANGE(Posting_Date,CALCDATE(‘<-CM>’,WORKDATE),WORKDATE);

CustLedgerEntrySales.OPEN;

IF CustLedgerEntrySales.READ THEN

Amount := CustLedgerEntrySales.Sum_Sales_LCY;

SI-Cue-5
Save the table.

Adding the Cue to the Role Center Page
To display the Cue fields on the Role Center, We will create a new Page [PageType  = CardPart] name Sales Invoice Cue.
SI-Cue-6

SI-Cue-7
Page Designer should look similar to shown above illustration.

Open the C/AL code for the page, and then add the following code to the OnAfterGetRecord Trigger to assign the Sales This month field to the CalcSalesThisMonthAmount function of table Sales Invoice Cue:

“Sales This Month” := CalcSalesThisMonthAmount;

SI-Cue-8
Also add code to the OnOpenPage Trigger.

RESET;
IF NOT GET THEN BEGIN
INIT;
INSERT;
END;

Formatting the Cue Data
We does not want to display any decimal places. To achieve this, we set the AutoFormatType Property and AutoFormatExpr Property of the Cue field on the page. As shown above.

To change the data format

In the Properties window, set the AutoFormatType property to 10.

This enables you to create a custom data format.

Set the AutoFormatExpr property to the following text.

‘<precision,0:0><standard format,0>’

<precision,0:0> specifies not to display any decimals places.

<standard format,0> specifies to format the data according to standard format 0.

Close the Properties windows, and then save and compile the page.

Run the Page created above the output should be similar to as below:
SI-Cue-9

Now you can add this CardPart Page to any of your Role Centre Page.

Development Tips, Office Integration

Using Automation to Create a Graph in Microsoft Excel

Ashwini Tripathi

In this walkthrough, you will transfer data for top 10 Customers Sales Contribution to Microsoft Excel and create a graph.

This example shows how to handle enumerations by creating a graph in Excel that shows the distribution of Sales by Customer.

ExcelChart-1

You will run the codeunit directly from Object Designer. In a real application, you would call it from an appropriate place, such as from a menu or any other window.

About This Walkthrough

This walkthrough illustrates the following tasks:

  • Creating a codeunit that declares the Automation variables that are required for using Excel Automation.
  • Adding a function to calculate Top 10 Customers Sales Contribution.
  • Adding C/AL code to the codeunit to run the Automation object that opens Excel.
  • Adding C/AL code to the Automation codeunit to transfer data from a table record to Excel.
  • Adding C/AL code that creates a graph in Excel. 

Prerequisites 

To complete this walkthrough, you will need:

  • Microsoft Dynamics NAV 2015 with a developer license.
  • The CRONUS International Ltd. demo data company.
  • Microsoft Excel 2013 or Microsoft Excel 2010.

Creating the Codeunit and Declaring Variables

To create the codeunit and declare variables

  • To implement Automation in a codeunit, you define the Automation variables. To define an Automation variable, you specify an Automation server and the Automation object.
  • The language in the regional settings of your computer matches the language version of Microsoft Excel.
  • In Object Designer, choose Codeunit, and then choose the New button to create a new codeunit.
  • On the View menu, choose C/AL Globals.
  • On the Variables tab, add the following variables:

Note

For the Automation data type variables, the subtype Microsoft Excel 15.0/14.0 Object Library defines the Automation server, and the class specifies the Automation object of the Microsoft Excel 15.0/14.0 Object Library.

ExcelChart-2

Name DataType Subtype Length
xlApp Automation ‘Microsoft Excel 15.0 Object Library’.Application
xlBook Automation ‘Microsoft Excel 15.0 Object Library’.Workbook
xlSheet Automation ‘Microsoft Excel 15.0 Object Library’.Worksheet
xlChart Automation ‘Microsoft Excel 15.0 Object Library’.Chart
xlRange Automation ‘Microsoft Excel 15.0 Object Library’.Range
Cust Record Customer
Window Dialog
CustAmount Record Customer Amount
CustFilter Text
CustDateFilter Text 30
ShowType Option  [Sales (LCY),Balance (LCY)]
NoOfRecordsToPrint Integer
CustSalesLCY Decimal
CustBalanceLCY Decimal
MaxAmount Decimal
BarText Text 50
i Integer
TotalSales Decimal
TotalBalance Decimal
ChartType Option  [Bar chart,Pie chart]
ChartTypeNo Integer
ShowTypeNo Integer
ChartTypeVisible Boolean
Integer Record Integer
Customer Record Customer
CellNo1 Text 5
CellNo2 Text 5
  • Close the C/AL Globals window.

Adding the Code

Now you add the code for the codeunit.

To add the code

  • Add a Function to calculate Top 10 Customers Sales Contribution as:

ExcelChart-3
Add code to it as:

I have not used all the values, just shown this way also you can think of while you design any such code & functions.

Window.OPEN(Text000);

i := 0;

Cust.RESET;

IF Cust.FINDSET THEN

REPEAT

Window.UPDATE(1,Cust.”No.”);

Cust.CALCFIELDS(“Sales (LCY)”,”Balance (LCY)”);

IF (Cust.”Sales (LCY)” <> 0) OR (Cust.”Balance (LCY)” <> 0) THEN

BEGIN

CustAmount.INIT;

CustAmount.”Customer No.” := Cust.”No.”;

IF ShowType = ShowType::”Sales (LCY)” THEN BEGIN

CustAmount.”Amount (LCY)” := -Cust.”Sales (LCY)”;

CustAmount.”Amount 2 (LCY)” := -Cust.”Balance (LCY)”;

END ELSE BEGIN

CustAmount.”Amount (LCY)” := -Cust.”Balance (LCY)”;

CustAmount.”Amount 2 (LCY)” := -Cust.”Sales (LCY)”;

END;

CustAmount.INSERT;

IF (NoOfRecordsToPrint = 0) OR (i < NoOfRecordsToPrint) THEN

i := i + 1

ELSE BEGIN

CustAmount.FIND(‘+’);

CustAmount.DELETE;

END;

TotalSales += Cust.”Sales (LCY)”;

TotalBalance += Cust.”Balance (LCY)”;

ChartTypeNo := ChartType;

ShowTypeNo := ShowType;

END;

UNTIL Cust.NEXT = 0;

CustSalesLCY := Cust.”Sales (LCY)”;

CustBalanceLCY := Cust.”Balance (LCY)”;

Window.CLOSE;

IF CustAmount.FIND(‘-‘) THEN

REPEAT

CustAmount.”Amount (LCY)” := -CustAmount.”Amount (LCY)”;

Customer.GET(CustAmount.”Customer No.”);

Customer.CALCFIELDS(“Sales (LCY)”,”Balance (LCY)”);

IF MaxAmount = 0 THEN

MaxAmount := CustAmount.”Amount (LCY)”;

CustAmount.”Amount (LCY)” := -CustAmount.”Amount (LCY)”;

UNTIL CustAmount.NEXT = 0;

  • In the C/AL Editor, make call to above function by adding the following code to the OnRun trigger.

CustAmount.DELETEALL;

TopTenCustomer(10,ShowType::”Sales (LCY)”,ChartType::”Bar chart”);

  • Create an instance of Excel by adding the following code.

CREATE(xlApp, FALSE, TRUE);

  • Add a new workbook to Excel.

xlBook := xlApp.Workbooks.Add(-4167);

xlSheet:= xlApp.ActiveSheet;

xlSheet.Name := ‘Top 10 Customer’;

The following describes the code:

    • In the first line, you use the Add method of the Workbooks collection to return a new workbook. The attribute -4167 is the enumerator value of worksheets as they apply to Workbook objects.
    • In the second line, you use the ActiveSheet property of the Application class to ensure that what is done next affects the active sheet of the new workbook.
    • In the third line, you use the Name property to name the sheet.

Transferring Data

To transfer the data, you must calculate the data and transfer the results of the calculation.

To transfer data

  • In the C/AL Editor, on the codeunit, use following code to transfer data of Top 10 Customers to Excel. To transfer the data to Microsoft Excel, add the following code.

CellNo1 := ‘A1’;

CellNo2 := ‘B1’;

IF CustAmount.FINDSET THEN

REPEAT

CellNo1 := INCSTR(CellNo1);

xlSheet.Range(CellNo1).Value := CustAmount.”Customer No.”;

CellNo2 := INCSTR(CellNo2);

xlSheet.Range(CellNo2).Value := ABS(CustAmount.”Amount (LCY)”);

UNTIL CustAmount.NEXT = 0;

  • The final step is to create the graph. You will use the ChartWizard method to create chart. This is a fast and simple way to do it. You can more tightly control the design of the graph by setting it up using the methods and properties of the various Chart objects, such as ChartArea and Legend.

Creating the Graph

The final step is to create the graph. You will use the ChartWizard method to create chart. This is a fast and simple way to do it. You can more tightly control the design of the graph by setting it up using the methods and properties of the various Chart objects, such as ChartArea and Legend.

To create the graph

  •   In the C/AL Editor, on the current codeunit, define a range for the data in the graph.

xlRange := xlSheet.Range(‘A2:’+FORMAT(CellNo2));

  • Add a new chart sheet and give it a name.xlChart.Name := ‘ Top 10 Customer – Graph’;

xlChart := xlBook.Charts.Add;

xlChart.Name := ‘ Top 10 Customer – Graph’;

  • Create the graph.

xlChart.ChartWizard(xlRange,-4101,7,2,1,0,1,’Top 10 Customer’);

The following table describes the optional arguments that are used in the ChartWizard method.

Argument Description Value in method call
Source The range that contains the source data for the new chart. xlRange – The object returned by xlSheet.Range(‘A2:C3’).
Gallery The chart type. -4101 – The enumerator for the Chart Shown above.
Format The option number for the built-in auto formats. 7
PlotBy An integer specifying whether the data for each series is in rows or columns. 2 – The enumerator for the xlRows XlRowCol enumerator.
CategoryLabels An integer specifying the number of rows or columns within the source range that contains category labels. 1 – There is one row with category labels (the department names).
SeriesLabels An integer specifying the number of rows or columns within the source range that contains series labels. 0 – There are no series labels in your data.
HasLegend TRUE to include a legend. 1
Title VARIANT with the title of the chart. You pass a string such as ‘Top 10 Customer’.
  • Make Excel visible by adding the following code.

xlApp.Visible := TRUE;

Excel produces a General Protection Fault error when you close a new Excel worksheet that is created when Excel is invisible. To resolve this, you can make Excel visible immediately after you create a new worksheet. You can also make Excel visible just before you create a new Excel worksheet and then make it invisible again immediately after creating the new Excel worksheet. In this case, you would add the following code.

xlApp.Visible := TRUE;

xlBook := xlApp.Workbooks.Open(FileName);

xlApp.Visible := FALSE;

  •  Clearing the Temp Table by adding the following code.

CustAmount.DELETEALL;

  • Complete code in OnRun trigger should look like below:

ExcelChart-4
Saving and Running the Codeunit

You can test the codeunit for creating the graph by running the codeunit from Object Designer.

To save and run a codeunit

  1. On the File menu, choose Save.
  2. In the Save As window, enter an ID and name, and then choose the OK
  3. In Object Designer, select the codeunit, and then choose the Run

The Microsoft Excel graph should appear. As show above in beginning of the post.

Note

If you get an error states Old format or invalid type library, then make sure that the language in the regional settings of your computer matches the language version of Microsoft Excel.

Below detailed reference to the values used in above code:

 

XlChartType

Name Value Description
xl3DArea -4098 3D Area.
xl3DAreaStacked 78 3D Stacked Area.
xl3DAreaStacked100 79 100% Stacked Area.
xl3DBarClustered 60 3D Clustered Bar.
xl3DBarStacked 61 3D Stacked Bar.
xl3DBarStacked100 62 3D 100% Stacked Bar.
xl3DColumn -4100 3D Column.
xl3DColumnClustered 54 3D Clustered Column.
xl3DColumnStacked 55 3D Stacked Column.
xl3DColumnStacked100 56 3D 100% Stacked Column.
xl3DLine -4101 3D Line.
xl3DPie -4102 3D Pie.
xl3DPieExploded 70 Exploded 3D Pie.
xlArea 1 Area
xlAreaStacked 76 Stacked Area.
xlAreaStacked100 77 100% Stacked Area.
xlBarClustered 57 Clustered Bar.
xlBarOfPie 71 Bar of Pie.
xlBarStacked 58 Stacked Bar.
xlBarStacked100 59 100% Stacked Bar.
xlBubble 15 Bubble.
xlBubble3DEffect 87 Bubble with 3D effects.
xlColumnClustered 51 Clustered Column.
xlColumnStacked 52 Stacked Column.
xlColumnStacked100 53 100% Stacked Column.
xlConeBarClustered 102 Clustered Cone Bar.
xlConeBarStacked 103 Stacked Cone Bar.
xlConeBarStacked100 104 100% Stacked Cone Bar.
xlConeCol 105 3D Cone Column.
xlConeColClustered 99 Clustered Cone Column.
xlConeColStacked 100 Stacked Cone Column.
xlConeColStacked100 101 100% Stacked Cone Column.
xlCylinderBarClustered 95 Clustered Cylinder Bar.
xlCylinderBarStacked 96 Stacked Cylinder Bar.
xlCylinderBarStacked100 97 100% Stacked Cylinder Bar.
xlCylinderCol 98 3D Cylinder Column.
xlCylinderColClustered 92 Clustered Cone Column.
xlCylinderColStacked 93 Stacked Cone Column.
xlCylinderColStacked100 94 100% Stacked Cylinder Column.
xlDoughnut -4120 Doughnut.
xlDoughnutExploded 80 Exploded Doughnut.
xlLine 4 Line.
xlLineMarkers 65 Line with Markers.
xlLineMarkersStacked 66 Stacked Line with Markers.
xlLineMarkersStacked100 67 100% Stacked Line with Markers.
xlLineStacked 63 Stacked Line.
xlLineStacked100 64 100% Stacked Line.
xlPie 5 Pie.
xlPieExploded 69 Exploded Pie.
xlPieOfPie 68 Pie of Pie.
xlPyramidBarClustered 109 Clustered Pyramid Bar.
xlPyramidBarStacked 110 Stacked Pyramid Bar.
xlPyramidBarStacked100 111 100% Stacked Pyramid Bar.
xlPyramidCol 112 3D Pyramid Column.
xlPyramidColClustered 106 Clustered Pyramid Column.
xlPyramidColStacked 107 Stacked Pyramid Column.
xlPyramidColStacked100 108 100% Stacked Pyramid Column.
xlRadar -4151 Radar.
xlRadarFilled 82 Filled Radar.
xlRadarMarkers 81 Radar with Data Markers.
xlStockHLC 88 High-Low-Close.
xlStockOHLC 89 Open-High-Low-Close.
xlStockVHLC 90 Volume-High-Low-Close.
xlStockVOHLC 91 Volume-Open-High-Low-Close.
xlSurface 83 3D Surface.
xlSurfaceTopView 85 Surface (Top View).
xlSurfaceTopViewWireframe 86 Surface (Top View wireframe).
xlSurfaceWireframe 84 3D Surface (wireframe).
xlXYScatter -4169 Scatter.
xlXYScatterLines 74 Scatter with Lines.
xlXYScatterLinesNoMarkers 75 Scatter with Lines and No Data Markers.
xlXYScatterSmooth 72 Scatter with Smoothed Lines.
xlXYScatterSmoothNoMarkers 73 Scatter with Smoothed Lines and No Data Markers.

expression .ChartWizard(Source, Gallery, Format, PlotBy, CategoryLabels, SeriesLabels, HasLegend, Title, CategoryTitle, ValueTitle, ExtraTitle)

expression A variable that represents a Chart object.

Parameters

Name Required/Optional Data Type Description
Source Optional Variant The range that contains the source data for the new chart. If this argument is omitted, Microsoft Excel edits the active chart sheet or the selected chart on the active worksheet.
Gallery Optional Variant One of the constants of XlChartType specifying the chart type.
Format Optional Variant The option number for the built-in autoformats. Can be a number from 1 through 10, depending on the gallery type. If this argument is omitted, Microsoft Excel chooses a default value based on the gallery type and data source.
PlotBy Optional Variant Specifies whether the data for each series is in rows or columns. Can be one of the following XlRowCol constants: xlRows or xlColumns. Values can be [1 or 2]
CategoryLabels Optional Variant An integer specifying the number of rows or columns within the source range that contain category labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
SeriesLabels Optional Variant An integer specifying the number of rows or columns within the source range that contain series labels. Legal values are from 0 (zero) through one less than the maximum number of the corresponding categories or series.
HasLegend Optional Variant True to include a legend.
Title Optional Variant The chart title text.
CategoryTitle Optional Variant The category axis title text.
ValueTitle Optional Variant The value axis title text.
ExtraTitle Optional Variant The series axis title for 3-D charts or the second value axis title for 2-D charts.

Remarks

If Source is omitted and either the selection isn’t an embedded chart on the active worksheet or the active sheet isn’t an existing chart, this method fails and an error occurs.

You can use other values from above table to create graph of your choice.

Troubleshooting Tips

General Troubleshooting Tips

Ashwini Tripathi

Record Locked by Another User

If you have ended Microsoft Dynamics NAV in busy mode, and then restarted Microsoft Dynamics NAV, you may experience the following error message: “Record locked by another user”. This is caused by a lock on data in a job that is still running on the server.

To release this lock, you must either shut down the SQL server manually, or restart the Microsoft Dynamics NAV Server session using the Microsoft Dynamics NAV Server Administration tool.

Sorting Actions in System Groups

In the Microsoft Dynamics NAV Windows client, you cannot order actions in system groups in the Home tab.

Remove all actions from the group, apply changes, and then add the actions again. This will ensure that metadata is created for all actions and that they can be sorted as you want.

Removing Promoted Actions in Home Tab

In Microsoft Dynamics NAV removing a promoted action in the Home tab, and then re-adding it in the same session will cause duplication of the action.

Remove the action, apply changes, add the action again, and then apply changes. Or, remove the action, add it again in same session, and then apply changes. Remove duplicate instances and apply changes.

Compression Option in IIS

Users may experience slow mobile client responses especially noticeable when they are running with a slow network connection. The reason for the mobile client running slowly can be that the dynamic compression is not enabled on the IIS server.

Configure HTTP compression of dynamic content to use bandwidth more efficiently. Enabling dynamic compression always gives you more efficient use of bandwidth, but if your server’s processor utilization is already very high, the CPU load imposed by dynamic compression might make your site perform more slowly.

You can perform this procedure by using the user interface (UI), by running Appcmd.exe commands in a command-line window, by editing configuration files directly, or by writing WMI scripts.

  1. Open IIS Manager and navigate to the level you want to manage.

  2. In Features View, double-click Compression.

  3. On the Compression page, select the box next to Enable dynamic content compression.

  4. Click Apply in the Actions

The File that You Are Trying to Use Is Too Large

If you are trying to upload a large image file that is larger than 4 MB in size, such as a high-resolution photo, Microsoft Dynamics NAV will give you an error message saying that the file you are trying to upload is too large. This behavior can be changed by modifying the IIS configuration to support large file uploads.

The IIS administrator should make the following changes in the Internet Information Services (IIS) Manager.

  1. Launch the IIS Manager.
  2. In the left pane of the IIS Manager, select the Microsoft Dynamics NAV web site, and choose Request Filtering.
  3. In the Actions pane of the Request Filtering window, choose Edit Feature Settings.
  4. Set the field Maximum allowed content length (Bytes) to an appropriate value, such as 100000000 and choose the OK button.
  5. In the left pane of the IIS Manager, select the Microsoft Dynamics NAV web site, and choose Configuration Editor.
  6. In the Configuration Editor, make sure that the From field is set to Microsoft Dynamics NAV 2013 R2 Web Client Web.config
  7. Set the Section field to system.web/httpRuntime and now a number of properties will appear.
  8. Set maxRequestLength to an appropriate value, such as 100000 (kilobytes) and choose the Apply action on the right.
  9. Close the IIS Manager.

The new settings should take effect immediately without refreshing the IIS or the site.

Development Tips, Performance

Bulk Inserts – in Navision 2015

Ashwini Tripathi

By default, Microsoft Dynamics NAV automatically buffers inserts in order to send them to Microsoft SQL Server at one time.

By using bulk inserts, the number of server calls is reduced, thereby improving performance.

Bulk inserts also improve scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the amount of time that tables are locked; especially tables that contain SIFT indexes.

Application developers who want to write high performance code that utilizes this feature should understand the following bulk insert constraints.

Bulk Insert Constraints

If you want to write code that uses the bulk insert functionality, you must be aware of the following constraints.

Records are sent to SQL Server when the following occurs:

  • You call COMMIT to commit the transaction.
  • You call MODIFY or DELETE on the table.
  • You call any FIND or CALC methods on the table.

Records are not buffered if any of the following conditions are met:

  • The application is using the return value from an INSERT call; for example, “IF (GLEntry.INSERT) THEN”.
  • The table that you are going to insert the records into contains any of the following:
    • BLOB fields
    • Fields with the AutoIncrement property set to Yes

The following code example cannot use buffered inserts because it contains a FIND call on the GL/Entry table within the loop.

IF (JnlLine.FIND(‘-‘)) THEN BEGIN

GLEntry.LOCKTABLE;

REPEAT

IF (GLEntry.FINDLAST) THEN

GLEntry.NEXT := GLEntry.”Entry No.” + 1

ELSE

GLEntry.NEXT := 1;

// The FIND call will flush the buffered records.

GLEntry.”Entry No.” := GLEntry.NEXT ;

GLEntry.INSERT;

UNTIL (JnlLine.FIND(‘>’) = 0)

END;

COMMIT;

If you rewrite the code, as shown in the following example, you can use buffered inserts.

IF (JnlLine.FIND(‘-‘)) THEN BEGIN

GLEntry.LOCKTABLE;

IF (GLEntry.FINDLAST) THEN

GLEntry.Next := GLEntry.”Entry No.” + 1

ELSE

GLEntry.Next := 1;

REPEAT

GLEntry.”Entry No.”:= GLEntry.Next;

GLEntry.Next := GLEntry.”Entry No.” + 1;

GLEntry.INSERT;

UNTIL (JnlLine.FIND(‘>’) = 0)

END;

COMMIT;

// The inserts are performed here.

Disabling Bulk Inserts

Disabling bulk inserts can be helpful when you are troubleshooting failures that occur when inserting records. To disable bulk inserts, you set the BufferedInsertEnabled parameter in the CustomSettings.config file of the Microsoft Dynamics NAV Server to FALSE.

Performance

SETAUTOCALCFIELDS Function – in Nav 2015

Ashwini Tripathi

Sets the FlowFields that you specify to be automatically calculated when the record is retrieved from the database.

[OK :=] Record.SETAUTOCALCFIELDS([Field1,Field2, …])

To remove the automatic calculation setting on a field that you previously set with the SETAUTOCALCFIELDS function, you must call SETAUTOCALCFIELDS without parameters.

If you omit the optional return value and if the record cannot be set to automatically calculate, then a run-time error occurs.

If you include a return value, then you must handle any errors.

You can improve performance by using the SETAUTOCALCFIELDS function before looping through records with FlowFields instead of calling the CALCFIELDS function on each record in the loop.

The automatic calculation setting applies to the fields that you specify until the Record parameter goes out of scope or until you call SETAUTOCALCFIELDS without any parameters.

The fields that you specify in the Field parameters are added to any fields that you previously set with the SETAUTOCALCFIELDS function. Previously set fields are not overwritten when you call SETAUTOCALCFIELDS with different parameters. To clear the list of fields that are automatically calculated, call the SETAUTOCALCFIELDS function without parameters.

If you assign a record to another record variable, then the automatic calculation setting on FlowFields is not transferred with the record.

If you copy a record by using the COPY Function (Record), then the automatic calculation setting on FlowFields is copied with the record.

The Rec and xRec system variables are set to always automatically calculate FlowFields. If you call the NEXT function on Rec or xRec, then FlowFields in the record are automatically calculated.

Below example show how to use the CALCFIELDS function.

// Using CALCFIELDS

IF (Customer.FIND(’-’)=true) THEN

REPEAT

Customer.CALCFIELDS(Balance,”Net Change”);

… // your other codes for processing.

UNTIL (Customer.NEXT=0)

Below example show how you can use the SETAUTOCALCFIELDS function instead of the CALCFIELDS function to improve performance.

// Using SETAUTOCALCFIELDS

Customer.SETAUTOCALCFIELDS(Balance,”Net Change”);

IF (Customer.FIND(’-’)=true) THEN

REPEAT

// Customer.Balance and Customer.”Net Change” have been auto calculated.

… //your rest of the code for processing.

UNTIL (Customer.NEXT=0)

The following example shows how to add fields to the list of automatically calculated FlowFields and how to clear the list of automatically calculated FlowFields

Customer.SETAUTOCALCFIELDS(Balance);

Customer.SETAUTOCALCFIELDS(“Net Change”);

// Above lines are equivalent to the following:

Customer.SETAUTOCALCFIELDS(Balance,”Net Change”);

// To clear the list of automatically calculated FlowFields so that no fields are calculated automatically, use the following code.

Customer.SETAUTOCALCFIELDS();

It is a common task to retrieve data and request calculation of associated FlowFields. The following example traverses customer records, calculates the balance, and marks the customer as blocked if the customer exceeds the maximum credit limit.

Note the below Customer record and associated fields are imaginary.

IF Customer.FINDSET() THEN

REPEAT

Customer.CALCFIELDS(Customer.Balance)

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END  ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

In Microsoft Dynamics NAV 2015, you can do this much faster. First, we set a filter on the customer.

This could also be done in Microsoft Dynamics NAV 2009, but behind the scenes the same code as mentioned earlier would be executed.

In Microsoft Dynamics NAV 2015, setting a filter on a record is translated into a single SQL statement.

Customer.SETFILTER(Customer.Balance,’>%1’, LargeCredit);

IF Customer.FINDSET() THEN REPEAT

Customer.CALCFIELDS(Customer.Balance)

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END   ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

In the previous example, an extra call to CALCFIELDS still must be issued for the code to be able to check the value of Customer.Balance.

In Microsoft Dynamics NAV 2015, you can optimize this further by using the new SETAUTOCALCFIELDS function.

Customer.SETFILTER(Customer.Balance,’>%1’, LargeCredit);

Customer.SETAUTOCALCFIELDS(Customer.Balance)

IF Customer.FINDSET() THEN REPEAT

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END   ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

Development Tips

How to list Virtual Tables in Navision

Ashwini Tripathi

You may have seen several posts listing the Virtual tables. Most of they say you create page/form and type in one by one table id in Range (2000000001.. 2000000200) and find the Virtual Tables and note them.

Today I will demo you easy way for getting this list with no additional efforts.

You will get below table list in every posts. I have listed tables below in Nav-2015.

Object Designer Virtual Itself from Virtual Table
Type ID Name   Object Type Object ID Object Name
Yes Table 2000000001 Object
1 2000000004 Permission Set Table 2000000004 Permission Set
1 2000000005 Permission Table 2000000005 Permission
1 2000000006 Company Table 2000000006 Company
Yes Table 2000000007 Date
Yes Table 2000000009 Session
Yes Table 2000000020 Drive
Yes Table 2000000022 File
Yes Table 2000000026 Integer
Yes Table 2000000028 Table Information
Yes Table 2000000029 System Object
Yes Table 2000000038 AllObj
Yes Table 2000000039 Printer
Yes Table 2000000040 License Information
Yes Table 2000000041 Field
Yes Table 2000000043 License Permission
Yes Table 2000000044 Permission Range
Yes Table 2000000045 Windows Language
Yes Table 2000000048 Database
Yes Table 2000000049 Code Coverage
1 2000000053 Access Control Table 2000000053 Access Control
Yes Table 2000000055 SID – Account ID
Yes Table 2000000058 AllObjWithCaption
Yes Table 2000000063 Key
1 2000000065 Send-To Program Table 2000000065 Send-To Program
1 2000000066 Style Sheet Table 2000000066 Style Sheet
1 2000000067 User Default Style Sheet Table 2000000067 User Default Style Sheet
1 2000000068 Record Link Table 2000000068 Record Link
1 2000000069 Client Add-in Table 2000000069 Client Add-in
1 2000000071 Object Metadata Table 2000000071 Object Metadata
1 2000000072 Profile Table 2000000072 Profile
1 2000000073 User Personalization Table 2000000073 User Personalization
1 2000000074 Profile Metadata Table 2000000074 Profile Metadata
1 2000000075 User Metadata Table 2000000075 User Metadata
1 2000000076 Web Service Table 2000000076 Web Service
1 2000000078 Chart Table 2000000078 Chart
1 2000000079 Object Tracking Table 2000000079 Object Tracking
1 2000000080 Page Data Personalization Table 2000000080 Page Data Personalization
1 2000000100 Debugger Breakpoint Table 2000000100 Debugger Breakpoint
Yes Table 2000000101 Debugger Call Stack
Yes Table 2000000102 Debugger Variable
Yes Table 2000000103 Debugger Watch Value
1 2000000104 Debugger Watch Table 2000000104 Debugger Watch
1 2000000110 Active Session Table 2000000110 Active Session
1 2000000111 Session Event Table 2000000111 Session Event
1 2000000112 Server Instance Table 2000000112 Server Instance
1 2000000114 Document Service Table 2000000114 Document Service
1 2000000120 User Table 2000000120 User
1 2000000121 User Property Table 2000000121 User Property
1 2000000130 Device Table 2000000130 Device
Yes Table 2000000135 Table Synch. Setup

All marked as Yes in above table under Virtual column are all the Virtual Tables.

I have not type one by one in the Page/form to get this list.

The Virtual table itself in Navision lists the Virtual tables in Navision. List can be different depending on the Version of Navision you see them.

Today I will share you smart way of finding this list of Virtual tables.

Next time you need not to do so hard work to list all the Virtual tables.

Here too you need to create a list page but you will not be required to key in the id of tables one by one, in fact only one table id which will give answers to your query.
VirtualTables-1
Save and execute your Page/Form.

VirtualTables-2
Now go to object Designer. Copy all the tables with id in range defined above and paste in Excel.

Copy above table list in Excel side by side and compare as above table shown in beginning of the post. All those which don’t match are your Virtual tables.

So easy, if anything is available why to waste effort for same.

It always good to share what you know with community, by this way we keep learning from each other.

Stay tuned for many such Tips.

Development Tips

Configuring Web Services to Use SSL (SOAP and OData)

Ashwini Tripathi

Secure Sockets Layer (SSL) is a cryptographic protocol that helps provide security and data integrity for data communications over a network.

By encrypting your Microsoft Dynamics NAV web services using SSL, you make your data and the network more secure and more reliable.

Microsoft Dynamics NAV Web Services and SSL

Microsoft Dynamics NAV supports SSL authentication for SOAP and OData web services.

The server authenticates itself to the client, but the client does not authenticate itself to the server.

When the web service client connects to the Microsoft Dynamics NAV Server instance, the server replies by sending its digital certificate to the client. This certificate contains the server’s public encryption key and the name of the authority that granted the certificate. The client verifies the certificate using the authority’s public key.

We have covered above top related to Creating and Implementing Certificates for Microsoft Dynamics NAV Server. If not seen please follow below links for better understanding:

How to create a root CA and a private key file by using the makecert.exe utility

How to create certificate revocation list for the root certification authority

How to create and install a test certificate for the Microsoft Dynamics NAV Server computer

How to grant access to the certificate’s private key to the service account for the Microsoft Dynamics NAV Server

Implementing Security Certificates.

Configuring Microsoft Dynamics NAV Web Services to Use SSL

The first step is to prepare Microsoft Dynamics NAV to use SSL. This involves configuring the relevant Microsoft Dynamics NAV Server instance to specify SSL.

The following procedure uses Microsoft Dynamics NAV Server Administration tool to configure the Microsoft Dynamics NAV Server instance.

  • Choose the Start button, and in the Search programs and files box, type Microsoft Dynamics NAV Administration, and then choose the related link.
  • In Microsoft Dynamics NAV Server Administration tool, in the left pane, under Console Root, expand the node for the Microsoft Dynamics NAV Server computer. This is typically named Microsoft Dynamics NAV (Local), which is the local computer.

The Microsoft Dynamics NAV Server instances on the computer appear in the left pane and center panes.

In the left pane, choose the relevant instance to display settings for that instance in the center pane.

UseSSL-1

  • At the bottom of the center pane, choose Edit.
  • To configure SOAP web services to use SSL, expand the SOAP Web Services tab, and then select the Enable SSL

Make a note of the port that is used by SOAP web services. You will need the port number later on in this walkthrough.

UseSSL-2

  • To configure OData web services to use SSL, expand the OData Web Services tab, and then select the Enable SSL

Make a note of the port that is used by OData web services. You will need the port number later on in this walkthrough
UseSSL-3

  • Choose Save at the bottom of the center pane.
  • In the right pane, under Actions, choose Login Account.
  • Make a note of the service account information. You will need this information later on in this walkthrough.

UseSSL-4

Configuring the Access Control List and the Web Services Ports for SSL

An access control list (ACL) is part of the Windows security infrastructure and features. The ACL controls who can access resources on a computer.

Note

These procedures use the netsh tool (netsh.exe) for configuring the HTTP server. The netsh tool is supplied with Windows 7 and Windows Server 2008.

By default the netsh tool is located in the c:\Windows\System32 folder.

To configure the ACL

  1. On the computer running Microsoft Dynamics NAV Server, open a command prompt as an administrator as follows:
    1. From the Start menu, choose All Programs, and then choose Accessories.
    2. Right-click Command Prompt, and then choose Run as administrator.
  1. At the command prompt, type the following command to change to the Windows\System32 directory.

cd\Windows\System32

  1. To view the ACL for the web services ports to determine if any entries are already using the relevant host name and port, type the following command:

netsh http show urlacl

  1. Each entry is listed by the Reserved URL field, which has the format http://hostname:port. hostname is the name of the computer running the service and port is the port number the service runs on. A ‘+’ (plus sign) in the Reserved URL field represents localhost, which indicates the computer that you are working on.
  2. By default, SOAP and OData web services in Microsoft Dynamics NAV use ports 7047 and 7048, respectively, and connect to a Microsoft Dynamics NAV Server instance named DynamicsNAV80. The default reserved URL entries for these services are: http://+:7047/DynamicsNAV80/ and http://+:7048/DynamicsNAV80/.
  3. You must delete any entries that use the same port as the SOAP or OData web services.

To delete an entry, type the following command:

netsh http delete urlacl url=http://hostname:Port/NAVserver/

Substitute Port with the port number of the SOAP or OData web service and NAVserver with the name of the Microsoft Dynamics NAV Server instance.

For example, to delete the default entries for SOAP and OData, use the following two commands:

netsh http delete urlacl url=http://+:7047/DynamicsNAV80/

netsh http delete urlacl url=http://+:7048/DynamicsNAV80/

  1. To register the ports for the SOAP and OData web service with https, type the following command for each service:

netsh http add urlacl url=https://hostname:port/navserver user=DOMAIN\user

  1. Substitute the following options with the proper values:
Option Description
hostname The name of the computer running Microsoft Dynamics NAV Server. Use + for localhost.
port The port that is used by the web service.
navserver The name of the Microsoft Dynamics NAV Server instance to use with the web service. The default is DynamicsNAV80.
DOMAIN\username The domain and user name of the service account for Microsoft Dynamics NAV Server. If the service account for Microsoft Dynamics NAV Server is Network Service, then use “NT AUTHORITY\NETWORK SERVICE”.
  1. For example, if the service account for Microsoft Dynamics NAV Server has the domain ABC and the user name xyz, and then the command for the SOAP web service is as follows:

netsh http add urlacl url=https://+:7047/DynamicsNAV80 user=”NT AUTHORITY\NETWORK SERVICE”

  1. If the service account for Microsoft Dynamics NAV Server is Network Service, then the command is as follows:

netsh http add urlacl url=https://+:7047/DynamicsNAV80 user=”NT AUTHORITY\NETWORK SERVICE”

  1. To verify that your port has been registered, repeat step 3.

To configure the port to use the SSL certificate

  1. At the command prompt, type the following command to view the current port configurations:

netsh http show sslcert

  1. All port bindings to SSL certificates are listed. Each entry is indicated by the IP:port field, where IP is the IP address that uses the port (0.0.0.0 indicates all IP addresses) and port is the port number.
  2. If SSL is already configured on the address and port that you want to use for SOAP or OData web services, then use the following command to delete the entry:

netsh http delete ssl ipport=ipaddress:port

  1. Substitute ipaddress and port with the IP address and port number of the entry that you want to delete.
  2. For example, to delete the entry on the default SOAP port, use the following command:

netsh http delete ssl ipport=0.0.0.0:7047

  1. To bind an SSL certificate to the SOAP or OData web service port, use the following command:

netsh http add sslcert ipport=ipaddress:port certhash=thumbprint appid={id}

  1. Substitute the following options with the proper values:
Option Description
ipaddress The IP address that can use the web service. Use 0.0.0.0 to include all IP addresses.
port The port that is used by the web service.
thumbprint The certificate thumbprint that you obtained in previous post.
id A 32-digit hexadecimal number that identifies the Microsoft Dynamics NAV application. The id must be in the format {NNNNNNNN-NNNN-NNNN-NNNN-NNNNNNNNNNNN). If you do not know the appid, then use any value, such as {00112233-4455-6677-8899-AABBCCDDEEFF}.
  1. For example, the following command binds a certificate that has the thumbprint c0d0f27095b03d4317e219841024328cef248779 to port 7047 for all IP addresses:

netsh http add sslcert ipport=0.0.0.0:7047 certhash=c0d0f27095b03d4317e219841024328cef248779 appid={00112233-4455-6677-8899-AABBCCDDEEFF}

Imp Note: – Please replace the port and thumbprint values with your valid values.

Restart the Microsoft Dynamics NAV Server Instance

You can use the Microsoft Dynamics NAV Server Administration Tool to restart the Microsoft Dynamics NAV Server instance.

To restart Microsoft Dynamics NAV Server

  1. In Microsoft Dynamics NAV Server Administration tool, in the left pane, under Console Root, choose the Microsoft Dynamics NAV Server computer.
  2. In the center pane, choose the Microsoft Dynamics NAV Server instance that you have configured, and then in the right pane under Actions, choose Restart.
  3. After the instance restarts, close Microsoft Dynamics NAV Server Administration tool.

Verifying the Configuration

You should now be able to use web services that are encrypted with SSL. To verify this, type the following URL in the address bar for your browser.

https://localhost:7047/DynamicsNAV80/WS/services

The page lists any web services that have been published.

Development Tips, Office Integration, Report

Using Automation to Write a Letter in Microsoft Office Word

Ashwini Tripathi

Automation lets you use the capabilities and features of Microsoft Office products, such as Microsoft Word or Microsoft Excel, in your Microsoft Dynamics NAV application.

Today we will implement Word Automation from a customer card in the Microsoft Dynamics NAV Windows client.

Note: The Microsoft Dynamics NAV Web client does not support automation.

Most information that we need to transfer to Word for this example is in the Customer table. The Customer table contains a FlowField called Sales (LCY) that contains the aggregated sales for the customer.

In this example we are learning about Automation, so we will use the existing value. In a real customer installation, we would need to set up an appropriate date filter to get the sales for the past year only.

We also need to retrieve the information about our own company that we will use in the letterhead and in the greeting of the letter. This information is contained in the Company Information and User tables.

  • The Automation server must be installed on the computer that compiles an object that uses Automation. If you must recompile and modify an object on a computer that does not have the Automation server installed, then you must modify the code to compile it again. We recommend that you isolate code that uses Automation in separate codeunits.
  • Performance can be an issue if extra work is needed to create an Automation server with the CREATE system call. If the Automation server is to be used repeatedly, then you will gain better performance by designing your code so that the server is created only once instead of making multiple CREATE and CLEAR calls).

Performance can be improved by putting the code on the customer card because you do not have to open and close Word for each letter that is created in the session.

You can work around this problem. If Word is already open when it is called from the code, then the running instance is reused. You can manually open Word or do not close Word after creating the first letter.

We will extract and transfer data one customer at a time. We will also initiate this processing and the subsequent processing in Word from the customer card.

We will insert fields into the Word template and give these fields convenient mnemonic names that correspond to the names of the record fields that we are using.

To make this work, C/AL code must make two extra calls to Microsoft Office Word. You must call the ActiveDocument.Fields.Update method before using the fields. After you have transferred all the information, you must call the ActiveDocument.Fields.Unlink method. This ensures that you can successfully use the Word fields as placeholders.

In addition, while you can name the Customer or Address fields, you must reference them by indexing into the Fields collection of the document. This can make the C/AL code harder to understand.

Creating the Word Template for Use by Automation

First, task is to create a Word template that we will use to create letters to customers that qualify for a discount. To create the template, we will add mail merge fields for displaying data that is extracted from Microsoft Dynamics NAV that you want included in the customer letter, such as the customer’s name, contact, and total sales.

You will create and save the template on the computer running the Microsoft Dynamics NAV Windows client, because you will configure the automation object to run on the client.

  • On the computer running Microsoft Dynamics NAV Windows client, open Word and create a new document.

WordAutomation-1

  • Choose where you want to insert the fields. Then, on the Insert tab, in the Text group, choose Quick Parts, and then choose Field.

WordAutomation-2

  • In the Categories list, select Mail Merge.
  • In the Field names list, select MergeField.
  • In the Field Name box under Field Properties, type Contact. This field will display the name of your contact person at the customer site as taken from the Customer table.
  • Choose OK to add the field.

WordAutomation-3

  • Repeat steps as above to add the remaining fields as follows:
Field name Description Underlying table
Name The name of the customer. Customer
Address The address of the customer. Customer
Sales (LCY) The total amount that the customer has purchased from you. Customer
Company Name The name of your company. Company Information
  • Save the Word document as a template with the name Discount.dotx in folder of your choice.

WordAutomation-4

Creating the Codeunit and Declaring the Variables

The next step is to create the codeunit that calls Word and creates the letter.

To create the codeunit

  • In Object Designer, choose Codeunit, and then choose the New button to create a new codeunit.
  • On the View menu, choose Properties to open the Properties window of the codeunit.
  • In the TableNo field, choose the AssistEdit button to open the Table List window.
  • In the Table List window, select the Customer table, and then choose OK.

WordAutomation-5

  • Close the Properties window.

To declare the variables

  • Choose the OnRun Trigger and on the View menu, choose C/AL Locals, and then choose the Variables tab.
  • On a blank line, type wdApp in the Name field and set the Data Type field to Automation.

Note

When you create an Automation variable, some hidden events are also created for it. If you want to delete the variable, be aware that the events are also not deleted. This can cause issues if you then create a variable with the same name.

  • In the Subtype field, choose the AssistEdit button. The Automation Object List window is displayed.
  • In the Automation Server field, choose the AssistEdit button.
  • In the Automation Server List, select Microsoft Word 15.0 Object Library if you are running Word 2013, or select Microsoft Word 14.0 Object Library if you are running Word 2010, and then choose OK.
  • From the list of classes in the Automation Object List, select the Application class, and then choose OK.

WordAutomation-6

  • Repeat steps above to add the following two Automation variables:
Name Data type Subtype Class
wdDoc Automation Microsoft Word 14.0/15.0 Object Library Document
wdRange Automation Microsoft Word 14.0/15.0 Object Library Range
  • Add the following variables.
Name Data type Subtype Length
CompanyInfo Record Company Information
TemplateName Text 250
  • Close the C/AL Locals window.

Writing the C/AL Code

Before you start writing the C/AL code that uses Automation, you must do some initial processing. You start by calculating the Sales (LCY) FlowField. Then, you check whether the customer qualifies for a discount. Finally, you retrieve the information from the Company Information and User tables that you use to fill in some of the fields in the letter.

To write the C/AL code

  • In the C/AL Editor, add the following lines of code to the OnRun section.
  CALCFIELDS(“Sales (LCY)”);CompanyInfo.GET;
  • To create an instance of Word before using it, enter the following line of code.
 CREATE(wdApp, FALSE, TRUE);
  • This statement creates the Automation object with the wdApp variable.
    1. The first Boolean parameter in the statement (FALSE) tells the CREATE function to try to reuse an already running instance of the Automation server that is referenced by Automation before creating a new instance. If you change this to TRUE, then the CREATE function always creates a new instance of the Automation server.
    2. The second Boolean parameter in the statement creates the Automation object on the client. This is necessary to use this codeunit on a page in the Microsoft Dynamics NAV Windows client.
  • Enter the following lines of code to add a new document to Word that uses the template that you designed earlier. If required, replace C:\Users\atripathi5283\Desktop\Nav-2015\Word Letter with the correct folder path to the template that you defined in the procedure.
 TemplateName := C:\Users\atripathi5283\Desktop\Nav-2015\Word Letter\Discount.dotx’;wdDoc := wdApp.Documents.Add(TemplateName);wdApp.ActiveDocument.Fields.Update;
  • Because the Add method of the Documents collection requires that you pass the path to the template by reference, you must set up the TemplateName variable to hold this information. You will get a compilation error if you put the path into the call as a literal string.
  • The Documents property returns a Documents collection that represents all open documents. You can also see that the Documents collection object has an Add method, and that the Add method has the following syntax.
  • expression.Add(Template, NewTemplate, Document Type, Visible)
  • expression is a required argument, and it must be an expression that returns a Documents object. All the arguments are optional. You will use Template to open a new document that is based on your template.
  • For the syntax in the C/AL Symbol Menu, note that the Documents property returns an object of type DOCUMENTS, which is a user-defined type. The property returns a Documents class or IDispatch interface. This information helps the compiler perform a better type check during compilation. The following statement can also pass both the compile-time and the run-time type checks.
  • wdDoc := wdApp.Documents.Add(TemplateName);
  • Finally, the Add method returns a Document class. While you did not need to declare a C/AL variable for the interim Documents class, you have declared a variable for the wdDoc return value,.
  • The third line contains a call that must be made to ensure that the template works as intended.
  • wdApp.ActiveDocument.Fields.Update;

Transferring Data to Word

Now you can transfer the actual data from the Customer record to the placeholder fields in the Word document.

You have set up the first three fields in the template so that they can contain the contact, name, and address of the customer and you can transfer the data.

To transfer data to Word

  • Transfer the data by adding the following lines of code.
 wdRange := wdAPP.ActiveDocument.Fields.Item(1).Result; wdRange.Text := Contact; wdRange.Bold := 1; wdRange := wdAPP.ActiveDocument.Fields.Item(2).Result; wdRange.Text := Name; wdRange.Bold := 1; wdRange := wdAPP.ActiveDocument.Fields.Item(3).Result; wdRange.Text := Address; wdRange.Bold := 1;
  • You cannot use the fields directly as variables and make an assignment such as Fields.Item(3) := Address. Instead, you use the Result property of the field. This property returns the result of the field as a range. You place this range in the wdRange Automation variable that you declared.
  • You then set the Text property of the range to the desired values, which is the name of your contact person and the name and address of the customer. Finally, you add bold formatting.
  • The data you are transferring must be in text format. If it is not in text format, then you get a compilation error. wdRange.Text expects arguments to be of type BSTR, which maps to either Text or Code. This means that any data that is not Text or Code must be converted before it is passed to Word. To convert a field to Text, you use the FORMAT function. All the fields that are transferred in this step are in text format, so no conversion is needed and the FORMAT function is not used. However, in this example, you also need to transfer the Sales (LCY) field, which is a Decimal field. To see how to convert the Sales (LCY) field, go to the next step.
  • To transfer and format the data from the Sales (LCY) field, add the following code.
 wdRange := wdAPP.ActiveDocument.Fields.Item(4).Result;wdRange.Text := FORMAT(“Sales (LCY)”);wdRange.Bold := 0;
  • To transfer the information from the Company Information table, add the following code.
 wdRange := wdApp.ActiveDocument.Fields.Item(5).Result;wdRange.Text := CompanyInfo.Name;
  • To complete the processing in Word, add the following code.
 wdApp.Visible := TRUE;wdApp.ActiveDocument.Fields.Unlink;
  • The first statement opens Word and shows you the letter that was created. The second statement makes the fields work as placeholders.

WordAutomation-7

  • Save and compile the codeunit

To-Do List

Although this code will work, you must add a few things to make it complete:

  • We recommend that you do not use a hardcoded template name. You should keep the template name in a table, and the user should select it from a page. You can then have different templates for different types of letters that you want to send to your customers.
  • You should add some error-handling code. For example, the CREATE call fails if the user does not have Word installed or if the installation has been corrupted. You should check the return value of CREATE and give an appropriate message if it fails.
  • The user should get a message if the customer does not qualify for the discount. In the example, the codeunit closes without any message.

Calling the Codeunit from the Customer Card

The final task is to ensure that you can call the codeunit from the Customer Card page in the Microsoft Dynamics NAV Windows client.

To call the codeunit from the Customer card page in the Microsoft Dynamics NAV Windows client

  • Open Object Designer, and then choose Page.
  • Select the Customer Card page and then choose Design.
  • On the View menu, choose Page Actions.
  • To add a new action, locate the action container with the subtype set to ActionItems.
  • Right-click the next line after the ActionItems container, and then choose New.
  • In the Caption field of the new line, type Word Letter.
  • Set the Type field to Action.
  • With the new action selected, on the View menu, choose Properties.
  • In the RunObject field, type codeunit Discount Letter.

Note

If you saved the codeunit that you created in the previous procedure under a different name, then substitute Discount Letter with the name that you used.

  • Use the arrow buttons to make sure that the new action is indented only once from the ActionItems container above it

WordAutomation-8

  • Save and compile the Customer Card page.

To run the Customer Card and view the Word letter

  1. In Object Designer, choose the Page
  2. Select the Customer Card page, and then choose Run.
  3. In the ribbon, on the Actions tab, choose the Word Letter

The letter document opens in Word.

WordAutomation-9

Next Steps

The letter that you have just created only contains five fields and sample body text. Before you can use this letter in an actual situation, you will need to add some more fields, such as the name and address of your own company, the date, and the currency code, and the main text of the letter. It will also need some formatting to make it look more attractive. If you alter the order in which the fields appear in the template, you must change the numbering of the fields in the codeunit to ensure that the correct data is inserted into the appropriate fields.