Data Management

Performing text calculations in FileMaker

FileMaker, long considered a toy by -serious" developers, is ideal for rapid development and includes many tools to help build useful apps fast.

FileMaker, long considered a toy by -serious" developers, is ideal for rapid development and includes many tools to help build useful apps fast.

It goes without saying that FileMaker occupies a unique place in the pantheon of desktop databases. It claims ease of beginner use as its calling card, and yet, used imaginatively and with skill can provide a robust platform for many corporate applications. Moreover, it is certainly an appropriate tool for rapid, low-cost application development when seeking to model a new data solution.

Among attributes which put space between FileMaker and near competitors is its ability to enable users to create 'text calculations' - literally the ability to sum the text across a range of fields, even across dynamic relationships. This, coupled with its inbuilt scripting language ScriptMaker, adds considerably to both its usefulness and usability. Combined text calculations, simple scripting operations and easily modified layout presentation provide users with substantial capabilities in generating viewable, printable, customised forms, creating personalised template-based letters, and even delivering emails directly to the user's default outgoing desktop email application.

Another useful application lies in the ability to conduct Boolean comparisons between one 'text sum' in a modified record and that stored in an existing record to ensure that the possibility of data duplication is eliminated, or that the content of the better version is maintained.

Where new information does not match that created earlier, and the calculation yields a disparity, this can enable either the replacement of the former record, or the creation of a new one, as determined by the user. The result of that calculation can in turn be returned in text, and delivered into a field to create a button which executes the appropriate conditional script.

Here, above in Illustration 1, is a simple example in the form of a simple customer record in a database we'll name 'SimpleCRM'. Seen here in the field-definition module of FileMaker, the 'NameComplete' field is the 'sum' of the text in the title, first and last name fields contained within an established record.

The database user will see NameComplete displayed as above in Illustration 2, whose text is shown here in red only for purposes of illustration. Nevertheless, this level of format control is also generated through FileMaker's text-formatting calculation function.

Above, in blue, we see the content of the new information to be added to the database, created within separate 'temporary' fields (in FileMaker parlance, 'global fields') whose purpose is simply to enable the comparison between the old record and the new. Again, the purpose of the blue formatting shown here is solely illustrative.

However, these global fields also exist in this example in a different table, 'SimpleCRM_Globals', which is used as a safeguard to manage the introduction of either new content to established records, or new records to the SimpleCRM table. 'Global' content is easily erased.

Importantly also, the exclusive relationship between the new information and the original record is established by their matching record IDs.

Finally, as shown in Illustration 4, we can set up a further text calculation such that when the result of the comparison between the two sum text 'NameComplete' fields does not match, it returns an instruction within its own dedicated field to overwrite the original record with the updated content.

The construction of this field which yields the result "overwrite with new information" is shown above in the definition of 'cNameCompleteCompareResult'. It returns this value only if the calculation of new text, in the field cNameCompleteCompare, is 'not equal' to cNameComplete in the original record.

Additionally, for purposes of usability, the resulting "overwrite" instruction may be rendered on the layout within a button, as demonstrated above in Illustration 4.

As seen above in Illustration 6, we can then further enable the automated replacement of data by attaching to this button a conditional script, 'Replace original field information', whose activation is dependent on receiving the field instruction, -overwrite with new information": if the comparison field remains empty because comparison of the two text sums does not generate this result (that is, there is no difference between old and new text), the script to replace old content with new will not operate.

Jumping off point
The above comprises just a simple beginning to the power of text calculations in FileMaker, and there is much, much more besides on offer when it comes to deeper-level formatting, conditional calculation formulation, and script execution. That said, the use of text calculations can greatly enhance user-friendliness at any level of data entry, or record selection at the level of, and for purposes of, wide-scale data manipulation. The limit really does begin with the builder's imagination.

Editor's Picks

Free Newsletters, In your Inbox