Today an interesting puzzle came across my path. Well, I found the puzzle somewhat interesting, but I began to be fascinated by the solution I had come up with for it, which I think is rather novel.
Here’s the puzzle: Given a FileMaker system that uses merge fields in text blocks to construct form letters with a different layout for each form letter, how would you build a system that eliminated the need for the multitude of layouts.
It’s a problem I’ve seen solved elsewhere with many buttons for each field the user might want to merge into a letter. Click a button and a merge code is inserted into the form. The many buttons could be eliminated with a popup menu that displays the available fields, but I starting thinking about how to make the available fields dynamic. A few years ago I would have placed them all on a dedicated layout and used the FieldNames() function to retrieve a list of them. Is it possible to retrieve a list of available fields without the use of a layout dedicated to that purpose?
I don’t know where it came from, but I thought about field comments. Field comments are available from FileMaker’s Define Database dialog box below the field name text field, but I’ve very seldom used it for its intended purpose. Data fields are pretty self-documenting (do you really need a comment for a first_name field?), as are summary fields if they’re properly named (total_of_invoice_amounts, for example). Calculation fields have a much better commenting mechanism that can be placed in the calculation definition. So field comments tend to remain unused in my systems.
What if they were used to store metadata about the field. In this case, some of the fields need to be available to the end user to merge into correspondence templates. What if I used the comment field to indicate this and, further, to store the user-friendly text that should be displayed to the user?
The puzzle seemed interesting enough that I not only made the suggestion to the individual who posed it but decided to go ahead and try it out to prove that it could work. The following tutorial is more of a proof-of-concept than a final solution, but perhaps you’ll find it useful for this type of problem or even something completely unrelated. Download the sample file or follow along. If you happen to use this technique for any other type of problem, I’d very much like to hear about it.
Foundational Concepts
There are two FileMaker functions that allow this solution to work. First is FieldComments(), which, given the name of a field, will return the comments defined for that field. But the second allows us to retrieve all of the fields in a table, or more properly, in a table occurrence within the relationship graph: ExecuteSQL(). FileMaker offers some “virtual” tables that can be used for introspection with this function, including FileMaker_Fields, which is a “table” of all of the fields in the system. So getting all of the fields in a particular table (occurrence) is simply a matter of using an ExecuteSQL() query:
ExecuteSQL(
"SELECT FieldName
FROM FileMaker_Fields
WHERE TableName = ?";
""; "";
"TableName"
)
What I’ll show you here is the use of field comments to record field metadata that can then be parsed and used within the user interface, in this case, to provide a list of fields that can be inserted into a merge text field. Once a protocol has been selected for storing metadata in the field comments, the rest is mostly a matter of text parsing, albeit some rather intricate text parsing.
Field Comment Protocol
The first thing to decide is the format that field comments will take to indicate first that they are available to users for merging into a letter template and second what the user-friendly name of the field will be. If you’re using field names like First Name, perhaps this isn’t necessary for you, but I prefer to exclude spaces from my field names and so have fields named first_name or birth_date. The comments can be used to translate these user-unfriendly names into user-friendly names.
The method I decided on was to use the format flag:label. In this case flag is is_merge_field and the label is the user-friendly name of a field. This might be more than is actually needed for this solution, since there are no other uses for the field comment metadata, but it works for now. As an example, the field comments for first_name would therefore be is_merge_field:First Name.
A Plethora of Custom Functions
You may have read elsewhere on my blog that I’m a fairly big fan of FileMaker custom functions, and the method I’ve used to build this solution makes liberal use of them. Here are all of the custom functions with some commentary on each.
appl.CommentValueSeparator = ":"appl.mergeFieldFlag = "is_merge_field"appl.MergeTextStart = "<<"appl.MergeTextEnd = ">>"
Any string constant that I use and may want to globally change later will probably get a custom function, and that’s all these four are. When inserting merge codes into text blocks on a FileMaker layout, FileMaker uses the less-than and greater-than symbols to indicate the text is meant to be later merged. I see no reason not to keep that convention.
appl.MergeEncoded( _merge_text ) = appl.MergeTextStart & _merge_text & appl.MergeTextEnd
Simply enclosing the text to be merged within the codes for indicating it’s merge text. For example, appl.MergeEncoded( "First Name" ) = "<<First Name>>".
appl.FieldNames( _to_name ) =ExecuteSQL( "SELECT FieldName FROM FileMaker_Fields WHERE TableName = ?";
""; "";
_to_name)
Given a table occurrence name this function retrieves all of the fields for the table it’s linked to. For the People table occurrence that’s linked to the People table shown in the screen shot above this returns
iddate_time_createddate_time_modifiedfirst_namemiddle_namelast_namebirth_dateletter_templateui_fieldsg_field_selectionletter
appl.FieldComment( _to_name; _field_name ) =FieldComment( Get( FileName ); Case( not IsEmpty( _to_name ); _to_name & "::"; "" ) & _field_name)
I abstracted FieldComment() here to not worry about the file name and to offer the possibility of including a table occurrence or omitting it, but returning the appropriate comment regardless. Ironically this is probably the least necessary custom function but is the basis for the technique.
appl.IsMergeField( _to_name; _field_name ) =Let( [ _comment = appl.FieldComment( _to_name; _field_name );
_comment_as_list = Substitute( _comment; appl.CommentValueSeparator; ¶ );
_first_comment_item = GetValue( _comment_as_list; 1 ) ];
_first_comment_item = appl.MergeFieldFlag)
Given a table occurrence and field name, this function returns True if the named field’s comments indicate it’s a merge field. It first extracts the comment from the field and then transforms that comment into a list by replacing the comment separator with a carriage return. Finally it uses the list to extract the first item, which would be the indicator that it’s a merge field, returning True if the first item is the same as defined in appl.MergeFieldFlag ("is_merge_field").
Going through it a step at a time, we’ll first assume that a merge field is passed. So, assuming we called appl.IsMergeField( "People"; "first_name" ), the _comment variable will be set to "is_merge_field:First Name". _comment_as_list will then be "is_merge_field¶First Name" and _first_comment_item will be "is_merge_field", which does in fact equal the contents of appl.MergeFieldFlag, so the function returns True.
What if a non-merge field’s name is passed? If there’s no comment for the field, all three of the Let() variables will be empty strings and the final return value will be False.
appl.MergeFieldUIName( _to_name; _field_name ) =Let( [ _comment = appl.FieldComment( _to_name; _field_name );
_comment_as_list = Substitute( _comment; appl.CommentValueSeparator; ¶ ) ];
GetValue( _comment_as_list; 2 ))
Here we’re using the field comments to retrieve the user interface name that should be displayed. It’s using almost the exact same logic as appl.IsMergeField, but instead of comparing the first item to a known value it’s simply returning the second item, which our protocol defines to be the user-friendly field name. If I were to continue with this beyond a proof-of-concept stage, I’d probably place the lines that set _comment and _comment_as_list variables into a custom function since I’m using it twice.
That covers the “simple” custom functions. Next come three sets of wrapper/recursive functions. My convention is to name a recursive function called by a wrapper with the same name as the wrapper but followed by an underscore. So appl.MergeFieldUINames is the interface into appl.MergeFieldUINames_, a recursive custom function.
appl.MergeFieldUINames_( _to_name; _field_list ) =Case( ValueCount( _field_list ) = 0; "";
Let( [ _first_field = GetValue( _field_list; 1 ); _field_list = RightValues( _field_list; ValueCount( _field_list ) - 1 );
_ui_name = Case( appl.IsMergeField( _to_name; _first_field ); appl.MergeFieldUIName( _to_name; _first_field ) & ¶; "" ) ];
_ui_name & appl.MergeFieldUINames_( _to_name; _field_list ) ))
This function returns a list of all the user-friendly field names when given a field list. The first thing this recursive function does is test for the exit condition, which in this case is being passed an empty _field_list. Then it extracts the first field from the list followed by popping that field off the list. If the field is a merge field, it calculates the user interface name for the field. Finally it takes that user interface name and calls itself with the rest of the list.
appl.MergeFieldUINames( _to_name ) =Let( [ _field_list = appl.FieldNames( _to_name ) ];
appl.MergeFieldUINames_( _to_name; _field_list ))
Here’s our wrapper for appl.MergeFieldUINames_, which first gets a list of all the fields in the passed table occurrence and then passes that on to the recursive function.
appl.FieldName_( _to_name; _ui_name; _field_list ) =Case( ValueCount( _field_list ) = 0; "";
Let(
[ _first_field = GetValue( _field_list; 1 ); _field_list = RightValues( _field_list; ValueCount( _field_list ) - 1 );
_field_comment = appl.FieldComment( _to_name; _first_field ); _field_found = appl.MergeFieldUIName( _to_name; _first_field ) = _ui_name ];
Case( _field_found; _first_field; appl.FieldName_( _to_name; _ui_name; _field_list ) ) ))
Our second recursive custom function is responsible for being given a user-friendly name and translating that into the original field name. It’s first task is to check for our (first) exit condition, which would only be reached if the passed _ui_name isn’t specified for any of the fields in _field_list. Then it, like the previous recursive custom function, extracts the first value and pops it off of the list.
Now we use our appl.FieldComment function to get the comment for the first field and check to see if the field was found to have the _ui_name specified. If it does, we just return the field we’ve landed on, and if not, continue the recursive function until we do or we run out of fields.
appl.FieldName( _to_name; _ui_name ) =Let( [ _field_names = appl.FieldNames( _to_name ) ];
appl.FieldName_( _to_name; _ui_name; _field_names ))
The wrapper for appl.FieldName_, which compiles the list of fields in the given table occurrence and passes that list to our recursive function.
appl.MergedResult_( _to_name; original_text; _field_list ) =Case( ValueCount( _field_list ) = 0; _original_text;
Let( [ _first_field = GetValue( _field_list; 1 ); _field_list = RightValues( _field_list; ValueCount( _field_list ) - 1 );
_ui_field_name = appl.MergeFieldUIName( _to_name; _first_field ); _encoded_ui_field_name = appl.MergeEncoded( _ui_field_name ); _field_value = GetField( _to_name & "::" & _first_field );
_new_text = Substitute( _original_text; _encoded_ui_field_name; _field_value ) ];
appl.MergedResult_( _to_name; _new_text; _field_list ) ))
The most complicated custom function is responsible for taking some text with merge codes and using those merge codes to insert the values indicated. As with other recursive functions, it begins with an exit condition, in this case, like the others, doing nothing when the number of fields passed in _field_list is zero. Also like the others, if there are values in _field_list, it extracts the first one and pops it off of the list. It then uses this first field’s name to query the field comment as to what its user-friendly name should be and calculates the merge encoded version of that and uses GetField() to retrieve the field’s contents. Next is substitutes the merge code for the field’s value and finally passes this new text on to itself with the reduced field list.
Let’s follow this function through one time. Assume we call it with appl.MergedResult_( "People"; "Dear <<First Name>> <<Last Name>>"; "id¶first_name¶last_name" ). We’ll assume that the current record has a first_name of "Charles" and a last_name of "Ross" (the value of id doesn’t matter because it’s not a merge field).
The first time through our initial condition in the Case() function fails because there are three values in _field_list, not zero, so we enter the Let() function. _first_field will be set to "id" and _field_list to "first_name¶last_name". With a _first_name of "id", _ui_field_name will be blank because it’s not a merge field, so _encoded_ui_field_name will be "<<>>", which doesn’t appear in the _original_text, so _new_text will be the same as _original_text, and we call the function again, this time one field shorter: appl.MergedResult_( "People"; "Dear <<First Name>> <<Last Name>>"; "first_name¶last_name" ).
On our second pass the initial condition of Case() again fails because we have two values. _first_field becomes "first_name" and _field_list becomes "last_name". _ui_field_name becomes "First Name" and _encoded_ui_field_name becomes "<<First Name>>". _field_value becomes "Charles" and _new_text becomes "Dear Charles <<Last Name>>". We take this new text and pass it to the function, again, one field shorter in the list: appl.MergedResult_( "People"; "Dear Charles <<Last Name>>"; "last_name" ).
This third time through the initial condition still fails because there’s one value left. _first_field becomes "last_name", _field_list becomes an empty string, _ui_field_name becomes "Last Name" and _encoded_ui_field_name becomes "<<Last Name>>". After _field_value gets set to "Ross" the _new_text becomes "Dear Charles Ross" and we call the function one last time: appl.MergedResult_( "People"; "Dear Charles Ross"; "" ).
The initial condition finally succeeds in this case because there are no more fields to enumerate, and we move back up the call stack with the top call (the first one) returning the final text: "Dear Charles Ross".
appl.MergedResult( _to_name; _original_text ) =appl.MergedResult_( _to_name; _original_text; appl.FieldNames( _to_name ) )
After all that the final function is almost anti-climatic, being just our wrapper for the recursive function above.
Solution Fields
Once all of the custom functions are in place, an actual implementation of the feature is a matter of a few calculation fields and some scripting involving script triggers.
First we need a field to store the list of user-friendly field names. In the screen shot that demonstrated the field comments for the first_name field you can see that this is ui_field and is an indexed calculation with a text result of appl.MergeFieldUINames( "People" ). We use this to define a value list called UIFields that simply uses the values from this field. Since the field will contain a return-separated list of user-friendly field labels, each field label will show up as a separate value in this value list.
Next we need a global text field, g_field_selection, which will have this value list attached as a drop-down menu.
The last field is letter, which will store the merged correspondence. All the work for that is already done in the custom function, so its calculated value is simply appl.MergedResult( "People"; People::letter_template ).
Scripting the Rest
The rest of the solution is a matter of creating two scripts and attaching script triggers to them. The first script is Store Selection Position. We need this because when the user clicks our field label menu to insert a merge code we need to know where in the text to insert the merge code. This could be at a point or it could be that the merge code should replace an existing selection. Therefore all Store Selection Position does is record the active selection values into global variables.
Set Variable[$$_SELECTION_START; Value: Get( ActiveSelectionStart )]Set Variable[$$_SELECTION_SIZE; Value: Get( ActiveSelectionSize) ]
Attach this script to the OnObjectExit trigger of the letter_template field.
The next script, Field Selected, is to be triggered when the user selects a field label from the g_field_selection field and reads as follows:
Set Field[People::letter_template; Let( [ _inserted_text = appl.MergeEncoded( People::g_field_selection ); _inserted_text_len = Length( _inserted_text );
_old_sel_start = $$_SELECTION_START; _old_sel_size = $$_SELECTION_SIZE;
$$_SELECTION_SIZE = Case( $$_SELECTION_SIZE = 0; 0; _inserted_text_len );
$$_SELECTION_START = $$_SELECTION_START + Case( $$_SELECTION_SIZE = 0; _inserted_text_len; 0 ) ];
Replace( People::letter_template; _old_sel_start; _old_sel_size; _inserted_text ) )]Set Selection[People::letter_template; $$_SELECTION_START; $$_SELECTION_START + $$_SELECTION_SIZE]
All of this work with the selection start and size may require some explanation. Selecting a merge field from the drop down menu should insert the merge code into the letter_template field, but where? Well, that depends on what was selected. If nothing was selected we should simply insert the merge code at the insertion point. If something was selected we should replace it.
What should be selected after the insertion? Again, it depends on what was selected before. If nothing was selected before, nothing should be selected afterwards but the insersion point should be placed after the inserted text, and if there was a selection before insersion, the merge code should be selected.
The code in the Let() function that’s recording the selection values into _old_sel_start and _old_sel_size and recalculating the global variables is handling this logic. We record the old values because we’re about to change them but need them for the Replace() function later. This could be avoided if we were to recalculate the global variable values after using them in the Replace() function, but I generally use existing calculations to set such variables. $$_SELECTION_SIZE should remain zero if it was zero to begin with, and if it wasn’t it should now be the length of the inserted text. $$_SELECTION_START should stay the same if something was originally selected by move forward by the length of the inserted text if nothing was.
After these calculations are made in preparation for the next script step we use the Replace() function to replace text at a particular point for a particular size with the contents of the field indicated by the merge code. Then we use Set Selection to set the selected text as appropriate. With the script in place we use it as a script trigger for the g_field_selection‘s OnObjectModify trigger.
Refinements?
I built this by the seat of my pants, not worrying too much about designing for the future. I just wanted to see if it could be done and how that might look. But there are a few things that I now know I would change about the design. First of all, all of the recursive functions are based on a full list of the fields in a table, but not all need to be. The appl.MergedResult_ function could instead check for the presence of merge codes in the _original_text parameter and use their absence as the basis for the exit condition. This would be faster than looping through probably many unnecessary fields. For example, as it’s currently built and with the People table, it executes 12 times (once for each field and once for the exit condition) when it only needs to execute 4 times (once for each unique merge code and once for the exit condition). If there are hundreds of fields in the table, this time savings might be significant.
Also, in a production system I’d probably choose some other protocol for storing metadata in the field comments. As far as I can tell there are no limitations on what characters can be stored there, and the amount of text that could be stored is 30,000 characters, plenty of space for multiple pieces of metadata, should they be needed. Perhaps name/value pairs would be a better protocol, as in user_friendly_label = "First Name";, which could be easily translated into variables within custom functions and scripts. I can’t think of other uses for field comment metadata at this point, but if I later do, storing the metadata as name/value pairs would make it much easier to parse multiple items without having to rewrite the parsing code.
Regardless, for some reason i find this technique very interesting. I hope you do too, and if you find some other purpose for it, please let me know.
Comments
Leave a Reply
*
Be the first to comment.