Retroactively validate based on a value list

[abailey3]

I have a large database with a multitude of fields where a value list via drop down is associated. However, when we set out to develop this, we didn't know what many of the values in these lists would be comprised of, so we didn't have any strict validation in place. Users were allowed to edit the very basic lists we came up with and we didn't have validation on any of the fields. So now what we have is a ton of fields and records with data that doesn't match the associated value list, meaning there's lots of redundancy (some records will have "Black & Blue" in a field while others will have "Black + Blue" -- and the value list has just "Black & Blue"). We've obviously reached the point where our value lists are fairly well determined and we need to really start policing what people are able to enter.

 

So I'm curious if there's a way to retroactively find records that do not match exactly to a value in a given value list. I've tried some things using PatternCount and IsValid, but neither have given me the results I was hoping for (PatternCount, for instance, searches for words rather than exact matches, so "Black & Blue" and "Black + Blue" are both being validated).

 

I'm stumped at this point and, like I said, its a large database which means there's a lot of fields. If it was one or two fields, I could probably kill a day manually sifting through the records and making the changes manually. 

 

Thoughts?

[Lee Smith]

I'm not sure I'm fully understanding your situation, and your examples sounds like a generic description of what you have in the lists?

 

In your example, which one is the correct entry?  i.e. all of them should be Black/Blue or Black and Blue, or just Black with another conditional value list such as:

Grey

Jet

Ebony

etc?

 

If you have just the one field with these entries, then perform finds for each variation that is an error, and use the Replace from the menu to correct them. The Replace option is unforgiving however, and you can not UNDO it, so perform it on a copy of the file. However once they have been corrected, they will no longer appear in your menu.

 

HTH

 

Lee

[abailey3]

So in that example, "Black & Blue" would be the correct answer.

 

There are a different number of line items per value list. There's a couple with only 4-5 which I've done manually (using the Find/Replace), but there are other lists with as many as 75-80 options in the value list. 

[Lee Smith]

AFAIK, there isn't an easier way. 

 

With the number of errors you have described, I would certainly look into using some of the other functions, such as the Filter, Substitute, and the loop script step. Without see the actual data, it is hard to make suggestions other than how you have already approached the problem.

 

There are some custom functions at Brian Dunnings site. I did a search for Filter and it returned several CFs. I would also search for things like substitute and replace.

 

HTH

 

Lee