Magic Portals

Today we’re going to look at a design pattern I’ve recently been using to accommodate a client request. The request is to be able to view and edit a parent, all related children, and all related grandchildren via a single “flattened” interface.

Demo file: magic-portals.zip (requires FM 16 or later)

Some Background Information

Invoices received from vendors are entered as “payables”…

…and once a week checks are generated aggregating all open payables for a given vendor onto a single check.

In today’s demo, the preceding is assumed to have already taken place, and sets the scene for the technique we’re about to dive into.

Here’s the relevant portion of the relationships graph…

…and as you can see checks have one or more related payables, and each payable has one or more related line items (a.k.a. “payline”).

Specific Requirements

#1. After checks have been issued it is sometimes necessary to make adjustments to the underlying payable data. Obviously the check payee, issue date, check # and amount are carved in stone, but the user needs to have wide latitude to adjust the underlying payables, which could include…

• revising/adding/duplicating/deleting payable line items
• revising/adding/duplicating/deleting entire payables

…though, naturally, the bottom line total will still need to agree with the check total.

#2. Flattened interface: view and interact w/ multiple levels of data via a simple UI.

#3. Keyboard driven: the users like to keep their hands on the keyboard and bang things out quickly, and they are used to entering data into portals based on “allow-creation” relationships, i.e., w/ a blank row at the bottom. To create a new line item on a payable they tab into the empty bottom row and start typing so we need to support that.

#4. Ability to hit the Cancel button and have all changes evaporate. Changes are only committed if the user clicks OK, however…

#5. When the user clicks OK, the changes must be validated… we don’t want to allow any garbage into the system.

#6. Finally, the process needs to be transactional, i.e., when the user clicks OK all changes must be committed in a single step, as opposed to via separate sequential steps. (You can learn more about the transactional model in this article by Jeremy Brown.)

See It In Action

Go to the Checks tab and click “Edit” on one of the checks.

A new “dialog” window will appear…

…with salient features indicated as follows. You can…

1. Add or edit a note on the original check
2. Add a new payable
3. Duplicate an existing payable
4. Delete a payable (this button is greyed-out when there is only one payable)
5. Duplicate a row within a payable
6. Delete a row
7. Click Cancel and all changes will evaporate
8. Click OK to commit the changes (this button will be greyed out if the sum of all related payables is not in balance with the original check amount)

Numbers 2/3/4 are where the “magic” happens, and I need to confess that the title of today’s article is doubly duplicitous, because A. as you may have already guessed, these are not actually portals, and B. no actual magic is occurring.

But, to me at any rate, there is something that feels magical about clicking here…

…and seeing your “portal” instantly duplicated on screen.

When this window is displayed in normal (as opposed to debug) mode, a “Restricted” custom menu is loaded, to help ensure you don’t cause any inadvertent mischief. Because this is a dialog-style window, you cannot interact w/ any other FileMaker window until you have dismissed this window, either by clicking OK or Cancel. This “modality” is important because we have locked the Check record in the original window and want to ensure that it stays locked throughout this process.

Two other points of interest:

1. You can press tab to move forward, and shift-tab to move backwards, through all editable fields, moving from row to row, as you would if these were indeed genuine portals, as opposed to ersatz imitations.
2. To further mimic the behavior of a standard portal based on an “allow creation” relationship, when you enter data into the empty bottom row, a new empty bottom row will be created on field exit.

Behind The Scenes

Here are table definitions:

Here’s the full graph:

Here’s the layout, showing the script triggers that help facilitate the illusion that the user is interacting with portals. This layout is based on temppayline and is in essence a browse mode summary report with id_payable as the break field.

When The User Clicks Edit

1. Attempt to take ownership of the check by issuing the Open Record command. If an error occurs, display a dialog showing who is currently editing the record and bail out; otherwise…
2. In a temporary window, import payables and related line items into scratch tables, then close the temporary window (meanwhile the check stays locked in the original window)
3. In a new dialog-style window, display the freshly-imported temppayline records using the layout shown above
4. Generate one empty temppayline record per payable (to simulate empty bottom portal rows)
5. Sort ascending on temppayline::id_payable, and descending on temppayline::flag_empty

If The User Clicks Cancel

1. Close the Edit window
2. Select the original window
3. Unlock the check via Revert Record

If The User Clicks OK

1. Validate the data
2. Delete the empty rows
3. Encode (temp)payable and (temp)payline data as JSON in a variable
4. Close the Edit window and select the original window (reminder: the check is “open” in this window)
5. Update the data transactionally
   • Delete the original related payable and payline records via the Payables portal
   • Write the JSON out to a new set of payable and payline records using the Magic Key technique
   • Commit the check record

Running In Debug Mode

Checking the Debug box changes what happens when you click the Edit button as follows:

• The temporary window where the imports take place will draw on-screen instead of off-screen — it comes and goes very quickly so you’ll only see it if you step through the script using the debugger
• The edit window will be invoked as a document-style window rather than as a dialog-style window, facilitating exploration should you be so inclined
• The “Restricted” custom menu set will not be installed
• The two “temp” tables will be displayed in their own windows

Conclusion

I mentioned at the outset that this technique allows one to edit parent, child and grandchild data. In today’s demo I’ve intentionally set things up so that the only Check (i.e., parent) data that can be edited is the Note field, because in this particular scenario, all other Check data was off-limits. But there’s no reason this technique couldn’t be adapted to allow full-blown creation and/or editing of a parent, multiple children, and multiple grandchildren.

Other possible uses for this technique might include reviewing/creating/updating…

• billable time entries, itemized by project
• employee expense submissions for a given time period
• purchase orders for a given time period or vendor
• etc./etc./etc.

This is a “hybrid” technique as it uses both a scratch table (two, actually) as well as the transactional model, and this is my initial attempt at implementation. Presumably there is room for improvement, and I am open to suggestions.