Modern Commitment

FileMaker

Have you ever wanted to prevent a record from being committed when a user clicks on the layout? There are various reasons you might want to do that. In my recent scenario, I wanted to allow users to create records in the Request table, but not view or edit them. I set up my security like so:

This setup functions as a sort of drop-box — in the Request table, users can create a record, but that’s all. They can’t go back and look at their data or that of other users. Note: the user can enter and edit data in fields, as long as the record has not yet been committed.

The one problem with this is that if a user accidentally commits the record they’ve created, for example by simply clicking on the layout outside a field, they will lose the ability to edit or even view the layout they were just working on.

So, we need a way to prevent an accidental commit. Here’s a clean and simple method to handle it. All it takes is one simple script, assigned to a layout trigger.

First, let’s create the script. The script will be triggered when the record is committed — i.e. using the layout’s OnRecordCommit trigger. This trigger runs before the action happens, and like all such triggers we can stop the action with the script command:

Exit Script[ Text Result: False ]

That’s essentially the whole script. But we don’t want every commit to be stopped. We want to allow commit when the user clicks the Submit or (maybe) the Cancel buttons for example. That means we’ll need to use some kind of branching logic, so let’s pass a parameter into the script and act on it:

Set Variable[$param ; Value: Get(ScriptParameter) ]
If[ IsEmpty( $param ) ]
  Exit Script[False]
End If

What should we use as the parameter? An elegant method is to simply use Get(ScriptName). That way your buttons can still have their own parameters. (Credit to Daniel Wood and this article for demonstrating this technique.) Note: your button action must be Perform Script, it can’t be Single Step.

Now we’ll go into the Layout Options and assign our new script. Save record changes automatically should be enabled on the General tab.

On the Script Triggers tab we select our script, which I’ve named Stop trigger, and specify the parameter:

The logic when you accidentally commit is:
– the OnRecordCommit script will trigger
– the parameter is empty, since there was no script running
– the script exits ‘False’
– the commit fails
– you can still edit fields

The logic when you commit using a button is:
– the button’s script commits the record
– the OnRecordCommit script will trigger
– the parameter is the name of the script attached to the button
– the button’s script continues

That’s it! This works in FileMaker Pro, WebDirect, and FileMaker Go.

Commitment.fmp12Download

Download the example file to see it in action.

There are other ways to control data entry — via global fields, or the web viewer background trick — but I really like this triggered method.

Bookmarking FileMaker Help on a Mac has changed

FileMaker

It’s fast and convenient to have the FileMaker help file bookmarked, e.g. for quick access to the functions reference and script step reference.

In FileMaker versions 7 – 12, you would find the help file here:
file:///Applications/FileMaker%20Pro%2012%20Advanced/FileMaker%20Pro%20Advanced.app/Contents/Resources/English.lproj/FileMaker%20Pro%20Help/index.html

This has changed in FileMaker 13. The help file is now found here:
file:///Applications/FileMaker%20Pro%2013%20Advanced/Extensions/FileMaker%20Help/FileMaker%20Help.bundle/Contents/Resources/en.lproj/FileMaker%20Pro%20Help/index.html

Right-click the help bundle, not the application.
In FileMaker 13, right-click the help bundle, not the application.

The examples above are for the default location of FileMaker Advanced. FileMaker Pro would be slightly different.

To get to an application’s Contents, control-click or right-click it and choose “Show Package Contents.”

In the Help folder, open the index.html file in your browser, then bookmark it, or navigate to your reference section of choice and bookmark that.

An old trick but still a good one.

FileMaker DevCon 2012

FileMaker

Hello to all my friends and colleagues in Miami this week! I’m sorry I can’t attend DevCon this year, but I look forward to hearing all the latest and greatest FileMaker news. As a consolation prize, the weather in Portland is beautiful and OSCON is here, right down the street from my office. I’m not attending the official sessions, but I plan to spend some time hanging out with the Big Data guys and healthcare IT folks. Should be interesting.

Formatting numbers as text in FileMaker

FileMaker

TIP:
Sometimes we need to format numbers in a block of text, to send an email for example. We want comma separators:
1,000 — not 1000.

I’d always thought this required a custom function, and there may be times when you do want to manage currency symbol or precision, etc. (For example, this.)

There is a built-in function, though, that is all you need if you just want to separate the digits with commas: NumToJText

EXAMPLE:
NumToJText ( 1234567 ; 1 ; 0 ) gives us 1,234,567. Nice!

FileMaker Portal Sorting Redux

FileMaker

I got some great responses to my last article, and I’d like to share a few of them with you (with permission from the developers).

First up, from Chris Manton of BabelFix: rather than gathering the child IDs with a script, he set up a separate sorted relationship for each sort order. This lets you use a simple List function to instantly grab the list of IDs.

Great idea, but it turns out, testing with thousands of related records, that the slow part is not gathering IDs, but the portal’s Sort that introduces the bigger delay. (And remember, the field we’re sorting on is an unstored calculation.) Yes, the List function is king of speed, but you won’t notice the difference in portal sorting unless your related record set is in the hundreds or thousands.

Chris, undaunted, figured out a way to take the portal’s Sort out of the picture! He used the virtual list technique that Bruce Robertson developed. The portal displays the “virtual” records, which are already sorted, so the portal doesn’t need to do it.

If you sort thousands of records in portals, this is a killer technique. It’s really two techniques: 1. gathering the IDs using sorted relationships and the List function; and 2. a non-sorted portal that uses virtual records. Part 1 is useful, but does require an extra relationship in your graph for each sort order. It’s part 2 that’s the real breakthrough in terms of speed for large record sets.

Our next example is from Bruce Robertson of Concise Design, who pioneered the use of virtual lists, and naturally that’s the meat of his implementation. What’s interesting is that his approach is kind of the inverse of Chris’s: he uses sorted relationships, but on the virtual records. He also demonstrates a really clever way of grabbing the data by evaluating a script parameter.

Last but not least is a file modified by Matt Petrowsky of FilemakerMagazine.com. What you notice immediately is how great it looks. Subtle shading in the portal header, reverse shading on the header’s separator lines, and neat little sort direction arrows. Even the font sizes are just so.

Behind the scenes, he kept essentially the same simple relationship graph, with one important change: an additional “refresh” criteria in the relationship. You can read more about how that works at FileMaker Weetbicks. Essentially, it handles refreshing the portal without the screen flashing that comes with a refresh script step.

Matt also adds ascending/descending sorts, and uses some interesting custom functions. He uses one for the portalSort field. He uses another with GetNthRecord to create the child ID list. (As I mentioned in the comments of the previous article, GetNthRecord is not a problem for small sets of related records.)

Ultimately there is no single “right” way to sort a portal in FileMaker. The technique you choose depends on where it will be applied. For now I’ll be sticking with my original approach, it doesn’t require any changes to my graph (although I’m going to play around with the relationship-based refresh) and the setup is straightforward enough that other developers I work with should be able to make use of it without much trouble.

Many thanks to Chris, Bruce, and Matt, and everyone who gave feedback on Portal Sorting that Doesn’t Suck. Have fun digging into these files!

PortalSort Manton.zip (260 KB, contains large sample data)
PortalSort Petrowsky.zip (24 KB)
PortalSort Robertson.zip (16 KB)