solutions wanted noob Question

[lowndes]

Hello. I'm new to FM and Databases in general, but I won't be for long. I love what I've learned so far, and am studying as much as I can in my free time. I've skipped ahead in "the missing manual" to the "logical functions" section, because I want to analyze some data now, and I don't want to wait for me to finish reading another 300 pages before I figure this problem out on my own.

I've been trying CASE function, but haven't really figured it out yet. It sounds like that's the right function I need to use for my problem, but I'm not 100% sure. That's my background, now my request:

I have a table with a field named "Ship_City". That's the ship-to address for my customers.

I would like to automatically assign each city to a zone; such as Woodstock - zone 1, Burlington - 2, Bethel - 1, Rutland - Zone 4, etc. I have a couple dozen cities to assign to 4 zones.

I created a zone field and am having trouble

getting zone field to read the Ship_City field and return the proper zone number.

Can someone help me figure this out? I understand novices are a PIA, but I truly won't be a novice for long :.

Thanks,

Larry

Edited January 30, 2010 by Guest

[TheTominator]

You can use a Case() statement for this, but it will be tedious for a couple dozen cities.

Create a calculated field for your zone with a definition like the following.

Case(

Ship_City = "Woodstock"; 1;

Ship_City = "Burlington"; 2;

Ship_City = "Bethel"; 1;

Ship_City = "Rutland"; 4;

""

)

Instead of the empty quotes at the end you could type in a default zone number since it will be the value returned when no matches were found in the Case statement.

When you are ready to create more than one table in your solution and add relationships between tables, a better way

to handle this type of situation is to create a table containing

cities. One of the fields would be the Zone.

You would then create a match on the city name and have the zone automatically pulled from the info in the table of cities.

The method of using a table scales well as you add many more cities and zones. It also means that you can add cities and zones without needing to change any calculation formula. It could be a data entry task and not a database design task.

[lowndes]

Thanks for responding. Why would this only affect one record, when many fit the criteria? The first match was the only one that returned a value.

[TheTominator]

The Case() statement is supposed to return the first matching result only.

I need to have more information on your implementation to help figure out why it isn't working for you.

[lowndes]

Here's what the table looks like:

I just figured out that the statement is looking for an exact match. I expanded the field (in browse mode, and saw that there was extra information (zip codes) in most the Ship_City records. Only one field did not have a zip.

Can I use a wild card in the Case() statement, or say "match the first five characters"?

Thanks again for your expertise, patience and time. I will pay it forward someday.

[TheTominator]

Can I use a wild card in the Case() statement, or say "match the first five characters"?

Wild card? No.

Match first 5 characters, yes.

Case(

Left(Ship_City; 5) = "Woods"; 1;

Left(Ship_City; 5) = "Burli"; 2;

Left(Ship_City; 5) = "Bethe"; 1;

Left(Ship_City; 5) = "Rutla"; 4;

""

)

Or if you want to keep the full names in there for readability you can do

Case(

Left(Ship_City; 5) = Left("Woodstock"; 5); 1;

Left(Ship_City; 5) = Left("Burlington"; 5); 2;

Left(Ship_City; 5) = Left("Bethel"; 5); 1;

Left(Ship_City; 5) = Left("Rutland"; 5); 4;

""

)

You can reduce some of the redundancy and evaluate the Left(Ship_City; 5) expression once by wrapping the statement in Let() and assigning this value to a temporary variable. If you go that route, I would also code the number 5 as a temporary variable so that you can change it once at the beginning to a different number and not have to alter it on each line should you decide that 5 isn't appropriate for your needs.

[comment]

I created a zone field and am having trouble

getting zone field to read the Ship_City field and return the proper zone number.

I'd suggest a different approach: define a table of Cities (with fields for city and zone), and use a relationship to get the zone.

[LaRetta]

there was extra information (zip codes) in most the Ship_City records

And you should split the zip into its own field as well so that, when you follow the advice of using another table (matching city = city), you can pull in the Zone.