Export (0) Print
Expand All

Walkthrough: Creating a Dynamic Dataport

Microsoft Dynamics Nav 2009

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.

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

  1. Click Tools, and then click Object Designer.

  2. In Object Designer, click Dataport, and then click New.

  3. In the Properties window for the new dataport, set the following properties.

    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.

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

  1. In Dataport Designer, in the first DataItem field, click the AssistButton, and then select the Item table.

  2. In the Properties window of the Item data item, in the DataItemTableView property, click the AssistButton.

  3. 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.

  4. Click View, and then click C/AL Globals.

  5. 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

  1. In Dataport Designer, select the Item data item, click View, and then click Dataport Fields.

  2. 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.

  3. Repeat step 2 to add the Description and Unit Price fields.

  4. 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

  5. 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.

    IF 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.

  6. 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.

    IF "Tariff No." <> '' THEN
    BEGIN
      "Tariff Number"."No." := "Tariff No.";
      "Tariff Number".FIND;
    END
    ELSE
      "Tariff Number".Description := 'NO TARIFF NUMBER';
    
  7. Save and compile the dataport.

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

  1. 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.

  2. Save changes to the 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

  1. Click File, and then click Run to run the dataport.

  2. In the request form, set the following properties.

    Property Value

    FileName

    Prices.csv

    Direction

    Export

  3. Click OK.

    The dataport runs, and the records that match the criteria you have set up are written to a file called Prices.csv.

  4. 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

  5. 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.

  6. Save the changed file as NewPrices.csv.

  7. 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

    NoteImportant

    The 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.

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.

Community Additions

ADD
Show:
© 2014 Microsoft