One of the main reasons we use FileMaker is because it saves us time and makes entering data accurately and consistently a breeze. The key to making FileMaker our data entry go-to tool of choice is mastery of the Auto-Enter tab in the Options for Field dialog box.
When we create a new field or edit an existing one in the Manage Database window, we have the opportunity to modify the field's setting via the Options for Field dialog box. There are four tabs in the Options for Field dialog, and we're going to focus on the first one, Auto-Enter. This tab lays out all the options we have for allowing FileMaker to enter or even modify our data along with us as we enter it. However, the option we're interested in for this post is the calculated value. The calculated value option is very flexible and allows us to do many things that the other options allow and more.
Let's walk through some of the things we can do with the calculated value option:
- Create a unique value that we might use as a key in a relationship.
- Perform math (any function or group of functions) on existing data, much like a Stored Calculation can.
- Retrieve a related piece of data, just like the "look-up value" option.
Create
Let's look at the first one, creating keys to link tables together to create a relationship. It can't be stressed enough how important the concept of relationships is to developing applications with FileMaker, so this first example is a big deal. When many of us first started using FileMaker to solve our organizational problems, we created relationships and used the field options serial number feature. The problem with this feature is that it can fail when someone edits the file's schema via the Manage Database window. When other users attempt to create records simultaneously and need those serial numbers, they will not be able to because the file can't increment the numbers. Using the Auto-Enter option with a get(UUID) function helps us avoid this limitation and guarantees our users a unique key to use with their relationships. Another thing we must do when using this technique is to deselect the "Do not replace existing value of field." We do this so that FileMaker will properly set a new unique key when we duplicate one of these records.
When importing records into a table using an Auto-Enter calculation as a key, we must turn off the Auto-Enter option for the entire import or at least the key field. Otherwise, we might not get the tables to link correctly, especially if the purpose of the import is to restore lost or old data.
Perform
We're not going to go into all the mathematical aspects when using the Auto-Enter calculated value option because it's too deep and wide a topic. Suffice it to say we can do a lot. In fact, the first topic in this discussion is really just an offshoot of this one. So, we don't have to go into the weeds to see the various directions and power of this concept. We can do anything in a standard calculation field using the calculated value, like creating a full name from our first and last name fields.
Contact::FirstName & " " & Contact::LastName
Remember, since this is a standard text or number field, we can always type something into it later if we aren't ultimately satisfied with the results. Another option is to create a "Status" field that uses some logic to determine, for example, whether a book is overdue.
If ( Books::Due Date < get(CurrentDate) ; "Overdue" ; "Checked Out" )
If the book is overdue but lost or not expected back, we can select "Lost" from a pick list, something a pure calculation field wouldn't let us do.
Retrieve
The last thing to go over might seem a little arcane to those new to the FileMaker platform, but we can use the calculated value option to replace our old-timey look-uped value fields and get more control in the bargain. Using FileMaker's calculation engine, we can set up the calculated value option to retrieve our related data and then manipulate it. We can do more with this function than we can with the look-uped value. Depending on how we toggle the "Do not replace existing value of field" option, we can even replace our original value.
We hope this post inspires you to explore the field option's calculated value. It is like having a little helper by our sides to fill out our data.
Check out more of our tips and tricks for custom app developers, such as how to move data from repeating fields and how to manage FileMaker error codes.
This article is also published on FileMakerProGurus.com.