Table of Contents

Templates Management

The use of the app requires that the import templates have been configured within the Import Templates page.

Base Settings

The basic configurations to set up a template are the following.

General

  • Type: the type of import to be performed (Journal/Document/Master Data). It is set automatically when the page is selected and must match it: journal pages require Journal, document pages require Document, the Customer, Vendor and Item lists require Master Data.
  • Page ID: select the page where the data from this template will be imported, for example, the General Journal page has ID 39. You can retrieve the page ID using the page inspection tool. or select it from the list.

  • Format: the import format: Excel, CSV, fixed-length (positional), JSON or XML. JSON and XML files are read node by node, as described in the import page; switching between a column format and JSON or XML deletes the mapping lines.
  • Write Mode: what the import is allowed to do with the records: Insert only creates records, Update only updates existing records, Insert or Update does both. It applies to master data templates and to Phys. Inventory Journal templates, and is hidden on the other templates, which always insert.

Options

  • Field Delimiter: if the format is CSV, specify the character to be used as the field delimiter - for example ;.

  • First Import Line: if the file contains header rows, such as column names, select the row to start reading data from.

  • Record Node Path: for a JSON or an XML file, the repeating node that holds one record - $.orders or $ for JSON, /Orders/Order for XML. It replaces the settings that describe rows and columns, which are hidden for these formats.

  • Line Node Path: for a JSON or an XML document template, the repeating node that holds one document line, relative to the record node - lines for JSON, Lines/Line for XML. Leave it empty when the records have no nested lines.

  • Import All Sheets: for Excel files, every sheet of the workbook is imported in one run, in workbook order and with the same mapping lines; the header row and First Import Line apply to every sheet, empty sheets are skipped, and for a document template a change of sheet starts a new document. Without it, the sheet is chosen when the import starts.

Lines

In the rows, you can define the structure of the file to be imported and make various configurations for each field:

  • Column Id: the column of the file from which the field should be read, so the value 1 indicates to read from the first column.
  • Application Order: the order in which the fields are applied to the record, important for their validation.
  • Line Type: in the case of document import, it indicates whether the imported value should be applied to a field in the document header or in the document line.
  • Field Id: the field to which the imported value, or the default value, is applied.
  • Default Value: a fixed value for the field, written on every record without a column in the file.
  • Import Parameter: it is possible to select the value of a field during the import process. This allows for the use of variable values during importation, such as: the posting date of a transaction, the customer for whom the document is being imported, etc.
  • Sheet Name as Value: with Import All Sheets, the line takes the name of the sheet the row comes from instead of a column; mapping rules and formats apply to it, and it can be the New Document Indicator, one document per sheet.
  • Concatenate and Concatenation Separator: map the same field more than once and join the pieces, separated by the Concatenation Separator, instead of overwriting them; for example a Description built from a reference and a name.

Advanced settings

General

  • Assigned Users: it is possible to specify that the template can only be used by configured users. Configuration can be done by clicking on the field or through the appropriate action.

Options

  • Trim Characters: it is possible to specify any characters to be removed from the beginning and end of fields.
  • File Encoding: the encoding to be used in reading the file.
  • Check Column Headers: if this flag is activated, the file header is checked with the structure of the template. In particular, the check occurs between the column name in the file and the value of the field "Column Name". If "Column Name" is not populated, it is checked against "Field Name".
  • Headers Line: the row that contains the column headers.

Lines

  • Column Name: field for internal use for the imported column name; during the export of the file structure, it will be inserted in the header if populated. For a JSON or an XML template the same field holds the path of the value inside its node and is shown as Path.
  • Dimension Code: you can use this field to manage additional dimensions beyond the two global dimensions. The value of "Field ID" must be set to 480 to allow correct processing.
  • Lenght: the length of the field, in case a fixed-length format of fields (positional) is used.
  • Mapping Rule: rules for transcoding imported values.
  • Transformation Rule: a standard Business Central transformation rule applied to the value; the custom transformations of the app are listed below.
  • Data Format: the format of the imported value (for example, date format), in conjunction with culture settings.
  • Data Formatting Culture: the culture (e.g., it-IT, en-US) to be applied to the imported data (for example, date format), in conjunction with the format.
  • New Document Indicator: for documents import, it can be used to indicate that a new value in this column should trigger the generation of a new document. For example, if the imported file contains a column (with this configuration activated) containing the same document number repeated on multiple rows, a document will be created containing the respective rows for each document value in the column.
  • Fixed Line: to be used if the value should be read only from a specific row, for example, the document posting date in the imported file is always in a specific line. This field is useful for managing the importation of documents in formats with Excel with a header.
  • Cut to Lenght: truncate the imported value to the maximum length of the field, avoiding errors during import.
  • Skip Validation: import skipping field validation, should be used with caution as it allows importing inconsistent data.
  • Skip Empty Values: skip the field if the imported value is empty - an empty string or 0 if it is a numeric field.
  • Linked Field Id: builds the default value from the value of another field, for example a description that repeats the document number; the placeholders are listed below.

