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

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

Rate This
  • Comments 4

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 2013 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 2013 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. Set a filter for pages 50001..50008, select the eight pages, choose Tools, and then choose Compile.
  6. In the Object Designer, choose MenuSuite, choose New, select an appropriate design level, and then choose OK. Alternatively, select an existing MenuSuite, 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

    50002

    Caption

    Posting Exchange Definitions

    CaptionML

    ENU=Posting Exchange Definitions

    Department

    Lists

  11. Choose the OK button.
  12. Select the new or edited MenuSuite, 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.


    Field

    Description

    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.

    <?xml version="1.0" encoding="UTF-16" standalone="no"?>
    <root>
       <PostExchDef Code="MYCSV" Name="My CSV Bank Statement" Type="0" CustomXMLPort="1220" ColumnSeparator="2" FileType="1">
        <PostExchLineDef Code="" Name="" ColumnCount="0">
         <PostExchColumnDef ColumnNo="1" Name="Transaction date" Show="true" DataType="1" DataFormat="dd-MM-yyyy" DataFormattingCulture="da-DK" />
         <PostExchColumnDef ColumnNo="2" Name="Text" Show="true" DataType="0" />
         <PostExchColumnDef ColumnNo="3" Name="Amount" Show="true" DataType="2" DataFormattingCulture="da-DK" />
         <PostExchColumnDef ColumnNo="4" Name="Balance" Show="true" DataType="2" DataFormattingCulture="da-DK" />
         <PostExchColumnDef ColumnNo="5" Name="Value date" Show="true" DataType="1" DataFormat="dd-MM-yyyy" DataFormattingCulture="da-DK" />
         <PostExchMapping TableId="81" Name=" My CSV to General Journal Mapping" PostExchNoFieldId="1220" PostExchLineFieldId="1223">
           <PostExchFieldMapping ColumnNo="1" FieldId="5" />
           <PostExchFieldMapping ColumnNo="2" FieldId="8" />
           <PostExchFieldMapping ColumnNo="3" FieldId="13" />
         </PostExchMapping>
         <PostExchMapping TableId="274" Name=" My CSV to Bank Rec. Lines" PostExchNoFieldId="17" PostExchLineFieldId="18">
           <PostExchFieldMapping ColumnNo="1" FieldId="5" />
           <PostExchFieldMapping ColumnNo="2" FieldId="6" />
           <PostExchFieldMapping ColumnNo="3" FieldId="7" />
           <PostExchFieldMapping ColumnNo="5" FieldId="12" />
         </PostExchMapping>
        </PostExchLineDef>
       </PostExchDef>
    </root>
     
  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 “To import an XML file with a data exchange setup” 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 “To specify the setup and describe the line and column formatting” 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 “How to: Import Bank Statements” or “How to: Export  Payments to a Bank File” 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 “How to: Import Payroll Transactions” topic in the Application Help.

Best regards,

The NAV Application team 

 

 

Attachment: Data Exchange Framework Files.zip
Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • You have forgot that nobody can edit menusuite 1010 without Microsoft license... ;-)

  • Thanks, Kine - The procedure has been updated.

  • There is no page 1211 in Microsoft Dynamics Nav 2013 R2 build 7.1.35473.0

    How can I get the object for that page?

  • Hello,

    We have updated the page ID in step 10 in the procedure "To implement page objects published on NAV Team Blog". Sorry for the confusion!

    Best regards,

    The NAV team

Page 1 of 1 (4 items)