Assuming that Table A and Table B are defined in the same table:
Not IsEmpty ( FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotesHere" ) TableB::Field ) )
will be true if the value in TableB::FIeld is a member of the value list named in quotes in the ValueListItems function.
Thanks for your suggestion. It has helped open my eyes to some possibilities.
Attached is what I have derived from your suggestion, however it doesn't seem to be working. The value list consists of over 900 unique letter number combinations such as H23, A&R21,...and I am trying to ascertain whether "Property ID" field in the current record is a member of the list. Testing what I have so far always comes up yes even though I have inserted some records which should give me a no. I do not want to kick out the record upon entry with the "Validate" option against the Value List, I want to keep the record and just mark it whether or not it is valid.
Filter and FilterValues are two different functions. I recommended FIlterValues, you are using FIlter.
Given what you are wanting to accomplish, I would not use this method. I'd use a relationthip with the Valid Lot Numbers field as a match field to the table from which you are getting the values for you value list. Then Not IsEmpty ( RelatedTable::Field ) is all you need to determine that there is a matching value in the other table.
I am not having any luck using these suggestions.
There must be a simple expression (operator) to give a true or false to this statement:
"A" is a member of "Alphabet"
That is all I am trying to do. Is there a function for "is a member of" ?
I know I can make a table with a record for each letter of the alphabet and step through them one by one to see if "A" equals one of them. But this seems tedious and time consuming.
DId you see my posted comment that you were using Filter instead of FIlterValues? What results did you get once you used the correct function?
You shouldn't need to create any additional table in order to use a relationship to check for this. You already have that table or you wouldn't have a value list that lists these vaues from a table. Even if this is the same table as the one where you are defining this calculation, you can still set up what is called a "self join' relationship to check to see if the value in a field matches to existing values in your list.
Eureka! I've got it!
Thanks for your help. What was driving me nuts was I didn't realize there was a carriage return after the filter response and I had to include that in my "If" statement. I was comparing the field data to the filter data and while they looked like they were the same they were not because the filter data had a carriage return added to it.
Thanks again. I will remember this solution, to be sure.
Yep, that's why I used the Not IsEmpty () expression instead of checking to see if they're equal. Sorry that I missed that detail myself when I looked at your screen shot. I'm so used to using that combination of the IsEmpty and Filtervalues functions I failed to note that you hadn't set up your expression that way nor did I explain why I was using IsEmpty instead of the = operator.