5 Replies Latest reply on Jan 22, 2017 1:19 PM by siplus

    Calculation to substitute entry with item from customizable value list

    LAPaiva

      I would really appreciate some help. I work with schools that accept children from different localities. The school sends me import data in Excel format. The Locality field may say the child is from one of 12 localities such as “Richmond Public Schools”. I would like the field to say just “Richmond" like in my Locality value list: Charles City, Chesterfield, Goochland, Hanover, Henrico, Hopewell, King and Queen, New Kent, Petersburg, Powhatan, Prince George, Richmond. If the value list were known (like above), I would just run the following script:

       

      If [(PatternCount (Student:Locality; "King and Queen")]

      Set Variable [$Locality; Value: LeftWords (Student::Locality;3)]

      Set Field [Student:Locality; $Locality]

      Else If [PatternCount (Student::Locality.t ; "Charles City") or PatternCount ( Student::Locality.t ; "New Kent") or PatternCount ( Student::Locality.t ; "Prince George")]

      Set Variable [$Locality; Value: LeftWords (Student::Locality;2)]

      Set Field [Student:Locality; $Locality]

      Else If [WordCount (Student::Locality.t )>1]

      Set Variable [$Locality; Value: LeftWords (Student::Locality;1)]

      Set Field [Student:Locality; $Locality]

      End If

       

      Here’s the question: If I work with a new school that pulls kids from other localities so that my Locality value list is different, the above script won’t work. I’d like a script that basically customizes itself based on the Locality value list. Basically a calculation that will recognize that a field entry like “Richmond Public Schools” is similar to “Richmond” in the Locality value list and make the substitution…. your thoughts? Thanks for considering it.