Do you have a table of Cities, where each city has a local/regional/long attribute?
There are actually 5 cities considered local, about 10 regional and all other cities fall into the long distance category.
Here's how I currently have it set up:
Case( City_pickup = "Toronto" ; "Local Move" ; City_pickup = "North York" ; "Local Move" ; City_dest = "Toronto" ; "Local Move" ;City_dest = "North York" ; "Local Move" ; "Not a local move. Extra charges apply.")
It worked fine when I only had local and long distance to worry about. Now I have to deal with regional cities as well. Any thought?
Storing data in calculation fields is not good practice. I suggest you put the cities in another table with (at least) two fields:
and populate the Region field with "local", "regional" and "long distance".
Define two relationships beween the Trips table and Cities (you will need two occurrences of the Cities table on the relationship graph), matching City with City_pickup and City_dest, respectively.
Then you can define a calculation field like =
Let ( combo = List ( OriginCity::Region ; DestinationCity::Region ) ; Case ( not IsEmpty ( FilterValues ( "long distance" ; combo ) ) ; "long distance" ; not IsEmpty ( FilterValues ( "regional" ; combo ) ) ; "regional" ; ValueCount ( combo ) = 2 ; "local" ) )
I have used OriginCity and DestinationCity as the names for the two ccurences of the Cities table.
If you haven't already found a solution to this problem try this link http://www.briandunning.com/zipcodesfm/ I have used this zipcode software in a store finder solution and it worked very well. Possibly you could use this to calculate mileage and then used the mileage to deliver your conditional message.
You seem at great ease with any problem. This too worked,
Thanks a bunch buddy!