Convert STATE to ST

[Greg-SSG]

I created a value list of State names ( Kansas, Missouri, New York) and need to change the text field to the State abbrevation( KS, MO, NY,....) I've changed the value list for future entries but need to change all the records existing in the current db. I got it to change one State name but the others...

[Ender]

Click into the field you will be putting the abbreviations into and select Records->Replace.

In the Replace dialog, select Replace with calculated results. In the calculation field that pops up, insert a case statement like this (Forgive me if my abbreviations are off)

Case(State="Alabama","AL",

State="Arkansas","AK",

...

State="West Virginia","WV",

State) //optional. include the State field at the end in case there is no match

Be sure to backup your database before using a Replace as you can't undo. What I like to do is perform a replace into a new field until I know I have the data correct, then move the data back into the destination field. That way I don't screw up the original data if there is an error in my calc.

[Doug Leib]

This would work fine. A variation would be to find the records you wish change followed by this script.

Go to Record/Request/Page [First]

Freeze Window

Loop

Set Field ["ST", "(insert calculation described in Ender's response)"]

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

End Loop

As shown "ST" would be the field you wish to set with the abbreviation.

This method is extra work to set up but will run faster than the replace technique with a large number of records provided that you FREEZE the window. Otherwise it will be slower and more work to set up. You decide which to try... either will have the same outcome.

In general a looping script with the FREEZE WINDOW script step before the loop will be faster than a replace.

[MoonShadow]

Or you might like to just get your hands on a database to do it for you. Attached is a quick db I had to create to assign Territories. It simply contains state abreviation and state full name. The correct relationship could then be used to run a replace quite easily. Join StateName to StateName and you'd be in business. You may want to test it with IsValid to be sure you match exactly first.

I had to do this before I got my zipcode database to attach. If any names are incorrect, I plead insanity. I had to create it from memory 5 minutes before a meeting. Thank God I type fast ... but I couldn't remember them all and had employees throughout the office hollering them out for me.

Doug said ...

In general a looping script with the FREEZE WINDOW script step before the loop will be faster than a replace.

I do not believe that is correct. It has been my impression that the opposite is true. Replace Contents would indeed be quicker. The problem with Replace Contents is that it does not throw an error message so can't be trapped (for instance, if multi-user and the field is locked.) Otherwise, Replace Contents would win the speed contest.

States.zip

[stanley]

I've got to agree with MoonShadow on this one. A "case" statement has to be manually altered to add new parameters, whereas a related database can have endless possibilities - what happens when your solution gets used in the UK or Germany or India? If you've got a way for the end user to enter their own "state to ST" abbreviations (i.e., in a related database) then you're all set. If you've got it all in a "case" statement, then you've got to deal with it yourself...

-Stanley

[Ender]

It sounded like this was a one time conversion, thus the Replace. No need to add a lot of junk to the database structure if you're not going to need it again.

[MoonShadow]

It would be a lot quicker to create a relationship from StateName to StateName; use Replace Contents with: NewRelationship::StateAbrev than writing a calculation containing all the states, don't you think?

Whether this is a one-time thing or not, my suggestion would be easier and quicker and that is why I suggested it.

[Greg-SSG]

Moonshadow:

I like the idea of using a db to store the statename and the abbrevation. How or where would I find a sample script to use this approach.

Greg

FileMaker Version: 5

Platform: Windows XP

[Ender]

Sorry, I tend to think in terms of a large multi-user system, where taking the database down for field additions is a hassle. Any more than 50, and I probably would go with a related lookup or replace.

[MoonShadow]

Hi Mike. There would be no field additions. StateName already exists in state abreviation field. All that would be needed is joining to this outside db and using that for the replace after you isolate records that need to be changed.

Greg, there may be an easier way but here is one way ...

From your main db, create a relationship to this state db I attached above. Call this relationship TempState. Join from your State field (the one that is partially filled with abreviations and partially filled with the full state name) and join to my StateName field.

Create a calculation (number) with:

Length(state) > 2

This will produce a 1 on every record that has the state spelled out instead of just two characters (already an abreviation). Once you have found your set, switch to a form layout that contains your state field. Place your cursor in your state field and be sure you are on record 1 of this found set. Perform Replace Contents with calculation and enter:

TempState::State

A script won't be necessary. And Greg? Please back up first. This is a down-and-dirty way to replace your mismatched field contents quickly.

If you are asking how to use a related State db, that is another issue. I use a zipcode database which, when User enters the zip code, fills in the city and state for them. There are many variations on this theme and maybe others can provide their ideas also. At the very least ...

Use this State db to provide validation on your State(abreviation) field by creating a Value List called State (in your main db). Select 'Use Values from Field' and select 'All Values' and select the state db. Bottom left, select STATE field. Then go to your State(abreviation) field in Define Fields. Options > Validation and select 'must be member of Value List'. Select 'Strict Do not Allow override' and provide a message.

[Ender]

Oops, how'd I miss that one?

[Greg-SSG]

That was easy. Thanks MoonShadow. I'm learning more FMP everyday.