5 Replies Latest reply on Sep 10, 2009 6:44 AM by Invectus

    calculation field to display custom message

    Invectus

      Title

      calculation field to display custom message

      Post

      Hi there,

       

      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.

       

      For example:

      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 

       

        • 1. Re: calculation field to display custom message
          comment_1
             Do you have a table of Cities, where each city has a local/regional/long attribute?
          • 2. Re: calculation field to display custom message
            Invectus
              

            No,

             

            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? 

            • 3. Re: calculation field to display custom message
              comment_1
                 Storing data in calculation fields is not good practice. I suggest you put the cities in another table with (at least) two fields:

              • City
              • Region

              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" ) )

               

              Note:
              I have used OriginCity and DestinationCity as the names for the two ccurences of the Cities table.






              • 4. Re: calculation field to display custom message
                directis5451
                  

                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.

                 

                John

                • 5. Re: calculation field to display custom message
                  Invectus
                    

                  Hey Comment,

                   

                  You seem at great ease with any problem. This too worked,

                   

                  Thanks a bunch buddy!