FileMaker developers building integrations keep asking the same question: can I lock a record through the Data API or OData and have that lock show up for everyone using the database? The short answer is no. Native locks belong to interactive clients, and the APIs are stateless. But the need is real, especially as richer interfaces move into WebViewers and the UI has to coordinate with the database through scripts.

This article describes a lease-based soft lock that works across FileMaker Pro, Go, WebDirect, WebViewers and API clients. It is my current understanding, and corrections and alternative approaches are welcome.

1. Why native locks don’t cross over #

Native record locking belongs to a client that has a record in edit mode, and it lasts only as long as the edit. The Data API and OData are stateless: each request stands alone, a script run through an API ends when it returns, and neither API has a lock endpoint. FileMaker Server also has no way to push a lock to connected clients.

A server-side script therefore can’t hold a native lock across requests, and it can’t put a field into edit mode on behalf of an API user. What a script can do is hold a native lock for its own duration. That is useless as the lock itself, but it is exactly what we need as a short mutex that makes lock transitions atomic (section 4).

2. Design principles #

1. A separate table. Lock state lives in its own Locks table, never as fields on the data record. Heartbeats written to the data record would bump its modId (breaking optimistic concurrency), collide with editors (error 301), and pollute modification timestamps, audit logs and triggers.
2. Leases, not locks. A lock is a lease with an expiry that the holder keeps renewing. A crashed client simply stops renewing.
3. One clock. All timing uses the server’s UTC milliseconds. Client clocks are never compared.
4. A fencing token. Acquiring returns a secret token. Renew, release and every write must present it, so a client that slept past its lease can’t act on a lock it no longer owns.
5. Atomic transitions. Every state change happens while the lock row is open (natively locked) inside one script, which closes the race the old design only narrowed.
6. Enforcement by privileges. Direct writes to protected tables are denied. Writes go through scripts that check the lock.
7. Two layers. The soft lock prevents concurrent editing. modId catches whatever still slips through.

One consequence is worth stating early: a lease that has expired but has not been taken over is still valid, because the token proves nobody else acquired the lock in between. Expiry only decides when others may take over.

3. The Locks table #

One row per lockable record, created lazily on first use.

The unique lock_key turns a creation race into error 504 (value not unique), which the scripts treat as “someone else created it first, find it again”.

Add a small LockLog table (ts_ms, lock_key, event, owner_id, generation, detail). Events: ACQUIRE, REACQUIRE, TAKEOVER, DENIED, RELEASE, EXPIRE, FORCE_RELEASE, WRITE_OK, WRITE_REJECTED. It costs almost nothing and saves hours when someone asks why a record was locked.

4. The scripts #

All scripts take a JSON parameter, return JSON through Exit Script, and have Run script with full access privileges enabled. A custom function LockResult ( ok ; code ; message ; data ) builds the result:

{ "ok": 1, "code": "GRANTED", "message": "", "data": { ... } }

4.1 Lock_Open_Row (subscript) #

Finds the lock row, creates it if allowed, and leaves it open in the current session. Everything else builds on this.

# Lock_Open_Row   param: { key, table, id, create }
Set Error Capture [ On ]
Set Variable [ $p ; Get ( ScriptParameter ) ]
Set Variable [ $key ; JSONGetElement ( $p ; "key" ) ]
Set Variable [ $create ; GetAsNumber ( JSONGetElement ( $p ; "create" ) ) ]
Go to Layout [ "Locks" ]
Set Variable [ $try ; 0 ]
Loop
  Set Variable [ $try ; $try + 1 ]
  Exit Loop If [ $try > 6 ]
  Enter Find Mode [ Pause: Off ]
  Set Field [ Locks::lock_key ; "==" & $key ]
  Perform Find [ ]
  If [ Get ( LastError ) = 401 ]                # no row yet
    If [ not $create ]
      Exit Script [ "MISSING" ]
    End If
    New Record/Request
    Set Field [ Locks::lock_key ; $key ]
    Set Field [ Locks::target_table ; JSONGetElement ( $p ; "table" ) ]
    Set Field [ Locks::target_uuid ; JSONGetElement ( $p ; "id" ) ]
    Commit Records/Requests [ No dialog ]
    If [ Get ( LastError ) ≠ 0 ]                # 504: another script created it first
      Revert Record/Request [ No dialog ]
    End If
  Else
    Open Record/Request
    If [ Get ( LastError ) = 0 ]
      Exit Script [ "OK" ]
    End If
    # 301: another script holds the row right now (milliseconds)
    Pause/Resume Script [ Duration (seconds): 0.15 ]
  End If
