Search for Empty Searching for records with an empty field works most of the time using a single equal sign (=). It can fail if a user has fat fingered a field by entering a space, a return or some other invisible character. While the field contains valid characters, the field is technically empty. In order to avoid this problem, search with two equal signs (==).
FileMaker, Inc. FileMaker, Inc. or FMI is a wholly owned subsidiary of Apple, Inc. It was once named Claris when it also sold MacWrite, MacPaint, Claris Emailer, ClarisWorks, etc. but changed it's name in 1998 when it focused on FileMaker. FileMaker started out as a DOS application called Nutshell but was shaped into the powerhouse it is today by Claris and FMI.
The Set Field script step is one of the most widely used steps in FileMaker due to it's versatility at moving data around a solution. You can simply transfer data from one field to another or even construct a complicated calculation and then target a field with the result. But that's just skimming the surface of what it can do. Set Field is workhorse script step that has no equal. In this article, I'll discuss seven essential techniques you can perform with Set Field. These concepts are crucial to learning how to program complex FileMaker solutions.
The History Imagine a scripting interface, that's a shadow of the current Script Workspace, with simple check boxes and radio buttons for programming. That's what life was like before FileMaker Pro 3.0 was released. All you could do in FileMaker Pro 2.0 was automate a report. Not only was 3.0 relational, instead of flat, but the development team added a whole new scripting experience that we still use today. It's more akin to a programming language than automation. It took a long time to chew on all the new scripting capabilities but one step always stood out for me... Set Field.
Matt Petrowsky and I first wrote about Set Field in our book, "Scriptology - FileMaker Pro Demystified". We talked about seven important techniques: Overwrite, Substitute, Extract, Append, Prepend, Push and Pull. These techniques haven't changed a bit since FileMaker Pro 3.0 was released but I find many budding developers aren't familiar with these indispensable concepts. They're really the building blocks to understanding how to create complex FileMaker solutions.
First... The Basics Set Field isn't complicated on the surface. Only two parameters to populate. Simply specify a target field and a calculation and you're ready to go. The calculation formula doesn't even need to be that complicated. You can accomplish a lot just by referencing another field. The script step below moves the data from "mysecondfield" to "myfirstfield".
Set Field [MYTABLE::myfirstfield; MYTABLE::mysecondfield]
If there is any data in "myfirstfield", it will be overwritten. We'll talk about methods for adding to the contents of "myfirstfield" but it's important to first understand the basic functionality of Set Field is to overwrite the contents of the target field with the result of the calculation formula.
Since Set Field doesn't need to select the target field or even have it on the current layout, it ignores validation. It really does run on a layer below data entry that most other script steps can't come close to matching. For example, you might consider using the Copy and Paste script steps to move data from "mysecondfield" to "myfirstfield". Not only does Copy destroy the contents of the clipboard, it also requires the field to be on the layout and is subject to validation rules. Not even Set Field's little brother, Insert Calculated Result, can avoid having the target field on the current layout. It's truly a powerhouse script step that makes scripting more like programming than automation.
FYI: I often tell students in my classes that Cut, Copy and Paste script steps are evil and should be avoided at all costs unless the button says "copy" on it. Imagine a user placing an image in the clipboard, only later finding it gone because your copy script destroyed it. Yikes!
So, how does Set Field differ from other methods for storing the result of a calculation formula? Calculation fields, Auto-Enter calculations and Replace Field Contents can all place a result in a field, just like Set Field. Well, Calculation fields are live formulas that update whenever a field referenced in the formula is modified while Set Field only updates the result when the script is run. It's kinda like taking a snapshot.
Auto-Enter calculations, Replace Field Contents and Set Field are much more alike, rewriting the target field only when called upon. However, Auto-Enter calculations are schema level while Set Field is layout level. In other words, Auto-Enter is defined in Manage Database so it functions wherever that field is placed. Set Field requires the script to be added to a layout via a button or some other mechanism for it be triggered from the context of each layout.
As for Replace Field Contents, it's much more like Set Field except it performs it's calculation formula across all the records in the found set. Think of Replace Field Contents as a Set Field inside a record loop. It goes to each record and performs the same snapshot formula on each record but without leaving the context of the current record. Truly, Set Field and Replace Field Contents are more like each other than any other feature in FileMaker, with the possible exception of Insert Calculated Result.
TIP: If Set Field doesn't have a target field specified, it will place the calculation result in the currently active field, much in the same way Insert Calculated Result functions.
Append & Prepend These are probably the two most important Set Field techniques to learn. They basically allow you to add data to a target field without deleting the current contents. It's really quite simple but crucial to developing complicated parsing techniques. Let's start with the basics and show you how to combine a first and last name field into a full name field. Normally, you'd use a Calculation field for this task but it's not the example I'm trying to teach you but the technique behind the example. I'm keeping it simple so it's easy to explain. Append and Prepend can be implemented in so many more complicated scenarios that we'll never even be able to scratch the surface in this article. If I can just plant the seed with a basic example, I'll have done my job.
Set Field [MYTABLE::name_full; MYTABLE::name_first] Set Field [MYTABLE::name_full; MYTABLE::name_full & " " & MYTABLE::name_last]
In this example, the first Set Field overwrites whatever is stored in the "name_full" field with the contents of the "name_first" field. The second Set Field appends the current contents of the "name_full" field with a space and the "name_last" field. By referencing the target field in the formula, it's possible to preserve the target field data and add on to it. Seems strange but that's the way it works. Think of it as an order of operations. The calculation formula evaluates first and then the target field is populated.
If you want to prepend, simply swap the order of the target field and the content field. For instance, you might already have the last name in the "name_full" field and want to add the contents of the "name_first" to the beginning..
Set Field [MYTABLE::name_full; MYTABLE::name_first & " " & MYTABLE::name_full]
If you want to see more complicated implementations of the Append and Prepend techniques, look at the articles Parsing for Features and Back and Forward. While Scriptology is also a good resource for these techniques, it hasn't been updated since it was published twenty years ago, making everything else covered mostly irrelevant in FileMaker 17.
Extract & Substitute Extract and Substitute are a little more complicated, even when distilled down to the basics. If you read the articles mentioned above, you'll have gotten a taste for Append, Prepend, Subtract and Extract already. You could even come to the conclusion that these techniques aren't just for parsing but allow you to make features that wouldn't otherwise be possible. They are truly important in the whole scheme of FileMaker programming so don't lose sight of the goal with these simple examples.
Let's use the name parsing example again to demonstrate subtraction and extraction. The example here uses three fields. The full name field contains the first and last name together in a single field (a common import situation). In the first example, we want to separate the data into the first name and last name fields to perform an extraction. Seems pretty simple and a fairly common task when fixing imported data. Unfortunately, FileMaker doesn't really have a tool for removing data from one place and placing it another. You'll need two script steps to accomplish this task.
During this process of extraction, the full name field will be emptied. You might not care if the full name continues to hold the name data anymore, because you are likely to delete it. But, that's not the point. I'm offering a very simple example for a technique that is often very complex. Understand the concept and you'll understand how FileMaker functions at a most basic level. It will help you to understand how to approach solving problems knowing how the tools can be manipulated at a root level.
So, how to approach this problem. First, you need to grab the data you want to place in the first and last name fields. You can do this with the LeftWords and RightWords functions. They have an algorithm built into them that recognizes words.
Set Field [MYTABLE::name_first; LeftWords(MYTABLE::name_full; 1)] Set Field [MYTABLE::name_last; RightWords(MYTABLE::name_full; 1)]
This works great as long as all your first and last names are one word. You'll need something more complicated to handle two word last names.
Set Field [MYTABLE::name_last; RightWords(CUSTOMERS::name_first; WordCount(CUSTOMERS::name_first) - 1)]
The basic premise in this last example is to assume all first names will be one word, whereas last names could be any number of words. By counting the words and subtracting one word, you can grab everything but the first word from the right. If you think this is a cop out then you are setting up yourself to do a lot more work than you need. Any good developer looks at the data to be parsed before designing a solution and provides just enough complexity to complete the task. Anymore more depth to the formula would just be busy work and likely to fail by it's inherent complexity, as well as cost your client unnecessarily.
If your names get any more complicated, and sometimes they do, you'll need to look at more advanced techniques for parsing names that can be found in this article: Extracting Title, First Name, Middle Name, Last Name from a field in FileMaker Pro. Again, this isn't really the point. What you want to glean from this article is how FileMaker works at the foundation. Consider these simple tools like a hammer, screwdriver and saw, that when put into the hands of a master carpenter can build a house.
There are two ways to subtract data from a field. The easiest approach is to use the Substitute function. Let's say we want to remove the prefix from the full name field:
Set Field [MYTABLE::name_full; Substitute(MYTABLE::name_full; ["Mr"; ""]; ["Mrs"; ""]; ["Ms"; ""]; ["Dr"; ""]; ["Rev"; ""])]
This works great if you know every single prefix that could occur. Substitute is also case sensitive so everything has to be spelled in proper case in the example above. But the biggest concern is the Substitute function finding the search criteria somewhere else in the full name field. Here are some examples:
Mr John Drover Mrs. Tom Revere Dr Jacob Mo'Revs
Here are the results after running through the formula above:
John over Tom ere Jacob Mo's
Okay, I stretched a little bit on the last example but you get the idea. The Substitute function doesn't care where it finds the search text, making it a scary function to deploy. If you know your data and you're careful, you shouldn't have problems. Also, notice the result has a space at the beginning since we didn't search for the prefix and a space. Adding spaces to the beginning (or end) can solve that problem and also avoid inadvertently locating the incorrect strings.
With all the gotchas that come with the Substitute function, you should always consider the second approach:
Set Field [MYTABLE::name_full; RightWords(MYTABLE::name_full; WordCount(MYTABLE::name_full) - 1)]
As long as you know that every name is preceded by a prefix then this formula will work perfectly. Otherwise, add a Case statement:
Set Field [MYTABLE::name_full; Case(PatternCount("Mr Mrs Ms Dr Rev"; LeftWords(MYTABLE::name_full; 1)); RightWords(MYTABLE::name_full; WordCount(MYTABLE::name_full) - 1);MYTABLE::name_full)]
Push & Pull These are probably my two favorite techniques. While Pull is great, I absolutely love Push. It's solved so many problems for me over the years, I always keep this approach in my back pocket. However, it seems developers have forgotten about this useful technique over the years and when I mention it they say, "huh?!?" So, let's start with push since pull is just the opposite.
Push allows you to push data from one table to another using just Set Field. The first step is to create a relationship (which you may already have in your solution). Here's the example relationship and options.
While the relationship doesn't matter for the Push technique, the options can make a difference. In this example, we have the option to "Allow creation of records in this table via this relationship" to allow Set Field to create records. The "Delete related records in this table when a record is deleted in the other table" has no bearing on the Push technique but is a common choice so related records aren't orphaned.
Let's trying pushing the first name contents from MYTABLE across the relationship using this script:
Set Field [RELATED::name_first; MYTABLE::name_first]
If you run this script when there are no records related to the current parent record, a new related record will be created for the data to reside. Here's what happens, in the order it happens:
Set Field creates a new related record to house the data being pushed.
Set Field places the primary key into the foreign key to link the parent to the new child record.
Last but not least, the calculation result is placed into the target field.
If you run the following script next, the last name will be placed on the same related record:
Set Field [RELATED::name_last; MYTABLE::name_last]
However, if you run these scripts again, they will simply overwrite the first and last name on the same related record. To really see this behavior, change the first and last name data in the parent table and run the scripts again. This happens because Set Field can only create one related record and only if no related records exist. And, if the option to create related records is not checked, Set Field will only write to existing records. If no related record is present then the Set Field will do nothing.
TIP: Even if your portal is sorted, Set Field will modify the first record in creation order (which may not be the first record at the top of the portal). The only way to change the related record Set Field targets is to sort the relationship in Manage Database.
The Pull technique isn't quite as useful but comes in handy every once in a while. All you have to do is reverse the Set Field target and calculation. For example, if you want to pull the first and last name from the related table into the parent table, you would write the script this way:
Set Field [MYTABLE::name_first; RELATED::name_first] Set Field [MYTABLE::name_last; RELATED::name_last]
If you click on a portal row before running the pull script or place the button in the portal, the data will pull into the active portal row. Otherwise, the first portal row will be selected.
Let's get back to the more useful Push technique and compare it other methods for creating related records so we can understand how useful it can be. The most common alternative method for scripting the creation of a related record is to place the primary key in a variable, visit a layout based on the related table, create a record and set the primary key into the foreign key along with any other fields you want populated. This is a great technique that's straightforward to program and doesn't require a relationship. However, that's not usually an advantage since most times tables are related, but you never know.
The big disadvantage of the variable method is loss of context. When you return back to the original layout, the portal row, selected field and tab are reset. You can either take great pains to reestablish context or try opening a new window instead. All the navigation in the new window is separate from the first window so context isn't lost. Unfortunately, new window creation causes screen flashing even if it's pushed off the screen to negative coordinates. Here's where the Push technique shines. You never lose context or see flashing cause you never navigate anywhere.
For a more complicated implementation of the Push technique, look at the article titled Form Letters in Browse. There's a lot going on in this article so the Push example doesn't come till halfway through the article when discussing how to save a letter to another table. I have no examples of the Pull technique cause it isn't nearly as useful in real life development but I'm sure you'll find a use for it from time to time.
WHat Next? Learn these basic Set Field methodologies, commit them to memory and you'll never be sorry. You might never use the specific examples shown in this article so you'll have to trust me that they are invaluable tools in the FileMaker development world. I'd love to hear how readers have implemented any of the these techniques in a more complicated development scenario so don't be shy and comment below.