Easy FileMaker modal edit dialogs with full rollback support

Let’s say you have a requirement to prevent users from accidentally editing field values in the main layout of the application.  As we all know, the default behavior of Browse mode is to allow direct editing of field values, including fields of related records in portals.  So, to achieve this requirement, you will need to make these fields and/or portals read-only on the main layout and provide an alternative method for editing these fields.  Let’s then say that the requirement further states that the user should be able to cancel whatever changes they have made while editing and all changes should be “rolled back”.

A common approach to these requirements is to provide an “Edit” button on the main layout that opens a modal popup window displaying the fields to be edited along with “Cancel” and “Save” buttons.  You might then use one of two techniques to collect the data and provide rollback.  One, you might use global fields to collect the data and then script the process of copying the data into the real fields when the user clicks “Save”.  By using global fields, when the user clicks “Cancel” the script only needs to close the window to leave the original data untouched.  Alternatively, you might copy the real data into script variables when the window opens and allow the user to edit the actual fields on the layout.  If the user clicks “Save”, their edits are simply committed, but if they press “Cancel” the script copies the original data out of the variables and re-populates the fields with the data that was present when the window opened.  This is a common technique, and it works fine for simple situations.

But let’s say that we need to display dozens of fields and/or portals records, and that in order to support the popup functionality, we need to build relationships to multiple tables, etc., and finally, we need to allow the user to create and delete portal records.  How can we do all that with global fields or script variables and still provide rollback functionality?

With the requirement to support relationships among tables in the popup, global fields are not going to get you there.  Almost certainly you would conclude that it would be easier to present the user with the actual parent and related record fields.  And you could use the method described above where data is saved into script variables when the popup opens.  This method, however, gets much more laborious when you add portal records to the mix and vastly more complicated when the user is allowed to delete the portal records.  Providing rollback functionality is a true scripting challenge.  I know, because I’ve done it; but it’s really tedious and required a lot of maintenance.  If you add a new field to the list of fields that need to be edited, you need to alter the script every time.

Transactions

Fortunately, there’s a much easier way, and it takes advantage of FileMaker’s built-in support for transactions.  Much has been said about transactional processing in FileMaker, most prominently by Todd Geist.  I’ve used transactional techniques in a number of systems where the script requires “all or nothing” processing.  In the technique I’m about to describe, we are going to use a script trigger to “capture” and prevent the database commit operation that FileMaker natively and silently performs for the user as they work in Browse mode.  Only when the user clicks “Save” will we allow all the data to be committed to the database.  If the use clicks “Cancel”, the edits that has been saved but not committed (including addition and deletion of records in related tables) can be reverted in a single script step.  In other words, FileMaker has already done the hard work of remembering the state of the database before the user began their edits and knows how to restore the database to the previously saved state.  We’re simply taking advantage of the way FileMaker already works.  Thus, the technique requires no global fields or script variables to store and restore data.  It supports rollback for editing the parent record or creating, editing and deleting portal records.  And since the editing layout is built using the actual fields, it supports all the richness of the relationships tied to the table occurrence on which the layout is built.

Demo

I’ll be using the FileMaker 11 Faculty Staff” starter solution for this demo.  I added a “Class” table to the file and created a 1:M realtionship between Faculty Staff and Class.  I then added a read-only “comments” field and read-only portal of Classes to the Faculty Staff form layout, as shown.  The idea is that each teacher may teach several classes and we need to associate these classes to the teacher.

Faculty Staff - screenshot

When the user presses the “Edit” button, the “Class Selector” popup window is displayed as a modal dialog.  The layout for this window is built on the Faculty Staff table occurrence, and displays the same Comments field (from Faculty Staff) and the same portal of Classes that we saw above, except that now both sections are editable.  Note that we have provided the user “Cancel” and “Save” buttons.  The user can select classes in the portal (using a simple drop-down list for this example) or even delete them.  Comments can be added as free text in the text box.

Class selector editing

Nothing fancy so far.  The whole key to this technique, however, is that we also have an “On Record Commit” script trigger on this layout.  The script trigger calls a script called “OnCommit”.  Here’s that script (comments omitted for brevity):

Freeze Window
Set Error Capture [ On ]
#
If [ $allowCommit = 1 ]
Exit Script [ Result: 1 ]
Else
Exit Script [ Result: 0 ]
End If

