Hello, I don't know if anybody can help me but I'm trying to specify a field to replace the value based upon a variety of parameters specifically for States.

 

For example I attempted to do an if statement to turn the word of the state into the abbreviation

"

 

 

if(
    Upper(Self="ALABAMA");"AL";
    if(Upper(Self="ALASKA");"AK";
        if(Upper(Self="AMERICAN SAMOA");"AS";
            if(Upper(Self="ARIZONA");"AZ";
 

 

"

and so on.

 

I also tried:

 

 

Case(
    Upper(Self="ALABAMA");
    "AL";
    
    Upper(Self="ALASKA");
    "AK";
    
    Upper(Self="AMERICAN SAMOA");
    "AS";

 

 

In both situations I end up with a Too Many Parameters error. Perhaps I am going about this problem all of the wrong way. Any suggestions?

FileMaker isn't understanding the Upper ( Self = "ALABAMA" ) part.

 

Basically what you are telling FileMaker to do is:

• Make the result of Self = "ALABAMA" upper case.
• Result of Self = "ALABAMA" is either TRUE or FALSE
• Plus the syntax isn't what FM is expecting.

Try this.

Case (
    Upper ( Self ) = "ALABAMA" ;
    "AL";
    
    Upper ( Self ) = "ALASKA" ;
    "AK";
    
    Upper ( Self ) = "AMERICAN SAMOA" ;
    "AS";

     ...Rest of Calc...

)
 

Edited February 9, 201313 yr by Josh Ormond

Josh, that's what I originally did but after completing the entire calculation it said I had too many parameters. Here's what it wouldn't let me use:

 

 

Case(
	Upper(Self="ALABAMA");
	"AL";
	
	Upper(Self="ALASKA");
	"AK";
	
	Upper(Self="AMERICAN SAMOA");
	"AS";
	
	Upper(Self="ARIZONA");
	"AZ";
	
	Upper(Self="ARKANSAS");
	"AR";
	
	Upper(Self="CALIFORNIA");
	"CA";
	
	Upper(Self="COLORADO");
	"CO";
	
	Upper(Self="CONNECTICUT");
	"CT";
	
	Upper(Self="DELAWARE");
	"DE";
	
	Upper(Self="DISTRICT OF COLUMBIA");
	"DC";

	Upper(Self="FEDERAL STATES OF MICRONESIA");
	"FM";
	
	Upper(Self="FLORIDA");
	"FL";
	
	Upper(Self="GEORGIA");
	"GA";

	Upper(Self="GUAM");
	"GU";
	
	Upper(Self="HAWAII");
	"HI";

	Upper(Self="IDAHO");
	"ID";
	
	Upper(Self="ILLINOIS");
	"IL";
	
	Upper(Self="INDIANA");
	"IN";
	
	Upper(Self="IOWA");
	"IA";
	
	Upper(Self="KANSAS");
	"KS";
	
	Upper(Self="KENTUCKY");
	"KY";
	
	Upper(Self="LOUISIANA");
	"LA";
	
	Upper(Self="MAINE");
	"ME";
	
	Upper(Self="MARSHALL ISLANDS");
	"MH";
	
	Upper(Self="MARYLAND");
	"MD";
	
	Upper(Self="MASSACHUSETTS");
	"MA";
	
	Upper(Self="MICHIGAN");
	"MI";
	
	Upper(Self="MINNESOTA");
	"MN";
	
	Upper(Self="MISSISSIPPI");
	"MS";
	
	Upper(Self="MISSOURI");
	"MO";
	
	Upper(Self="MONTANA");
	"MT";
	
	Upper(Self="NEBRASKA");
	"NE";
	
	Upper(Self="NEVADA");
	"NV";
	
	Upper(Self="NEW HAMPSHIRE");
	"NH";
	
	Upper(Self="JERSEY");
	"NJ";
	
	Upper(Self="NEW MEXICO");
	"NM";
	
	Upper(Self="NEW YORK");
	"NY";
	
	Upper(Self="NORTH CAROLINA");
	"NC";
	
	Upper(Self="NORTH DAKOTA");
	"ND";
	
	Upper(Self="NORTHERN MARIANA IS.");
	"MP";
	
	Upper(Self="OHIO");
	"OH";
	
	Upper(Self="OKLAHOMA");
	"OK";
	
	Upper(Self="OREGON");
	"OR";
	
	Upper(Self="PALAU");
	"PW";
	
	Upper(Self="PENNSYLVANIA");
	"PA";
	
	Upper(Self="PUERTO RICO");
	"PR";
	
	Upper(Self="RHODE ISLAND");
	"RI";
	
	Upper(Self="SOUTH CAROLINA");
	"SC";
	
	Upper(Self="SOUTH DAKOTA");
	"SD";
	
	Upper(Self="TENNESSEE");
	"TN";
	
	Upper(Self="TEXAS");
	"TX";
	
	Upper(Self="UTAH");
	"UT";
	
	Upper(Self="VERMONT");
	"VT";
	
	Upper(Self="VIRGINIA");
	"VA";
	
	Upper(Self="VIRGIN ISLANDS");
	"VI";
	
	Upper(Self="WASHINGTON");
	"WA";
	
	Upper(Self="WEST VIRGINIA");
	"WV";
	
	Upper(Self="WISCONSIN");
	"WI";
	
	Upper(Self="WYOMING");
	"WY";	
)

 

 

There are two Custom Functions at Brian Dunning's site;

 

http://www.briandunning.com/cf/939

 

and

 

http://www.briandunning.com/cf/939

there may be more, these appeared doing a search for Convert States

Lee, interesting,

 

I would have to modify the function a bit to suit my needs but I'm getting the picture that a custom function is the way to go rather than inputing everything into the calculation directly.

I think you have too many ;, try removing the 

 

Upper(Self="WISCONSIN");
    "WI";
    
    Upper(Self="WYOMING");
    "WY";    
)

the last one.

The parenthesis is in the wrong place. Upper(self)= whatever.

Hi Christopher,

Another option would be to use a related table which has three benefits over a calculation:

1. Additional information can be included (thus used) with a zip table, such as County, Governor, Tax rates, Sales Rep, UPS Zone, etc.

2. Simple addition of a record if a new state is added instead of the business requiring a Developer to open Manage Database.

3. Ability to enter zip only and look up City and State.

Zip code databases for US are free/inexpensive and attach in seconds. :-)

BTW, I am not sure why Upper() even is used because kentucky = KENTUCKY = Kentucky anyway ... And you explicitely cap the two-character State regardless. 'Equal' is not case sensitive.

Lee, this definitely solved the error. Good call, I didn't know that the last entry didn't need a semicolon. Thanks so much!

I think you have too many ;, try removing the 

 

Upper(Self="WISCONSIN");
    "WI";
    
    Upper(Self="WYOMING");
    "WY";    
)

the last one.

 

 

LeRetta, I think this is a great idea, I was told that using the exSQL command might be the best way to accomplish this.

 

Hi Christopher,

Another option would be to use a related table which has three benefits over a calculation:

1. Additional information can be included (thus used) with a zip table, such as County, Governor, Tax rates, Sales Rep, UPS Zone, etc.

2. Simple addition of a record if a new state is added instead of the business requiring a Developer to open Manage Database.

3. Ability to enter zip only and look up City and State.

Zip code databases for US are free/inexpensive and attach in seconds. :-)

BTW, I am not sure why Upper() even is used because kentucky = KENTUCKY = Kentucky anyway ... And you explicitely cap the two-character State regardless. 'Equal' is not case sensitive.

 

My logic in using that was to attempt to transform incoming data to upper case to match with an absolute, I didn't realize that "Equal" wasn't case sensitive, I guess I was thinking about php. But maybe I got that mixed up too, because I think two equal signs are used for absolute match "==" versus "=".

It happens all the time, LOL and let's not even get started on reserved-word differences or character sets...

ExeuteSQL() is great and an option here as well but relationships still rule the FileMaker realm (relationships are generally faster, conditional value lists are possible, portal data can be filtered on demand either relationally or portally. etc) but only you can say which is best for your situation. If working with addresses I would use relationship since we would be extending that functionality to include lookups and auto-filling etc. .

My logic in using that was to attempt to transform incoming data to upper case to match with an absolute, I didn't realize that "Equal" wasn't case sensitive, I guess I was thinking about php. But maybe I got that mixed up too, because I think two equal signs are used for absolute match "==" versus "=".

 

What you DO want to watch for is leading and trailing spaces. "Wisconsin" ≠ " Wisconsin" ≠ "Wisconsin " ≠ " Wisconsin ".

 

Brian Dunning's cf site has Trim4() by Ray Cologon. It's great for cleaning white space from data.