ACID – Is FileMaker Consistent? (Part 3)

The consistency property of the ACID model mainly involves topics related to validation, constraints, and even the implementation of business rules. Related FileMaker features include:

• Field Validation Options
  • Basic data requirements, unique, non-empty, type, etc.
  • Member of value list
  • Validation by custom calculation or stored function
• Field Auto-Enter Options (limited situations)
• Calculated Fields (stored)
• Cascading Record Deletion
• Triggers (not really at database level, relates to user interface)

A database developer architects a solution with some expectations on how business information will be structured. He or she might even define rules about the type of data and related information that should be stored. The expectation is that a new solution database, where all defined rules are satisfied, starts out in a “consistent” state. Going further, any operation will transition the database into a new “consistent” state. For purposes of this article, we will focus on consistency of the record data. Some assessments of consistency focus at a lower level, on the invalid or potentially corrupt state of database files while a transaction is in flight. I’m going to defer those considerations to the durability section.

Here are a few rules that you might implement in a typical database: Maybe you want to define an identifier field on a table to always be non-empty and unique. Further, you might want to require every sales order to have a customer id (foreign key). At a higher level, you might be storing a recipe for mixing ratios of oil and vinegar, where the total of the two parts must equal 100%.

Calculated Fields

The task of applying a rule which says column A plus column B must equal column C can be a real challenge in some database platforms. In FileMaker, this task almost becomes trivial. Developers typically identify dependent and independent variables, and then simply define one or more calculated fields. When all of the functional inputs to the calculation are local to a table (and a few other requirements) then this result field is updated automatically at the database engine level. When fields on the record are edited, the formula or function is automatically re-executed, storing the new result in the adjacent field, and potentially updating that field’s index. No scripting or user error can violate the rule of the calculated field, helping us to keep information consistent.

FileMaker also supports un-stored calculated fields.  While these are of huge benefit, they don’t really fall under the scope of consistency, because this data is never stored or indexed. Instead, the formula or function is evaluated on demand. These might be perceived as consistent, but for this discussion we are focused on stored data.

Field Validation

Non-calculated fields support various validation options, that can be configured in numerous combinations as is appropriate for your solution schema. The first two options in this dialog box are probably the most important to consider. First we have the choice to validate “always” or “only during data entry”. Choosing the later allows validation to be bypassed by scripts that utilize Commit Record [skip data entry validation] and is also skipped during bulk record imports. If you are looking for consistency, then you really need to use the “always” option. The second checkbox, “allow user to override during data entry”, combined with a privilege set toggle, allows users to ignore the validation requirement after a prompt. Enabling this checkbox would violate the consistency principle. This doesn’t preclude the use of these features completely, they would just fall outside the scope of supporting ACID consistency.

Script Triggers and Auto-Entered Data

Both auto-enter and triggers can be helpful in maintaining record consistency. However, there are many variables that impact when and how these operations fire. For example, Script Triggers, typically fire based on events in a layout. It’s common to have multiple layouts tied to the same table and there might be inconsistencies in the ways a developer has assigned triggers to each. As such, Script Triggers are better suited to higher level business processes and workflows. Auto-Enter data can be used in some ways to provide data consistency, but the firing of these correspond to the calculation dependency tree. Also of note, if users are allowed to interact with the import records menu or step, then they may be able to bypass auto-enter processing. Ultimately, triggers and auto-enter options can supplement, but do not replace field validation for purpose of ACID consistency.

Cascading Deletes

The cascading delete feature of FileMaker allows records of a child table to be automatically deleted if the parent record is deleted. This behavior promotes consistency by preventing orphan records. While my testing of cascading deletes has been limited, they do seem to behave in the ideal atomic way. This is true even when related records can not be deleted due to record locks or security restrictions. For example, you may configure a relationship between an order record and it’s corresponding line items, such that the deletion of an order record, will automatically delete the line items. If for some reason, any one of the line item records can not be deleted, then none of the child or parent records are deleted. This also seems to work when deleting all records in a found set.

Example: Referential Integrity

The concept of foreign key constrains are often discussed with the consistent property. Lets look at a simple example of Orders and Customers with the necessary steps to enable referential integrity in FileMaker. For our business rules, we’ll make the assumption that every Order has a CustomerID foreign key. An order may not have a customer, but whenever it does, it must reference the valid primary key of a customer record. Further, our business rules will state that a customer record may not be deleted if there are any orders associated with them.