Simple enough, right?  Basically this script reads the value of the global variable $$allowCommit.  If it is set to 1 (true), the script returns 1, meaning the database commit is allowed to proceed. Otherwise, the commit is “captured” and suppressed.  Next we’ll look at how we control the value of $$allowCommit.

The controller script for the popup window is called “ClassSelectorController”.  This is the script that runs when the user clicks “Edit” on the main layout.

Freeze Window
Set Error Capture [ On ]
#
# this global controls whether commits are currently allowed
Set Variable [ $allowCommit; Value:0 ]
#
# commit record in parent window in case this user has it locked
Commit Records/Requests [ No dialog ]
#
# launch the new task window and isolate this record
New Window [ Name: "Class Selector"; Top: -9999; Left: -9999 ]
Freeze Window
Go to Layout [ “Class Selector” (Faculty Staff) ]
Show All Records
Omit Record
Show Omitted Only
#
# check for record locks
Open Record/Request
Set Variable [ $error; Value:Get ( LastError ) ]
If [ $error ]
Close Window [ Current Window ]
Show Custom Dialog [ Title: "Error"; Message: "This record is currently unavailable for editing. Please try again later."; Buttons:“OK” ]
Exit Script [ ]
End If
#
# finish window prep
Show/Hide Status Area [ Lock; Hide ]
Adjust Window [ Resize to Fit ]
Move/Resize Window [ Current Window; Top: ( Get ( WindowDesktopHeight ) / 2 ) - ( Get ( WindowHeight ) / 2 ); Left: ( Get ( WindowDesktopWidth ) / 2 ) - ( Get ( WindowWidth ) / 2 ) ]
#
# Wait for user input: Cancel or Save
Loop
Pause/Resume Script [ Indefinitely ]
If [ Get( ScriptResult ) = "cancel" ]
Show Custom Dialog [ Title: "Warning"; Message: "All changes you have made in this window will be lost. Do you want to revert your changes?"; Buttons: “Cancel”, “Revert” ]
If [ Get ( LastMessageChoice ) = 2 ]
Revert Record/Request [ No dialog ]
Close Window [ Current Window ]
Exit Script [ ]
End If
Else If [ Get ( ScriptResult) = "save" ]
If [ not IsEmpty ( Faculty Staff::z_validationErrors_ct ) ]
Show Custom Dialog [ Title: "Error"; Message: "Please fix any errors before clicking "Save""; Buttons: “OK” ]
Else
# setting $allowCommit lets the OnCommit script know that it's OK to allow the commit to OnRecordCommit trigger event to proceed
# the act of closing the window will trigger the record commit
Set Variable [ $allowCommit; Value:1 ]
Close Window [ Current Window ]
Exit Script [ ]
End If
End If
End Loop

The first thing this script does is initialize the value of $allowCommit to 0 (suppress commits).  Then it opens the popup window, attempts to obtain a lock on the record, and pauses and waits for the user to click “Cancel” or “Save”.  Meanwhile, the user can add, edit or delete comments and related Class records.  When the user clicks out of a field, FileMaker would ordinarily commit changes to the database, overwriting the previous data and preventing rollback functionality.  But our script trigger script suppresses this native behavior.  Only when the user presses the “Save” button (which passes the paramteter “save” to this script) does the script set $allowCommit to 1 and closes the window.  FileMaker attempts to commit the data upon closing the window and this time the  OnCommit script will allow it to proceed.  On the other hand, if the user presses “Cancel”, the controller prompts the user to confirm that they intend to cancel all changes that have been made since the window opened.  If they confirm their decision, the single script step “Revert Record/Request” rolls back all the changes the user has made, including all edits and portal record additions and deletions.

That’s it.  No global fields.  No storing values in variables.  Just a single script trigger with that calls a very simple script to control when the commit happens.

Just for fun, I’ve included some custom validation using an unstored calculation field,  illustrating the power of being able to use the actual fields on the layout, rather than global fields.  If the user selects more than 3 schools, an unstored calculation generates an error message in the window.  If the user selects “Save” when the error exists, the controller detects the error, displays a dialog, and prevents the save operation.

Class selector validation

Conclusion