End Loop
Exit Script [ "BUSY" ]

4.2 Lock_Acquire #

# Lock_Acquire   param: { table, id, user, name, client, ttl }
Set Error Capture [ On ]
Set Variable [ $in ; Get ( ScriptParameter ) ]
Set Variable [ $table ; JSONGetElement ( $in ; "table" ) ]
Set Variable [ $id ; JSONGetElement ( $in ; "id" ) ]
Set Variable [ $user ; Get ( AccountName ) ]     # see section 5 for shared service accounts
Set Variable [ $ttl ; Case ( IsEmpty ( JSONGetElement ( $in ; "ttl" ) ) ; 90 ;
                       Min ( Max ( GetAsNumber ( JSONGetElement ( $in ; "ttl" ) ) ; 30 ) ; 600 ) ) ]
If [ IsEmpty ( $table ) or IsEmpty ( $id ) ]
  Exit Script [ LockResult ( 0 ; "INVALID" ; "table and id are required" ; "" ) ]
End If
Set Variable [ $key ; $table & "|" & $id ]

Perform Script [ "Lock_Open_Row" ; Parameter: JSON ( key, table, id, create = 1 ) ]
If [ Get ( ScriptResult ) ≠ "OK" ]
  Exit Script [ LockResult ( 0 ; "BUSY" ; "Lock table busy, retry" ; "" ) ]
End If

# The row is open, so these values are current and nobody else can change them
Set Variable [ $now ; Get ( CurrentTimeUTCMilliseconds ) ]
Set Variable [ $held ; not IsEmpty ( Locks::owner_id ) and Locks::expires_ms > $now ]

If [ $held and Locks::owner_id ≠ $user ]
  Set Variable [ $out ; LockResult ( 0 ; "DENIED" ; "Locked by another user" ;
      JSON ( owner = Locks::owner_name, since = Locks::acquired_ms, expires = Locks::expires_ms ) ) ]
  Revert Record/Request [ No dialog ]
  Perform Script [ "Lock_Log" ; "DENIED" ]
  Exit Script [ $out ]
End If

Set Variable [ $event ; Case ( IsEmpty ( Locks::owner_id ) ; "ACQUIRE" ; $held ; "REACQUIRE" ; "TAKEOVER" ) ]
Set Field [ Locks::owner_id ; $user ]
Set Field [ Locks::owner_name ; JSONGetElement ( $in ; "name" ) ]
Set Field [ Locks::owner_client ; JSONGetElement ( $in ; "client" ) ]
Set Field [ Locks::lock_token ; Get ( UUID ) ]              # a new token invalidates any previous holder
Set Field [ Locks::generation ; Locks::generation + 1 ]
Set Field [ Locks::acquired_ms ; $now ]
Set Field [ Locks::renewed_ms ; $now ]
Set Field [ Locks::expires_ms ; $now + $ttl * 1000 ]
Set Field [ Locks::renew_count ; 0 ]
Commit Records/Requests [ No dialog ]
If [ Get ( LastError ) ≠ 0 ]
  Exit Script [ LockResult ( 0 ; "ERROR" ; "Commit failed" ; JSON ( error = Get ( LastError ) ) ) ]
End If
Perform Script [ "Lock_Log" ; $event ]
Exit Script [ LockResult ( 1 ; "GRANTED" ; "" ;
    JSON ( token = Locks::lock_token, generation = Locks::generation, expires = Locks::expires_ms, ttl = $ttl ) ) ]

A same-user re-acquire from another session is granted and rotates the token, so the older session sees LOST on its next heartbeat. If you prefer one session per user to be strict, store and compare a session id as well.

4.3 Lock_Renew #

# Lock_Renew   param: { table, id, token, ttl }
Open the row (Lock_Open_Row, create = 0)
If [ result = "MISSING" or IsEmpty ( $token ) or Locks::lock_token ≠ $token ]
  Revert Record/Request [ No dialog ]
  Exit Script [ LockResult ( 0 ; "LOST" ; "Lock was released or taken over" ; "" ) ]
End If
# A matching token proves nobody took over, even if the lease has lapsed
Set Field [ Locks::renewed_ms ; $now ]
Set Field [ Locks::expires_ms ; $now + $ttl * 1000 ]
Set Field [ Locks::renew_count ; Locks::renew_count + 1 ]
Commit Records/Requests [ No dialog ]
Exit Script [ LockResult ( 1 ; "RENEWED" ; "" ; JSON ( expires = Locks::expires_ms ) ) ]

