InRiver ↔ FileMaker integration patterns: wiring a PIM into a custom app

Why connect a PIM to a FileMaker app at all

A PIM (Product Information Management system) like inRiver becomes the source of truth for the rich, market-facing version of your product data: marketing copy, taxonomy, channel assignments, media, enrichment. But that data almost never originates there. It originates in the system the business already runs on — and for a lot of companies that system is a mature FileMaker solution holding SKUs, costs, vendors, dimensions, and the operational logic nobody wants to rebuild.

So you end up with two systems that both need the same products, and a person in the middle pasting rows from one into the other. That manual bridge is where the pain lives: typos, half-finished updates, products that exist in FileMaker but never made it to the web, and a "which one is right?" question every time a price changes. The fix is a real integration — FileMaker stays the operational source of truth, inRiver owns enrichment and publishing, and an API connection keeps the core fields in sync without anybody copy-pasting.

In a recent integration I built exactly this: a FileMaker solution pushing products and their variants up to inRiver, linking them, and keeping core fields current on a nightly schedule. What follows are the patterns that survived contact with production — generalized so you can reuse them, not lifted from any one client's system.

Architecture overview

The shape of the integration is deliberately boring, because boring is what you want running unattended. FileMaker is the application and data layer. A thin configuration table decides which inRiver environment you're talking to. Every HTTP call funnels through one parent script. Above that sit small, single-purpose "core" scripts — upsert, link, read, delete — and above those sit record-aware wrappers that know your tables and build payloads. inRiver's REST API is the only thing on the far side.

A few decisions are worth calling out before the code:

• Sync direction. Phase one is usually one-way: FileMaker → inRiver. But you should architect for two-way from day one, because the read-back (pulling channel IDs, image URLs, or computed fields down into FileMaker) almost always becomes phase two. Keep the HTTP layer symmetric so reads aren't a rewrite.
• Auth. inRiver authenticates with a static API key sent in an X-inRiver-APIKey header on every request. There's no token dance, which keeps the client simple — but it also means the key is the whole ballgame, so it never gets hardcoded or committed.
• One HTTP chokepoint. Routing every call through a single script means auth, base-URL resolution, header capture, and error detection are written once and inherited everywhere.

Pattern 1 — Credential & environment config

The first thing I build is the thing most integrations bolt on last: a clean way to hold credentials and flip between test and live without touching code. This is the same discipline you'd use for Shopify keys, Stripe keys, or any other provider — the API is different, the hygiene is identical.

Create a Settings table with one row per environment:

Settings
| environment | baseURL                                                      | apiKey        |
|-------------|--------------------------------------------------------------|---------------|
| test        | https://api-test1a-use.productmarketingcloud.com/api/v1.0.0/ | YOUR_TEST_KEY |
| live        | https://apiuse.productmarketingcloud.com/api/v1.0.0/         | YOUR_LIVE_KEY |

The parent script resolves both values at call time with a small ExecuteSQL lookup, choosing the row from a single global toggle. Set $$devEnv while you're developing; clear it for production. One switch, no scattered If blocks:

# --- Resolve environment: "live" by default, "test" when the dev flag is set ---
Set Variable [ $env ; Value: If ( not IsEmpty ( $$devEnv ) ; "test" ; "live" ) ]

Set Variable [ $baseURL ; Value:
    ExecuteSQL ( "SELECT \"baseURL\" FROM \"Settings\" WHERE \"environment\" = ?" ; "" ; "" ; $env ) ]

Set Variable [ $apiKey ; Value:
    ExecuteSQL ( "SELECT \"apiKey\" FROM \"Settings\" WHERE \"environment\" = ?" ; "" ; "" ; $env ) ]

Why a table and not a custom function full of literals? Because credentials change, and you want rotating a leaked key to be a row edit a non-developer can do — not a code change and a redeploy. It also keeps the keys out of your script text, which is where they'd otherwise end up in a screen-share or an exported clone.

Pattern 2 — Entity retrieval

