Field Mapping

Overview

Field mapping defines how Transact (T24) application fields are transformed into SQL columns in the Operational Data Store (ODS). When a replication job runs, the system reads each registered application’s field mappings to determine what to extract from the source, how to convert it, and where to store it.

This guide covers:

  • Understanding field mapping concepts
  • Viewing and editing field mappings in the web UI
  • The Fields Advisor for bulk name harmonisation
  • Best practices for naming, types, and indexing
  • Common scenarios and troubleshooting

What is Field Mapping?

How data flows

Transact (T24)           Field Mapping           ODS (SQL Server)
┌──────────────┐        ┌─────────────┐        ┌──────────────┐
│ CUSTOMER     │        │             │        │ tbCUSTOMER   │
│              │        │  MapName    │        │              │
│ CUSTOMER.CODE│───────>│ CustomerCode│───────>│ CustomerCode │
│  (10R)       │        │  (int)      │        │  int         │
│              │        │             │        │              │
│ SHORT.NAME   │───────>│ ShortName   │───────>│ ShortName    │
│  (35L)       │        │ (varchar)   │        │  varchar(35) │
└──────────────┘        └─────────────┘        └──────────────┘

Key concepts

T24 Field — the original field from the Transact application (e.g. SHORT.NAME). It has a field number, a T24 data type (e.g. 35L = 35 characters, left-justified), and optional metadata such as help text.

ODS Column — the SQL Server column where the value lands (e.g. ShortName varchar(35)). The column name, SQL type, and indexing are all defined in the field mapping record.

Map Name — the SQL column name you assign. Use PascalCase (e.g. CustomerCode). This is the main thing you control.

SQL Type — the SQL Server data type (e.g. int, varchar(35), decimal(15,2)). Defaults are inferred from the T24 format but can be overridden.

Index Type — whether the column has an index: 0 = clustered, 1 = non-clustered, -1 = no index.

Viewing Field Mappings

Web UI

Open the MorphTool at https://todsdev.fintegrator.eu and navigate to Field Mapping in the left menu.

Select a T24 application from the dropdown. The grid loads all mapped fields for that application:

Column Description Example
Field Number T24 field number 0, 1, 2
Field Tag Internal field identifier c0, c1
Field Name T24 field name SHORT.NAME
Field Type T24 data type category D (data field)
Field Format T24 format specification 35L, 10R
SQL Type SQL Server column type varchar(35), int
Index Type Indexing: 0/1/-1 -1
Multi Value S = single, M = multi-value S
Map Name ODS column name ShortName
Map Group Group for multi-value fields ADDRESS
Sensitive PII / confidential flag false
Map Description Business description —

REST API

# All mappings
curl https://todsdev.fintegrator.eu/api/mappings

# Mappings for a specific application
curl https://todsdev.fintegrator.eu/api/mappings/CUSTOMER

Example response:

[
  {
    "t24Application": "CUSTOMER",
    "fieldNumber": 0,
    "fieldTag": "c0",
    "fieldName": "CUSTOMER.CODE",
    "fieldType": "D",
    "fieldFormat": "10R",
    "fieldTypeSql": "int",
    "fieldIndexType": -1,
    "multiValue": "S",
    "multiValueOrder": 0,
    "mapName": "CustomerCode",
    "mapGroup": null,
    "sensitive": false,
    "mapDescription": "Customer unique identifier"
  }
]

Editing Field Mappings

What you can edit

Field Editable Notes
Map Name Yes ODS column name — PascalCase, no spaces
SQL Type Yes SQL Server data type
Index Type Yes 0 = clustered, 1 = non-clustered, -1 = none
Map Group Yes Multi-value grouping
Sensitive Yes Marks field as PII / confidential
Map Description Yes Business description
Field Name No Sourced from T24 metadata
Field Number No Sourced from T24 metadata
Field Format No Sourced from T24 metadata

Editing in the UI

  1. Select the application in the Field Mapping grid.
  2. Click the field row you want to change.
  3. Edit the value inline (Map Name, SQL Type, Index Type, etc.).
  4. Save — the system calls mfe.spUpdateField to persist the change.

Editing via API

curl -X PUT https://todsdev.fintegrator.eu/api/mappings/{id} \
  -H "Content-Type: application/json" \
  -d '{
    "t24Application": "CUSTOMER",
    "fieldTag": "c0",
    "fieldName": "CUSTOMER.CODE",
    "fieldTypeSql": "int",
    "fieldIndexType": 0,
    "multiValue": "S",
    "multiValueOrder": 0,
    "mapName": "CustomerCode",
    "mapGroup": null,
    "sensitive": false,
    "mapDescription": "Customer unique identifier"
  }'

Returns HTTP 204 on success.

Sensitive flag

Mark Sensitive = true for fields containing customer identifiers, personal data, or financial secrets that must be masked in non-production environments. This flag is stored as metadata and used by data masking tooling.

Creating ODS Objects

After editing field mappings, use Create Views to regenerate the ODS table, view, and stored procedure for the application.

What Create Views builds

For each application, the system generates:

  • dbo.tb{App} — the ODS table with columns derived from the field mapping
  • dbo.vw{App} — a view filtered to live records
  • dbo.sp{App} — the replication stored procedure that reads from the CDC source and populates the table

Example generated table (CUSTOMER):

CREATE TABLE dbo.tbCUSTOMER (
    RECID          varchar(50) NOT NULL PRIMARY KEY,
    CustomerCode   int,
    ShortName      varchar(35),
    Mnemonic       varchar(10),
    -- ... remaining mapped fields ...
    TimeStamp      datetime2(3),
    UserStamp      varchar(50)
);

