Open Transaction

Starts a transaction; subsequent record changes are held in the transaction until a Commit Transaction or Revert Transaction script step is performed.

Note  Transactions are handled within the originating window. Actions taken in different windows from the window of the originating transaction will be committed automatically outside existing transactions.

See also 

Options 

  • Skip auto-enter options overrides any field options set for automatic data entry. When this option is deselected, auto-enter options are performed in fields for all new or modified records only when the Commit Transaction script step is reached. See Defining automatic data entry.

  • Skip data entry validation overrides any data entry validation options set for fields and commits the record anyway. This option only skips validation for fields set with the Only during data entry validation option in the Options for Field dialog box; fields set to Always validate will still validate, even if the Skip data entry validation option is selected. See Defining field validation.

  • Override ESS locking conflicts allows clients of ODBC data sources to force-commit changes when optimistic locking otherwise prevents the commit action. ODBC data sources are also referred to as external SQL sources (ESS).

Compatibility 

Product Supported
FileMaker Pro Yes
FileMaker Go Yes
FileMaker WebDirect Yes
FileMaker Server Yes
FileMaker Cloud Yes
FileMaker Data API Yes
Custom Web Publishing Yes

Originated in version 

19.6.1

Description 

A transaction consists of changes to one or more records in the current window. Changes in a transaction can affect multiple tables in multiple database files. All changes in a transaction are committed together or, if an error occurs, reverted together. A transaction starts with an Open Transaction script step. After you start a transaction, subsequent record changes are held in the transaction until a Commit Transaction or Revert Transaction script step is performed. The steps below can change records stored in the databases schema and can be used as transactional script steps.

When you add an Open Transaction script step to a script, the Commit Transaction script step is automatically paired with it.

Transactions:

  • Start by trying to exit, and commit any changes in, the currently active record before proceeding. If it can't exit the active record, Open Transaction returns an error and the script steps within the transaction are skipped.

  • Combine one or more field or table modifications as a single set of changes

  • Commit or revert an entire set of changes

  • Group field or table modifications between the Open Transaction and Commit Transaction script steps

  • Allow you to create a more reliable set of changes

  • Can be automatically reverted using the Revert Transaction script step

  • Succeed (commit) or fail (revert) as a group

  • Succeed when schema rules have been met

  • Automatically revert when schema rules have not been met

  • Can optionally skip validation settings and override automatic reverts when schema rules have not been met. Overriding schema rules can break transactional integrity.

  • Are modified in a local temporary file before the Commit Transaction script step saves the changes to the main file

Transactions automatically commit and end when:

  • The following dialog boxes are used: Manage Database, Manage Container, or Manage Data Source

  • The following actions or script steps are performed: Save a Copy as or Re-Login.

  • The file is closed

By default, transactions automatically revert when:

  • Unresolved schema error exists before beginning a transaction. Transactions are automatically skipped when this occurs.

  • A change to a field, which is set to always validate, does not match the field's validation options

  • The Revert Transaction script step is used

  • A script is canceled

  • The Halt Script script step is used within a transaction

  • A script is canceled using the Script Debugger

  • There are privilege errors such as no field access, no record access, and no table access

To control whether transactions automatically revert when certain errors occur, use the Set Revert Transaction on Error script step.

Notes 

  • Nested transactions:

    • Are not permitted within the same script (the Script Workspace prevents putting one transaction inside another)

    • Can be approximated by opening a transaction in a main script, then using the Perform Script script step to call a sub-script. Everything in the sub-script is performed within the scope of the currently open transaction. If the sub-script contains Open Transaction and Commit Transaction steps, those steps are skipped and return error 3 ("Command is unavailable"), but everything before, between, and after those steps in the sub-script is performed within the main script's transaction. If the sub-script includes a Revert Transaction step, then it can revert the main script's transaction and jump to the transaction's matching Commit Transaction step. See Example 4.

  • The Delete Record action, when used within transactions, deletes a single record at a time.

  • The Truncate Table action can't be reverted.

  • The Import Records action doesn't show a summary when used within transactions.

  • The Revert Transaction script step doesn't revert the Auto Enter Serial Increment value.

  • The Save record changes automatically option in the Layout Setup is ignored during transactions.

  • Changes made by the Execute FileMaker Data API script step are not included in the script's transaction. Any operation completed within this script step, even if enclosed within a transaction, will not be reverted.

  • To determine whether a transaction is currently open, use the Get(TransactionOpenState) function.

Example 1 

Opens and commits a transaction.

Copy 
Open Transaction []
    Open Record/Request
    Set Field [ Assets::Description ; "descriptive text" ]
Commit Transaction

Example 2 

Opens and commits a transaction without data entry validation.

Copy 
Open Transaction [ Skip data entry validation ]
    Open Record/Request
    Set Field [ Assets::Description ; "descriptive text" ]
Commit Transaction

Example 3 

Opens and commits a transaction without ESS locking protection.

Copy 
Open Transaction [ Override ESS locking conflicts ]
    Open Record/Request
    Set Field [ Assets::Description ; "descriptive text" ]
Commit Transaction

 

Example 4

The main script opens a transaction, sets TextField, then calls a sub-script. Because the sub-script is called while a transaction is already open, its Open Transaction step is skipped, TextField is set to a different value, then the condition in the Revert Transaction step is evaluated.

If NumberField is 1, the current transaction (opened in the main script) is reverted. The sub-script's Commit Transaction step is skipped, then control passes back to the main script's Commit Transaction step, skipping any steps after Perform Script. When the main script is finished, the value of TextField is unchanged.

If NumberField is 0, then the sub-script doesn't revert the transaction, so control passes back to the step after Perform Script, where TextField is set to abc123 and the transaction is committed.

Main script

Copy 
Open Transaction [ ]
    Set Field [ Table::TextField ; "abc" ]
    Perform Script [ Specified: From list ; "Sub-script" ; Parameter: ]
    Set Field [ Table::TextField ; "abc123" ]
Commit Transaction

Sub-script

Copy 
Open Transaction [ ]
    Set Field [ Table::TextField ; "xyz456" ]
    Revert Transaction [ Condition: Table::NumberField = 1 ]
Commit Transaction

Related topics