by John Mark Osborne, <jmo@filemakerpros.com>, http://www.filemakerpros.com
The Many Faces of Set Field
BONUS: SETFELD1.FP3
PLATFORM: Mac/Win
The FileMaker Pro 4.0 online help system contains this definition of the Set Field script step:
"Replaces the entire contents of the specified field in the current record with the result of a calculation. The result of the calculation must match the field type. For example, you can't assign a date calculation to a container field".
This definition tells so little about what Set Field can really do. For instance, Set Field can actually append, subtract, extract data or even push and pull data between files. On an even more basic level, the definition in the online help makes it seem as though Set Field can do no more than Paste Result.
Much More than Paste Result
Set Field and Paste Result, at first, may seem to perform the same action. They both allow you to specify a field and they both allow you to specify a calculation. So, what's the difference?
The biggest difference is that Paste Result requires that the destination field be on the current layout. That means that the field needs to be present on every layout you could possibly run the Paste Result script. But, what if you don't want the field on every layout where the script could run? You could create a master layout that contains all your fields and switch to it every time you run the script. However, this creates yet another problem. Switching to the master layout requires you to add a Freeze Window step to your script so the screen doesn't flash. You could avoid the master layout issues and try to hide your field on the layouts where it is needed, but this is harder than it sounds. No matter how good you are at hiding the field, it flashes a little when Paste Result places the result of the calculation in it.
As you can see, the Paste Result script step has many considerations. If you use Set Field, all these problems go away. Set Field does not require the field to be on the current layout or even to be in the current file. The user's cursor will even stay in the present location after Set Field is performed.
Entire Contents?
Let's go back to the definition in the FileMaker online help system. It says that Set Field replaces the entire contents of the specified field with the result of a calculation. Technically this is true, but it is possible for the result of the calculation to include parts or all of the specified field. This opens up all kinds of doors for manipulating data.
Let's say you wanted the feature of adding a title like "Mr." to the beginning of a Name field. With the FileMaker, Inc. definition of Set Field, it may not seem possible. In reality, it is easy to append data to the current contents of a field with a simple formula and the Set Field script step:
"Mr. " & Name
This formula will take the current contents of the Name field, add "Mr. " to the beginning and place the result into the Name field. The result is as if you added to the field rather than replaced the entire contents. For instance, if the first name on the current record is "John Osborne", running this Set Field script would change the contents to "Mr. John Osborne".
If you wanted to expand this formula to include all possible titles, you could do something like this:
Case(Gender = "Male", "Mr. ", "Mrs. ") & Name
Subtracting
Another feature you might want to add is the ability to remove the title. There are many ways to approach this formula. You could use the LeftWords function to determine if the first word was either "Mr." or "Mrs." and then use the RightWords and WordCount function to remove all but the first word:
Case(LeftWords(Name, 1) = "Mr" or LeftWords(Name, 1) = "Mrs", RightWords(Name, WordCount(Name) - 1), Name)
Since we know that the Name field is very unlikely to contain the word "Mr." or "Mrs." more than once in the Name field, it is possible to use a shorter formula:
Substitute(Substitute(Name, "Mrs. ", ""), "Mr. ", "")
Notice that the "Mr." and "Mrs." text strings in this formula contain a space at the end. This is to remove the space between the title and the first name.
Extracting
You can take the subtraction concept one step further and use it to extract data. Extracting data allows you to remove the data from the field and place it somewhere else. Let's say you imported a lot of data from a mainframe computer, but Name field contained both the first name and last name, making sorting by last name impossible. Set Field could remove the last name from the Name field and place it in a new field called Last Name. Here is the complete script which involves two Set Field steps:
Set Field ["Last Name", "RightWords(Name, 1)"]
Set Field ["Name", "LeftWords(Name, 1)"]
This script assumes for simplicity that all names are two words with no middle names or multiple word last names.
Pushing and Pulling Data
One of the most remarkable abilities of Set Field, is how it can push and pull data from one file to another via a relationship. All you have to do is specify a related field and the data will be transported from the current record in the primary file to the first related record in the secondary file.
A simple example would be the transporting of a name from one file to the other. Here is what the script would look like:
Set Field ["PushPull Data::Name", "Name"]
This script will push the data from the field called Name in the primary file to the Name field on the first related record in the secondary file. It is important to note that there could be several records in the secondary file that are related to the current record in the primary file. Set Field will use the first of these related records based on creation order when pushing the data. It is also important to know that Set Field will create a new related record if there are no matches in the secondary database and the relationship allows creation of related records.
Pulling data from the secondary file is as easy as reversing the formula. Instead of referencing a related field with the Specify Field option in Set Field, the related field is placed in the formula:
Set Field ["Name", "PushPull Data::Name"]
Notice that the same relationship can be used for pushing and pulling data. Pushing and pulling are almost identical, except that the data is going a different direction.
Testing Formulas
Most likely your scripts are going to be much more complicated than the examples given. The script will contain one Set Field statement or more, plus a variety of other script steps. This makes testing your formulas a long process of running through the entire script just to find out your formula is slightly off.
A faster way to test your formulas is to make a temporary script that contains just the Set Field step. Once you have perfected the formula, you can move it into the final script. Or, you can use a calculation field to perfect the formula. This technique works well because there are fewer steps to enter a calculation field than a Set Field script. With a Script, you have to choose ScriptMaker from the Script menu, double-click on the script you want to edit, select the Set Field step and click on the Specify button. With a calculation field, you only have to choose Define Fields from the File menu and then double-click on the field to start editing. In fact, you can even use a keyboard shortcut for Define Fields to speed up the editing process even more. The only drawback of calculation fields is that they can take a long time to process if you have many records in your database. However, if you are designing in accordance with standard developer techniques you should have a subset of data in the unfinished database.
Replace vs. Set Field
Set Field only works on the current record. If you want to apply the same formula on all of the records in the found set, use the Replace script step. The syntax for entering formulas is exactly the same for the Replace script step, the Set Field script step and calculation fields, making it possible to interchange the formulas easily using copy and paste. Don't test your formulas with the Replace script step. Use a Set Field script step or a calculation field to perfect the formula. Otherwise, you will be forever resetting the data on all your records in the found set just to test the revised formula.
Next Issue
The examples given in this article were not meant to parallel real-life solutions, but to make it simple to learn fundamental Set Field concepts. In the next issue of ISO FileMaker Magazine, I will explore intermediate and advanced applications of these concepts to solve real database issues.
## END ##