Before you write anything, you need to read. Reading also doubles as your smoke test: if you can fetch a known entity, your auth, base URL, and JSON handling are all wired correctly.

inRiver reads entity data through POST entities:fetchdata. You pass an array of entity IDs and an objects string that says how much you want back (entity summary, field values, links, media, and so on). The response is always an array — even for a single entity — so the read script unwraps element [0] and hands callers a plain object:

# Parameter: { "entityId": 2060, "objects": "EntitySummary,FieldValues", "fieldTypeIds": "ProductName,ProductPrice" }

Set Variable [ $params       ; Value: Get ( ScriptParameter ) ]
Set Variable [ $entityId     ; Value: JSONGetElement ( $params ; "entityId" ) ]
Set Variable [ $objects      ; Value: JSONGetElement ( $params ; "objects" ) ]
Set Variable [ $fieldTypeIds ; Value: JSONGetElement ( $params ; "fieldTypeIds" ) ]

If [ IsEmpty ( $objects ) ]
    Set Variable [ $objects ; Value: "EntitySummary,FieldValues" ]
End If

# entityIds must be a JSON array of integers, not strings
Set Variable [ $body ; Value: JSONSetElement ( "{}" ;
    [ "entityIds[0]" ; GetAsNumber ( $entityId ) ; JSONNumber ] ;
    [ "objects"      ; $objects                   ; JSONString ]
) ]

# Only narrow to specific fields if the caller asked for it
If [ not IsEmpty ( $fieldTypeIds ) ]
    Set Variable [ $body ; Value: JSONSetElement ( $body ; "fieldTypeIds" ; $fieldTypeIds ; JSONString ) ]
End If

Set Variable [ $callParams ; Value: JSONSetElement ( "{}" ;
    [ "method"   ; "POST"               ; JSONString ] ;
    [ "endpoint" ; "entities:fetchdata" ; JSONString ] ;
    [ "body"     ; $body                ; JSONString ]
) ]
Perform Script [ "APICall" ; Parameter: $callParams ]

# Unwrap the array: fetchdata returns [ {...} ] even for one entity
Set Variable [ $entity ; Value: JSONGetElement ( $$inRiver_Response ; "[0]" ) ]

If [ IsEmpty ( $entity ) or $entity = "?" ]
    Set Variable [ $$inRiver_Error ; Value: "Entity " & $entityId & " not found" ]
    Exit Script [ Result: "" ]
End If

Exit Script [ Result: $entity ]

When you need to find entities rather than fetch known IDs, use POST query with field criteria; it returns matching entity IDs that you then feed to fetchdata. Two things to design for here: filtering (query by your business key so you can answer "does this already exist?") and pagination/batching — inRiver caps a single request at 100 first-level entities, 5,000 linked entities, and 100,000 field values. Above those limits the API returns 4xx and writes nothing, so chunk your reads and writes well under the ceiling.

Pattern 3 — One reusable CRUD engine

This is the heart of the whole thing. Rather than scatter Insert from URL steps across a dozen scripts, build one documented parent script — call it APICall — that takes a method, an endpoint, and a body, and does every piece of HTTP plumbing exactly once. Every other script becomes a thin wrapper that hands APICall a JSON parameter.

Here's the parent script. Note the cURL options are built as a separate string (FileMaker uses a cURL library, not the command line), the API key rides in the header, and -D dumps the response headers into a container so we can read the status line back:

# === APICall — the only script that talks HTTP ===
# Parameter (JSON): { "method": "POST", "endpoint": "entities:upsert", "body": "{...}" }

Set Variable [ $params   ; Value: Get ( ScriptParameter ) ]
Set Variable [ $method   ; Value: JSONGetElement ( $params ; "method" ) ]
Set Variable [ $endpoint ; Value: JSONGetElement ( $params ; "endpoint" ) ]
Set Variable [ $body     ; Value: JSONGetElement ( $params ; "body" ) ]

# Resolve $baseURL and $apiKey from the Settings table (see Pattern 1)
Set Variable [ $url ; Value: $baseURL & $endpoint ]

