How do I update information in a whole bunch of records at once in FileMaker?

If you just want to easily leaf through a batch of records looking at each record before making a change, you will be happy to know that FileMaker lets you do this from the keyboard, without reaching for the mouse each time to move to the next record.

Hold down the Control key on your keyboard and then tap the Up Arrow key, you will move backwards one record. The opposite is also true, Control Down Arrow will take you ahead one record.

Image Source: popart.uk

Occasionally you need to replace the contents of the same field in a group of records with new data. Here is how it is done using FileMaker’s powerful ‘Replace’ function. Please use this very carefully. Be sure you know what you are doing before using it. As Spiderman says, ‘With Great Power comes Great Responsibility’.

In this example we are searching for all records with Issue = Winter and Year = 2010.

Once you have the Found Set of Records isolated that you want to make the change to, edit the first record to the new value you desire. In this case, we are changing 2010 to 2009.

While you are still clicked into the field, from the Records menu, select “Replace Field Contents…” Note the shortcut for this change is Command (on a Mac or Control on Windows) and the Equals sign. This is handy if you are going to use this command often.

The three options available are:

1. The first option is a simple change to the value you entered in the previous step. In this example, clicking ‘Replace’ will blast the number ‘2009’ into the selected field in all Found Records.
2. Option two is to set the field value in a batch of records to incremental serial numbers (ex. 1, 2, 3, 4 etc). If the field you are updating is a serial number field, choosing the option “Update serial numbers in Entry Options?” will increment the serial number assigned to this field to a number one more than the last one you used.
3. Finally, the most powerful of all is a calculated replace, selecting the contents of another field, or creating some kind of complex text or mathematical calculation. You will be delving into FileMaker’s calculations abilities here. A simple example would be to combine two fields together into a single field. See the FileMaker manual on how to use more complex calculations.

Depending on the number or records you are operating on, you may see this progress bar displayed for some time. When the update is completed, your records should be all set to the new value.

Now all the records in the found set are updated to ‘2009’.

The Replace Field Contents is a very powerful command. Remember there is no undo available to this command. To take a more cautious approach, consider the next technique, employing the Find/Replace Command.

A more measured approach to updating a batch of records, is to employ the Find/Replace command. This allows you to move through a batch of records and update a specific value. It is a more subtle approach than the ‘Replace’ command above. It is useful when you are looking for a specific word to change. For example, if I was working on a Notes field and wanted to change every instance of the word ‘Bob’ with ‘Doug’, I would use Find/Replace.

Another benefit of Find/Replace over Replace is that you can see what is going on as you are doing it. The Replace command you want to definitely know what you are doing before running the script. With Find and Replace, you can see the effects of each change a single record at a time.

This dialog box is very similar to what you are probably already familiar with from most word processing applications.

The one big difference from a word processor’s approach to Find/Replace is the options at the bottom of the dialog box on where you are searching. You can limit the search to ‘All records’, or just the ‘Current record’. It is also possible to limit the search to a specific field on a record by clicking on ‘Current field’.

The Find/Replace action only works on the Current Found Set, so if you want to affect every single record, be sure to do a ‘Show All’ command from the Records menu first. If you want to do this action on a select set of records, run your Search Criteria first and limit this action to the Current Found Set.

If you use these buttons in this order, you will be able to see the effects of what the changes you are asking FileMaker to conduct on your records.

1. Find Next will move you through the records revealing what you are searching for (the ‘Find what;’ item) It doesn’t make any changes. Remember to go back to the First Record when you are ready to make the change.
2. If you start with just the ‘Replace’ button, you will see the change in the record you are on. (You may need to reposition the Find/Replace dialog box so you can see the effect the changes on a specific field. Click and hold down your mouse button on the top bar and drag the dialog box off to the left or right to see the layout below.)
3. Once you are confident that you are doing the right thing, you can use the ‘Replace & Find’ button, which makes the change and immediately moves to the next record that has the ‘Find what’ information.
4. When you are totally confident that you want to make this change throughout your Found Set, go for the ‘Replace All’ button. This is very similar to the Replace command discussed above.

Related Articles:
Highlighting Rows in FileMaker lists or portals
‘Type Ahead’ Style Searching through Portals in FileMaker Pro
Shortening URLs using FileMaker