Applying a value list retroactively

[sconolly]

Is there a calculation or script, which would find entries in a field which do not match a value list? In this case, the value list is being imposed after the file has been in use for several years. The "Category" field in the approximately 4,500 records contains all sorts of two-letter codes. I have created a value list and want to isolate records whose data does not match the list.

[LiveOak]

You can create a calculation field:

ErrorCheck (calculation, text) =

Case(

IsEmpty(Category), "",

Category = "AA", "",

Category = "BB", "",

Category = "CC", "",

"Error")

This assumes that valid entries are (no entry), AA, BB, and CC. Anything else will set the field to "Error". This could also be a number field set to a one for an error. A find would be performed for the error state ("Error" or 1, your choice) in the field ErrorCheck -bd

[sconolly]

Thanks for the tip. It's a nice solution.

We worked it out by putting all the value list items in a comma separated text field called "Validate category 1" and applying the following calculation:

If(PatternCount(Validate category 1,Category )>0 ,1,0 )

Performing a find on the resulting "0" gave us the records with unauthorized data.