NAV Team: How to: Enable Import/Export of Bank or Payroll Files Using the Data Exchange Framework

[Blog bot]

The formats of files for exchange of bank or payroll data with ERP systems vary depending on the supplier of the file and on the
country/region. The generic version of Microsoft Dynamics NAV does not support local bank or payroll file formats out-of-the-box. Microsoft Dynamics NAV
therefore includes a data exchange framework that makes it easy for partners to enable users to import or export bank and payroll files in any format. For more
information, see “Microsoft Dynamics NAV R2 Data Exchange Framework - Design Insights” on Microsoft Dynamics PartnerSource.

In the Posting Exchange Definition and the Posting Exchange Mapping windows, you describe the formatting of a bank or payroll file and which columns in the file map to which fields in Microsoft Dynamics NAV. When such a data exchange setup is created and activated in the framework, users can choose the related file format to start import or export of the bank files in question.

Note: This blog post is accompanied by the Data Exchange Framework Files.zip, which contains the page objects that make up the UI to the Data Exchange Framework. The page objects will be generally available, in a
different number range, in Microsoft Dynamics NAV R2 Feature Pack 1.

This topic includes the following procedures:

• To implement page objects published on NAV Team Blog
• To make the required actions and setup fields available to users
• To create a data exchange setup
• To export a data exchange setup as an xml file for use by others
• To use an XML file with an existing data exchange setup
• To activate a data exchange setup

To implement page objects published on NAV Team Blog

1. Download DataExchangeSetupPages.txt to an appropriate location.
2. Open Microsoft Dynamics NAV Development Environment.
3. On the toolbar, choose File, and then choose Import.
4. Select DataExchangeSetupPages.txt, and then choose Open.
5. Select DataExchangeSetupPages.txt, choose Tools, and then choose Compile.
6. In the Object Designer, find MenuSuite 1010, and then choose Design.
7. In the Navigation Pane, choose the Administration button.
8. Choose Application Setup, and then choose General.
9. Right-click the General node, and then select Create Item.
10. In the Create Item window, fill the fields as described in the following table.
    
    Field
    
    Description
    
    Object Type
    
    Page
    
    Object ID
    
    1211
    
    Caption
    
    Posting Exchange Definitions
    
    CaptionML
    
    ENU=Posting Exchange Definitions
    
    Department
    
    Lists
11. Choose the OK button.
12. Select MenuSuite 1010, choose Tools, and then choose Compile.
13. Restart Microsoft Dynamics NAV.

You can now find the Posting Exchange Definitions page with the Search function.


To make the required actions and setup fields visible to users
Because no bank or payroll file formats are supported out-of-the-box, the actions and setup fields for the import functionality are hidden by default. As a first step, you must make the Import Bank Statement, Import Payroll Transaction, and Bank Statement Details actions visible in the General Journal window and you must make the Import Bank Statement and Bank Statement Details actions visible in the Bank Acc. Reconciliation window. In addition, you must make the Bank Statement Import Format field visible in the Bank Account Card window, and you must make the Payroll Data Import Format field visible in the General Ledger Setup window. This is described in the following procedure.

1. Open Microsoft Dynamics NAV Development Environment.
2. In the Object Designer, find page 379, Bank Acc. Reconciliation, and then choose Design.
3. Find the ImportBankStatement action and change the Visible property to TRUE.
4. Find the ShowStatementLineDetails action and change the Visible property to TRUE.
5. Save and compile the object.
6. In the Object Designer, find page 39, General Journal, and then choose Design.
7. Find the ImportBankStatement action and change the Visible property to TRUE.
8. Find the ImportPayrollTransaction action and change the Visible property to TRUE.
9. Find the ShowStatementLineDetails action and change the Visible property to TRUE.
10. Save and compile the object.
11. In the Object Designer, find page 370, Bank Account Card, and then choose Design.
12. Find the Bank Statement Import Format field and change the Visible property to TRUE.
13. Save and compile the object.
14. In the Object Designer, find page 118, General Ledger Setup, and then choose Design.
15. Find the Payroll Trans. Import Format field and change the Visible property to TRUE.

Note: You can also perform the work described above by using the Customize This Page function in the UI to add the actions and field to the pages.


To create a data exchange setup
If no data exchange setup exists to enable import or export of a specific bank or payroll file, either in Microsoft Dynamics NAV or as an external XML file, then you must manually create the setup. In the Posting Exchange Definition window, you must specify the setup and describe the line and column formatting of the file. In the Posting Exchange Mapping window, you must map columns in the file to fields in Microsoft Dynamics NAV. This is described in two following procedures.