Linked Parameters

  • Row Filters: by selecting the field, you can insert filters on the rows to be imported. For example, you can decide to exclude from the import rows that have a certain column empty or populated with a string or defined rule
  • Table Actions: you can specify additional actions to be performed on the imported data, if supported - for example, you can ensure that documents are automatically released after import.
  • Line Table Actions: you can specify additional actions to be performed on the imported data at the row level, if supported - for example, you can ensure that journal lines are automatically posted after import.
  • Skip Table Triggers: if set, the table triggers (OnInsert and OnModify) and their logic are skipped when the records are written.
  • Delayed Insert: if set, records are inserted only after all fields in the row have been populated.

Import History

  • Save Import History: if the feature is enabled, a history of imports that have occurred for the template is saved, with the file name, the user, the date and the number of documents or journal lines created.
  • Save Imported File: if the feature is enabled, the imported file is also saved in the history. The functionalities related to saving the history must be activated with caution to keep the environment size under control.
  • Prevent Duplicate File: refuses a file whose name was already imported successfully for the template; it relies on the import history.

Transformation Rules

The following custom rules are available:

  • Invert Sign: invert the sign of a numeric value.
  • Apply Date Formula: apply a date formula to a date value.
  • Multiply: multiply a numeric value by the Multiplier of the rule.

Linked Fields

The value of the linked field replaces the following placeholders in the default value:

  • %1: the value of the linked field.
  • %YY: for date fields, the year with 2 digits.
  • %YYYY: for date fields, the year with 4 digits.
  • %MM: for date fields, the month with 2 digits.
  • %DD: for date fields, the day with 2 digits.
  • %MONTH: for date fields, the month name (e.g. January).

Management

Copy template

Copy an existing template to modify it.

Export Structure

Export an empty file in the template format (CSV or Excel) to be filled with data. Not available for JSON and XML templates.

Read Structure from File

Read a sample file (Excel, CSV, JSON or XML) to create the mapping lines from its column headers: Page ID must be set first, the Format is taken from the file extension. Columns are matched to fields by name or caption, or to a dimension code; for CSV files the delimiter, the encoding and the date and decimal formats are detected. The columns that could not be matched are listed at the end, to be completed by hand.

For JSON and XML files the repeating node of the file is detected and written into Record Node Path and, for documents, Line Node Path; one mapping line is created for every value found, with its path in Path. XML attributes are read as @name.

Suggest with Copilot

From a sample file (Excel, CSV, JSON or XML), Copilot proposes the whole template: the file settings and all the mapping lines.

Pick the file and, if useful, tell Copilot what the file does not say: what the columns mean, the date and number formats, which column separates the documents, values to use when a column is missing. The prompt guide offers ready-made sentences. Generate shows the proposal: every line with its field, default value, format, new document indicator, mandatory flag, a short Reason and a Keep check box; the columns Copilot did not map are listed under Not Mapped. Regenerate tries again with new instructions, Discard leaves the template untouched, Keep it writes the lines marked to keep. The result is an ordinary template: review it and complete it by hand, in particular for related records, row filters, mapping rules and actions, which Copilot does not create.

Values that the file does not contain are proposed as fixed values or as import parameters asked when the import starts. The document number is never proposed: it comes from the number series, and the order number of the sending system goes to External Document No. or Vendor Order No..

Copilot receives the header row and the first ten data rows of the file (the first record nodes for JSON and XML), the names of the fields of the target tables and your instructions; nothing else is sent. An administrator turns the capability on or off in Copilot & agent capabilities. See the Responsible AI FAQ.

Validate File

Check a file against the template without importing: the file is validated as in the check step of the import and the errors are listed. The import parameters are asked first, as in a real import.

Import Data

In the templates list, import with the selected template without opening the target page. Available for document and master data templates; journals are imported from the journal page, which provides the batch.

Export

In the templates list, export the templates to import them in a different company or environment.

Import

In the templates list, import previously exported templates.