Read a JSON API response in FileMaker.

1. Inspect the whole response.

{
  "data": [
    {"id": "00042", "name": "Maya Chen", "email": null},
    {"id": "00043", "name": "Alex Rivera", "email": "alex@example.com"}
  ],
  "pagination": {"nextCursor": null}
}

Format this in the JSON formatter. The data member is an array. Each item is an object. pagination is a separate object, not a third contact.

For a real request, first confirm transport and HTTP success under the API’s documented contract. An error response can also be valid JSON. Do not treat successful formatting as permission to import it.

2. Check the envelope before extracting rows.

Require an object at the root and an array at data. Run these checks separately so an unexpected response stops before row processing:

JSONGetElementType ( $response ; "" ) = JSONObject

JSONGetElementType ( $response ; "data" ) = JSONArray

Both should return 1 for this sample. If either fails, retain a redacted diagnostic and handle the response as unexpected. Avoid converting a missing data key into “zero contacts” silently.

3. Read a value using its path.

Array indexes start at zero. The first contact’s ID is:

JSONGetElement ( $response ; "data[0].id" )

Expect text 00042. The second name is at data[1].name, giving Alex Rivera. Keys are case-sensitive. These paths describe this sample; another API may use a different envelope or key spelling.

ValueCount ( JSONListKeys ( $response ; "data" ) )

After validating the array, this count gives 2. For "data": [], expect zero and skip the loop. A loop should start its index at zero, stop before the count, and increment exactly once per item.

4. Decide what null means for your app.

The first email is explicitly null. Inspect its type before reading it:

JSONGetElementType ( $response ; "data[0].email" ) = JSONNull

Expect 1. A missing email key instead produces a type-check error; an empty string has type JSONString. Those distinctions matter during updates. Your contract might mean “clear the local email” for null, “leave it unchanged” for an omitted key and “save blank” for an empty string—or something else. Follow the service’s documented meaning.

Do not use an empty extracted value alone to decide which case occurred. In particular, a partial response should not erase a field just because it omitted that field.

5. Plan the mapping before writing records.

For each array item, require an object and a nonempty text ID, validate the remaining types, then match the remote ID to a dedicated local field. Keep a count of accepted, rejected and unchanged items. Decide whether duplicate IDs are an error before importing.

This sample’s nextCursor is null, meaning no next page under our fictional contract. Real APIs may use links, offsets or tokens. Follow their rules and stop conditions; the length of one array does not establish the total number of records.

• Test an empty array and a single-item array.
• Test an item with a missing ID and one with the wrong type.
• Test null, omitted and blank email values separately.
• Test multiple pages and a repeated page before enabling writes.

Keep raw examples fictional or redacted. The browser formatter checks syntax; field mapping, duplicates and commit behavior must be tested in your FileMaker working copy.

Continue reading

• Create JSON from FileMaker fields
• Pass JSON script parameters in FileMaker
• Write your first FileMaker ExecuteSQL query