"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Create a FileMaker Calendar



Become a patron of this FREE web site!


Recent Blogs:

Window Locking
Window Locking

Everything Changes
Everything Changes

Subsummary Unique Count
Subsummary Unique Count

Scripted Change Log
Scripted Change Log

Abstracted Log
Abstracted Log


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


FileMaker 20 Video Tutorials


Quick Tip:

Page Count
The new {{PageCount}} symbol in FileMaker Pro 19 allows you to place the total page count on a multiple page document. In previous versions, only the {{PageNumber}} symbol was available. Now you can produce results like "Page 2 of 10" without using a script. The {{PageCount}} also has a function counterpart called Get(PageCount) you can use inside a calculation formula to produce conditional results on the last page of a report.



$10.00 Beginner Video Training


Fun Stuff:

The Wedge
Claris Corporation and the FileMaker, Inc. have been housed in a building called the wedge in Santa Clara, California since they spun off from Apple, Inc. The building is nicknamed the wedge because it looks like a wedge. For some years they had an adjacent building called the interstitial but it was let go when the company downsized to focus on FileMaker products.

The Wedge



Fireside FileMaker Podcast







RSS Feed
Form Letters in Browse
Level: Intermediate
Version: FileMaker 17
Category: Calculations
Tuesday, September 4, 2018
Empowering users of your solution to create their own form letters is life-changing. No longer do they have to contact the developer, explain their needs and go through a back-and-forth testing stage. The developer just designs a single interface that allows the user to create, save, edit and delete their own form letters. It's really not that hard to program either. You just need some global fields, a calculation and a couple of scripts for the foundation of this technique. This approach seems like less income for the developer but that's shortsighted. I always say happy clients equal more business!

Form Letters in Browse


A Bit of History
The merge field feature was one of the best new features added to FileMaker Pro 3.0. Before FileMaker Pro 3.0, developers had to create calculation fields in order for the text in a letter to resize to the width of the embedded fields. With FileMaker Pro 3.0 and higher, you can create form letters that rival the capabilities of most word processors. Developers still have to create a new layout for every form letter but it's much better than cluttering up Manage Database with extra fields. The only other way for a user to modify a letter is to export to merge format and import into a word processor.

Form Letters in Browse


FileMaker Pro 7 was another gigantic milestone in the evolution of FileMaker but there may have been one change that went unnoticed by most. In versions prior to FileMaker Pro 7, text formatting is lost when transformed through a calculation. FileMaker Pro 7 and later preserves text formatting and can even add formatting through specialized functions. At first this caused a lot of trouble for developers who were used to the old way of doing things. Over time, format preservation has become a blessing and is crucial to the technique being covered in this article!

Form Letters in Browse

Schema
The example used throughout this article will be a table of PEOPLE containing standard fields you would have in a simple contact manager.

Form Letters in Browse

The Interface
In order for this solution to be multi-user compatible, global fields will be used to construct the form letters. Not only are the contents of global fields local to each user on the network but they don't cause record locking. Each user will be able to construct their own letter independent of the other users. Other record locking issues will be discussed but let's first define the global fields needed for the basic form letter interface.

xLetter
xField
xName


NOTE: Preceding a global field name with an "x" allows you to quickly identify it from other field types. It also pushes all the global fields to the bottom of the Manage Database dialog list when viewing by field name, making it easier to locate them in the sea of fields. Other naming conventions include "g", "z" and "zz".

The xLetter global field will house the currently active letter so it should have a scroll bar in case a letter gets verbose. The xField global field will be assigned a custom value list containing the names of the fields users can add to a form letter. The names of the fields in the popup menu do not have to match the names of the fields in Manage Database. The xName global field will store the name of the letter for purposes of saving and inserting.

Form Letters in Browse

The first thing you'll want to do is create a script that inserts a merge field. It's quite simple:

Insert Calculated Result [PEOPLE::xletter; "<<" & xfield & ">>"

Insert Calculated Result is used instead of Set Field because it doesn't replace the entire contents of the field. Normally, Set Field is the de facto standard over it's little cousin since it doesn't require the target field to be on the current layout. While the xletter field will obviously be on the layout, the real reason is the Insert Calculated Result can toggle the "Select entire contents" feature. In this case, it is turned off so the merge field is inserted at the cursor point. If the xletter field is not selected as the target then the merge field is inserted at the end of the existing text. Works perfectly for this technique!

Form Letters in Browse

BTW: Less than (<) and greater than (>) signs are used instead of an actual chevron because they are easy to type and cross-platform compatible. Not to mention they look exactly the same as a chevron.

The reason why Set Field is a poor choice in this situation is because it always replaces the entire contents of the target field. There is a technique called Append where the target field can be referenced in the calculation formula thus preserving the original content of the target field. However, trying to place the new content in the middle of the existing content is not a simple task, making Insert Calculated Result a better choice in this scenario. Otherwise, Set Field is almost always a better choice.

Merging the Data
In order to merge the field data with the global field template, a calculation is required. The best way to populate the template with data is to employ the Substitute function. However, there are a few things you need to be wary about when using the Substitute function. First, it's Case sensitive. Most function don't distinguish between upper and lowercase letter but the Substitute function does. In other words, don't fat finger the formula when you type it in or it won't work.

Secondly, the Substitute function is like a tree shredder. If you stick anything in the shredder, it's going to come out different on the other side. For example, if you tell the Substitute function to replace "he" with "she", you're going to end up with words like "tshere", "shello" and "otsher". In this particular case, it's better to search for " he " surrounded by spaces as well as "He " if it starts off a sentence.

With these warnings in place, let's see the formula that transforms the global field template into a separate letter for each person in the found set:

Substitute(

PEOPLE::xletter;

["<<First Name>"; PEOPLE::name_first];
["<<Last Name>>"; PEOPLE::name_last];
["<<Address>>"; PEOPLE::address];
["<<City>>"; PEOPLE::city];
["<<State>>"; PEOPLE::state];
["<<Zip>>"; PEOPLE::zip]

)


FYI: Fat fingering includes extra spaces in the text string of the Substitute function. Substitute is very precise and will look for exactly what you provide. I wouldn't mention this except that every time I teach a class, at least one person puts in extra spaces on accident.

This formula can be used in a simple script using a Replace Field Contents step to deliver the data to a regular field called Letter. This script is purposely very simple to emphasize the key element. I'm sure you can devise a more sophisticated print script that actually prints.

Form Letters in Browse

Saving a Letter
It's fairly common in the world of form letters to use them over and over. Therefore, it's important to have a script to save them to another table. The most common method for scripting the creation of a record in another table is to navigate to a layout based on the related table and use the New Record/Request step along with a Set Field step. The only problem with this approach is the loss of context in terms of a selected field, tab or portal row. This can be solved by creating a new window temporarily but then a new problem occurs with screen flash.

I prefer a different approach. It doesn't replace the previous methods all the time but it works great in this situation. Matt Petrowsky and I wrote about the Push technique way back in Scriptology: FileMaker Pro Demystified for FileMaker Pro 4.0. The technique is still valid to this day! Just cause this technique has a fancy name doesn't mean it's complicated. All it really means is that the target field for the Set Field step is in another table.

The reason I'm using the Push technique here is to eliminate the need to visit another layout. In addition, since we are already going to need a relationship between PEOPLE and the new SAVE table, I'm not creating any additional schema and cluttering up Manage Database. All I'm adding is a script step! So, start by creating a new table called SAVE containing two text fields:

Name
Letter


Then create a relationship between PEOPLE and SAVE using the global xname and name fields as the keys:

Form Letters in Browse

It's very import that the option to allow creation of records is checked on the SAVE side of the relationship. Otherwise, the following script with the Set Field step will not be able to create a new record across the relationship. It's also important to note at this time that Set Field can only create a record in a related table when there are no existing matches. If there are one or more matches already, then the Set Field step will simply overwrite the first related record.

Form Letters in Browse

FYI: The first related record is considered the first record in creation order. If the relationship (not the portal) is sorted then the first related record will be the one that sorts to the top.

The script below may look complicated but it's mostly interface. The important step is the Set Field line.

Form Letters in Browse

Start by asking the user to name the letter. Then, commit the record since the relationship is based on the global xname field and needs to be updated. If the user decides to continue, determine if an existing related record or saved letter exists by comparing the xname field in PEOPLE to the name field in SAVE. There's actually quite a few ways to accomplish this comparison but this seemed the easiest. If no match is detected then the Set Field step creates a new record using the Push technique. If a match is found then a warning appears asking the user if they want to overwrite their existing letter.

NOTE: Even though the same Set Field step is used twice in the same script, I didn't find it necessary to modularize this simple step. It's unlikely to change and placing it in a sub-script will just make reading the script more complicated. Remember, keep it simple and only modularize when the benefits are clear.

Inserting a Letter
In order to recall a saved letter, you're going to need some way to insert it. You might think it's best to use another script and we could. All you need to do is reverse the Push technique. The Pull technique is like a mirror image of the Push technique with the target field and calculation reversed. However, I think there's a better approach so I'm going to reach down to my tool belt and pull out a better tool. This other tool is an auto-enter. This will allow the letter to be inserted simply by selecting it from a popuop menu, making a better interface that avoids a second step of clicking a button. This could also be accomplished with a script trigger but I try to avoid script triggers since they tend to interfere with other triggers and/or button driven scripts. The less scripts, the less problems. If you are interested in an example of this issue, see the article Trigger Tightrope.

Start by adding a popup menu to the xname field based on the contents of the SAVE table.

Form Letters in Browse

The auto-enter calculation uses the same concept as the Pull technique. Just reference the related letter field from the SAVE table when attaching the auto-enter calculation to the xletter field.

SAVE::letter

Make sure to uncheck the option to "do not replace existing value of field (if any)" or the auto-enter formula will not work if the xletter field contains any data.

Deleting a Letter
I could design a more complicated interface than what I'm about to show you but it seems like this is intuitive and straightforward. All a user has to do is select the letter from the existing popup menu on the xname field and then click the delete button right next door. This gives the user a chance to see the contents of the letter, since it is auto-entered in the xletter field, and double-check that it's really the letter they want to delete. Alternatively, I could take the user to the SAVE layout and allow them to search for the letters they want to delete but that would require a more complicated series of steps and an additional interface they would have to learn.

Here's the script for deleting the letter selected in the xname field.

Form Letters in Browse

As you can see, I had to visit a layout based on the SAVE table using the Go to Related Record step. In order to prevent loss of context, I decided to open a new window and then close it once I was done. In order to reduce screen flash, I moved the new window off screen by setting the location to -10,000 for both the upper and left hand sides of the window. You can't see the relocation of the window on the Go to Related Record step but trust me, it's there.

Feature Conflict
It's always smart to test your solution before deployment. While I was testing, I noticed that If I tried to save a letter with a new name, it would delete the contents of the xletter field. This happens because modifying the xname field triggers the auto-enter calculation. Since there is no match to the new letter name, the auto-enter formula finds no related record and enters a blank value into the xletter field. The end result is the erasure of the letter you worked on so diligently, not only in the xletter field but also in the SAVE table. Poof... it's gone!

I might be able to rescue the auto-enter calculation with a Case statement but there's an easier solution IMHO. The answer is to use a Lookup or the Lookup function. Both of these features have the ability include an alternative reaction if there is no exact match. I'm going to choose the Lookup function since it's based on newer code than the Lookup feature.

Lookup(SAVE::letter; xletter)

The alternative behavior for the Lookup function is contained in the optional second parameter. If there is no match, I instruct the Lookup function to auto-enter itself thus preserving the contents of the xletter field.

FYI: I know this sounds crazy but the Lookup function was introduced long after the Lookup feature. Although it does essentially the same thing, it is based on completely different code. I've even heard stories from the System Engineers where the Lookup feature causes slow downs in certain scenarios. Note to self... avoid this feature whenever possible, which is always, since there is a function counterpart.

The Lookup function solves one problem but not all of them. Turns out, if you try to edit the letter, it writes back over itself. Ooops! Here's where ancient technology is superior. Even though the Lookup function can substitute a value if there is no exact match, it doesn't have the ability to "do not copy, if there is no exact match". This is different than the lookup function which actually has to insert something. While the Lookup function and the feature both solve the problem when a letter is overwritten, only the Lookup feature can solve the problem when the xletter field is modified. That's because the auto-enter is triggered when any field in the formula is modified. Since the xletter field is referenced in the auto-enter formula, it's doomed for failure and exactly why it is so important to know every feature in FileMaker so you can choose the right one for the job at hand.

Form Letters in Browse

Curses!!! Two reader comments and I still haven't solve the inserting problem. While the Lookup feature described just above solves the problem when the xletter field is modified, it doesn't solve an issue when you try to overwrite a modified letter. As soon as the Show Custom Dialog commits the record, the modified xletter field is replaced with the saved contents in the SAVE table, deleting the modified letter. Grrr! I so wanted this elegant lookup solution to work so inserting a letter was a one step process. No choice now but to revert to the Pull technique. All you need to do is reverse the Push technique.

Commit Records/Requests [With dialog: Off]
Set Field [PEOPLE::xletter; SAVE::letter]


I threw in a Commit Records/Requests step for no reason other than to make the fields look nice (no active field) after the script completes. Just goes to show you how important testing is and what a crappy job I did on this particular feature. Thanks to all you folks who wrote and let me know what was going wrong. Hopefully I've got the problem nailed now!

Record Locking
Record locking occurs when two or more users in a multi-user scenario attempt to edit the same record at the same time. FileMaker throws up a dialog to subsequent editors telling them they can't edit the record. When the first editor commits the record, the other users can edit the record. Make sense, right? Think about it. What would happen if two users edited the same record at the same time? Whoever committed the record last would overwrite the other users edits.

The built-in record locking works auto-magically and there is no need to program anything additional unless you are scripting. If a script attempts to edit a record, the same blockades are presented. It's pretty easy to trap for a 301 error and program your script to react appropriately on a single record. The challenge comes when the script is attempting to write to multiple records such as occurs with a record looping script or a Replace Field Contents script step. In this article, we are using the Replace Field Contents step to populate the letter field with the template from the xletter global field.

Think about it. If you are looping through records and FileMaker encounters a record that is locked, what do you do? Do you go back and set the earlier records back to their original values? What if one of the earlier records is now locked? Do you try store the record number of the locked record and come back later? It's a bit of a bind. Some developers apply the same solution to every record locking issue. While I do like the transaction processing approach, I prefer not to blindly apply a single solution to every scenario. It could be inefficient or ineffective. In this case, it simply doesn't work as well as the approach I'm about to show you.

The basic reason I prefer the approach I'm going to show is simplicity. Transaction processing has it's time and place but it does add complexity to any script. It also requires a portal. I'm not going to go into detail so just click on the link in the previous paragraph for more details. In addition, the transactional processing is an all or nothing solution. Either all the records are updated or none of them are. The solution I'm about to show you always works, even when a record is locked. My point in stalling is to help you understand why thinking before doing is important. So many developers just jump without thought. The other reason I'm taking my time is the solution is so simple, it only takes a paragraph to explain, LOL.

The solution is... change the letter field from a text field to a calculation field and use the formula from the Replace Field Contents step. That's it! Of course, you want to remove the Replace Field Contents step from the script but there's no other steps. So, why does it stop record locking? Because the formula contains a reference to a global field, it turns the calculation field into a global formula unique to each user. If one of the standard fields on a record is being edited when a form letter is printed, all that happens is the information before the edit is used in the letter.

More Record Locking
There are other record locking scenarios I cover in this blog and there will be many in the future. It's a very important consideration. In this article, all I'm suggesting is you become familiar with record locking and don't blindly apply the same solution to every record locking scenario. By becoming familiar with the variety of solutions available, you will better understand the issue and be able to design your own custom record locking solutions unique to the problem at hand.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

Example File (available to patrons at $5.00 per month): Download here if you are a patron

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window or becoming a patron. Thanks so much!

Comments:

Martin Paff 09/07/2018
  Hi, great article as I often wished to have something like that at hand. I would have loved to read about how to prepare the selection list for the fields - but I think I will figure it out in the article. What also would be great to read how the selection list would only show the fields that have not been used in the letter already. But this certainly depends on the case if you want to have such a feature or not.

In the demo file the saving does not work right. If I alter a letter and hit save under the same name the letter text is reset to the former status. Only if I use a new name, it gets saved properly...
Response by:   John Mark Osborne 09/11/2018
Seems I've been having a lotta trouble with this insert feature. Thanks for letting me know. I had to resort to a script for inserting a letter. Not as elegant but bullet proof. I left all my trials and tribulations in the article and just added on to the end of the insert section. I also uploaded a new file. Thanks again and let me know if you find any trouble with this file. Happy FileMaking!
Paul Jansen 09/05/2018
  Hi, Really interesting article and demo. I have found that if I edit the letter contents, as soon as I exit the field, the changes are overwritten with the original content. I can't find any way to actually save any changes to an existing letter.
Response by:   John Mark Osborne 09/05/2018
My bad. Had been a while since I programmed this technique from scratch and I forgot about one of the limitations of the Lookup function. I have modified the article and the example file to use the Lookup feature rather than the Lookup function. Thanks for pointing this out. I really appreciate it!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!