Field Mapping & Data Type Comparison: How ERPs Handle Dates, Currencies, Multi-Language Fields, and Custom Fields Differently

TL;DR

• Bottom line: Every ERP has different date formats, currency representations, null semantics, and custom field APIs. The #1 cause of silent integration data corruption is assuming two systems use the same conventions when they do not.
• Key limit: SAP stores currency amounts in the smallest currency unit (cents) while most others use decimal notation — mapping without conversion creates 100x overcharges or underpayments.
• Watch out for: NetSuite datetimes default to PST/PDT (not UTC), Salesforce picklists are strings but D365 enums are integers, and Oracle flexfields are not discoverable via REST metadata.
• Best for: Any cross-ERP integration project where you need to build field transformation logic between two or more of these eight platforms.
• Authentication: N/A for this card — see the ERP REST API Comparison card for auth flow details.

System Profile

This card compares how eight major ERP/CRM platforms represent and handle fundamental data types in their APIs. It covers cloud deployments only. On-premise variants may differ significantly.

Quick Reference

Date/DateTime Format Comparison

Currency & Amount Handling

Custom Field / Extensibility Comparison

Picklist / Enum / Lookup Value Handling

Null / Empty Value Handling

Multi-Language / Locale Field Handling

Character Encoding & String Limits

Data Mapping

Cross-System Date/Time Transformation Rules

Data Type Gotchas

• SAP amounts in smallest currency unit: SAP CURR data type stores amounts in the smallest currency unit (e.g., 10050 for $100.50 USD). The TCURX table defines decimals per currency (default 2). OData V4 may still deliver internal representation. Always check sap:unit annotation. Forgetting this creates 100x overcharges. [src2, src3]
• NetSuite timezone trap: Datetimes default to PST (UTC-8) / PDT (UTC-7). If integration user's timezone preference is wrong, all datetimes are systematically shifted. Set integration user timezone to UTC. [src4]
• Salesforce 18-char vs 15-char IDs: API returns 18-character case-insensitive IDs, but formulas may use 15-character case-sensitive IDs. Always store 18-character versions. [src1]
• D365 enum integer vs string: Uses integer enums internally (NoYes::Yes = 1) but exposes as string labels via OData. Write behavior varies per entity. [src6]
• Salesforce multi-select picklist delimiter: Values are semicolon-delimited in API but comma-separated in UI. Parsing the wrong delimiter silently corrupts data. [src1]
• Oracle DFF segment order: Attribute1 in dev may map to a different business field than Attribute1 in production. Validate per environment.
• NetSuite currency precision: Only supports 0 or 2 decimal places. Currencies requiring 3+ decimals (BHD, Bitcoin) need workaround fields. [src8]
• Workday Reference_IDs vs WIDs: WID (UUID) is immutable; Reference_IDs can be changed by admins. Store WIDs as the stable identifier.

Error Handling & Failure Points

Common Data Type Errors

Failure Points in Production

• SAP currency conversion drift: Integration maps SAP amounts directly without TCURX conversion. Works for USD/EUR (2 decimals) but JPY amounts are 100x too large (0 decimals in TCURX). Fix: always read TCURX for the transaction currency; never assume 2 decimal places. [src2, src3]
• NetSuite timezone shift on DST transition: Integration user set to PST. During March DST transition, datetimes shift by 1 hour. Fix: set integration user timezone to UTC; parse all datetime fields with explicit offset. [src4]
• D365 enum write failures on extensible enums: Integration writes valid string label but entity rejects it expecting integer. Fix: test both integer and string forms; prefer integer for extensible enums. [src6]
• Oracle DFF segment mismatch across environments: Segments mapped differently in dev vs production. Fix: export DFF segment mapping from both environments and diff; include in deployment checklist.
• Salesforce multi-select picklist corruption: Writing comma-delimited instead of semicolon-delimited. No error thrown. Fix: always use semicolon delimiter; validate against describe metadata. [src1]

Anti-Patterns

Wrong: Assuming all ERPs use UTC for datetime fields

// BAD — Treating NetSuite datetime as UTC
const order_date = new Date(netsuite_record.tranDate); // "2026-03-15T00:00:00.000-08:00"
const utc_date = order_date.toISOString(); // Wrong if not parsing offset correctly
// Sends to Salesforce as 2026-03-15T08:00:00Z — 8 hours off!

Correct: Parse timezone offset from each system's format

// GOOD — Respect the timezone offset in NetSuite's response
const ns_datetime = "2026-03-15T00:00:00.000-08:00";
const parsed = new Date(ns_datetime); // JS Date correctly handles offset
const utc_iso = parsed.toISOString(); // "2026-03-15T08:00:00.000Z" — correct UTC
// For Salesforce, send the UTC value directly

Wrong: Mapping SAP amounts directly without TCURX conversion

# BAD — Direct mapping of SAP amount to Salesforce
salesforce_amount = sap_record['NetAmount']  # 10050 (SAP smallest unit for USD)
# Salesforce receives 10050.00 instead of 100.50 — 100x overcharge!

Correct: Apply TCURX-based currency conversion

# GOOD — Convert SAP smallest-unit amount using TCURX decimal places
TCURX_DECIMALS = {'USD': 2, 'EUR': 2, 'JPY': 0, 'KWD': 3, 'BHD': 3}
currency = sap_record['TransactionCurrency']
decimals = TCURX_DECIMALS.get(currency, 2)  # Default 2 if not in lookup
salesforce_amount = sap_record['NetAmount'] / (10 ** decimals)
# USD: 10050 / 100 = 100.50 (correct)
# JPY: 10050 / 1 = 10050 (correct — no decimals for yen)

Wrong: Writing D365 enums as integers without checking entity behavior

