Filtering a Text Field with Data from a Value List

[Steelers1]

I am trying to setup a calculation that filters out the data in a Text Field from entries in a Value List (i.e. provides keywords from an associated list). The data in the Text Field is NOT position specific and the list of potential keyword values is hundreds of entries long and will be changing over time. So I generated a Value List based on entries for values entered in another table.

The "ValueListItems" function works well to provide a return delimited list of the entries in the value list but how do I turn this into a Filter command that requires the filterText to be a list of values each surrounded by quotations?

Filter ( textToFilter ; filterText )

I would be very grateful if someone out there had some suggestions on what direction I should try and take this.

[comment]

Have a look at the FilterValues() function.

[Steelers1]

I am afraid that that too is not working well for me. So I thought that perhaps using the ValueListItems function with a Let () command might help but still am not getting anywhere fast.

Attempt #1= No results:

Let ( var1 = ValueListItems ( "test" ; "test" ) ; FilterValues ( test::Text Field ; var1) )

Attempt #2= Error results:

Let ( var1 = " & ValueListItems ( "test" ; "test" ) & " ; FilterValues ( test::Text Field ; var1) )

Error message from Attempt #2:

"The specified table can not be found."

Let ( var1 = " & ValueListItems ( "test" ; "test" ) & " ; FilterValues ( test::Text Field ; var1) )

[comment]

I am not sure what exactly are you trying to accomplish. This formula:

FilterValues (

ValueListItems ( Get (FileName) ; "YourValuelistName" ) ;

YourTable::YourTextField

)

will return a return-separated list of values that are common to both the value list and the text field - provided the text field too contains a return-separated list.

[Steelers1]

The text field that I am trying to filter is not a return delimited list unfortunately. It is a generic text description that I am just trying to filter to certain keywords, which may be any random order but are valuable to highlight.

[comment]

Filtering and highlighting are two different things. Are all your values single words?

[Steelers1]

"Highlighting" may have been a poor choice of words on my part. Yes I am just trying to setup a calculation to show which keywords may be in the text description of a particular record. And yes I think that all of the keywords are limited to one word values.

[comment]

Then you should start by generating a return-separated list of all the words in the description. This would be best done by a custom function - though depending on your actual data you might get by removing all punctuation and substituting spaces with returns.

The result can then be filtered against the value list items to produce the list you want.

Another option is to construct a custom function that works in the opposite direction - i.e. checks each item of value list in turn to see if it appears in description. This is more suitable when the items can contain multiple words.

Edited February 24, 2010 by Guest

[Steelers1]

That's it! It worked. Hooray!!! Thank you for your help!