Compare field values to a Value List

[eswanborg]

Here's my dilema - I have two tables - tableOne with fields to populate several value lists, and tableTwo for tracking details that use those value lists for various fields (a little confusing, I hope it makes sense).

So I go through records in tableTwo and populate fields using the value lists (from tableOne). What I want to be able to do, is identify any field entries that are not pulled directly from a value list.

Value list built from tableOne would be something like, 1, 2, 3, and 4. FieldA in tableTwo would use that value list. If I enter 5, in tableTwo::FieldA, I want that entry to be easily identified. I know I can create a container field with a calculation to fill a background color (or something like that), I'm just not sure how I can quickly identify a field entry that's not a part of the Value list.

Again, sorry if this is confusing, not sure how to better explain. Any help is greatly appreciated.

eric

[Fenton]

If you have a relationship from the table 2 to table 1, between the 2 fields, then entries in 2 not in 1 would not be related. Since this is more or less a "negative" test, then you'd need to create a calculation field to show that it's not related. Or possibly use auto-enter coloring of the field itself; but that would not recalculate if you added data to table 1.

[eswanborg]

Since this is more or less a "negative" test, then you'd need to create a calculation field to show that it's not related. Or possibly use auto-enter coloring of the field itself; but that would not recalculate if you added data to table 1.

Yes, that's what I need to figure out how to do. I'm not sure how to do the negative calculation. Any thoughts on how to do either that calculation or the auto-enter calc? I'm just not sure how to check if the contents of a field match the entries of a related field.

Thanks

eric

[comment]

Try something like:

Case ( not IsEmpty ( FieldA ) and IsEmpty ( tableOne::Value ) ; << something to highlight>> )

This should be an unstored calculation field.

Edited November 20, 2006 by Guest

[eswanborg]

Thanks for the suggestions. Got a suggestion from someone else that seems to work well, thought I'd pass it along - from Carlos:

If [FilterValues ( ValueListItems("Database";"ValueList");NewText ) = ""]

{Do something to show there was no match in the value list}

End If

In this example, "Database" is the name of the database that contains the reference value list.

"ValueList" is the name of the reference value list.

NewText is the name of the field with the text the user entered.

The way it works, ValueListItems gets all the "ValueList" items. If the text in NewText matches any item in "ValueList", then FilterValues returns that text; this indicates that NewText was an item already in "ValueList". If it doesn't find a match, FilterValues returns a blank (i.e. "").

Thanks again for the help.

eric