Note: If the bank or payroll file is in xml format, then the term column in the following procedure should be interpreted as an xml element that contains data.


To specify the setup and describe the line and column formatting

1. In the Search box, enter Posting Exchange Definitions, and then choose the related link.
2. On the Home tab, in the New group, choose New.
3. On the General FastTab, describe the data exchange setup and the file type by filling the fields as described in the following table.
   
   Field
   
   Description
   
   Code
   
   Enter a code to identify the data exchange setup.
   
   Name
   
   Enter a name for the data exchange setup.
   
   Type
   
   Specify what type of exchange the data exchange setup is used for.
   
   You can select between three types:
   
    Bank Statement Import
   
    Payment Export
   
    Payroll Import
   
   File Type
   
   Specify what type of file that the data exchange setup is used for. You can select between three file types:
   •  XML: Layered strings of content and markup surrounded by tags denoting function.
   •  Variable Text: A file type where records have variable length and are separated by a character, such as comma or semi-colon. Also known as delimited.
   •  Fixed Text: A file type where records have the same length, using pad characters, and where each record is on a separate line. Also known as fixed-width.
   Processing XMLPort ID
   
   Specify through which XMLport the data in the file will be exchanged.
   
   Header Lines
   
   Specify how many header lines exist in the file. This ensures that the header data is not imported.
   
   Note: This field is only relevant for import.
   
   
   Header Tag
   
   If a header line exists in several places in the file, enter the text of the first column on the header line.
   
   This ensures that the header data is not imported.
   
   Note: This field is only relevant for import.
   
   Footer Tag
   
   If a footer line exists in several places in the file, enter the text of the first column on the footer line.
   
   This ensures that the footer data is not imported.
   
   Note: This field is only relevant for import.
   
   Column Separator
   
   Specify how columns in the file are separated, if the file is of type Variable Text.
   
   File Encoding
   
   Specify the encoding of the file.
   
   Note: This field is only relevant for import.
   
   
4. On the Posting Line Definitions FastTab, describe the formatting of lines in the file by filling the fields as described in the following table.
   
   Note: For import, you only create one line for the single format of the bank statement file that you want to import.
   For export, you can create a line for each payment type that you want to export. In that case, the Posting Column Definitions FastTab shows different columns for each payment type.
   
   
   
   Field
   
   Description
   
   Code
   
   Enter a code to identify the line in the file.
   
   Name
   
   Enter a name that describes the line in the file.
   
   Column Count
   
   Specify how many columns the line in the bank statement file has.
   
   Note: This field is only relevant for import.
5. Repeat step 4 to create a line for every payment type that you need to export.
6. On the Posting Column Definitions FastTab, choose Insert Default Definitions to prefill values for columns that are required by minimum to import a bank statement file.
   
   Note: You cannot use the Insert Default Definitions function for a data exchange setup of type Export.
7. Describe the formatting of columns in the file by filling or editing the fields as described in the following table.
   
   Field
   
   Description
   
   Column No.
   
   Specify the number that reflects the column’s position on the line in the file.
   
   For XML files, specify the number that reflects the element’s type in the file.
   
   Name
   
   Specify the name of the column.
   
   For XML files, specify the markup that marks the data to be exchanged.
   
   Data Type
   
   Specify if the data to be exchanged is of type Text, Date, or decimal.
   
   Data Format
   
   Specify the format of the data, if any. For example, MM-dd-yyyy if the data type is Date.
   
   Note: For export, specify the data format according to Microsoft Dynamics NAV. For more information, see Identifiers, Data Types, and Data Formats.
   
   Note: For import, specify the data format according to .Net. For more information, see Standard Date and Time Format Strings.
   
   Data Formatting Culture
   
   Specify the culture of the data format, if any. For example, en-US if the data type is Decimal to ensure that comma is used as the .000 separator, according to the US format. For more information, see Standard Date and Time Format Strings.
   
   Note: This field is only relevant for import.
   
   Description
   
   Enter a description of the column, for informational purposes.
   
   Constant
   
   Specify any type of data that you want to export in this column, such as extra information about the payment type.
   
   Note: This field is only relevant for export.

To map columns in the file to fields in Microsoft Dynamics NAV

1. On the Posting Line Definitions FastTab, select the line for which you want to map columns to fields, and then choose Field Mapping. The Posting Exchange Mapping window opens.
2. On the General FastTab, specify the mapping setup by filling the fields as described in the following table.
   
   <br clear="all" />FieldDescription
   
   Table ID
   
   Specify the table that holds the fields to or from which data is exchanged according to the mapping.
   
   Name
   
   Enter a name for the mapping setup.
   
   Processing Codeunit ID
   
   Specify the codeunit that is used to transfer the data in or out of Microsoft Dynamics NAV.
