Here are some tricks with Finds, especially how to use relationships and globals to do "find-like" operations.
One simple scripting technique with Finds is to isolate the current record, in the case where many are showing. There are two ways to that. The first uses the Find and Omit steps in a simple script:
| Find All | <-- shows all records |
| Omit | <-- omits the record you're looking at, just like clicking the Omit box manually |
| Find Omitted | <-- finds only that record |
Related "Find" (Actually "Go To Related Record")
The second method, using relationships, is a bit more complex, but can be customized to do more than one record. It requires the creation of a global field, Record_ID_g (Global, type text)
You should also have a Record_ID field, serial number (text), auto-entered for each new record. If not, define one.
(You can use the Replace, by Serial Number command to serialize an existing database file; look in the earlier articles.)
Also, create a self-relationship (same file) with the Record_ID_global field on the left and the Record_ID field on the right.
Name it something like "Self_Record_ID_g."*
Now all you need are simple steps in any script to save that Record's serial number and return to it:
Set Field ["Record_ID_g," "Record_ID"]
Exit Record, Request
Comment ["You can go do other things here; the next step returns."]
Go To Related Record [Show, "Self_Record_ID_g"]
Copy All Records Trick
So far so good. But what if you want to be able to return to more than one record? An elaboration of this method will do this.
You need a new layout. Put only the Record_ID field on it. That's all.
Create another layout. Put the Record_ID_g field on it, in the body. It could have other fields.
Neither of these needs to show on the Layout Menu (though I usually leave all layouts visible while I'm developing, at least till I'm sure they work).
The script works by using an undocumented but real ability of FileMaker to treat multiple entries separated by paragraph returns as if they were separate entries. It vastly extends the power of relationships, as you'll see. The script is fairly simple.
| Freeze window | <----- Looks better and saves time |
| Go to Layout ["Record_ID"] | |
| Copy All Records | <----- This will copy all the ID's in the found set of records |
| Go to Layout ["Record_ID_g"] | |
| Set Field ["Record_ID_g," """"] | |
| Paste [Select, No style,"Record_ID_g"] | <----- "Select" removes any previous content |
| Exit Record/Request | <----- important, so FM can register the key |
The Paste step pastes ALL the found ID's into the global field, separated by paragraph returns.
At this point you can go off and do other things, many of which would normally lose that found set. There's no limit to what you can do, as long as you don't reset the Record_ID_g field. The same single step at the end of the script or attached to a button will bring back the former found set of records.
Go to Related Record [Show, "Self_Record_ID_g"]
You would also want a step somewhere to go to the right layout (otherwise you'd still be on the Record_ID_g layout).
There is a limit to how many Record_ID's you can paste into a text field, but it's pretty high, 64K characters.
Global as Variable
The idea of using a global to temporarily hold the value of a regular field is one you'll use many times. In fact, the steps:
| Set Field ["Fieldx g," "Fieldx"] |
| Exit Record/Request |
| Comment ["Do anything else here, including Enter Find Mode"] |
| Set Field ["Fieldx," Fieldx g"] |
Should be used in most cases instead of Copy/Paste. It's faster, it doesn't use the clipboard, and it's safer, as Copy/Paste requires that the fields be on the present layout. You can have several Set Fields filling different fields in one script operation, whereas the clipboard is limited to one thing at a time; ergo only one Paste from the clipboard after entering Find Mode.
Some exceptions are Date and Time fields, which are finicky using Set Field; the Paste Result or even Paste Literal must sometimes be used.
Global Choice Field
A global ID field mirroring an ID field is also an easy way to present choices from a few values. You can allow a choice in a global field in Browse mode without changing any data in the records, such as using the field itself would do.
Create a global field mimicking the original (i.e., if Fieldx ID, then Fieldx ID g). Then, in layout mode, format the global field to be radio buttons, using the "values from a field" choice. Specify the original Fieldx ID field; show also the field's associated name field so the choices are recognizable (the ID alone isn't).
Stretch out a box on the layout for as many ID's as you have (they can form columns). It's not real pretty, as the ID numbers show (unless you do something to hide them), but it's highly functional.
Choose one, then hit a Go to Related Record scripted button (Fieldx ID g::Fieldx ID), and you'll be instantly transported to those records. You could also use a pop-up menu if you have a lot of names. You can even use checkboxes (or the shift key w/radio buttons) to choose multiple values, using the previously mentioned ability (multiple choices being automatically separated by paragraph returns).
Above a Portal
Another amazing use is to put such a global field above a portal whose relationship is based on it, i.e. Job ID g::Job ID. This could be a self-relationship in the same file or in a different file with that same ID field. Then, as you click on different buttons all the records in the portal will change immediately to reflect the new ID choice (as soon as you hit the Enter key :-(
BTW, you can begin a relationship with a global, but not end it with one, due to the non-indexable problem, i.e., global field on the left side, OK, on the right side, No Way.
*A Field by any other Name
You will need to come up with a naming scheme that makes sense to you. It is important that you can recognize fields and scripts by their names.
I like to name similar fields with similar beginnings, so that they sort alphabetically together (scripts and relationships must be positioned manually, so it doesn't matter).
Scripts that make a call to (or are called from) another file are also labelled with arrows (-> or <-) and the abbreviated file name, so I know where they are coming from or going to. It also makes them easier to separate from in-file scripts.
If your database is going up on the Web, then you have to pretty careful what you use to separate words in field names. The underscore, "_", is recommended, as almost everything else will cause problems.
However, I sometimes use "\" to separate names in my own files, just to make things clear to me (it signifies a "divide" or an "if" calculation. You can't use a "/" in field names, but the "\" is fine. I use "|" between names to signify a "concatenated" calculation field, i.e., Field1 & Field2. I'd have to remove them for the web, as they denote directory divisions in other systems, like ":" does on the Mac.
One of the greatest strengths of FileMaker, as opposed to others, is that it allows you to change the names of fields, layouts, scripts and relationships, readjusting them for you throughout all the related files. One thing it does not do is readjust for changed file names. If you change them it will break the relationships (which must then be manually reestablished, unless you do it with the Developer Edition).
Globals behave somewhat differently in networked databases. The local values reside independently on each machine, at least until you quit. So, for found sets, like we did above, they would still work fine.
Send comments, questions, toys to Fenton Jones