If [RightWords ( table::ExperimentLeader ; 1 ) = "Wood"]
Set Field [table::ExperimentLeader; "Ms. Joanna Wood"]
May do the job.
A script trigger won't "trigger" during an import. It only kicks in when a user interacts with the triggered layout object.
I'd set up a script either as part of the import or to run immediately after that looped through each newly imported record and updated each field where this is an issue.
That is working better than the script that I made. However, if someone were to enter "JWood" the script doesn't catch that.
Edit: There must be a way to generally search the contents of the field. Is there any way to use the funtionality of this symbol: "*"? Similar to the way I could use it in searches.
How do you set up a script as part of an import?
It'll be difficult to catch every permutation. Right ( table::ExperimentLeader, 4 ) = "wood" will catch the JWood example you give.
Instead of making a script as part of your import, make the import part of your script:
Import Records // specify the options you need for your import here
Go To Record [first]
# add code Clean up your fields here
Go to Record [ next, exit after last ]
One quick way to check for similar entries that may have slipped through:
Define a value list that specifies table::ExperimentLeader as it's source of values.
Use field setup... to format table::ExperimentLeader with a drop down menu that uses this value list.
Enter Browse mode and drop down the menu. You'll see one copy of every value that is stored in that field.
If you see something that has leaked past your script (such as "JWood"), enter find mode and select that value. Now use Replace Field Contents or Find/Replace to correct them.
Thanks for your help. I know for a fact that many of the imports will result in a value in the "ExperimentLeader" field that looks like "345098JWood_RS409". Which is why I was hoping that there was some way to just generically identify a specific string of text. Since it looks like that isn't something that can be done in FileMaker, it will be impossible for me to catch almost all of the possible permutations.
I just wish it was possible to use * in a script in the same way you would for searches.
You can also use PatternCount. PatternCount ( table::experimentLeader ; "wood" ) > 0 will identify any record where the given text contains "wood" anywhere within the field.
You can use * in a scripted find.
Enter Find Mode 
Set field [ table::ExperimentLeader ; "*Wood*" ]
Perform find 
will find all records with Wood, somewhere in the ExperimentLeader field.
If [PatternCount ( table::experimentLeader ; "wood" ) > 0]
SetField ( table::experimentLeader ; "Ms. Joanna Wood" )
Thanks for taking the time to help me out!
Works perfectly as long as you don't get entries such as
It's pretty much impossible to trap for all possible data entry errors. If you can control the user's input options with a drop down menu or some such feature in whatever application is generating the file you are importing, life will be much simpler.
True, I can see how that would cause some problems. However, there is only a list of 10 or so names that will ever be entered into this field. None of these names are similar and it is highly unlikely that new names will be necessary in the future. I think, for my application, this works perfectly.
With respect to data entry errors, I will use a drop down list for human input. The imports are computer generated files that automatically insert characters on either side of the name (for some unknown reason). I needed to figure this out so I don't have to manually go through and fix hundreds of records just so they are readable.