3. On the Field Mapping FastTab, specify which columns map to which fields in Microsoft Dynamics NAV by filling the fields as described in the following table.
   
   Field
   
   Description
   
   Column No.
   
   Specify which column in the file you want to define a map for.
   
   You can only select columns that are represented by lines on the Posting Column Definitions FastTab in the Posting Exchange Definition window.
   
   Field ID
   
   Specify which field the column in the Column No. field maps to.
   
   You can only select from fields that exist in the table that you specified in the Table ID field on the General FastTab.
   
   Optional
   
   Specify that the map will be skipped if the field is empty.
   
   Note: If you do not select this check box, then an export error will occur if the field is empty.
   
   Note: This field is only relevant for export.

To export a data exchange setup as an XML file for use by others
When you have created the data exchange setup for a specific bank statement or payroll transaction file, you can export the data exchange setup as an XML file that can be used to quickly enable import of the file in question. This is described in the following procedure.

1. In the Search box, enter Posting Exchange Definitions, and then choose the related link.
2. Select the data exchange setup that you want to export.
3. On the Home tab, in the Import/Export group, choose Export Data Exchange Setup.
4. Save the XML file that represents the data exchange setup in an appropriate location.

To use an XML file with an existing data exchange setup
If a data exchange setup has already been created, then all you have to do is to import the XML file into the Data Exchange Framework. This is described in the following procedure.

Note: For instructional purposes, this procedure includes steps to create the xml file from sample content. Alternatively, use the sample xml file that is published on the NAV Team Blog.

The resulting data exchange setup enables import of a bank statement file with the following sample content.

1. Create a text file and copy the following sample XML into it.
   
   
2. Save the text file as Unicode encoding.
3. In the Search box, enter Posting Exchange Definitions, and then choose the related link.
4. On the Home tab, in the New group, choose New. The Posting Exchange Definition window opens.
5. On the Home tab, in the Import/Export group, choose Import Data Exchange Setup.
6. Select the file that you saved in step 1.

To activate a data exchange setup
The data exchange setup for a payroll file is now ready for use. The data exchange setup for bank files is now available to be selected in the Bank Export/Import Setup window to activate the setup. For export of bank payment files, you must connect the setup code that you specified for one or more payment types to the related payment method. This is described in the two following procedures.


To select the data exchange setup from the relevant bank account

1. Open the Bank Account Card window of the bank that you want to import bank statement files to
2. In the Bank Statement Import Format field or the Payment Export Format field, choose the lookup button, and then choose Advanced.
3. In the Bank Export/Import Setup window, create a new line and fill the fields as described in the following table.
   
   Field
   
   Description
   
   Posting Exch. Def. Code
   
   Select the code that represents the data XML file with a data exchange setup that you have imported. For more information, see the &ldquo;To import an XML file with a data exchange setup&rdquo; section.
   
   Code
   
   Specify a code to identify for the setup.
   
   This is the code that users will select in the Bank Statement Import Format field in the Bank Account Card window.
   
   Name
   
   Specify a name for the setup.
   
   Direction
   
   Select Export or Import, to specify if this setup will be used to import a bank file or to export a bank file.
   
   Processing Codeunit ID
   
   Select the codeunit that will import the bank statement data.
   
   Processing XMLPort ID
   
   Select the XMLport through which the bank statement data is imported.

To connect the setup for one or more payment types with the relevant payment method(s)

1. In the Search box, enter Payment Methods, and then choose the related link
2. In the Payment Methods window, select the payment method that is used to export payments from, and then choose the Payment Type field
3. In the Payment Identifiers window, select the code that you specified in the Posting Line Definitions FastTab in step 4 in the &ldquo;To specify the setup and describe the line and column formatting&rdquo; section.

Users can start to import or export a bank file by selecting the related setup code in the Bank Statement Import Format or Payment Export Format fields. For more information, see the &ldquo;How to: Import Bank Statements&rdquo; or &ldquo;How to: Export Payments to a Bank File&rdquo; topics in the Application Help.

To start importing a payroll file that has been set up as a data exchange setup, users simply select the setup in the Payroll Trans. Import Format field. For more information, see the &ldquo;How to: Import Payroll Transactions&rdquo; topic in the Application Help.

Best regards,

The NAV Application team







Источник: http://feedproxy.google.com/~r/Micro...framework.aspx