# --- Build cURL options: method, auth header, Accept, and a header dump ---
Set Variable [ $curl ; Value:
    "-X " & $method &
    " -H \"X-inRiver-APIKey: " & $apiKey & "\"" &
    " -H \"Accept: application/json\"" &
    " -D $responseHeaders" ]

# Content-Type + body only for writes
If [ $method = "POST" or $method = "PUT" ]
    Set Variable [ $curl ; Value: $curl &
        " -H \"Content-Type: application/json\"" &
        " --data @$body" ]
End If

# Reset the return globals before every call
Set Variable [ $$inRiver_Response   ; Value: "" ]
Set Variable [ $$inRiver_StatusCode ; Value: "" ]
Set Variable [ $$inRiver_Error      ; Value: "" ]

Insert from URL [ Select ; With dialog: Off ;
    Target: $$inRiver_Response ; $url ; Verify SSL Certificates ;
    cURL options: $curl ]

The call itself is one step. Everything interesting happens on the way back out — and parsing the response is its own discipline. inRiver doesn't hand you the HTTP status in the body, so read it from the header dump, then check the body only when the status says to:

# --- Pull the status code out of the header dump ("HTTP/1.1 200 OK") ---
Set Variable [ $$inRiver_StatusCode ; Value:
    Let ( [
        firstLine = GetValue ( $responseHeaders ; 1 ) ;
        parts     = Substitute ( firstLine ; " " ; "¶" )
    ] ;
        GetValue ( parts ; 2 )
    ) ]

# --- 4xx / 5xx → capture inRiver's error message from the JSON body ---
If [ GetAsNumber ( $$inRiver_StatusCode ) ≥ 400 ]
    Set Variable [ $$inRiver_Error ; Value: JSONGetElement ( $$inRiver_Response ; "errorMessage" ) ]
End If

# --- FileMaker-level failure (DNS, TLS, timeout) never sets an HTTP status ---
If [ Get ( LastError ) ≠ 0 ]
    Set Variable [ $$inRiver_Error ; Value: "FileMaker error " & Get ( LastError ) & ": connection or cURL failure" ]
End If

Exit Script [ Result: $$inRiver_Response ]

Now the create/update path. inRiver's entities:upsert is the workhorse: it finds an existing entity by the fields you name in keyFieldTypeIds and either updates it or creates it. That single endpoint covers both your C and U. The one quirk to internalize is that fieldValues isn't an object — it's an array of two-element arrays, [ "fieldTypeId", value ], with each value carrying its real JSON type. So a thin upsert wrapper walks a flat fields object and emits that shape, preserving types as it goes:

# === UpsertEntity (core) — builds the array-of-arrays inRiver wants ===
# Walk every key in the flat $fieldsObj → [ ["ProductNumber","PR-1001"], ["ProductPrice", 89.95], ... ]

Set Variable [ $keys       ; Value: JSONListKeys ( $fieldsObj ; "" ) ]
Set Variable [ $fieldValues ; Value: "[]" ]
Set Variable [ $i ; Value: 1 ]
Loop [ Flush: Always ]
    Exit Loop If [ $i > ValueCount ( $keys ) ]
    Set Variable [ $key  ; Value: GetValue ( $keys ; $i ) ]
    Set Variable [ $val  ; Value: JSONGetElement ( $fieldsObj ; $key ) ]
    Set Variable [ $type ; Value: JSONGetElementType ( $fieldsObj ; $key ) ]  # preserve string/number/boolean
    Set Variable [ $idx  ; Value: $i - 1 ]
    Set Variable [ $fieldValues ; Value: JSONSetElement ( $fieldValues ;
        [ "[" & $idx & "][0]" ; $key ; JSONString ] ;
        [ "[" & $idx & "][1]" ; $val ; $type ]
    ) ]
    Set Variable [ $i ; Value: $i + 1 ]
End Loop

