Working with Records

You can get only so far with your scripts by working with field values. Eventually you need to deal with more than one record. Thankfully, FileMaker has script steps for creating, duplicating, and deleting records; navigating among existing records; and even managing the process of opening and editing a record, and saving (committing) or reverting the changes. You can also work directly with portal rows on the current layoutand the records they represent.

POWER USERS' CLINIC
Why Set Next Serial Value

The prevously used Set Next Serial Value script step may seem odd to you. After all, if you want to set the next serial value for a field, you can just do it yourself from the Define Database window. But this step can come in very handy in some situations.

Imagine you're in the auto parts business and you have 200 stores around the country. Each office has its own copy of your database, which gets updated periodically. The main office sends a new empty database to each store with all the latest enhancements, and the folks at each store have to import all the data from their old database into the new one (you'll learn about importing and exporting data in Chapter 17).

Now suppose this database includes an Orders table with an Order ID field. After the old orders have been imported, the database may have orders with IDs from one to 1000. But since no new records have been created yet, the Order ID field still has a "next value" of 1. The store's first thousand orders use IDs that are already used by other records.That's a big no-no.

The solution's obvious: You need to fix the "next value" on the Order ID field after the import's finished. To save the store manager the trouble, you can put a script in the database to fix this glitch for her. (In fact, you can make a script that does all the work of importing old data and fixing next serial values in every table.)

 

14.3.1. Creating, Duplicating, and Deleting Records

New Record/Request and Duplicate Record/Request have no options, and do exactly what you'd expect. The first script step creates a new record, just like the Records New Record menu command. The second duplicates the current record. In either case, the The Delete Record/Request script step deletes the current record. If you turn on its "Perform without dialog" option, the delete happens automatically with no warning. When this option is turned off, the user sees the same "Are you sure" message box he'd see when deleting a record manually.

---

Note: Each of these three script steps also works for find requests when a script runs in Find mode.

---

14.3.2. Navigating Among Records

There are two ways to change to a different record. You can move to it with the Go to Record/Request/Page script step, which works a lot like the Book icon in the status area. Or you can switch to a related record, or a set of related records, using the Go to Related Record script step.

14.3.2.1. Go to Record/Request/Page

FileMaker has one script that handles changing records, find requests, and pages. This might seem strange at first, but it makes sense because it's exactly how the Book icon in the status area works: If you're in Browse mode, the step goes to a different record. If you're in Find mode, it switches find requests instead. Finally, if you're in Preview mode, it flips through pages.

---

Note: You can't run scripts when in Layout mode, and a script can't go to Layout mode, so it doesn't apply here.

---

The Go to Record/Request/Page step has just one option. You get to pick which record, request, or page to go to from a simple list:

• First
• Last
• Previous
• Next
• By Calculation

When you lock your users out of the usual interface and control everything they do through the script (see the box on Section 14.6.5), the First, Last, Previous, and Next options let you provide your own customized replacement for the status area and Book icon. For example: Make four buttons and arrange them in a horizontal line (like those on a tape recorder or CD player). Give each button a Go to Record step and set them up in the follwoing order, so they mimic the tape recorder concept:

• Leftmost button Go to Record/Request/Page [First]
• Second button Go to Record/Request/Page [Previous]
• Third button Go to Record/Request/Page [Next]
• Rightmost button Go to Record/Request/Page [Last]

This visual concept is so common that your users are likely to know how the buttons work without any instructions. But you can add a tooltip (Section 19.4) to each button if you want to provide that extra boost.

Another common, but more advanced use of this step is to provide a way for a looping script to end. When you choose the Next or Previous option, a new checkbox appears in the Script Step Options area, called "Exit after last." When it's turned on FileMaker knows to exit the loop after it's finished with the last record (when you choose Go to Record [next]) or after the first record (when you choose Go to Record [previous]). (When to exit a loop is no small matter, as Section 13.4.2 explains.)

When you choose By Calculation, you get the chance to specify a calculation with a number result. FileMaker goes to exactly that record, request, or page. You could use this option if the record number's in a field, or if you want to skip ahead 10 records each time the script is run, for example.

14.3.2.2. Go to Related Record

You were first introduced to this power-step when learning about relationships (Section 8.1.3). It can go to a different record, found set, layout, window, and even file; all in one step. This step's job is simple: It takes you to a related record. But carrying out that job isn't so simple. When you click the Specify button, you get a wealth of choices, as shown in Figure 14-5. To go to a related record, you first need to tell FileMaker which related table occurrence you're interested in, by selecting it from the "Get related record from" pop-up menu. For example, if you're on the Customer layout and you want to see a related invoice, you should choose Invoices from the menu.

