2 Replies Latest reply on Dec 22, 2016 10:03 PM by jfletch

    calc field to assign categories based on text field

    rubinjm

      I have a list that includes a text field with the name of various police departments, rescue squads and fire departments. I'm trying to create a calc field that looks at the name in the text field and assigns a category like police or EMS. Here is what I'm using but it is not working consistently. For example, it works correctly for "rescue" and "police" but isn't working for "fire." What am I doing wrong?

       

       

      ---

      Case ( PatternCount ( MEANS OF ARRIVAL ; "ambulance" ) ; "EMS" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "taxi" ) ; "SELF" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "blank" ) ; "UNKNOWN" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "flight" ) ; "FFL" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "police" ) ; "POLICE" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL  ; "rescue" ) ; "EMS" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "car" ) ; "SELF" ) ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "fire" ) ; "EMS" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "MEDEVAC" ) ; "EMS" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "vehicle" ) ; "SELF" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "bus" ) ; "SELF" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "EMS" ) ; "EMS" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "LIFESTAR" ) ; "EMS" ;

       

      Case ( PatternCount ( MEANS OF ARRIVAL ; "self" ) ; "SELF"

       

      )))))))))))))

        • 1. Re: calc field to assign categories based on text field
          BruceRobertson

          Not putting fire in the field, apparently.

          But note the extra ")" in the car line.

          But it should be:

           

          Case (

          PatternCount ( MEANS OF ARRIVAL ; "ambulance" ) ; "EMS" ;

          PatternCount ( MEANS OF ARRIVAL ; "taxi" ) ; "SELF" ;

          PatternCount ( MEANS OF ARRIVAL ; "blank" ) ; "UNKNOWN" ;

          PatternCount ( MEANS OF ARRIVAL ; "flight" ) ; "FFL" ;

          PatternCount ( MEANS OF ARRIVAL ; "police" ) ; "POLICE" ;

          PatternCount ( MEANS OF ARRIVAL  ; "rescue" ) ; "EMS" ;

          PatternCount ( MEANS OF ARRIVAL ; "car" ) ; "SELF"  ;

          PatternCount ( MEANS OF ARRIVAL ; "fire" ) ; "EMS" ;

          PatternCount ( MEANS OF ARRIVAL ; "MEDEVAC" ) ; "EMS" ;

          PatternCount ( MEANS OF ARRIVAL ; "vehicle" ) ; "SELF" ;

          PatternCount ( MEANS OF ARRIVAL ; "bus" ) ; "SELF" ;

          PatternCount ( MEANS OF ARRIVAL ; "EMS" ) ; "EMS" ;

          PatternCount ( MEANS OF ARRIVAL ; "LIFESTAR" ) ; "EMS" ;

          PatternCount ( MEANS OF ARRIVAL ; "self" ) ; "SELF"

          )

          • 2. Re: calc field to assign categories based on text field
            jfletch

            This is a perfect situation for a separate table that the user can modify as necessary. While knowing the finer points of a complex case statement is advantageous, this could really be a small utility table:

             

            ID

            agency

            category

             

            That way the user can specify what a particular agency's category should be. New agencies with weird names can be accommodated without calling the developer.

             

            Put a portal in a preferences layout and use the first field as the value list for the popup on the Arrival Method entry field.

             

            Keeping a calculation like that in your solution is a guarantee that you will be called to adjust it more often than you would like.