Find and replace using FM Pro... also any good online tutorials? 2

[tyhand]

Hi all,

I'm new to Filemaker Pro and am currently using FM Pro 5.
Is there a Find and Replace feature on FM Pro?

I have a database with several thousand records and I would
like to change some data on all records at one time.
For example, I would like to change "Apt." to "apartment".
I there a way to do this? I know MS Office products like Excel and Word do this. Can FM Pro 5 do the same, if not
similar?

Also, are there any good online tutorials, resources OR
authoritative books on FM Pro5 that anyone can recommend?
Any help is greatly appreciated. Thanks. Peace!

 

[ImStevenB]

Hi Tyhand,

You can do a search and replace - kind of.

There will be a few more steps than the way Microsft has it setup - you will need to do some scripting. I'll use your example -

You've decided that you want to replace all Apt. abbreviations that appear in the Address1 field, with the word Apartment.

1. Go to a layout that will allow you to type in the Address1 field and Enter Find Mode.

2. In the Address1 field type Apt. and press Enter. This will return all the records that have Apt. in the Address1 field.

3. Now you want to replace the Apt. with Apartment. To do this create a script and call it Replace. The way this needs to work is you'll make the replace in the first record of the found set, then change to the next record, make the replacement, change to the next record, etc...

Let's break the script down;

A.) The first thing we need to do is make the change in the first found record - Filemaker uses a command called Substitute to make changes similar to replace. The format of the command is

Substitute (text, search string, replace string)

(NOTE: Don't confuse this with the Replace command in Filemaker. The Replace command starts at a specific numbered position in a field.)

In a script, you can get a Substitute to work using Insert Calculated Result.

So in Scriptmaker, create your new script, clear out the lines that are there and from the commands on the left find Insert Calculated Result and double-click it.

Now go to the bottom right and click on Specify. This will take you a calculation window. The commands on the top right of this window is where you will find the Substitute command. Find it in the list and double-click on it.

Now fill in the parameters - where is says text you want to put the Address1 field. For the search string type "Apt." and for the replace string type "Apartment". Make sure you include the quotes (&quot around the search string and the replace string, this tells Filemaker that it's text information and not a field name. When you are done it should look like this:

Substitute(Address1,"Apt.","Apartment&quot

When you are done click OK.

Now you have the first line of the script. From here it gets easier.

B.) Now we need to goto the next record so we can make the substitution there. From the commands on the left side of the Scriptmaker window find the Go to Record/Request/Page command and double click on it.

Now go to the bottom right and you will see Specify and the word First with a down arrow. Click on the down arrow and select Next.

C.) We don't want to have to try to figure out how many records will be returned for each find so instead of copying the first two lines over and over we'll use a Loop.

A Loop has a start (Loop) and stop (End Loop) point, the commands listed in between will be repeated over and over until a certain condition is met (for example, you get to the last record).

In the command list on the left, find the Loop command and double click it. This will put Loop and End Loop in the window on the right.

Here's where it gets easier - click on the first line of the script then click on Duplicate button. The first two lines of the script will now look the same. You can drag one of those lines down so that it goes between the Loop and End Loop commands.

Now click on the Go to Record/Request/Page command and drag it down so that it goes right after the Loop command. You'll notice that when you changed First to Next earlier, another line appeared with a checkbox that says Exit after Last. You will want to check that box or else this will loop until you stop it. By checking that box, the program will check to see if it currently on the last record of the found set. If it is, it stops.

That's it!

When you run this script It will change the first record then loop throught the rest until it gets to the last record.

I know there's a bit of information here - but you should be OK.

The two books that I use most are Scriptology and Filemaker Pro 5 Bible. Scriptology deals mainly with writing scripts but has lots of open examples for you use and pull apart to see how they work.

The Filemaker Bible is full of information without going over the heads of beginners or being too basic for the advanced users.

I hope this all helps. If you need more information - post it - I'm sure someone will be able to help.

(If you found this post helpful - let everyone know by marking it as a helpful post.) Im Steven B

http://www.myarcadia.net

 

[aboustayyef]

simply upgrade to the latest version of filemaker, it has a built in find and replace feature

 

[karelvanek]

Well there is another (simpler) solution: just find the records which you want to do some replacing. Click the field in which you want to perform "find and replace" (e. g. Address 1 field). As you are inside the field, choose Edit - Replace menu item and click the Specify button. Now you can write the proper calculation:

substitute(address 1, "Apt.", "Apartment&quot

The result is that all occurrences of "Apt." are substituted by "Apartment". No scriptmaking, no looping, the fastest solution. You can even nest multiple Substitute functions to make advanced "find and replace" with conditions (If). You can use Replace script step with calculations in scripts, of course.

Karel
karel.vanek@gavlas.cz