With this done, FileMaker can find the right record. But how should it show it to you? If you're visiting related records, chances are you can't view them directly on the current layout (since the layout is associated with the wrong table occurrence). So a Go to Related Record command almost always involves changing layouts. You pick the layout you want from the "Show record using layout" pop-up menu.

Figure 14-5. This window's the same one you saw when you attached a Go to Related Record script step to a button back on Section 6.7.2 But inside a script you wield a whole new level of power when you tie this command to other processes, like printing a report from a found set that changes based on whichever record a users on when running the script.

At first you might be surprised by the menu choices. It shows all the layouts associated with the table occurrence in the first pop-up menu, as you might expect. But it also shows layouts associated with any other occurrence of the same table. FileMaker uses the specified table occurrence to find the right related record, and the specified layout to show it to you. You can show an Invoice record from any layout that shows records from the Invoices table, no matter what occurrence it uses:

• If the table occurrence you picked in the first menu is an occurrence of a table from a different file, you can turn on "Use external table's layout" to see layouts in the file the table comes from. When you use this option, FileMaker switches to a window for the other file instead of showing records in the current window.
• If you want a new window (whether you're using an external table or not), turn on the "Show in new window" checkbox. When you do, you see the New Window Options dialog box, which is explained alongside the New Window script step on Section 14.6.

Finally, you get to decide how to deal with the found set when the script step finishes. See the box on Section 14.3.3.2 to help you decide when each option makes sense.

• If you don't turn on "Show only related records," FileMaker goes to the related table, but you see all the records in the related table, not just those that are children of the formerly active record. The first related record is active in your new found set. So, from the Customer layout, GTRR without "Show only related records" shows all your invoices, and the active record is the first one for the Customer that you're viewing when the GTRR script step runs.
• If you turn on "Show only related records" and "Match current record only," FileMaker returns a found set of only those records that match the active parent record. From the Customers Invoice, GTRR set as described above shows you a found set of just the Invoices for the Customer record that was active when the script step ran. The first related record would be the active one.
• Choosing "Show only related records" and "Match all records in current found set," is most useful when you have a found set selected before the GTRR script step runs. In this scenario, FileMaker shows a new found set in which all the records are related to at least one of the records in the old found set. So, in the Customers layout, you've found your two highest volume customers. Use GTRR, matching all records in the current found set to find all the invoices related to either of these two customers. The first record that's related to the Customer record that's active when the GTRR script step runs is the active record in the new found set.

14.3.3. Opening, Reverting, and Committing Records

When you use your database in Browse mode, FileMaker does a lot of things automatically. When you start typing in a field, it locks the record. When you exit the recordclick outside any field or press Enterit commits the record. When you use a script, though, you're not really clicking fields and pressing Enter. So how does FileMaker know when to lock a record and when to commit? You have to tell it, by including the appropriate script steps: Open Record/Request, Revert Record/Request, and Commit Record/Request.

14.3.3.1. Open Record/Request

The Open Record/Request step simply tells FileMaker you're about to start editing a record. If the record is already openor lockedit does nothing. That is, it doesn't automatically commit the record first. It just locks it if it isn't locked already.

FREQUENTLY ASKED QUESTION
To Show or Not To Show

How do I decide when to turn on "Show only related records" and when to leave it off?

The "Show only related records" option is useful when you use Go to Related Record as a navigational aid for your users, or when you really want all the related records, not just one. For example, if the user clicks a button on the Invoice portal, it makes good sense to show not just the invoice he clicked on, but also the other related invoices. The user can then just use the Book icon to flip through the rest of the records. Likewise, if your script is going to loop through all the related invoices to do its job, then you want to show just the right records before starting the loop.

The tradeoff is performance. When you turn this option off, FileMaker simply shows the record. It doesn't worry about the found set or sort order. With the option turned on, though, FileMaker has to find the correct records first, and then show the one you asked for. If your relationship is sorted, FileMaker even sorts the records for you. This sorting takes more time. If your script just needs to visit a specific related record, do something to it, then come right back, you can leave this option turned off to make your script run more quickly.

 

14.3.3.2. Commit Record/Request

Whether you've used the Open Record/Request step or just let FileMaker lock the record for you, you can explicitly commit the record with the Commit Record/Request step. It has two options:

• The "Skip data entry validation" option tells FileMaker to commit the record even if it violates field validation. This option works only when you turn on the "Only during data entry" radio button in the Validation options for the field. If you have set the validation to happen "Always," then the script can't get around it.
• When the "Perform without dialog" option is turned off, and you turn off "Save record changes automatically" in the Layouts Layout Setup windows General tab, FileMaker shows the message in Figure 14-6 when the step runs.

---

Tip: You know how to handle dialog boxes when they come up, but they often confuse and confound your users. Most database designers try to avoid requiring their users to interact with dialog boxes while scripts are running, especially when the user could make a choice that circumvents the purpose of their carefully crafted script.

---

14.3.3.3. Revert Record/Request

The Revert Record/Request step has only one option: "Perform without dialog." When this option is turned off, the user sees the message in Figure 14-7. Otherwise, FileMaker reverts the record directly when the script runs.

Figure 14-6. If the layout is set not to save record changes (Section 4.4.3.1), and you don't turn on the "Perform without dialog" checkbox you see this warning when the Commit Record/Request step runs. Click Save to commit the record. If you click Don't Save, FileMaker reverts the record instead. The Cancel button leaves the record open and locked.

Figure 14-7. Unless you turn on the "Perform without dialog" option, your users see this dialog box when the Revert Record/Request step runs. If a user clicks Cancel, FileMaker leaves the record open and locked, which is probably not your intention.

These script steps are relatively easy to understand, but when to use them is hard to figure out. Here are some things to keep in mind when you're trying to decide when you need to open or commit a record in a script:

• When you use a script step that inserts data into a field (and leaves the user in the field), then FileMaker locks the record when the step runs, but doesn't commit the change. You can then do more work with fields if you want. FileMaker commits the record later, when the user exits the record.
• If your script changes to a different layout, switches to Find or Preview mode, or closes the window, FileMaker automatically commits the record if needed.
• If you use a script step that modifies several recordsReplace Field Contents, for exampleFileMaker locks, edits, and commits each record in turn, as it goes.
• If you perform a series of Set Field steps in a script, and you are not in the record when the script runs, FileMaker locks the record and makes the field changes. When the script is done, you're not in the record (no field is active), but the record's still locked and uncommitted. In other words, you can use the Records Revert Record command to revert all the changes made by the script, which probably isnt what you want to happen. Add a Commit Records/Requests script step at the end of the script to prevent the loss of data your script enters.
• If your script changes some records and includes a step to revert them if something goes wrong, you should probably make sure to commit any changes your user was making before your script changes anything. That way, the script doesn't undo any of the user's work. Thus, put a Commit Records/Requests step at the beginning of your script.

And if you're still not sure if your script really needs an Open or Commit step, go ahead and open the record at the beginning of your script and commit it at the end. Sure it takes a nanosecond or two extra to run a couple of steps that might not strictly be needed. But what's a nanosecond on the grand scale of time when your data may be at risk?

FREQUENTLY ASKED QUESTION
Why Open a Record?

Why would I ever use the Open Record/Request step? Doesn't FileMaker automatically lock a record as soon as my script starts editing it?

For simple scripts, this step is almost always unnecessary. FileMaker does, indeed, just do the right thing. But as you'll learn in Chapter 18, you can set your FileMaker database up so that multiple people can use it at the same time, each on her own computer. When you set FileMaker up this way, lots of interesting things can start happening.

For example, a record can change while you're looking at it. Suppose a new area code is added in your area, and you write a script that looks at the phone number and decides, based on exchange code, whether or not to change the area code. The script might look like this:

	If [ "Exchange Code = 555 or Exchange Code
	= 377" ]
		Set Field [ Area Code, "602"]
	End If

 

You probably find this hard to believe, but technically, someone could change the Exchange Code field after the If step runs, but before the Set Field happens. (Remember that other people are editing records on other computers, so they're free to make changes while the script is running on your computer.) If this scenario happens, you end up assigning the customer an incorrect phone number.

To fix this, you need to lock the record before you start looking at it:

	Open Record/Request
	If [ "Exchange Code = 555 or Exchange Code
	= 377" ]
		Set Field [ Area Code, "602"]
		Commit Record/Request [No Dialog]
	Else
		Revert Record/Request [No Dialog]
	End If

 

Now, somebody else can't edit the Exchange Code field because the record is locked. In general, if your database has multiple users, you should open a record before you start looking at it in a script.

This script reverts the record when it didn't make any changes. FileMaker does this reverting for two reasons. First, committing a record means saving the data, and that's unnecessary here. Second, suppose a field has had validation turned on since this record was created. It's possible that this unmodified record has now-invalid data. If you try to commit this data back, you get a validation error. Reverting avoids this error since nothing's being saved. Moral of the story: Try to commit a record only when necessary.

 

14.3.4. Copying Records

FileMaker has two record-related script steps that do something you can't easily do manually in Browse mode: Copy an entire record to the clipboard. One version copies just the current record, while the other copies every record in the found set at once.

14.3.4.1. Copy Record/Request

The first, called Copy Record/Request, copies data from every field on the layout and puts it on the clipboard. FileMaker puts a tab character between each field value.

If any field has more than one line, FileMaker converts the new line character into a funny character called a Vertical Tab. Some programs, like Microsoft Word, convert these characters back into new lines when you paste in a copied record. Figure 14-8 shows the result of pasting a Customer record into Microsoft Word.

Figure 14-8. The window in the back shows what it looks like when you copy a customer record, then paste it into a Word document. You see the first and last name twice because technically it appears on the layout twice (once in the header, and again in the editable fields). Although this block of text needs some serious cleanup, it's often easier to copy/paste it all in one shot than to copy each field value individually. You can see a cleaned-up version in the front most window.

---

Note: Some programs don't recognize vertical tab characters, so when you paste into them, you see boxes, vertical lines, or nothing at all. Your multi-line field values are strung together in one long line.

---

14.3.4.2. Copy All Records/Requests

While Copy Record/Request copies the entire current record, its brotherCopy All Records/Requestscopies every record in the found set. Each individual record is added to the clipboard in the same format as the Copy Record/Request command produces, and FileMaker puts one record on each line.

Suppose, for example, you want to get all the Invoice IDs for a particular customer and put them in an email to a coworker. Or perhaps you're compiling a list of all the zip codes in Phoenix where you have customers. Using Copy All Records/Requests, you can do this job with ease.

The trick is to create a new layout that has only one field on it. For example, make a new layout with just the Invoice ID field, or just the Zip Code field. Write a script that switches to this layout and run the Copy All Records/Requests command to get a simple list of values on the clipboard.

14.3.5. Working With Portals

FileMaker has a script step specifically designed to go to a certain portal row. Why would you want to do that? First, when you use the Go to Field script step to target a field in a portal, it goes to the first portal row, which may not have anything to do with what the user needs at that point. If you want to put the user in the field on a different row, you need to go to that row first.

When you use data from a related field, or put data in a related field, and there are multiple related records, FileMaker grabs the value form the first record. If you want to tell FileMaker to work with a different related record instead, use a portal and go to the right row. See the box below for tips (and a warning) on working with portals.

DIAMOND IN THE ROUGH
Beware the Portal

It's technically possible to do all kinds of things through a portal using a scriptit's not always wise. For example, if you wanted to discount every line item on an invoice by 10 percent, you could write a looping script. It would start by visiting the first portal row and applying the invoice. It could then go to each additional row, applying the discount as it goes. In each pass through the loop, you'd be modifying the Line Items::Price fieldthe active portal row determines which line item is changed. Unfortunately, the active portal row is too transient to be relied upon. A script can do all kinds of things that make FileMaker forget which portal row you're on (change modes, change layouts, go to another field, commit or revert the record, and so forth).

If you accidentally do confuse FileMaker like this at some point inside your loop, you end up looping forever because you never reach the end of the portal.

It's much safer to find the appropriate records and work with them directly. You can get the same effect by using the Go to Related Records script step to find all the Line Item records, and then looping through the records directly with Go to Record/Request/Page, avoiding the perils of the portal.

 

14.3.5.1. Go to Portal Row

The Go to Portal Row script step works a lot like the Go to Record/Request/Page step. You can go to the First, Last, Previous, or Next portal row, or specify the row number with a calculation. It even has an "Exit after last" option to help when looping through portal rows.

This step includes an option you can use to make what's happening onscreen a little more obvious to your users"Select entire contents." When you turn this checkbox on, FileMaker highlights the whole portal row. Otherwise, it goes to the portal row without highlighting it onscreen. (After all, you don't always want your user to see what's going on.)

One setting you won't find with Go to Portal Row is which portal to use. The guidelines below are how FileMaker knows which portal you have in mind:

• If the current layout only has one portal, FileMaker uses it automatically.
• If the layout has more than one portal, it assumes you want the portal that's currently active.
• If you're writing a script and you want to be sure you go to a row in the right portal, just use Go to Field first, targeting a field that's displayed in the portal.

---

Note: In general, a portal is active whenever a person (or a script) puts the cursor in one of its fields. If you aren't sure what your user will be doing before your script runs, throw in a Go to Field script step, just to be safe.

---