VISUAL QUICKPRO GUIDE FileMaker Pro 7 Advanced FOR WINDOWS AND MACINTOSH [Electronic resources] نسخه متنی

Replacing Inconsistent Formatting

A straight Replace puts new data where old information once was. Sometimes, however, you only want to edit a portion of a text field and leave the rest of the field contents intact. That's when you need a calculation using the Substitute function.

For example, suppose you have a State field containing a variety of formats, like NY, N.J., Ca, and R.i. Searching for each state's variations would take so long you might as well input them by hand. The Substitute function replaces a letter, word, or any other piece of data with another, leaving the rest of the field intact.

To replace inconsistent formatting

1 .

Go to a layout containing the field whose data you want to clean up. Click in the field.

Our example uses the State field.

2 .Choose Records > Replace Field Contents.

3 .

When the Replace Field Contents dialog box appears, click the "Replace with calculated result" radio button (Figure 14.25).

This option replaces the data in the field with a calculation instead of text or numbers.

4 .When the Specify Calculation dialog box appears, scroll down in the function list on the right to Upper, and double-click it (Figure 14.26).

The Upper function turns all text within the field you specify into uppercase characters.

5 .With the Text parameter still highlighted in the Upper formula, double-click the Substitute function in the function list (Figure 14.27).

The Substitute function searches for characters in a field, then replaces them with whatever other characters you specify.

6 .

Highlight the word "text" in the formula box and double-click the State field in the field list (Figure 14.28).

This tells the Substitute function to make its changes in the State field.

7 .In the formula box, highlight "searchString" and click the quotes button. Type a period between the quotes (Figure 14.29).

8 .

In the formula box, highlight "replaceString" and click the quotes button. Don't type anything between the quotes (Figure 14.30). Click OK.

This tells the Substitute function to delete any periods it finds in the State field.

9 .In the Replace Field Contents dialog box, click Replace.

The information in the State field won't change, but it will all be in capital letters without periods.

Apply this technique to delete any characters. For instance, if you import addresses that have unwanted commas, leave out the Upper function and use a comma in the search string.

Tips

If you want to do a Find and Replace for a single record, you can do a Find to isolate that record. Then you can use a Replace with the Substitute function to search for all instances of a piece of text in a field and replace them with other text (or with nothing). This is especially handy for fields with long text blocks.

To make FileMaker's Find/Replace function more efficient, use it with View as List to have more records visible onscreen, and make it easier to verify that you've made all necessary changes.

FileMaker's Find/Replace also works in Layout mode, so you can use it to make changes in the text in layouts.