FileMaker to Salesforce migration, part 1: inventory the solution, map the data model, extract with the Data API or ODBC and transform in staging

Note

FileMaker to Salesforce migration series - part 1 of 2. Part 1 (this post): plan, map, extract and transform - inventory, data model mapping, org preparation, Data API/ODBC extraction, staging transforms and container files. Part 2: load, reconcile and cut over - Bulk API 2.0 upserts with external IDs, load errors, reconciliation, cutover, rollback.

#The goal

Move a FileMaker solution, its tables, relationships, value lists and container files, into Salesforce objects. Users must be able to work in Salesforce on Monday morning with every record present, parents and children linked, and owners and dates correct. Every row must be traceable back to its FileMaker source.

The hard part isn't moving the rows. It's that FileMaker allows things Salesforce doesn't: free-text values where a picklist is expected, repeating fields, calculation fields people treat as data, local timestamps with no time zone, and relationships held together by match fields rather than enforced keys.

#Architecture: always stage in between

FileMaker Server ──Data API / ODBC──> staging DB (PostgreSQL) ──transform + validate──> CSV per object
                                                                                     │
Salesforce <──Bulk API 2.0 upsert on Legacy_Id__c (parents first)───────────────────┘
        └── result files (success / failed / unprocessed) ──> back into staging for reconciliation

Don't load directly from FileMaker into Salesforce. A staging database lets you rerun transforms without re-extracting, keep failed rows, compare counts, and run delta loads at cutover.

#Step 1: inventory the FileMaker solution

Get the real schema before mapping anything. On a copy of the file, use Tools > Database Design Report (FileMaker Pro Advanced, or Pro 19.x and later) and export it as XML. It lists every table, field, field type, calculation formula, relationship, value list, script and privilege set.

Classify every field:

Profile the data in staging before you decide on picklists and lengths:

-- values a picklist must accept
SELECT status, COUNT(*) FROM stg.invoices GROUP BY status ORDER BY 2 DESC;
-- orphans: children whose match field points nowhere
SELECT COUNT(*) FROM stg.invoices i
LEFT JOIN stg.customers c ON c.customer_id = i.customer_id
WHERE c.customer_id IS NULL;
-- longest text, to size fields
SELECT MAX(LENGTH(notes)) FROM stg.customers;

Warning

Orphans are normal in FileMaker and fatal in a Master-Detail load. Decide per object: create a placeholder parent ("Unknown customer - migrated"), drop the rows with sign-off, or use a Lookup instead.

#Step 2: prepare the Salesforce org

1. Create every custom object and field, plus a Legacy_Id__c text field marked External ID and Unique on every migrated object. Add Legacy_Source__c (file and table name) when several FileMaker files feed one object.
2. Load Users first and build a mapping table of FileMaker account name → Salesforce User Id for OwnerId.
3. If created and modified dates must be kept, enable Set Audit Fields upon Record Creation in Setup (User Interface settings) and grant that permission to the migration user. CreatedDate, CreatedById, LastModifiedDate and LastModifiedById can then be set on insert only.
4. Give the migration user a permission that your automation checks, and bypass flows, triggers, validation rules and duplicate rules for it during the load. A common pattern is a custom permission or hierarchy custom setting (Migration_Bypass__c) checked at the top of every flow entry condition, trigger and validation rule.
5. Do a full rehearsal in a Full Copy sandbox (or Partial Copy for big volumes) before production.

Caution

Switching off automation in production means roll-ups, sharing and integrations don't run for migrated rows. Write down every bypass, re-enable each one after the load, and recalculate anything they would have maintained (roll-ups, sharing, derived fields).

#Step 3: extract from FileMaker

#Option A: FileMaker Data API (no driver, works through firewalls)

Enable the Data API in the FileMaker Server Admin Console. Give a dedicated account a privilege set with the fmrest extended privilege, and create a plain list layout per table that holds only the fields you migrate. The Data API returns only fields on the layout.

