I work on a FileMaker vertical market solution (or product). We're creating a new version of the product which uses Selector Connector.
I have recently realized that while it's fine to create records in tables connected to Selector when on a layout based on Selector, deleting records via a portal row on the Selector layout is problematic. This is because deleting a portal row always locks the Selector record, even though all fields in the Selector table are global. And, of course, while the single Selector record is locked no other user can perform a delete action via a portal as the implicit locking of the record does not take place.
To fix this, my initial thought was that I'd need to take a more traditional approach to transactions based on the Transactions module on Modular FileMaker. However, I have since thought of an alternative approach that I'd like to invite comments on. I am calling it Selector Transactions for convenience.
This approach allows the creation of additional (i.e. beyond the single record that we always need) records in the Selector table when we are working with FileMaker transactions. Just so we are clear on terms, by transactions I mean scripted changes to multiple records that need to take place atomically. That is, either all the changes happen or none of them. A simple example would be the creation of a new image record for a contact. Images are stored in their own table but there can only be one image for each contact. In this transaction we need to add a new image record and then delete the existing image record. If either the adding of the new image record of the deleting of the existing image record fails then we do not want the other action to proceed but to return the system to the state it was in before the transaction begun.
The process for running a Selector Transaction is as follows:
- Go to Selector layout (Always in a new modal window)
- Create a new record, do not commit it
- Perform transaction actions, for example creating a record in one table and deleting a record via a portal in another record
- Commit the record to confirm the changes
- a) If the commit is successful, delete the Selector record as long as there is more than one record in the Selector table
b) If the commit is unsuccessful, revert the Selector record, which will have the effect of deleting it.
The creating of a new record in the Selector table means that we no longer have issues with record locking. Other users can use the Selector as normal. Also, because the new record spends most of the transaction uncommitted and is deleted as soon as it's committed, the Selector record count would very rarely increase above 1.
Points for information
- As part of the installation process for each customer who receives our product, we ensure that there is a single record in the Selector (and Connector) table. Therefore we can safely assume that there will be one record in the Selector table when the system is used for the first time.
- For reasons I have not had time to fully explore, the Refresh xJoin script takes a very long time to run, around 0.8 - 1.1 seconds. I assume this is because of the large number of table occurrences in the solution. Therefore, almost all record creation is done via layouts based on the Selector to avoid the need to run the Refresh xJoin script and allow for much quicker record creation. I know this is not the standard Selector Connector way of doing things.
Points for discussion
- In the rare case where the Selector record created for the transaction was successfully committed but then not successfully deleted, there would be an additional record in the Selector table. Does anyone think that this would cause problems? Note that all the fields in the Selector table are global, so I don't think this would break any relationships. Of course it's easy to delete these stray records eventually so they don't mount up, but would they cause any harm in the meantime?
- Any other comments?
- Any clarifications required?
I can't stop myself trying to tempt toddgeist to respond to this, but I'm keen to hear from anyone using Selector Connector if they have tried this approach, see any issues with it or have alternative approaches I could try.