Finds using globals in scripts
Hello again. I think this month we’re going to flatten out the learning curve just a bit, for both our sakes. There isn’t enough time, space (or knowledge) for me to be writing a manual here, and I’d rather write about tips to actually do things. Not to worry, I’ve got lots to keep us busy.
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)
It’s useful in Preview -> then Printing scripts, as Preview Mode always takes you to the first record of the found set. These lines in a print script allow you to print just the one record you’re looking at in Browse mode, or have found with a Find script.
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”]
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
Comment [“Looks better and saves time.”]
Go to Layout [“Record ID”]
Copy All Records
Comment [“This will copy all the IDs in the found set of records.”]
Go to Layout [“Record ID g”]
Set Field [“Record ID g,” “”””]
Comment [“This initializes (clears) the global. Good idea.”]
Paste [Select, No style,”Record ID g”]
Comment [“This 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).
You can remove the Comment steps. There is a limit to how many record IDs you can paste into a text field, but it’s pretty high, 64,000K, including paragraph characters.
The idea of using a global to temporarily hold the value of a regular field is one you’ll use many, many times. In fact, the steps:
Set Field [“Fieldx g,””””]
Comment [“Makes sure it’s empty”]
Set Field [“Fieldx g,” “Fieldx”]
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.
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).
Another amazing use is to put such a global field above a portal whose relationship is based on it, e.g., 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. More on Portals later. Please note that due to the non-indexable problem you can begin a relationship with a global, but not end it with one (example: global field on the left side, OK, but global field on the right side, No Way).
Well, as usual I went off on a tangent (albeit an interesting one), ignoring the increasing incline of the learning curve. Hopefully I’ll be more fun next month.
*BTW, you may find my naming techniques strange; but one thing I can tell you for certain, you will need to come up with a 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 sorted 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.
Oh yeah, I use the “\” sometimes to separate names. You can’t use a “/” in field names, but the “\” is fine. If I want words to be visually apart, such as “JobID g,” then I use an option-space before the “g.” It really sets it off, but still allows you to double-click it as one word to put into calculations. [Note: It’s just a regular space here because DOCMaker, which My Mac is written in, can’t deal with option-spaces.]
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 changed file names. This means that if you change them it will break the relationships (which must then be manually reestablished).
**Globals have different characteristics on networked databases. It should still work on each local machine’s file, at least until you quit. I know little about networking.
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.