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

Calculation to substitute entry with item from customizable value list

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.

• 1. Re: Calculation to substitute entry with item from customizable value list

have a field accepting a number that lookups a table of zip codes and returns the county/city.

• 2. Re: Calculation to substitute entry with item from customizable value list

ValueListItems ( "" ; "name of your value list here" )

Will list all the values in your value list. A looping script can take one value at a time and compare it to the imported locality text.

Set Variable  [\$Values ; ValueListItems ( "" ; "name of your value list here" ) ]

Loop

Set Variable [\$K ; \$K + 1 ]

Exit Loop If [\$K > ValueCount ( \$Values ) ]

If [ patternCount ( yourTable::Location ; GetValue ( \$Values ; \$K ) > 0 ]

Set Field [ yourTable::modifiedLocality ; GetValue ( \$Values ; \$K ) ]

Exit Loop [True]

End If

End Loop

• 3. Re: Calculation to substitute entry with item from customizable value list

That is it! Thank you so much! One modification though... I believe the ">0" is not needed in the PatternCount line:

ValueListItems ( "" ; "name of your value list here" )

Will list all the values in your value list. A looping script can take one value at a time and compare it to the imported locality text.

Set Variable  [\$Values ; ValueListItems ( "" ; "name of your value list here" ) ]

Loop

Set Variable [\$K ; \$K + 1 ]

Exit Loop If [\$K > ValueCount ( \$Values ) ]

If [ patternCount ( yourTable::Location ; GetValue ( \$Values ; \$K )]

Set Field [ yourTable::modifiedLocality ; GetValue ( \$Values ; \$K ) ]

Exit Loop [True]

End If

End Loop

Thanks again!!

• 4. Re: Calculation to substitute entry with item from customizable value list

>0 is not needed, but it does make the expression a tiny bit more obvious in how it works--and thus can avoid confusion when posting to a forum like this where not all readers may be fully aware of how Boolean expressions evaluate.

• 5. Re: Calculation to substitute entry with item from customizable value list

you are hardcoding.

It's a no-no.