Example generated view:

CREATE VIEW dbo.vwCUSTOMER AS
SELECT RECID, CustomerCode, ShortName, Mnemonic, TimeStamp, UserStamp
FROM dbo.tbCUSTOMER
WHERE RecordStatus = 'LIVE';

Running Create Views

Via UI: in the Field Mapping page, select the application and click Create Views. Confirm the operation. The system generates and executes the DDL.

Via API:

curl -X POST https://todsdev.fintegrator.eu/api/mappings/CUSTOMER/create-views

Fields Advisor

The Fields Advisor helps harmonise Map Name values across an application using field-name and description similarity scoring. Use it when bringing a new application online or cleaning up inconsistent naming.

Workflow

  1. Open Fields Advisor from the main menu.
  2. Select a T24 application.
  3. Set a similarity threshold (start at 0.72; lower values suggest more candidates).
  4. Click Analyze — the advisor generates candidate groups, each showing:
    • Current field name
    • Effective description (custom description if set, otherwise T24 help text)
    • Suggested canonical Map Name
    • Confidence score
  5. Review each group. Uncheck rows you do not want to change, or Ignore Group to skip the whole group.
  6. Click Apply Selected — the system persists the renames.

Audit and rollback

Every rename applied by the advisor is recorded in mfe.ProcessingLog with the prefix FieldAdvisor: and the old and new Map Name values. Use the Advisor Audit panel to review recent changes.

Rollback is manual: re-run the advisor with the previous name, or edit the field directly in the mapping grid. Apply changes in small batches and re-run Create Views after each batch to verify the generated objects before wider rollout.

Best Practices

Naming (Map Name)

  • Use PascalCase: CustomerCode, ShortName, AccountOfficer
  • Be descriptive but concise
  • Avoid abbreviations unless they are well-known domain terms
  • No spaces, no special characters except underscore
  • Do not start with a digit

Data type mapping

T24 Format Meaning Recommended SQL Type
10R 10 chars, right-justified (numeric) int or bigint
35L 35 chars, left-justified (text) varchar(35)
D Date (YYYYMMDD) date
15.2 15 digits, 2 decimal places decimal(15,2)
1A 1 character char(1)
RECID / cross-refs T24 record ID varchar(50)

Indexing

Index Type Value When to use
Clustered 0 Primary key or dominant query field (one per table)
Non-clustered 1 Frequently filtered or joined fields
None -1 Rarely queried fields, large text, JSON/XML columns

Indexes speed up SELECT but slow down INSERT/UPDATE. Index primary keys, foreign keys, and date range fields. Leave description and free-text fields unindexed.

Multi-value fields

Single-value fields (multiValue = "S") map one-to-one to a column.

Multi-value fields (multiValue = "M") represent repeating groups in T24 (e.g. multiple address lines). They generate a child table. Use MapGroup to name the group and MultiValueOrder to preserve sequence.

T24 CUSTOMER.ADDRESS[1] = "123 Main St"
               ADDRESS[2] = "Suite 100"
               ADDRESS[3] = "New York, NY"

ODS dbo.tbCUSTOMER_ADDRESS
  RECID   | AddressLine   | AddressOrder
  --------|---------------|-------------
  CU001   | 123 Main St   | 1
  CU001   | Suite 100     | 2
  CU001   | New York, NY  | 3

Common Scenarios

Adding a new field

  1. Verify the field exists in T24 (visible in the Field Mapping grid with no Map Name set).
  2. Set the Map Name and SQL Type.
  3. Run Create Views to add the column to the ODS table.

Changing a data type

  1. Update the SQL Type in the field mapping.
  2. Run Create Views — the system will alter the column type if the table exists, or recreate it if this is a fresh deployment.
  3. If the table already holds data and the type change is not safely castable, back up first: 
    SELECT * INTO dbo.tbCUSTOMER_Backup FROM dbo.tbCUSTOMER;

Removing a field

  1. Clear the Map Name (set it to empty in the grid or via API).
  2. Run Create Views — the column is excluded from the regenerated table.
  3. If you need to physically drop the column from an existing table: 
    ALTER TABLE dbo.tbCUSTOMER DROP COLUMN ObsoleteField;

Troubleshooting

Field not appearing in the grid

The grid shows fields of type D (data field). System fields and sub-values may appear separately. If a field is entirely missing:

-- Check whether the field is in the source metadata
SELECT * FROM mfe.vwFieldsSS
WHERE Application = 'CUSTOMER' AND FieldName = 'MISSING.FIELD';

If it is absent from vwFieldsSS, the field is not in the T24 Standard Selection for this application and cannot be mapped until the source metadata is refreshed.

Create Views fails with duplicate column error

Two fields share the same Map Name. Find and fix them:

SELECT MapName, COUNT(*) AS n
FROM mfe.FieldMapper
WHERE Application = 'CUSTOMER'
GROUP BY MapName
HAVING COUNT(*) > 1;

Create Views fails with invalid SQL type

Verify the SQL type is valid SQL Server syntax. Common mistakes:

  • varchar without a length — must be varchar(N)
  • string — not a SQL Server type; use varchar(N)
  • int(10) — int does not take a length parameter
  • varchar(10000) — exceeds the 8000-byte limit; use varchar(max) for unlimited

Field change not reflected after replication

Field mapping changes apply to future replication jobs. Data already in the ODS table is not retroactively updated. To reload historical data, run a full replication for the application via the Replication panel in the UI or trigger it via the API.