RATING: Intermediate
PLATFORM: Windows Only
VERSION: FileMaker 4/5
TECHNIQUE FILES: Smart Ranges Solution, Pict 1-15
Introduction
The concepts discussed in this article are geared towards multi-user networked solutions, in which controlled data entry, editing and manipulation need to be enforced for usage by non FileMaker-savvy users. This implies that the solution is always accessed with a limited password, allowing only data editing, that data fields are not directly accessible, that access to scripts, layouts and value lists is denied and each action is scripted.
The Basic Concept
FileMaker Pro's standard way of operating is to create a record, write data, exit the record and have the data cached automatically sent or "saved" on the server. The basic idea is to revert this paradigm: make "real" fields inaccessible, create as many global fields as "real" fields, put them on a data-entry layout, let the user enter data into the global fields and transfer data to "real" fields by clicking buttons attached to scripts.
A basic script for new record creation would present the user with a data entry layout that contains, among other things, two buttons: Cancel and Submit. The Cancel button would clear all global fields on the layout. The Submit button would create a new record and write into the "real" fields what was in the corresponding global fields. When editing a record, "real" values would first be loaded into the global fields and then written to "real" fields by a script, without creating a new record.
Let's take a look at the benefits of this technique.
- Validations. FileMaker Pro's built-in validation system is quite sufficient when it has to check if a value is appropriate or not, but can be confusing and is limited when the validation fails. A custom message can be used to guide a user but the Revert button isn't as intuitive as users might expect; unwanted record creation must be monitored by deleting the newly created empty records; and every script must include steps at its beginning to make field validation take place and use subsequent error checking and handling.
By using the technique we're discussing every validation is possible, including not only simple data type or presence of required values, but also very complex validations performed by script. Should a validation fail, reverting original values is unnecessary since data was not changed; deleting a created record is unnecessary since no new record was created!
- Data access. Once data has been entered into a FileMaker record the next step is usually to prevent improper changes of data. While there are many ways of accomplishing this effectively (layout duplication, scripted access to fields, field validation by a key value) they're all time-consuming to set up and difficult to maintain (a change that would normally be done on only one layout would require a change on multiple similar layouts).
By using the technique we're discussing limiting access to data is achieved with no extra effort.
- Scripts. One of the most requested features by FileMaker developers is the ability to launch scripts automatically when exiting a field, a feature that presently can be obtained only using a plug-in.
By using the technique we're discussing a similar end can be accomplished with no extra effort -- simply add the desired scripts to the Submit button's script.
- Indexable fields. Fields must be indexed in order to be used on the right-hand side of a relationship, however, this isn't possible if the field used for the relationship relies on related data.
An example might be a "Paid" field in an Invoices.fp5 file, calculated as
Sum (Relation to Line Items::Line Total = Relation to Payments::Amounts Paid)
By using the technique we're discussing, we might change the field type to Numeric and add a step to the Submit script:
Set Field (Paid, If(Relation to Line Items::Line Total = Relation to Payments::Amounts Paid),1,0)
In other words, the script would set the "Paid" field to 1 if the paid amount equals the invoice amount: this field is a "plain" numeric field and as such can be indexed and used to target relationships.
- Multiline keys. Multiline keys are a very useful and powerful way to relate records, especially if you want to create so-called "filtered relationships".
By using the technique we're discussing, multiline keys can be created by a script rather than by calculation (calculated multiline keys make imports very slow) and can be created when the presence of a variable number of items might preclude the use of a calculation.
So far we've seen many benefits of this technique, but as with every technique, there are some drawbacks:
- Record locking. We've made our solution "record-locking aware" by denying any access to "real" data fields. However, we haven't yet made our solution completely safe. When a user enters a global field on the record being edited they are still locking the current record and a script trying to write data into that record by another user would fail (a work around to this might be to enter globals when the current found count is zero but this would preclude global-triggered lookups and portal buttons from working).
- Developer work. Global fields and data entry layouts need to be created and maintained in each solution file and this may be considered a waste of time.
The Advanced Concept (aka, Circumventing the Drawbacks)
The advanced concept uses an "ad-hoc" additional file containing the global fields that are used for the data entry interface. Basically, you create a Dialog.fp5 file with some global container fields for button icons, some global text fields for button text and as many text, number, date, time, and global fields you may need to match. Create dialog-like layouts with a gDialogHeader global text field on the top bar and a gMessage global text field below the top bar. Add into each file a Constant field, calculated as = 1, and relate each file's Constant field to Dialog.fp5's Constant field.
A basic script for new record creation would look like:
Perform script [External (Dialog.fp5: Clear global fields)]
Set field [Dialog::gDialogHeader,"New record creation"]
Set field [Dialog::gMessage,"Please enter the data"]
Perform script [External (Dialog.fp5: Go to Data Entry Layout)]
In the Data Entry layout the Cancel button would perform a script like
Toggle Window [Hide]
Perform script [External (MainFile.fp5: Go to Layout [Data])]
while the Submit button would perform a script like
Perform validations, if failed exit script and show proper message
Toggle Window [Hide]
Perform script [External (MainFile.fp5: New record)]
This latter script would now look like:
New record/Request
Set field [DataField1, Dialog::gDataField1]
Additional steps here...
Go to Layout [Data]
Data editing would start loading into Dialog.fp5's global fields the record's data and would then be performed as outlined above with due differences.
Let's see how our cost/benefit balance stands now:
- Record locking is fully prevented: Any access to any field is prevented so there is no basis for record-locking, apart from the very brief locking occurring when scripts write data into "real" fields. This prevents conflicts when multiple users attempt to access the same record.
- Developer work is greatly reduced: Global fields need to be created only once, and it's easy to duplicate layouts and scripts to deal with different files. Getting data either from or into another file is a matter of the mapping used from the global fields to the data fields in the record of the file being modified or created.
- User interface is significantly enhanced: Data entry interface is consistent throughout the solution and the logic of writing data by "submitting" is sound and commonly accepted.
Gotchas and Optimizations
By denying direct access to fields we're removing the FileMaker concept of record locking but we're opening the possibility for multiple users to edit the same record and/or to delete it while it's being edited. To account for this we can create a numeric field "Flag", set it to 1 when starting to edit the record and setting it back to 0 when done. This is like a library check-out system where only one person can interact with the record information but any looping script that may use a replace or use a Set Field will be able to interact with the record because it is not locked by the user. Basically we'll add these steps at the beginning of the Edit and Delete script:
If (Flag = 1)
Exit script
End If
We must then make sure that no record locking can ever occur, even for brief periods by adding some steps that will make FileMaker re-try writing data should transient record locking occur:
Set Error Capture [On]
Loop
Go to field [A field on the layout]
Exit loop If (Status Current Error = 0)
End Loop
Set field [DataField1, Dialog::gDataField1]
Since both record creation and editing deal with the same fields we can save time using only one "writing" script. Create a global field named gSwitch that is set to 1 when a new record is being created and adjust the "write" script in order to create a new record only if needed, as follows:
If (gSwitch = 1)
New record/Request
End If
Set field [DataField1, gDataField1]
The Final Scripts
Putting together the fundamental improvements of the scripts evolved above, we end up with the following scripts:
New record creation/start
Perform script [External (Dialog.fp5: Clear global fields)]
Set field [Dialog::gDialogHeader,"New record creation"]
Set field [Dialog::gMessage,"Please enter the data"]
Set field [Dialog::gSwitch,1]
Perform script [External (Dialog.fp5: Go to Data Entry Layout)]
Edit record /start
If (Flag = 1)
Show Message [The record is being modified by another user]
Exit script
End If
Set field [Flag,1]
Perform script [External (Dialog.fp5: Clear global fields)]
Set field [Dialog::gDialogHeader,"Record editing"]
Set field [Dialog::gMessage,"Please edit the data"]
Set field [Dialog::gSwitch,2]
Perform script [External (Dialog.fp5: Go to Data Entry Layout)]
The Submit button in the Dialog.fp5 file would perform validations and launch this script in the Data.fp5 file:
Set Error Capture [On]
If (gSwitch = 1)
New record/Request
Else
Loop
Go to field [A field on the layout]
Exit loop If (Status Current Error = 0)
End Loop
End If
Set field [Flag,0]
Set field [DataField1, Dialog::gDataField1]
Conclusion
By using an additional file, some global fields and a fair amount of scripting we can create extremely flexible and powerful solutions for larger multi-user database systems.
Happy FileMaking!
Giuseppe Pupita < gpupita@sestante.net >