1. Configure the Customer::ID primary key field to automatically enter a serial number and validate it by choosing: Validate: Always, Not empty, Unique value. (Either numeric serial or UUID would be fine.)
2. Configure the Order::ID primary key the same way.
3. Create a value list of Customer IDs. This is like a named index, that contains every customer primary key in a list.
4. Configure the Order:CustomerID foreign key field with validation by choosing: Validate: Always, Member of value list: Customer IDs
5. If you don’t already have a relationship in your data file, make at least one table occurrence relationship between Orders and Customers by way of this CustomerID field.
6. Protect the Customer records from improper deletion. Configure the user privilege set(s) with: Records: Custom privileges -> Table: Customer -> Delete: Limited -> “not IsEmpty ( CUS_ORD__Order::ID )” For this expression, use the calculation context of the relationship established in the previous step
7. Protect the Customer::ID field so that it can never be changed. Configure user privilege set(s) with: Records: Custom privileges -> Table: Customer -> Field Access: Limited -> ID: view only. This protection still allows the initial set of the primary key, but no further modification.

This example is really just the base case. Additional complexity may be required for your own business rules and process. Referential integrity can be maintained while allowing record deletion through FileMaker’s cascading delete feature. In this example, it would probably not be appropriate to delete all related orders if a customer record is deleted. Cascading deletes have some inherent risk of accidental data loss. Any time deletion is allowed, especially with cascading deletes, extra caution is needed. It is probably a feature that you want to use sparingly.

Conclusion

Does FileMaker demonstrate consistency? YES.

Institutions willing to invest the necessary effort to configure field level validations and a robust security model, can enforce consistency in their data. The types of rules that can be enforced do have some limits in scope, but are suitable for many situations. Like many other platforms, certain features may not be advisable if consistency is your priority. Some things you may need to consider:

• avoid allowing use of the import records command directly against a data table
• avoid running scripts with full access privileges

Many additional capabilities are available in higher levels of the application that can be used to enhance workflows and overall data quality. These higher level features may be even more important to you then ACID consistency.

Caveats

Administrative privilege, called [Full Access] in FileMaker, is the tier that defines the schema and sets consistency rules for a database. While it might be helpful to utilize certain types of validation while operating in this role, it is ultimately unenforceable by it’s mere definition. You must be willing to curtail access to production data, by using non-full access accounts, if consistency is to be enforced.

Some have argued that field validation should not be used for reasons of performance. This is true in varying degrees of any system. Checking the integrity of data before it is saved into a record requires work. The impact of that performance hit is slightly outside the scope of this article. It is certainly appropriate to weigh the value of performance against consistency when configuring these options. Consider, for example, fewer validations on tables that are used for bulk processing of temporary data, while using more rigorous validation on infrequently updated tables.

Applying a new validation rule to an existing table in FileMaker does not automatically verify all existing records. It’s on you to make sure a database will be consistent before adding the new rule. If you don’t do this, it could lead to a catch-22  scenario; you might edit field A on record, where field B happened to have already been invalid. This is dangerous territory, as complex situations can arise such as FileMaker not validating field B because you haven’t edited it. Ideally rules would be applied to a tables fields before any records are created. The pitfalls when applying new rules to a table full of historical data are outside the scope of this article.

User prompts related to validation errors may cause significant user frustration. Many developers do not set field validation options because of complaints they have or will receive from users. There are also situations that can arise with GUI form interaction, where a user is told they must fix some invalid field, but they don’t understand what is necessary or a clear path for resolution. This leaves users stuck having to abandon their edit, or even forcefully terminating the application. In some cases, it may be preferable to utilize forgiving validation options or higher level application capabilities for a preferable user experience.

About the Author

Chris Irvine is a Senior Technology Consultant at Threeprong.com LLC, and a certified FileMaker Developer. He develops on multiple platforms, specializing in custom workgroup solutions, B2B integration, and performance tuning. Contact Threeprong for your next custom solution or for help taking your existing solution to the next level.

Previous: ACID – Is FileMaker Atomic? (Part 2)

Up Next: ACID – Does FileMaker Support Isolation? (Part 4)