Newbies lowndes Posted January 30, 2010 Newbies Posted January 30, 2010 (edited) 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 Posted January 30, 2010 Posted January 30, 2010 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.
Newbies lowndes Posted January 31, 2010 Author Newbies Posted January 31, 2010 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 Posted January 31, 2010 Posted January 31, 2010 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.
Newbies lowndes Posted January 31, 2010 Author Newbies Posted January 31, 2010 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 Posted January 31, 2010 Posted January 31, 2010 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 Posted January 31, 2010 Posted January 31, 2010 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 Posted January 31, 2010 Posted January 31, 2010 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.
Recommended Posts
This topic is 5676 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now