Marks, If’s, and Flags
Example file: Phone2 (Part of Phone.fp3)
Value Lists Using Related Files
Before getting started I wanted to fill in an omission from last month’s column. I left out an obvious point about using related fields for value lists.
I’d talked about creating a calculation field in the local file to mirror a field in a related file that you wanted to use as part of a Value List, as the second field, using the “Also display values from” checkbox. You’d use this when you have an ID field and you want to show names to identify the ID numbers.
Another way to do this, without having to create the field, is to just use the ID field in the other file for the Value List, choosing the file with “Specify File,” choosing the ID field, and also showing the Name field.
The difference between these methods is that the first way will show you only ID#s which you’ve entered in your local file. This is good if you’re using it in Finds, etc. The second method will show all the ID#s in the other file. This is better if you’re choosing IDs for new records, as you’ll often want an ID# which doesn’t yet exist in the local file.
Marks, If’s, and Flags
This month I’m going to write about a few little fields. They normally have only a “1” or an ID number in them, but they give FileMaker much of its power and flexibility.
Marks and Flags are fields, usually type number, that generally have either a 1 or nothing in them. They are very similar, but I’ll treat them separately because they are used differently. I don’t think there are rigid definitions, but this is how I think of them and use them.
A Mark field is often set during Field Definitions, as the result of a calculation or lookup, and seldom changes. A Flag can also be set via a calculation, but is usually more dynamic, changed back and forth from “off” to “on” as conditions change. It can also be set via a script.
They can both be indexed or non-indexable. I’ll deal with the indexed first. If they are indexed calculations or operations using them will be many times faster. You can Find them quickly, even in large files, as it’s pretty easy for FileMaker to search for either 1 or nothing. They can also be used in further indexed calculations, such as Counts. Only indexed fields can be the target of relationships, and can be used to create other fields that are the target of relationships.
A Mark can be the result of either a calculation or a lookup. The first is very flexible. Using the simple calculation; If (something, 1, “”) you get your Mark.
The calculation can be practically anything, based on the contents of other fields, so there’s often a way to mark records according to your needs.
Mark First Entry (again)
Another way to get a Mark is to use a lookup. In this way you can get a Mark auto-entered into a record when it is first created, when perhaps the conditions are only true at that moment.
An example of this which I use all the time is to lookup a Mark for the first entry, and only the first entry, of each unique ID#. For example, if I have a file that is storing daily hourly entries for work–for all different jobs–I need a way to filter those entries for only the first entry of each job, so I can easily see which jobs are in there. It would have to be fast and reliable, as only a relational match would be. A Find would be too slow and dependent on the found set. The way I do that is to lookup a Mark, 1, for the first entry, and nothing for subsequent entries. I can then use it in a relationship (later).
I’ve already written about how to do this, but it’s important, so I’ll cover it again quickly. First you’d need a Self-Relationship on the ID field, Job ID in this case.
You’d also need a strange global number field, Null.g. It never has anything in it. The lookup (Options, Auto-Enter, Lookup) for the JobMark field is based on the self-relationship. It is set to lookup the Null.g field if there is a match. If there isn’t a match, it is set to enter data, 1. Be sure to uncheck, “Don’t copy contents if empty,” as that’s exactly what we want it do.
The way this works is this: there isn’t a self-relationship when the first record for that ID# is created. FileMaker must exit the record before establishing a self-relationship, whereas the auto-enter lookup occurs immediately upon creation. But every record after that is. So a 1 is entered the first time, but for all subsequent entries of that ID the empty Null field is looked up.
It works on imports too, as long as the auto-enter checkbox is left on. It will be lost though if you either delete that first record, or perform a Relookup based on the ID field. It works fine though in files whose purpose is archiving, where individual records are never deleted. The ID# must be protected from editing and Relookups, but these are not often used. You can provide proper data import scripts so they aren’t needed, or run a Replace to fix them afterwards.
Marks and Relationships
Once you have a 1 in your Mark field, there are a number of things you can do with it. One of the easiest is to make a relationship from a Constant field (calculation, =1) to your Mark field.
This relationship will give you instant access to those records:
GoToRelatedRecords [Show, “Constant-MarkField Relationship”]
You can show those records in a portal, using that same Constant-Mark relationship. This is especially useful for files like my example where you have marked the first of several different IDs. The portal will show one (the first) of each unique ID#’s records. Put the name field in the portal and you’ve got the basics for a navigational system. Since there should be a Constant field in all of your files, you can relate back to a Mark field in this file to set up the same portal in any of the files, for a more universal tool, to choose which job, or whatever the main choice is.
I use a simple checkbox mark, “Active,” in one of my main files (auto-entering a 1 on record creation). Creating constant relationships back to it allows me to display only active jobs in a portal in any of the other files. When I’m done with the job I click the Active checkbox, which I also display in the portals. This turns it off in the main file, so the job no longer appears.
So far we’ve been able to relate to the first of each ID group within a file, but the next step takes you one level further.
If-ID Fields
I call these “If-ID” fields. They are defined by an If (or Case) calculation using either a Mark field (any Mark field, not just the first entry) or just the calculation itself. The concept is pretty simple. When the conditions are met, the ID# exists, if not, it doesn’t.
If (Mark = 1, ID, “”)
To do it without the Mark field:
If (Calculation, ID, “”)
So there will only be the ID in the records which have the Mark in the one case, and when the calculation is true in the second case.
You can use this record rather than the ID field itself as the target field in certain relationships. A relationship to this field will match the regular ID field in the file (or any file with the same ID field) only once for each ID, on the record that has the Mark.
If you are basing it on a FirstMark, you can use this relationship in a lookup to bring in data from another file only once for each ID. This can save a lot of disk space, especially when dealing with long text name fields or summarized totals, data that would be the same in every record for that ID. There’s no need to waste space having it in every record. It’s kind of a special case, probably most suited for archive-type files, but useful if you need it.
Because the data is in the first record of any ID’s set of records, it will be the value found by using a self-relationship on the ID (which goes to the first record in order of creation). So you can reliably get or display it using the self-relationship (more on this next month).
By using Marks and calculations using any number of conditions and fields, you can construct an If-ID field for practically anything. It is independent of the found set, so relationships, portals, and calculations (unstored) based on it will also be independent, as well as usable across file boundaries (which found sets are not).
Flag Fields
Flag fields are like Mark fields, but are generally used to “toggle” between a value (usually 1) and no value. The simplest way to set a flag is to a create a checkbox to click on the layout. Create the checkbox by making a checkbox value list, with only one value, 1. Format the field on the layout as a number (via Field Format menu); click “Boolean,” enter 1 for yes values, clear the no value. A click on the checkbox will either turn it on or off.
They can also be set during scripts.
Set Field [“Flag”, “1”]
This sets the Flag in the current record at that moment. It can be used any time during a script, to either temporarily or permanently flag that record.
You can have different Flags for different reasons, using different calculations in the Set Field. It’s a great way to mark records that have taken part in an operation, such as an export, so that they can be ignored the next time, either by being left out of the found set during a Find, or checking for the Flag with a looping script and omitting each during the operation’s script.
They can be used to check records during a Replace script step on the entire found set, by including a check for the Flag in the calculation, as in:
Replace [If (Flag <> 1, something, “”)]
You can set the Flag in the found set by using a Replace on the Flag itself:
Replace [“Flag”, “1”] or conversely,
Replace [“Flag”, “”]
One use for this would be to temporarily mark a found set, so that you could come back to it later after it was lost.
Another way to use a Flag is to make it a global field. It would be unstored, but would be the same for all records. One use would be a simple way to simulate opening preferences. You can make it a checkbox on the first layout so that the user can make choices, on or off; but other value lists with more choices are possible.
I use it on the introductory layout to allow you to click the checkbox saying, “Bypass this introduction,” after reading once or twice. An Opening script is written, which checks to see if the Flag is on, then goes to another layout if it is. In Preferences (Edit Menu), Document, Introduction is set as the layout to normally open to. But the Opening script is chosen to run on the document’s opening (just below in the dialog box), which can then override and skip the Introduction layout.
Another more serious use would be to set a global flag as 1, and then check it every time to see if the file had been cloned. Cloning removes all data including globals. If you have several global container fields holding buttons, etc., this can be a disaster. It can be mitigated somewhat by checking for a global upon opening, then going to a layout with “spare” buttons. Paste a version of the buttons as simple layout objects right next to their global container fields, with instructions how to paste them in. Other users would need access to Layout mode to do this (which could become problematic).
Relationship Flags
You can also use relationships to define fields which function like Flags. One of the simplest and most useful is:
Count (Self-relationship::ID field)
This will tell you how many records there are of that ID (it will equal one if there is only one). You can create a concatenated (put together) calculation field of more than one ID field, e.g., ID & ” ” & ID, to count how many there are of records that have both of the IDs in them. You can add a Mark field onto the end to find how many of those are marked. Even though these are non-indexed, FileMaker seems to be able to calculate them fairly quickly. And because they are unstored, they will give an immediate feedback. This is useful for such things as duplicate warnings, which must appear before the user leaves the layout. They do, however, need to either “tab” or “click out” of the field being used before it can recalculate.
Use these simple fields to create seemingly complex operations without adding very much space in your files. Everyone will be impressed.
Fenton Jones
Fenton Jones is a FileMaker database designer and consultant, based in San Diego, CA. FileMaker is a cross-platform rapid-development tool for affordable relational databases. If you have need of a FileMaker Pro expert, please be sure to visit his home page at http://www.fentonjones.com
Leave a Reply
You must be logged in to post a comment.