4.4 Lock_Release #

# Lock_Release   param: { table, id, token }
Open the row (create = 0)
If [ result = "MISSING" ]
  Exit Script [ LockResult ( 1 ; "RELEASED" ; "Nothing to release" ; "" ) ]     # idempotent
End If
If [ Locks::lock_token ≠ $token ]
  Revert Record/Request [ No dialog ]                       # never touch someone else's lock
  Exit Script [ LockResult ( 0 ; "LOST" ; "Not the current holder" ; "" ) ]
End If
Set Field [ owner_id, owner_name, owner_client, lock_token, acquired_ms, renewed_ms, expires_ms ; "" ]
Commit Records/Requests [ No dialog ]                       # generation is kept
Perform Script [ "Lock_Log" ; "RELEASE" ]
Exit Script [ LockResult ( 1 ; "RELEASED" ; "" ; "" ) ]

4.5 Lock_Status #

Read-only, used for banners and list views. It takes a list of records so a list screen costs one call, and it never returns the token.

# Lock_Status   param: { items: [ { table, id }, ... ] }
Set Variable [ $now ; Get ( CurrentTimeUTCMilliseconds ) ]
For each item:
  Find Locks::lock_key == table & "|" & id            # no Open Record needed
  If found and owner_id is not empty and expires_ms > $now
    append { id, locked = 1, owner = owner_name, client = owner_client, since = acquired_ms, expires = expires_ms }
  Else
    append { id, locked = 0 }
Exit Script [ LockResult ( 1 ; "STATUS" ; "" ; $array ) ]

4.6 Record_Update_Guarded #

This is the only way API clients write to a protected table. It checks the token, the modId and, optionally, the original values of the fields being changed.

# Record_Update_Guarded   param: { table, id, token, modId, fields: {…}, expected: {…} }
Set Variable [ $cfg ; Lock_Config ( $table ) ]     # layout, uuid field, allow-list of writable fields
Open the lock row (create = 0) in this window
If [ missing or Locks::lock_token ≠ $token ]
  Revert Record/Request ; Log WRITE_REJECTED
  Exit Script [ LockResult ( 0 ; "LOST" ; "Lock is not held by this token" ; "" ) ]
End If
# The lock row stays open for the whole write: nobody can take over, renew or release meanwhile.
# Use a second window for the data record. Changing layout in this window would commit the lock row.
New Window [ Name: "data" ]
Go to Layout [ $cfg.layout ]
Find where $cfg.uuidField == $id          # 401 -> NOT_FOUND
Open Record/Request
If [ Get ( LastError ) = 301 ]
  Close Window ; Revert the lock row
  Exit Script [ LockResult ( 0 ; "IN_USE" ; "Someone is editing this record" ; "" ) ]
End If
If [ not IsEmpty ( $modId ) and Get ( RecordModificationCount ) ≠ $modId ]
  → CONFLICT, with the current modId in data
End If
For each key in JSONListKeys ( $expected ):
  If GetField ( key ) ≠ expected value → CONFLICT, listing the field names
For each key in JSONListKeys ( $fields ):
  If key is not in $cfg.allowed → INVALID
  Set Field By Name ( $table & "::" & key ; JSONGetElement ( $fields ; key ) )
Commit Records/Requests [ No dialog ]
If [ Get ( LastError ) ≠ 0 ] → Revert, ERROR with the error code (validation errors included)
Set Variable [ $newMod ; Get ( RecordModificationCount ) ]
Close Window ; Revert the lock row (nothing changed in it) ; Log WRITE_OK
Exit Script [ LockResult ( 1 ; "OK" ; "" ; JSON ( modId = $newMod ) ) ]

On any failure path, close the window and revert both open records before exiting. The allow-list matters: without it this script is a generic write endpoint.

4.7 Lock_Cleanup and Lock_Force_Release #

Lock_Cleanup runs as a scheduled server script every few minutes. Takeover already handles expiry, so cleanup exists for tidy status data and logs:

Find Locks where owner_id is not empty and expires_ms < Get ( CurrentTimeUTCMilliseconds ) - 10000
Loop: Open Record/Request
      re-check that it is still expired (a renewal may have arrived)  → clear the owner fields, Commit, Log EXPIRE
Delete LockLog rows older than 30 days

Lock_Force_Release is for administrators: check Get ( PrivilegeSetName ), clear the row, log FORCE_RELEASE with the admin’s name. Every stuck-lock support call ends here, so build it on day one.

