Import related CSV files into FileMaker using a source-ID-to-UUID map

A source ID and an internal ID have different jobs

An incoming project may refer to client C101 while your Clients table uses UUIDs. Preserve both identities. A mapping record connects the source’s identifier to the internal client record; each imported project stores the internal UUID as its foreign key.

Do not put C101 into a UUID foreign-key field and hope FileMaker will translate it. Do not generate a new client UUID every time another file mentions C101. Both mistakes break continuity on a repeated import.

This tutorial builds a one-way import design. It does not infer that matching names represent the same client, merge existing people, or implement ongoing synchronization.

Set up the staging and mapping tables

Make identifiers text fields. Give each stored row an internal UUID. Use a unique, always-validated MatchKey for the mapping, with no override. In this teaching fixture, both source system and external ID are restricted to uppercase ASCII letters, digits and hyphens; colon is forbidden. That makes this composite unambiguous:

SourceMap::SourceSystem & ":" & SourceMap::ExternalID

For unrestricted provider IDs, define a collision-safe encoding and comparison policy first. A separator alone is not safe when either component may contain it. Check case behavior and validation in the target file. A pre-import duplicate check alone cannot protect two concurrent writers.

Stage three parents and five children

The source system is MEADOW. The parent file contains C101 / Cedar Studio, C102 / Maple Workshop and C103 / Birch Office. A separate child file contains:

Import both files into staging without writing to Clients or Projects. Preserve SourceSystem on every staged parent and child row. Resolve a child’s parent using its SourceSystem plus ExternalClientID; check the child’s own identity using SourceSystem plus ExternalProjectID. Compare source row counts with staged counts and reject duplicate parent identities before proceeding. C103 is intentionally unused by this child batch; that is not an error.

Local review fixtures: clients CSV and projects CSV. These contain fictional text data, not a FileMaker database.

Resolve parents before writing children

Look up each source identity in SourceMap. One mapping means reuse its ClientUUID after checking that the client still exists. Zero mappings means either create a client or ask the operator to select an existing client explicitly. More than one mapping is a data-integrity error; never select the first result.

For each validated incoming client:
    Find exactly SourceSystem + ExternalID in SourceMap.
    If one valid mapping exists: reuse ClientUUID.
    If more than one exists: hold the row for repair.
    If none exists:
        Generate ClientUUID and MapUUID into variables.
        Begin a transaction in one worker window.
        Create Clients with the explicit ClientUUID.
        Create SourceMap with that same ClientUUID.
        Commit both; on any failure, keep neither.
    Record the resolved UUID only after commit succeeds.

Assign UUID variables explicitly when the new parent and its map need the value before commit. Do not depend on an auto-enter value already having run inside a transaction. Keep every database change in the transaction’s window. Claris documents window scope and deferred auto-enter behavior in Open Transaction.

Resolve and save one child at a time

For each child, require exactly one valid parent mapping. Preserve blank or unknown keys in staging with a reason and make no business record for them. After resolution, check the child’s own source identity before insertion; parent mapping alone does not make repeated child imports safe.

# On the resolved parent map record; validate context first
Set Variable [ $parentUUID ; Value: SourceMap::ClientUUID ]
Set Variable [ $projectUUID ; Value: Get ( UUID ) ]
# Use these variables in the controlled child-write transaction.
# Do not recalculate the parent UUID for each project.

Enforce a unique source identity on imported Projects too. For an already-applied child, compare its stored import receipt and source values. If unchanged, report Already applied. If changed, send it to an explicit update policy; do not overwrite the existing record as a side effect of a retry.

Capture each find, record-creation, field-write and commit error immediately. Stop on an unexpected error, including an inaccessible parent. See the existing transaction guide for the mechanics of checking and reverting a grouped write.

Re-run the fixture

Starting from an empty teaching file, the first run should create three clients, three maps and three projects. Two child rows remain unresolved. The second run should add none of those records again. P201 and P202 must still point to the same ClientUUID.

Next, add an approved mapping for C999 and retry only P204. Expect one additional project. Leave P205 unresolved until its owner supplies a source key. Finally, deliberately cause a map validation failure and confirm that no orphan client survives the failed transaction.

Also stage another provider’s C101 under SourceSystem HARBOR. It must resolve through HARBOR’s mapping, independently of MEADOW’s C101. An identical external ID does not make two providers’ records the same identity.

If the import stops after a commit but before the progress screen refreshes, inspect the durable map and child identities before retrying. The screen’s last displayed count is not the authority for what was saved.