// BAD — Assuming all D365 entities accept integer enum values
const payload = { "IsActive": 1 }; // NoYes.Yes = 1
// Some OData entities reject this and require the string form

Correct: Use the OData string label format for D365 enum writes

// GOOD — Use string label format; test each entity individually
const payload = { "IsActive": "Yes" }; // OData exposes as string
// For entities requiring full qualified form:
// "IsActive": "Microsoft.Dynamics.DataEntities.NoYes'Yes'"

Common Pitfalls

• Salesforce picklist API name vs label: API returns API name, not display label. Comparing against label silently fails. Fix: use describe metadata to build label-to-API-name map. [src1]
• SAP OData V2 vs V4 date format: V2 uses datetime'2026-03-03T00:00:00', V4 uses 2026-03-03 or 2026-03-03T00:00:00Z. Mixing causes parse errors. Fix: check OData version per API on api.sap.com. [src2, src3]
• NetSuite internalId vs externalId: Setting currency by name ({"currency": "USD"}) fails. NetSuite requires internalId or reference. Fix: use internalId or externalId references; cache ID mappings. [src4]
• D365 date-effective entities: Some entities use ValidFrom/ValidTo. Simple PATCH may create new effective record instead of updating. Fix: check entity metadata for date-effective fields. [src5]
• Oracle DFF context sensitivity: Attribute segments depend on context value. Writing without correct context puts data in wrong segment. Fix: set context segment before populating attributes.
• Workday Reference_ID instability: Reference_IDs can change. Fix: store WID as immutable key; Reference_ID for display only.
• Acumatica Usr prefix case sensitivity: Must be exactly Usr (capital U, lowercase sr). Other casings fail silently. Fix: always use 'Usr' prefix exactly.

Integration Pattern Decision Tree

START — Data type mismatch between Source ERP and Target ERP
├── Date/DateTime issue?
│   ├── Both systems UTC? → Direct mapping
│   ├── NetSuite involved? → Convert from user-timezone to UTC first
│   ├── Date-only? → Use YYYY-MM-DD; strip time component
│   └── Full datetime? → Preserve with timezone conversion
├── Currency amount issue?
│   ├── SAP source? → Lookup TCURX decimals; divide by 10^decimals
│   ├── Zero-decimal currency (JPY, KRW)? → Verify both systems handle correctly
│   └── Multi-currency? → Map currency code alongside amount
├── Picklist/Enum mismatch?
│   ├── String → Integer (D365)? → Build lookup table
│   └── Multi-select? → Check delimiter (SF=semicolon, NS=pipe, Acumatica=comma)
├── Custom field issue?
│   ├── Oracle target? → Map DFF segments; set context first
│   ├── Acumatica target? → Use 'Usr' prefix exactly
│   ├── Business Central? → Verify field on API page definition
│   └── Others → Use standard custom field API (SF __c, NS cust*, SAP YY1_)
└── Null handling?
    ├── Target rejects NULL? → Map to default value
    └── Clear a field?
        ├── Salesforce → null in JSON or fieldsToNull (SOAP)
        ├── NetSuite → null (REST) or nullFieldList (SOAP)
        └── D365 → null (check if nullable first)

Diagnostic Commands

# === SALESFORCE: Inspect field metadata ===
curl "https://{instance}.my.salesforce.com/services/data/v62.0/sobjects/Account/describe" \
  -H "Authorization: Bearer ${SF_TOKEN}" | jq '.fields[] | {name, type, length, precision, scale, picklistValues}'

# === SAP S/4HANA: Check OData $metadata for field types ===
curl "https://{tenant}.s4hana.cloud.sap/sap/opu/odata4/sap/api_business_partner/srvd_a2x/sap/businesspartner/0001/\$metadata" \
  -H "Authorization: Bearer ${SAP_TOKEN}"
# Look for: Edm.Decimal (amounts), Edm.DateTimeOffset (datetimes), sap:unit annotation

# === ORACLE ERP CLOUD: Check entity fields ===
curl "https://{instance}.fa.{dc}.oraclecloud.com/fscmRestApi/resources/latest/invoices/describe" \
  -H "Authorization: Bearer ${ORACLE_TOKEN}" | jq '.Resources[].attributes[] | {name, type}'

# === NETSUITE: Inspect record metadata ===
curl "https://{account_id}.suitetalk.api.netsuite.com/services/rest/record/v1/metadata-catalog/customer" \
  -H "Authorization: OAuth ..." | jq '.properties | to_entries[] | {name: .key, type: .value.type}'

# === DYNAMICS 365 F&O: Check entity metadata for enums ===
curl "https://{env}.operations.dynamics.com/data/\$metadata" \
  -H "Authorization: Bearer ${D365_TOKEN}" -o metadata.xml
# Parse XML for EnumType definitions and Property types

# === CURRENCY TEST: Validate decimal handling ===
# Create test record with amount 12345 in JPY, USD, and BHD
# Verify stored value in both source and target systems

When to Use / When Not to Use

Cross-System Comparison

Important Caveats

• Version-dependent behavior: Data type handling can change between releases. SAP OData V2 and V4 handle dates and currency amounts differently. Always verify against your specific system version.
• Edition-specific custom field limits: Salesforce Professional Edition cannot create certain custom field types. NetSuite Standard tier has fewer options. Verify for your edition.
• Sandbox configuration drift: Custom fields, flexfield segments, enum extensions, and translations may differ between sandbox and production. Always validate against production before go-live.
• Cloud deployments only: On-premise variants (SAP ECC, Oracle E-Business Suite, Dynamics AX/GP) may have different representations, encodings, and custom field mechanisms.
• Currency handling is highest-risk: SAP's smallest-unit currency storage is the single most common source of financial data corruption in cross-ERP integrations. Always implement and test TCURX conversion.