"Repairing" Bad Database management

[peg]

HELP!!! I have inherited a database that was started and maintained by someone who has no database management ability. It is a database used to maintain standardized test scores. What I WANT is one field to show the Grade Equivalent Score and one field to show the Percentile Score. The other person put both pieces of information into the same field !! So, a sample records would have something like "4.5 gr 87 percentile". Is there a way to write a script to take this information and split it into the two new fields? Any thoughts and feedback would be greatly appreciated.

[Steve T.]

Hi, peg! I think I've seen similar posts in the forums before... I think it was address fields needing to be separated. Anyway, I think someone suggested using the LEFT/RIGHT WORDS function to separate them. Me... I'd probably export data to Word and use the REPLACE command to change "gr " to "gr ^t" (gr space to gr tab) and reimport. You can also use the REPLACE function to get rid of "gr" and "percentile", too, if you want numeric values.

Maybe someone can tell you how to do it entirely in FileMaker, but if you can't wait.. it should be easy enough in a text editor.

Good luck!

--ST

[Damocles]

Peg,

"IF" the data is all entered the same, this will work, just by using the number of words in the field.

Create two fields, one "Grade_Equiv" and one "Percentile". Click on the newly created "Grade_Equiv". Select menu Records>Replace Contents, and click "Replace with calculated result". When FM prompts for a calculation, use

LeftWords(yourfieldhere,2) for the Grade_Equiv

RightWords(yourfieldhere,2) for the Percentile

This should work well without a lingering script, unless your data is inconsistently entered. Good Luck!

Paul

[peg]

Thanks to both ST and Paul. I will try both options. Any additional feedback would be appreciated. This database is a true mess and all suggestions will more than likely help me keep my sanity!

[Damocles]

If you have any other "problems", or just don't know where to begin to sort out the mess, post a clone of the file with some sample data... I'll look at it.

Paul