5. Enforcement #

A soft lock is advisory unless the server refuses writes that skip it. For the API accounts:

• Records: view only on protected tables, and no edit, create or delete. Writes go through Record_Update_Guarded.
• Locks and LockLog: no direct access. This also keeps lock_token away from other clients, since a readable token is a bearer secret.
• Scripts: execute access only to the Lock_* scripts and the guarded writers.
• Identity: use Get ( AccountName ) as the owner when each person has a FileMaker account. If a trusted backend calls with one shared service account, it passes the user in the parameter and the lock is exactly as trustworthy as that backend.

Anything with full access, such as admin tools and server scripts, can still bypass the lock. Treat the soft lock as a coordination tool for cooperating clients, not a security boundary.

6. FileMaker Pro and Go clients #

Native locking still protects Pro users editing at the same moment, but API clients can’t see a native lock until their write hits error 301. To make Pro users first-class participants, have them take a soft lock too:

A window has only one timer, so this single script does both the heartbeat and the polling, and it can act as the dispatcher if the layout already uses a timer. Hide the edit controls or block field entry (OnObjectEnter returning False when $$LOCKED_BY_OTHER = 1) and show a banner such as “Locked by Maria since 10:42”. Polling still adds up to one timer interval of latency.

7. API and WebViewer clients #

A standalone script call through the Data API looks like this (the parameter is URL-encoded in practice, and scriptResult comes back as a string you parse a second time):

GET /fmi/data/vLatest/databases/CRM/layouts/Locks_API/script/Lock_Acquire
    ?script.param={"table":"Invoices","id":"3F2A…","name":"Maria","client":"webviewer","ttl":90}

The same scripts work through OData wherever your server version can run scripts there. Rules for a browser or WebViewer UI:

• Heartbeat at TTL/3 (30 s for a 90 s TTL), and also renew immediately on visibilitychange, because browsers throttle timers in background tabs.
• Keep the token in memory per tab. Two tabs of the same user are two sessions, and the second takes over from the first.
• On unload, send a best-effort Lock_Release with fetch and keepalive: true. navigator.sendBeacon can’t set the Authorization header the Data API needs, so use it only against your own backend. The TTL is the safety net either way.
• On LOST, freeze the form, keep the user’s changes visible, and offer to reload the record. Never retry a write silently.
• On BUSY, retry with a short randomized backoff.

8. Optimistic concurrency #

Because lock traffic lives in its own table, the data record’s modId now only changes when real data changes, so it is a reliable version. The Data API returns it with every record, and a PATCH that includes a stale modId fails with error 306. Use it in two ways:

• Plain PATCH on tables without a lock requirement: send the modId you read, and on 306 reload and retry.
• Guarded writes: send the modId and the token together.

modId is coarse: any change to the record, even an unrelated field, causes a conflict. The expected object in Record_Update_Guarded is the finer option. The client sends the original values of the fields it is changing, and the script rejects the write only if those particular fields changed, so non-overlapping edits merge.

Two scope limits to document for your solution: portal and related records have their own modId, and a lock on a parent doesn’t cover its children by itself. To lock a whole document, make the lock key the root record, and let the guarded writers for child tables accept the root’s token. I haven’t verified ETag or If-Match behavior on FileMaker’s OData endpoint. If it isn’t available, call the guarded script instead.

9. Failure modes #

If your FileMaker version provides the Open Transaction and Commit Transaction steps (introduced in FileMaker 2024, as far as I know), they are a good fit inside Record_Update_Guarded for multi-record writes. They still can’t span several API requests, so they don’t replace the lease.

10. When a soft lock isn’t enough #

FileMaker doesn’t push changes, so every approach above has polling latency, and the lock table is a hot spot under heavy contention. If you need instant notification to WebViewer users or high lock volume, move lock coordination to an external service, for example Redis with SET key value NX PX ttl and pub/sub, or a small service in front of it. FileMaker scripts then call it through Insert from URL. The trade-offs are an extra dependency and a second source of truth that must be reachable from every writer. For most solutions the table-based design here is enough.

Takeaway #

You still can’t trigger a native FileMaker lock from the Data API or OData, but you can get coordinated editing across every client with a few ingredients: a dedicated Locks table, leases renewed against the server clock, a fencing token checked on every write, lock transitions made atomic by a native lock held inside the script, privileges that force writes through those scripts, and modId as the second line of defense. Build it as coordination, enforce it on the server, and log everything.