calculation field to display custom message
I have 2 fields : City_pickup and City_dest in which I'm storing Canadian city names (Toronto region)
In my text field named "message", I would like to display a custom message to the user to tell them whether their trip will be local, regional or long distance.
If City_pickup AND City_dest are any of these cities (A, B, C, D); the "message" field should display "Local trip"
If City_pickup OR City_dest are any of these cities: (A, B, C, D, E, F, G); the "message" field should display "Regional trip"
If City_pickup OR City_dest are any other unspecified cities; the "message" field should display "Long distance trip"
I can use a case() statement for the local and regional cities, but since I have more than 15 cities to deal with, I thought there might be a more efficient approach.
I hope I'm making some sense so far.
I would appreciate any insight.
Thanks a lot