How can I find all inactive/invalid choice list values (values that show as blue) for a single field?

[ndt13]

We have a custom field on the incident table that was originally a string field with no choice list, but now a choice list exists.   Is there a way to query this field to return all of the items with invalid values for this field?   I would like to create a module under the Incident application menu for itil users to clean up their old incidents with these invalid values.

Thanks

[Uncle Rob]

So let me see if I understand the end-game.   You have a choice field with a ton of non-validated string data in it.   You want users to see their lists and go back to repair that data, right?

So what you need to do is generate a list that does not contain the valid choices.  

Thus, your module or report should have one criteria for this use-case:

<choice list field> <is not one of> <highlight valid choices>

The only thing left will be the invalid choices.

[ndt13]

Correct.   However, I tried this method of <choice list field> <is not one of> <highlight valid choices>, but since there are so many valid choices that are being filtered out, the incident form is unable to open when a user attempts to open an incident that needs fixing.

[ndt13]

When we try to open an incident after using the <choice list field> <is not one of> <highlight valid choices> filter, the form never loads and the screen remains blank.

[Uncle Rob]

If you've got over 700 valid choices then you should consider using a new table with a reference field instead of a choice list.

[Brad Tilton]

I completely agree with Robert here. That's really too many choices for a choice list and should be a lookup table.