Data Mapping

Data mapping is the process of defining how fields in one system correspond to fields in another system. This is essential for integration as different systems often represent the same data in different ways.

Practice Alert

You'll get hands-on experience with these data mapping concepts later when we integrate with QuickBooks Online.

Data Mapping Fundamentals

What is Data Mapping?

Data mapping addresses differences in:

Field names and types
Data formats and structures
Required vs. optional fields
Validation rules and constraints
Character encodings and localization

Common Mapping Challenges

Name Differences

Different systems use different field names for the same data:

System A: customer_first_name, customer_last_name
System B: firstName, lastName

Type Differences

Different systems use different data types for the same concept:

System A: birth_date (Date)
System B: birthdate (Text in MM/DD/YYYY format)

Structure Differences

Different systems organize data with different hierarchies:

# System A: Flat structure
{
  "customer_id": "12345",
  "first_name": "John",
  "last_name": "Doe",
  "shipping_street": "123 Main St",
  "shipping_city": "Springfield"
}

# System B: Nested structure
{
  "customer": {
    "id": "12345",
    "name": {
      "first": "John",
      "last": "Doe"
    },
    "shipping_address": {
      "street": "123 Main St",
      "city": "Springfield"
    }
  }
}

Data Mapping Techniques

Field Mapping

Explicitly define field correspondences:

# Simple field mapping
Set Field [Customer::FirstName; ExternalData::first_name]
Set Field [Customer::LastName; ExternalData::last_name]
Set Field [Customer::Email; ExternalData::email_address]

Value Transformation

Convert between different representations, in this case using Custom Functions (which we'll explore in detail later in this workshop):

# Date format transformation using custom functions
# Convert ISO 8601 date from external system to FileMaker date
Set Field [Customer::BirthDate; Date.FromISO(ExternalData::birth_date)]

# Convert FileMaker date to ISO 8601 format for external API
Set Field [ExternalData::formatted_date; Date.ToISO(Customer::BirthDate)]

# Handle timestamps for API integration
Set Field [Integration::LastSyncTimestamp; Timestamp.FromISO(ExternalAPI::last_updated)]
Set Field [ExternalAPI::modified_since; Timestamp.ToISO(Integration::LastSyncTimestamp)]

For more information on these date and time conversion functions, see the Date & Time Functions reference.

ID Mapping

When integrating systems, you'll often need to translate between external IDs (like QuickBooks Online IDs) and internal FileMaker IDs. This is a common mapping challenge that can be simplified with Custom Functions:

# Look up QBO ID based on FileMaker ID
Set Variable [$qboCustomerId; Value: lookupFieldValue(
  "Customers::QBO_ID";
  "Customers::CustomerID";
  Customer::CustomerID
)]

# Look up FileMaker ID based on QBO ID
Set Variable [$filemaker_id; Value: lookupFieldValue(
  "Customers::CustomerID";
  "Customers::QBO_ID";
  QBO::customerRef.value
)]

# Use the mapped ID to retrieve related data
Set Field [Integration::QBO_LineItem_ID; lookupFieldValue(
  "Product_QBO_Map::QBO_ID";
  "Product_QBO_Map::ProductID";
  LineItems::ProductID
)]

This mapping approach maintains a table of ID relationships between systems, providing a clean translation layer that simplifies integration maintenance. For more information on the lookup function used in these examples, see the lookupFieldValue function reference.

Aggregation and Splitting

Combine or separate fields:

# Combining separate name fields into full name
Set Field [TargetSystem::full_name; SourceSystem::first_name & " " & SourceSystem::last_name]

# Splitting full name into components
Set Variable [$fullName; Value: SourceSystem::full_name]
Set Variable [$spacePos; Value: Position($fullName; " "; 1; 1)]
Set Field [TargetSystem::first_name; Left($fullName; $spacePos - 1)]
Set Field [TargetSystem::last_name; Right($fullName; Length($fullName) - $spacePos)]

Default Values

Handle missing data with defaults:

# Applying default values when mapping
If [IsEmpty(SourceSystem::status)]
  Set Field [TargetSystem::status; "active"]  # Default value
Else
  Set Field [TargetSystem::status; SourceSystem::status]
End If

JSON Transformation

Transform between different JSON structures:

# Transform nested JSON to flat structure
Set Variable [$sourceData; Value: ExternalData::json_data]

# Extract nested values
Set Variable [$firstName; Value: JSONGetElement($sourceData; "customer.name.first")]
Set Variable [$lastName; Value: JSONGetElement($sourceData; "customer.name.last")]
Set Variable [$street; Value: JSONGetElement($sourceData; "customer.shipping_address.street")]
Set Variable [$city; Value: JSONGetElement($sourceData; "customer.shipping_address.city")]

# Create flat structure
Set Variable [$flatData; Value: JSONSetElement("{}";
  ["customer_id"; JSONGetElement($sourceData; "customer.id"); JSONString];
  ["first_name"; $firstName; JSONString];
  ["last_name"; $lastName; JSONString];
  ["shipping_street"; $street; JSONString];
  ["shipping_city"; $city; JSONString]
)]

Data Mapping

On this page