import csv, requests

BASE = "https://fms.example.local/fmi/data/vLatest/databases/Sales"
s = requests.Session()
tok = s.post(f"{BASE}/sessions", auth=("migration", PASSWORD), json={}).json()["response"]["token"]
s.headers["Authorization"] = f"Bearer {tok}"

def extract(layout, out_file, page=1000):
    offset, writer = 1, None
    with open(out_file, "w", newline="", encoding="utf-8") as f:
        while True:
            r = s.get(f"{BASE}/layouts/{layout}/records", params={"_offset": offset, "_limit": page})
            if r.status_code != 200:
                if r.json()["messages"][0]["code"] == "401":   # 401 = no records match (end of data)
                    break
                r.raise_for_status()
            rows = r.json()["response"]["data"]
            if not rows:
                break
            for rec in rows:
                d = rec["fieldData"] | {"_recordId": rec["recordId"]}
                writer = writer or csv.DictWriter(f, fieldnames=list(d.keys()))
                if f.tell() == 0:
                    writer.writeheader()
                writer.writerow(d)
            offset += page

try:
    extract("mig_Customers", "customers.csv")
    extract("mig_Invoices", "invoices.csv")
finally:
    s.delete(f"{BASE}/sessions/{tok}")

Repeating fields come back as field(1), field(2) only if each repetition is placed on the layout. Portal (related) records come back under portalData, but extract each table from its own layout instead.

#Option B: ODBC (faster for large tables)

Turn on ODBC/JDBC sharing for the file and the account's fmxdbc extended privilege, install the Claris ODBC driver, and pull with SQL (SELECT ... FROM "Customers") into staging. This suits tables of millions of rows. Calculation and container fields behave differently over ODBC, so test them.

Both ways, record source row counts per table at extraction time. You will need them for reconciliation.

#Step 4: transform in staging

The rules that cause most load failures:

Write one CSV per object with Salesforce API field names as headers. Reference parents by external ID instead of Salesforce Ids, using the relationship name in the header:

Legacy_Id__c,Name,Invoice_Date__c,Amount__c,Account__r.Legacy_Id__c,OwnerId
INV-000101,INV-000101,2025-02-11,1520.00,CUST-00042,005xx000001AbCdAAK

For a standard lookup such as Contact.AccountId the header is Account.Legacy_Id__c. For a custom lookup Account__c it is Account__r.Legacy_Id__c. This removes the classic "load parents, export their Ids, VLOOKUP into children" step and makes every load safely re-runnable.

#Step 5: container fields to Salesforce Files

Container fields hold PDFs, images and documents. Through the Data API, a container field's value is a URL. Download it with the same authenticated session, then create a ContentVersion linked to the migrated record:

import base64, pathlib
def upload_file(sf_session, instance, path, record_id):
    body = {
        "Title": path.stem,
        "PathOnClient": path.name,
        "VersionData": base64.b64encode(path.read_bytes()).decode(),
        "FirstPublishLocationId": record_id,   # links the file to the record
    }
    r = sf_session.post(f"{instance}/services/data/v62.0/sobjects/ContentVersion", json=body)
    r.raise_for_status()

Base64 JSON uploads suit small and medium files. For large files, use the multipart REST upload described in the Salesforce docs, and check the current file size limits. Files count against file storage, not data storage, so estimate both before you start.

#Next: load, reconcile and cut over

At this point you have one clean, validated CSV per object in staging, with parents referenced by Legacy_Id__c, and your files ready for upload. Part 2 loads them into Salesforce with Bulk API 2.0 in dependency order, handles the load errors you will meet, reconciles source and target, and runs the cutover with a rollback plan.

#Further reading

• In this series: Part 2 - load with Bulk API 2.0, reconcile and cut over.
• Claris: FileMaker Data API Guide, Database Design Report, ODBC and JDBC Guide.
• Salesforce: Enable Set Audit Fields upon Record Creation, ContentVersion object reference.