Page NAV-X Integration Fields

The Integration Fields page defines the fields that will be extracted from your source file (Excel, CSV, or Text). Each field definition specifies where the data comes from and how to process it.

To open: From the NAV-X Integrations page, select an integration and click the Fields action.

Overview

Integration fields represent:

  • Columns in Excel (A, B, C, etc.)
  • Columns in CSV (1, 2, 3, etc.)
  • Fixed positions in Text files (character offset and length)
  • JSON paths (dot-notation paths like customer.address.city)
  • Constant values (fixed data applied to all rows)
  • Dynamic values (system values resolved at import time)
  • Auto-generated values (line numbers, entry numbers)

Key Fields

Basic Information

Field Purpose
Field No. Sequence number for display and processing order
Title Field name (for reference; doesn't need to match source)
Column Type How the field value is generated (see below)

Standard Column Configuration

For Column Type = Standard (data from source):

Field Purpose Used For
Source Column Where to find the value in source Excel: A, B, C... / CSV: 1, 2, 3... / Text: Column number

Text-Specific Configuration (Fixed-Width)

When Integration Type = Text AND Fixed Width Format = Yes:

Field Purpose Example
Start Position First character position (1-based) Position 1 for field starting at column 1
Field Length Number of characters 5 for a 5-character field

Example Fixed-Width Text:

Position: 1     5     10    15    20
Data:     ACME  001   50.00 2024-01-15
Field:    Name  ID    Price Date
Config:   Start=1, Len=5; Start=6, Len=3; Start=9, Len=5; Start=14, Len=10

Constant Configuration

For Column Type = Constant:

Field Purpose
Constant Value Fixed value applied to every row
Source Column Leave blank (not used for constants)

JSON Configuration (New in v1.5.0)

For Integration Type = Json:

Field Purpose Example
JSON Path Dot-notation path to resolve value from JSON object customer.address.city
JSON Array Mode How to handle array values Flatten, Concatenate, or First

JSON Path supports a lookup action that discovers available paths from the stored JSON sample with sample values.

JSON Array Mode options:

  • Flatten - Expand child array elements into separate rows with parent values duplicated
  • Concatenate - Join array values with a separator into a single value
  • First - Take only the first element from the array

Dynamic Configuration (New in v1.4.0)

For Column Type = Dynamic:

Field Purpose
Dynamic Value Type System value to use: Today, Work Date, Current Time, Current Date/Time, User ID, or Company Name

Pre-Parsing Transformations (New in v1.4.0)

These fields configure data transformations applied before processing into Business Central:

Field Purpose Modes
Default Value If Empty Fallback value when source is blank Text value
Trim Start Mode Trim from beginning None, Flexible (character set), Fixed (N chars)
Trim Start Characters Characters to trim (Flexible mode) e.g., 0 and space to trim zeros and spaces
Trim Start Count Number of characters to remove (Fixed mode) Integer
Trim End Mode Trim from end None, Flexible, Fixed
Trim End Characters Characters to trim (Flexible mode) Text
Trim End Count Number of characters to remove (Fixed mode) Integer
Case Conversion Convert case None, Upper Case, Lower Case, Title Case
Pad Direction Pad side None, Left, Right
Pad Character Character for padding e.g., 0 for zero-padding
Pad Length Target length after padding Integer
Rounding Direction Rounding direction for decimals None, Nearest, Up, Down
Rounding Precision Rounding precision e.g., 0.01 for two decimals
Has Char. Replacements Indicates character replacement rules exist Read-only indicator

Transformations are applied in this order: default-if-empty, trim start, trim end, character replacements, case conversion, padding. Rounding is applied after multi-record splits.

See How to Use Pre-Parsing Transformations for detailed configuration guidance.

Multi-Row Configuration

Field Purpose When Used
Create Multiple Records Split comma-separated values into multiple rows When source has comma-separated lists
Value Separator Delimiter for splitting values Usually comma (,) when enabled

Example: If source has "A, B, C" and "Create Multiple Records" = Yes, creates 3 rows with values A, B, C respectively.

Column Type Values

Standard

Purpose: Extract value from source file

Configuration:

  • Set Source Column (Excel: A/B/C, CSV: 1/2/3, Text: column number)
  • For fixed-width text: Also set Start Position and Field Length

Use when: Most source fields

Example:

Title: ItemCode
Column Type: Standard
Source Column: C (Excel) or 3 (CSV)

Constant

Purpose: Apply same fixed value to every record

Configuration:

  • Leave Source Column blank
  • Set Constant Value to the fixed value

Use when:

  • Document Type (all "Order")
  • Status (all "Open")
  • Company or department (all same)

Example:

Title: DocumentType
Column Type: Constant
Constant Value: Order
Source Column: (blank)

Line Numbers (New in v1.3.0)

Purpose: Auto-generate line numbers (10, 20, 30...)

Configuration:

  • Leave Source Column blank
  • No Constant Value needed
  • System auto-generates incrementing by 10

Use when: Sales Line.Line No., Purchase Line.Line No.

Result:

Row 1 → Line No. = 10
Row 2 → Line No. = 20
Row 3 → Line No. = 30

Entry Numbers (New in v1.3.0)

Purpose: Auto-generate sequential entry numbers (1, 2, 3...)

Configuration:

  • Leave Source Column blank
  • System auto-generates incrementing by 1

Use when: Custom entry numbers, sequence tracking

Result:

Row 1 → Entry No. = 1
Row 2 → Entry No. = 2
Row 3 → Entry No. = 3

Dynamic (New in v1.4.0)

Purpose: Resolve system values at import time without requiring them in the source file

Configuration:

  • Set Dynamic Value Type to the desired system value
  • No Source Column or Constant Value needed

Dynamic Value Types:

  • Today - Current date
  • Work Date - Business Central work date
  • Current Time - Current time
  • Current Date/Time - Current date and time
  • User ID - ID of the user running the import
  • Company Name - Name of the current company

Use when: Stamping records with audit information, dates, or company context

Example:

Title: ImportDate
Column Type: Dynamic
Dynamic Value Type: Today

Formula (New in v1.3.0)

Purpose: Combine and transform values using formulas

Configuration:

  • Specify formula using field references
  • Example: =[OrderDate] + 30 days
  • Example: =UPPER([CustomerCode])

Use when: Calculated fields, concatenation, transformations

Example:

Title: DueDate
Column Type: Formula
Formula: =[OrderDate] + 30

Using Start Position and Field Length

For fixed-width text imports only (when Integration Type = Text and Fixed Width Format = Yes):

How It Works

Source text file with fixed positions:

Column:  1         2         3         4
Pos:     123456789012345678901234567890123456789
Data:    ACME12345John Smith    2024-01-15  USD
Field:   Company   ID Code Name            Date Currency

Configuration for each field:

Field Start Position Field Length
Company 1 8
ID Code 9 5
Name 14 16
Date 30 10
Currency 40 3

During import:

  • Company: Extract 8 characters starting at position 1 → "ACME12"
  • ID Code: Extract 5 characters starting at position 9 → "345Jo"
  • Etc.

Position Numbering

  • 1-based: First character is position 1 (not 0)
  • Inclusive: Both start and end positions included in extraction
  • Consecutive: Typically one field ends where next begins (no gaps)

Settings and Options

Column Type Examples by Integration Type

Excel Integration:

Field 1: Title=OrderNo, Column Type=Standard, Source Column=A
Field 2: Title=CustomerCode, Column Type=Standard, Source Column=B
Field 3: Title=DocumentType, Column Type=Constant, Constant Value=Order
Field 4: Title=LineNo, Column Type=Line Numbers

CSV Integration:

Field 1: Title=OrderNo, Column Type=Standard, Source Column=1
Field 2: Title=CustomerCode, Column Type=Standard, Source Column=2
Field 3: Title=DocumentType, Column Type=Constant, Constant Value=Order
Field 4: Title=LineNo, Column Type=Line Numbers

Text (Fixed-Width) Integration:

Field 1: Title=OrderNo, Column Type=Standard, Start Position=1, Field Length=8
Field 2: Title=CustomerCode, Column Type=Standard, Start Position=9, Field Length=6
Field 3: Title=OrderDate, Column Type=Standard, Start Position=15, Field Length=10
Field 4: Title=DocumentType, Column Type=Constant, Constant Value=Order
Field 5: Title=LineNo, Column Type=Line Numbers

Actions

Suggest Fields from Sample (JSON only, New in v1.5.0)

Analyzes the stored JSON sample file and suggests fields based on discovered JSON paths. Opens a confirmation page where you can select which fields to create.

Character Replacements (New in v1.4.0)

Opens the character replacement rules for the selected field. Allows you to define ordered find-and-replace rules that are applied during pre-parsing.

Best Practices

  1. Define fields in logical order - Field No. determines processing sequence
  2. Use descriptive titles - Helps with troubleshooting and documentation
  3. Validate Column Type selection:
    • Will value come from file? → Standard
    • Same value always? → Constant
    • Auto-generate numbers? → Line Numbers or Entry Numbers
    • Transform values? → Formula
  4. For fixed-width text: Carefully verify Start Position and Field Length match source
  5. Test with sample before full import
  6. Document assumptions about source file structure

Examples

Example 1: Sales Order Import (Excel)

Title                 Column Type  Source Column  Constant Value
OrderNo               Standard     A              
CustomerCode          Standard     B              
OrderDate             Standard     C              
ItemCode              Standard     D              
Quantity              Standard     E              
UnitPrice             Standard     F              
DocumentType          Constant                    Order
SalesLineType         Constant                    Item
LineNo               Line Numbers

Example 2: Legacy Vendor Invoice (Fixed-Width Text)

Title                 Column Type  Start Pos  Field Length  Constant Value
VendorNo              Standard     1          6             
VendorName            Standard     7          30            
InvoiceNo             Standard     37         10            
InvoiceDate           Standard     47         10            
Amount                Standard     57         12            
Currency              Constant                              USD
Status                Constant                              Open
EntryNo               Entry Numbers

Example 3: CSV Price List with Auto-Calculations

Title                 Column Type  Source Column  Constant Value
ItemCode              Standard     1              
Description           Standard     2              
BasePrice             Standard     3              
DiscountPercent       Standard     4              
FinalPrice            Formula      =[BasePrice] * (1 - [DiscountPercent]/100)
CurrencyCode          Constant                    USD
EntrySequence         Entry Numbers

Character Replacements

The Character Replacements action opens a sub-page where you can define ordered find-and-replace rules for the selected field. Each rule specifies a Find character or string and a Replace With value. Rules are applied in order during pre-parsing, before any other transformations.

Field Description
Order The sequence in which the replacement is applied
Find The character or string to search for
Replace With The replacement value (leave empty to remove the found characters)

JSON Path Lookup

When working with JSON integrations, the JSON Path Lookup action opens a dialog showing all available JSON paths discovered from the stored sample file. Each path displays a sample value to help you identify the correct path. Select a path to populate the JSON Path field.

JSON Field Suggestions

The Suggest Fields from Sample action for JSON integrations opens a dialog that auto-discovers the JSON structure and suggests field definitions. It detects the root array path, enumerates all leaf paths, generates human-readable field names, and creates Integration Field records. Re-running does not duplicate existing fields.

See Also