Finding misspelled word, same field, many records

[Maxwell Morlay]

The post office has corrected our address file. Using Excel, some acronyms in address lines came across in title case. Can I do a find and replace? Here's and example: PO Box 130 Sfa -- needs to be SFA.

Thanks Maxwell Morlay

[CobaltSky]

FMP v6 provides a find and replace feature out of the box.

If you're working in an earlier version, the best approach is probably to script your own, using a couple of global fields and a script which loops through the records applying the a Set Field step to each in turn, with the formula:

Substitute(YourTextField, gFindWhat, gReplaceWith)

If you have only one problem to deal with - ie the "Sfa" issue, you might prefer to dispense with the global fields, and hard code your script as:

Show all Records

Go to Record/Request/Page [First]

Loop

Set Field ["Address", "Substitute(Address, "Sfa", "SFA")"]

Go to Record/Request/Page [Exit after last, Next]

End Loop

---

Which will do the trick by way of a 'one hit wonder'

[Maxwell Morlay]

WOW! SUPER! Thanks so much!

[Jazz]

FYI next time, there is a quicker way, using the same idea, but no script.

First backup your file (if you make a mistake with either solution you might loose your entire field's contents)

In browse mode, select all records, then click in the address field.

Choose menu item Records->Replace... (cmd-= or ctrl-=)

Click 'Replace with calculated result...'

Enter the same formula as above, ie:

Substitute(Address, "Sfa", "SFA")

Click OK, then click Replace.

This does exactly the same thing as the above solution, but it is faster and doesn't require you to write a script.

(PS. Instructions generated for FMP5, but similar for other versions. Menu item moved from version 4->5).

Cheers,

John