The body that produces, ready to POST to entities:upsert, looks like this. Note the literal numbers and booleans — getting those types right is what separates a clean insert from a silent rejection (more on that in the gotchas):

[
  {
    "entityTypeId": "Product",
    "fieldSetOptions": { "fieldSetId": "DefaultFieldSet", "wipeOtherFields": false },
    "fieldValues": [
      [ "ProductNumber",      "PR-1001" ],
      [ "ProductName",        "Example Product" ],
      [ "ProductPrice",       89.95 ],
      [ "ProductDisplayOnWeb", true ]
    ],
    "keyFieldTypeIds": [ "ProductNumber" ]
  }
]

Read and delete round out CRUD. The read is Pattern 2. The delete is a DELETE entities/{id} with no body, where success is a 204 No Content rather than a JSON payload:

# === DeleteEntity (core) — id goes in the URL, there is no body ===
Set Variable [ $callParams ; Value: JSONSetElement ( "{}" ;
    [ "method"   ; "DELETE" ; JSONString ] ;
    [ "endpoint" ; "entities/" & GetAsNumber ( $entityId ) ; JSONString ] ;
    [ "body"     ; "" ; JSONString ]
) ]
Perform Script [ "APICall" ; Parameter: $callParams ]

# 204 (and sometimes 200) = success; DELETE returns no body
If [ GetAsNumber ( $$inRiver_StatusCode ) = 204 or GetAsNumber ( $$inRiver_StatusCode ) = 200 ]
    Exit Script [ Result: 1 ]
End If

The payoff of this layering: APICall is the only place that knows about cURL, headers, or status codes. Adding a new operation is a ten-line wrapper, not another copy of the HTTP plumbing.

Pattern 4 — Parent/child entity linking

Products and their variants (SKUs) are separate entities in inRiver — a Product and one or more Item entities — joined by a link. Creating the entities does not create the relationship; linking is a distinct step against a distinct endpoint.

The hard-won lesson here: links cannot reliably be created inside the upsert body. The Swagger hints at a links array, but the entity references inside it are undocumented and undiscoverable; I tried several shapes and every one failed with a different error. The endpoint that actually works is a plain POST links between two entities that already exist:

# === CreateLink (core) ===
# Parameter: { "linkTypeId": "ProductItem", "sourceEntityId": 94825, "targetEntityId": 94826 }

Set Variable [ $body ; Value: JSONSetElement ( "{}" ;
    [ "linkTypeId"     ; $linkTypeId ; JSONString ] ;
    [ "sourceEntityId" ; $sourceId   ; JSONNumber ] ;
    [ "targetEntityId" ; $targetId   ; JSONNumber ] ;
    [ "isActive"       ; "true"      ; JSONBoolean ]   # see callout — do not omit
) ]

Set Variable [ $callParams ; Value: JSONSetElement ( "{}" ;
    [ "method"   ; "POST"  ; JSONString ] ;
    [ "endpoint" ; "links" ; JSONString ] ;
    [ "body"     ; $body   ; JSONString ]
) ]
Perform Script [ "APICall" ; Parameter: $callParams ]
Set Variable [ $linkId ; Value: JSONGetElement ( $$inRiver_Response ; "id" ) ]

The link payload itself is small and worth memorizing — sourceEntityId is the parent, targetEntityId is the child, and the linkTypeId matches a link type configured in your inRiver model:

{
  "linkTypeId": "ProductItem",
  "sourceEntityId": 94825,
  "targetEntityId": 94826,
  "isActive": true
}

That makes the end-to-end workflow a clean three-step sequence, each step caching the ID it produces back onto the FileMaker record:

1. Upsert the parent Product → cache its returned EntityID.
2. Upsert each child Item (same engine, entityTypeId: "Item") → cache each EntityID.
3. Link each child to the parent via CreateLink → cache the returned LinkID.

Those cached IDs aren't just bookkeeping — they're what makes the next pattern possible.

Pattern 5 — Error handling & idempotency

An integration that works once in a demo is easy. One that can be re-run, interrupted, and re-run again without creating a mess is the actual job. Two ideas carry most of the weight.

