“Graphic” checkboxes and light schema

10 February, 2012 (06:03) | FileMaker, FileMaker Go, Script

I’m a bit anal about trying to keep my database schemas light and have been contemplating my belly button about this for the last year or so.

Light schemas are particularly important on devices like iPhones and iPads with FileMaker as well as WAN access to FileMaker databases.

One modus operandi is to throw as much of the logic into scripts rather than calc fields. More on this some day.

One task was to make a nice graphical check-box that works properly in browse and find mode and not bog down the schema. This has been solved many ways, here’s one I’ve used involving a single field, conditional formatting and a utility script.

Check boxes

Define a number field, with auto-enter options with an initial value and auto-replace self-referential getasboolean calculation.

Numberoptions

Put the field on the layout, set the font to a cross-platform font that works best for your situation.

Add some conditional formatting to the field. Condition 1 is when “self=true”: text colour is green, “self=false”: text colour is red.

Open the data inspector for the field and set the number format to Boolean and insert a tick character (or whatever works in your language and your selected font) for “Yes” and a cross character or appropriate for “No”.

Boolean display

Use the character viewer utilities for your platform to pick the character you want.

Create a script with the following steps:

    If [ not IsEmpty ( Get ( ScriptParameter ) ) ]
        #
        #                                 script parameter is the name of the field, including any table specifier
        #
        Set Variable [ $openstate; Value:Get ( RecordOpenState ) ]
        Set Field By Name [ Get ( ScriptParameter ); Abs ( Evaluate ( Get ( ScriptParameter ) ) - 1 ) ]
        If [ $openstate = 0                                 /* the record was committed */ ]
            Commit Records/Requests
        End If
    End If

Assign this script to the field (while you are at it, turn off entry on browse mode for the field), with a script parameter with the name of the field in quotes (this makes the script portable).

Untitled

The script preserves the record open state – that is, if the user clicks on the field and the record was not open, the script immediately commits the record, otherwise, it leaves the record open. Use this trick to minimise record locks. You can also add some logic to handle the case when the record is in use by another user, but that is beyond the scope of this post.

The set field by name step could be changed to use a (not getasboolean ( evaluate ( get ( ScriptParameter ) ) but this is just a habit I acquired in the early days of FileMaker when formulae would not evaluate under certain conditions.

Size and label the field the way you want. That’s pretty much it.

Click to download the sample file