I hope you see how easy it can be to provide true cancel/save functionality in your user interface.  With FileMaker’s built-in support for transactional processing and our ability to take control of the process via scripts and script triggers, we can provide this type of user experience with very little effort.

26 thoughts on “Easy FileMaker modal edit dialogs with full rollback support”

  1. Very nice! I’m wondering about a tweak to the Cancel script.

    Is there a way to detect that in fact no edits have been made and just allow the Cancel script to proceed? We can get(recordModificationCount) but that doesn’t seem to do the trick.

  2. Great thought, Bruce.  Yes, that would be a really nice enhancement, and certainly would provide an experience that users have grown accustomed to with other platforms.

    It's a tricky problem.  As you said, Get( RecordModificationCount ) doesn't work, because it only counts commit operations (which we're suppressing in this technique) and it also only counts the parent record.  Commits to the child records in portals wouldn't be captured by Get( RecordModificationCount ) from the parental context.

    The most sure-fire way I could think to do this is to put OnObjectSave script triggers on each and every field, including fields in portals, and count the number of times the script trigger fires.  If it fires more than 0 times, some field, somewhere on the layout has changed; otherwise FM wouldn't fire the trigger.  That solution seems a little inelegant to me, though.  

    Another way is a little more involved, but might just work, and it could be a generic solution that would only have to be coded once.  The general idea would be to use the FieldNames() function to retrieve a list of fields on the layout, including fully-qualified related field names, then use this list to construct a SQL query that retrieves the values for these fields.  This routine could run when the popup opens and then again when the user presses Cancel.  Using some kind of hashing function on each query result (to reduce the long string to a shortened but unique "key"), if the two hashes are the same, no Revert Record step is necessary.  I'm taking inspiration from a great talk Todd Geist did at Devcon this year on writing re-usable code.  With the use of ExecuteSQL, this would obviously be a FM12-only solution (or would require a plugin).

  3. No need to use ExecuteSQL if you don’t want to, evaluate will work just as well for capturing the current value of all fields on a layout into a global variable (using FieldNames() as you mention) and similarly capturing the values at another time to see if changes have been made and again if required resetting the field values. These methods can be made to be completely generic – I have rolled mine into three scripts, recordSnapshot, checkSnapshot and revertSnapshot. This method allows committed changes to be reverted.

  4. Great tip!

    It’s often overlooked that besides performing actions script triggers can be also very helpful in avoiding certain actions.

    Not only makes it easier to roll back unwanted changes but also saves processing time by doing just one instead of multiple unnecessary commits.

  5. One of my techniques is to load the entire record into a variable as XML whenever the user selects it. I then can compare each field to its XML brother as necessary. Yes, the FieldNames () function is the way to go for sure.

  6. Great idea, I’ve already implemented it in two or three places in my current project.

    I got caught out when my edit window contained a global field. These aren’t reverted if they are edited.

  7. @HOnza – I hadn't thought of the effect on processing time, but surely it should help some.  Not sure how expensive a "commit" is relative to a "save and commit".  Would be interesting to test.

  8. @Brian Rich – Glad you like the technique.  Nice to know that you're getting a chance to use it.  Yes, global fields are a complication.  What did you do in that case?  Store the value in a variable?

  9. Great article. This is one of those techniques that often comes in handy. I’ll be adding a couple tweaks to my current process, Thanks.

    As a standard practice, the only thing I tend to do different revolves around the isolating of the record. I started using a strict ‘Perform Find’ using the UUID to isolate the record…Instead of:

    Show All Records
    Omit Record
    Show Omitted Only

    I’ve found 2 benefits:
    (1) Performance is better. Showing all records forces FM to download at least 25 records to the local cache file. While it applies to even local files, the hit over a WAN can be much more ‘visible’. Plus it is one step instead of 3.

    (2) Potential danger of other records being created at the same time. For example, if someone else creates a record between when you ‘Show All Records’ and ‘Show Omitted’, it gets added to the ‘Omitted’ records. Showing Omitted Only will now show you more than 1 record, leaving unintended consequences. It may be rare, but I’ve actually seen it happen more than once in a solution with with only 3 users.

  10. @Josh Ormond  Josh, thanks for your comments.  Yes, great point about the sequence to isolate a single record.  Thanks for adding that!

  11. I was excited to try this method out…but quickly discovered it didn’t do what I expected it to. I was expecting the parent window data to actually keep the original values until you committed the records. Is there a way to do that? My reasoning is that I am wanting to use the original values (in the parent window) and the new values (in the edit window) to create a history record in a related table when different. The problem I am running into is that it is only seeing the last field as different, because all the other fields have already changed. I have also been considering using the FieldNames () Function. Any thoughts would be helpful. Thanks.

  12. @Lorena, you are correct; this technique alone won't accomplish what you're looking for.  The values in the new window are "saved", and therefore changed, even though they aren't yet committed to the database.  You could look into crafting a solution based on "OnObjectSave" for field(s) you're interested in tracking.  Or you could manually save all the values of the record to a log table as the window is opening (and/or closing).  This would preserve a snapshot of the record before modifications.  If you're looking for really robust field-level tracking/logging, I would look into the products from SyncDek at http://syncdek.com/. 

  13. Artie – This is great! I’ve already used it in three places in my current project, so thank you.

    One quick question re: rollback functionality with data shown via a portal. I have a sales order layout with a line items portal. If I edit the current sales order record and add a line item in the portal records, all of the changes made to the sales order record “roll back” when I cancel the edits, but any line item records added or deleted in the portal are not rolled back. Am I missing something?

    Thanks again!

  14. @Mike, I'm thrilled you find this useful!  As for portal records, I'm not sure what would be going on in your solution.  Certainly the technique should work for portal records; in fact, that was the original inspiration for developing the technique.  If you haven't looked at it lately, I would look back at the demo file again and see how it was done.  Perhaps you can see what is going on in your own file?  If that doesn't work, feel free to reach out to me back-channel at abrosius at soliantconsulting dot com

  15. I use not isEmpty ( Get ( ModifiedFields ) ) to determine if any changes have been made to the record. If not then no point in showing the user a dialogue to save changes. That combined with the above technique works great.

    Cheers,
    Philip

  16. Thank you for this great tip, Artie. Please excuse me for any dumb comments or questions, as I’m very new to this software.

    1. Where in the script are the “Save” and “Cancel” buttons created in the new popup window? Mine are not appearing for some reason.

    2. For some reason, the OnCommit script, which I’m calling through the On Record Commit script trigger, is making my entire application unusable, unless I close it through task manager (can’t seem to close it any other way) and reopen it. Everything is fine until I try to make a change, or hit the “Edit” button I’ve created. However, once the new window appears, I can edit fields and whatnot. I just can’t save the changes, or even close the new window. Maybe it’s because my “Save” and “Cancel” buttons aren’t there?

    Any help would be greatly appreciated.

    Thanks,
    Shane

  17. Hi Shane,

    Buttons in FileMaker are not defined in a script, you add them to the layout while in Layout mode.

    The reason why your file seems to hang is that the OnCommit script prevenst the commit from happening until the global variable $$allowCommit is set to 1.  That variable is set by the buttons that are missing from your layout.

    You can see this happening by leaving the Script Debugger on and tryng to close the window.  With the Script Debugger you can them manually move the execution point to the step that says "Exit Script[ 1 ]" which will allow the commit and close the window.

  18. Hiya, wonderful guide! I was wondering if you have any suggestions on how to create modals in list view. My solution needs a modal to show a list of records with an “ok” and a “cancel” button.

    I’m thinking that I might just send the user to the modal and end the script and then pick up with another script based on the two buttons. But I thought I’d explore this solution first.

    1. Hi Per,

      Modality in FM has changed a fair bit since this article was written. You can now use native modal windows by using the Advanced settings on the “New Window” script step. That makes it easy to create modal windows, even for list views.

      Your basic approach is solid: take the user to a new modal window, provide “ok” and “cancel” buttons to close the modal window and proceed with what is required.

      Keep in mind that this article was written from the perspective of being able to capture edits that a user makes to a record. Trying to do the same for a list view is a lot more challenging.

      Best regards,
      Wim

  19. Pingback: Creating Modal Dialog Windows - FileMakerProGurus

Leave a Comment

Your email address will not be published. Required fields are marked *

Are You Using FileMaker to Its Full Potential?

Claris FileMaker 2023 logo
Scroll to Top