Make every call inspectable. Because APICall populates the same three globals on every request — $$inRiver_Response, $$inRiver_StatusCode, and $$inRiver_Error — any caller can make a decision with one check. The rule across the whole library is simply: check $$inRiver_Error first; if it's empty, you succeeded. Capturing both the HTTP status (from the header dump) and FileMaker's own Get(LastError) means you can tell a real API rejection apart from a dropped connection — they want very different responses (surface vs. retry).

Never create what might already exist. Idempotency comes from two layers working together. First, the API: entities:upsert with a stable keyFieldTypeIds is inherently idempotent — run it twice and you update, not duplicate. Second, the FileMaker side: cache inRiver's returned IDs on the record and pre-check before any create.

# --- Don't create a duplicate: bail out (or switch to update) if we already pushed this one ---
If [ not IsEmpty ( Product::EntityID ) and Product::EntityID ≠ 0 ]
    # Already in inRiver with a cached entity ID — update instead of insert
    Exit Script [ Result: Product::EntityID ]
End If

# Belt-and-suspenders: confirm by business key in case the ID was never cached
Perform Script [ "QueryByProductNumber" ; Parameter: Product::ProductNumber ]
Set Variable [ $existingId ; Value: Get ( ScriptResult ) ]
If [ not IsEmpty ( $existingId ) ]
    Set Field [ Product::EntityID ; $existingId ]   # cache it and move on
    Exit Script [ Result: $existingId ]
End If

The same applies to links: a cached LinkID on a SKU means "already linked — skip," which keeps a re-run from stacking duplicate relationships. For retries, keep it disciplined: retry only on transient failures (5xx, timeouts, throttling), retry once or twice with a short backoff, and never retry a 4xx — a bad payload will be just as bad the second time. And because inRiver enforces hard batch ceilings (100 first-level entities, 5,000 linked, 100,000 field values per request), the safe failure mode for a nightly job is per-record: one bad row logs its reason and the batch keeps going.

Gotchas & lessons learned

The patterns above are the clean version. These are the things that cost me time so they don't cost you yours:

• Number vs. string types are not cosmetic. Building JSON with the wrong type constant sends "89.95" (a string) where inRiver wants 89.95 (a number), and it may reject the whole entity. Use JSONNumber for numeric field types and JSONBoolean for flags — and preserve types when you transform a fields object rather than stringifying everything.
• CVL keys are case-sensitive and rarely human-readable. A controlled value list might store "ACME001" where you assumed "Acme". The wrong key can silently null the field or throw an error. Look up the real keys in inRiver's model before pushing CVL fields — a small FileMaker lookup table pays for itself.
• Don't push expression-driven fields. Some field types are computed by inRiver from other fields (handles, slugs, derived codes). Pushing a value for them is ignored at best and conflicts at worst. Check a field's expression flag and skip anything inRiver builds itself.
• "Type error" is a liar. inRiver returns a generic Type error for many unrelated input problems — an unrecognized property, a missing required field, an unsupported value shape. When you see it, don't fixate on types; re-read the whole payload.
• Deleting a parent can orphan its children. Depending on tenant link rules, deleting a Product may leave its Items behind with no parent link. Before a destructive delete, fetch the entity's links and warn the user how many children are attached.
• The docs send you to Swagger. inRiver's written docs are thin; the Swagger/OpenAPI explorer is the real source of truth. Budget time to test calls there before you wire them into FileMaker.

When this approach fits

This layered, single-engine pattern is the right call when FileMaker is a genuine system of record (not a toy), when the product/variant model maps onto inRiver's entity-and-link structure, and when you need the integration to run unattended and survive being re-run. It scales from a manual "push this product" button to a nightly change-detection sync without changing the core scripts — you only add wrappers.

It's overkill if you have a handful of products you'll touch once. It's exactly right if product data is going to keep flowing between the two systems for years, which is the situation most teams are actually in by the time they reach for a PIM.