Help with field conversion script

[svarland]

I'm new to FMP scripting and working on cleaning up a rather large database. I need to create a script that searches for specific field value and then translates the data into values for several newly created fields. All are known values and text based fields. Basically something like this:

Find all records with Field1 = 'Green'
From those matches insert 'Blue' into Field2 and 'Yellow' into Field3

Is this easy to do with a FMP script?
Could you give me an idea as to the syntax or an example?

Thanks!
Scott

 

[ImStevenB]

I don't know if this a script you are going to run all the time or just this one time to expand your old information - but here it goes ----

What you want to do first is perform the Find you want in the script -
-- Enter Find Mode, type Green in field1 and press Enter to perform the search

Now go to Scriptmaker and create a new script then clear out the default entries.

Your first line can be Goto Layout if you have certain layout you want to perform the finds from.

The next line should be Enter Find Mode.
-- Use the Restore option if the script is only going to be used for the one search. The Restore option will save and use the Find criteria you just entered.
-- Use the Pause option if you want to enter different criteria everytime you run the script. Pause will wait for you to press Enter after entering the information.

The next line would be Perform Find.
-- If you used the Restore option in the previous line you don't need it here.

After these two lines you will have your found set. Now you can manipulate these records. Using your example, we want to make sure we are at the first record of the found set so use this command

Goto Record/Request/Page [First]
(I'll list all the commands at the end so you can see what the script looks like when it's all done)

Now set the fields the way you want them

Set Field [field2,""Blue""]
Set Field [field3,""Yellow""]
(Remember to put "'s around the words)

Now to change the remaining records you use the Loop command to goto the next record and make the same changes. In the loop will be a command that will take you out of the loop after changing the last record. So the loop steps look like this

Loop
Goto Record/Request/Page [Next,]
Set Field [field2,""Blue""]
Set Field [field3,""Yellow""]
End Loop

When you put the script together it will look like this

Enter Find Mode [Restore]
Perform Find
Goto Record/Request/Page [First]
Set Field [field2,""Blue""]
Set Field [field3,""Yellow""]
Loop
Goto Record/Request/Page [Next,]
Set Field [field2,""Blue""]
Set Field [field3,""Yellow""]
End Loop

I know there is a lot here - if you have any more questions email me or post them.

Hope This Helps.
Im Steven B

http://www.myarcadia.net

 

[Schlogg]

Rather than the loop, I'd use a 'Replace'.
In fact, if the number of possible values in Field1 is small, you could do the whole thing with a (rather complex) Replace.
Find all
Replace Field2, Case(Field1= "Green", "Blue",
Field1 = "Purple", "Pink",
etc.)
Replace Field3, Case(Field1 = "Green", "Yellow",
etc.

HTH
Paul J.
Cheers,
Paul J.

 

[svarland]

Thanks - This is very helpful.

Scott