In this walkthrough you will learn how to create a dataport that can be used to export data as well as import data. The dataport can update prices in the Item table in an external program such as Microsoft Office Excel.
About This Walkthrough
This walkthrough provides an overview of how to create a dataport with Microsoft Dynamics NAV. The walkthrough illustrates the following tasks:
-
Creating a dataport that can export data from the Item table to an external file.
-
Modifying the dataport to import data into the Item table from an external file.
Prerequisites
To complete this walkthrough, you will need:
-
Microsoft Dynamics NAV installed with a developer license.
-
The CRONUS International Ltd. demo data company installed.
-
An external editor, such as Microsoft Excel, which can open a .csv file.
To create a dataport
-
Click Tools, and then click Object Designer.
-
In Object Designer, click Dataport, and then click New.
-
In the Properties window for the new dataport, set the following properties.
Property Value Variable
"
"
;
In this walkthrough, the format of the external file is variable and uses a semicolon as the field separator. This is because the external editor is Microsoft Excel, and for this use, a .csv file is appropriate.
You have created a dataport and must now specify which tables and fields will be used in the dataport.
Enabling the Dataport to Export
Data
In the previous section, you created a dataport. Now, you will add a data item for the Item table.
To add a data item for the Item table
-
In Dataport Designer, in the first DataItem field, click the AssistButton, and then select the Item table.
-
In the Properties window of the Item data item, in the DataItemTableView property, click the AssistButton.
-
In the Table View window, in the Key field, click the AssistButton, and in the Key List window, select the No. field.
In this walkthrough, you will not use Table Filter field to set a filter even though you want to export a subset of the records in the Item table. Instead, you will set the filter in C/AL code in the next procedure.
-
Click View, and then click C/AL Globals.
-
In the C/AL Globals window, create a global variable called Tariff Number of data type Record, with the Tariff Number table as the subtype.
Next, you add fields to the data item.
To add fields to the Item data item
-
In Dataport Designer, select the Item data item, click View, and then click Dataport Fields.
-
In Field Designer, in the first row, in the SourceExpr field, click the AssistButton, and then in the Field List window, select the No. field.
-
Repeat step 2 to add the Description and Unit Price fields.
-
In the fourth row, in the SourceExpr field, type "Tariff Number".Description.
This adds the Description field to the data item by using the Tariff Number global variable.
In Field Designer, the StartPos Property and Width Property (Dataport field) are automatically set for some of the fields. You can calculate the appropriate values by looking up the length of each field. The first field always has a start position of 1. The Unit Price field is of data type Decimal and its length must therefore be 12.
For more information, see Properties for a Field on a Dataport Data Item.
The following table lists the values for each of the fields.
SourceExpr StartPos Width "No."
1
20
"Description"
22
30
"Unit Price"
53
12
"Tariff Number".Description
66
50
-
To set the filter that will select the records between numbers 1000 and 2000 where the Gen. Prod. Posting Group is RETAIL, open the C/AL editor, and enter the following code in the OnPreDataItem Trigger of the Item data item.
Copy CodeIF NOT CurrDataport.IMPORT THEN Item.SETRANGE("Gen. Prod. Posting Group",’RETAIL'); Item.SETRANGE("No.",’1000',’2000’);The filter is set only if the dataport is used to export. The reason for using this construction instead of setting a table filter in the DataItemTableView Property is that if you are placing the filter on a field that is not being exported with the other data. If you had used the TableFilter Data Type, the filter would always be set, including when the dataport is used to import data. However, because the Gen. Prod. Posting Group field is not in the file that you import, no records will be imported. If the field that you use for filtering is exported and imported, you could use the TableFilter field in the Table View window to set the filter.
-
To retrieve the text from the Tariff Number table and to export it, enter the following code in the OnBeforeExportRecord Trigger of the data item.
Copy CodeIF "Tariff No." <> '' THEN BEGIN "Tariff Number"."No." := "Tariff No."; "Tariff Number".FIND; END ELSE "Tariff Number".Description := 'NO TARIFF NUMBER';
-
Save and compile the dataport.
Enabling the Dataport to Import
Data
In the previous section, you created a dataport and enabled it for exporting data. Now, you will enable the dataport to import prices that have been modified in an external file.
To enable the dataport to import modified prices
-
In Dataport Designer, select the Item data item, and in the Properties window for the data item, set the following properties.
Property Value AutoSave
Yes
AutoUpdate
Yes
These settings update the existing records with the data that is different in the imported records, in this case, the unit price. For more information, see Properties for a Data Item in a Dataport.
-
Save changes to the dataport.
Testing the Dynamic Dataport
You have created a dataport that can export data from the Item table, and which can import modified data into the Item table. Now, you can test that it works as expected.
To test the dataport
-
Click File, and then click Run to run the dataport.
-
In the request form, set the following properties.
Property Value FileName
Prices.csv
Direction
Export
-
Click OK.
The dataport runs, and the records that match the criteria you have set up are written to a file called Prices.csv.
-
Open the Prices.csv file in Microsoft Excel.
The following table illustrates the result of exporting the data.
1000
Bicycle
4,000
1001
Touring Bicycle
4,000
1100
Front Wheel
1,000
1110
Rim
0
1150
Front Hub
500
1200
Back Wheel
1,200
1250
Back Hub
1,100
1300
Chain Assy
800
1310
Chain
0
1700
Brake
600
1710
Hand rear wheel Brake
0
-
In Microsoft Excel, change some of the unit prices.
The following table illustrates how you can change prices.
1000
Bicycle
4,500
1001
Touring Bicycle
4,000
1100
Front Wheel
1,000
1110
Rim
10
1150
Front Hub
500
1200
Back Wheel
1,200
1250
Back Hub
1,100
1300
Chain Assy
800
1310
Chain
20
1700
Brake
600
1710
Hand rear wheel Brake
20
In the example, the prices for items 1000, 1110, 1310, and 1710 have been changed.
-
Save the changed file as NewPrices.csv.
-
To import the new data, run the dataport and fill out the request form as shown in the following table.
Property Value FileName
NewPrices.csv
Direction
Import
ImportantThe records are not imported if you run the dataport from inside Dataport Designer. You must run it from, for example, Object Designer.
The item unit prices are updated according to the changes that you made in Microsoft Excel.
Next Steps
For this example, the price of each unit is used to calculate the Profit % field in the Item table when the field is validated. You have not used the CallFieldValidate Property to enforce that evaluation, but have left it at the default setting of No. Getting the validation to work as intended is not so easy, because the code that is triggered uses values from other fields that are not part of this dataport. At the time of the validation, these fields do not contain any values because they are updated later.
One way to solve this problem would be to export all the fields but this would make it harder to manage in Microsoft Excel. This problem shows that you must give careful consideration to all the interdependent data when you update a table from a dataport. The solution to the problem will, however, be different for each table and for each set of fields that are imported.
See Also
© 2010 Microsoft Corporation. All rights reserved.