Search through list to find a close match

[etsmarines]

If i have a value list (approximately 2000 values)

 

What is an efficient way to go record by record selecting a text field and see if i can make a match form the value list.

 

Example.... Go to Record 1 select field contents (ex. DOG)

go through my value list and see if I can find a value that has the word DOG in it. If i do find a match with the word DOG copy that value (even if there is more to the value than just DOG) and set another text field in record 1 with that value from the list.

 

Example Go to Record 1.... Select field and set variable. Variable is equal to DOG

Search through my value list and find a match All Dogs.

 

Go back to Record one and set the other empty field to All Dogs because the value All Dogs has the word DOG in it.

 

Thanks for the help!

 

Erik

[comment]

What if the field value is found in more than one value of the value list?

[etsmarines]

I would sort the value list by length the fields that i am trying to match against by length so that if a match is found it takes the first one. For example.

 

Value list

Microsoft Corporation

Microsoft

 

Field to Match

Microsoft Corporation

 

It would come across Microsoft Corporation would be used first and stop looking for a match and not get to Microsoft.

[comment]

Finding the first match is quite simple =

Let ( [
list = ValueListItems ( Get ( FileName ) ; "YourValueList" ) ; 
i = ValueCount ( Left ( list ; Position ( list ; YourTable::FieldToMatch ; 1 ; 1 ) ) )
] ;
GetValue ( list ; i )
)

Note: your last example does not match your original description:

go through my value list and see if I can find a value that has the word DOG in it.

The equivalent of this would be:

Value list:
Microsoft Corporation
Microsoft

Field to Match:
Microsoft

 

In the case of Field to Match containing "Microsoft Corporation" there would be only one match found in the list, not two.

 

Edited April 20, 2015 by comment

[etsmarines]

i don't get a match when using this function. it returns a blank value and i know that i have values in my value list that are contained in the fields I am trying to match against...

 

heres another example of what i am trying to do....

Field1= Microsoft .net framework

Field 2 is blank

Value List

.net framework

windows

microsoft

 

Take the value list and go through each value and see if i can find a match within the Field = Microsoft .net framework

 

since i sort my value list by length the first value i can match i will assign to field 2. so in this case i will find that the value .net framework is contained within Field1. And i will assign the value .net framework to field2.

[comment]

The problem is that you have turned the original question 180 degrees around. Originally, you asked how to:

go through my value list and see if I can find a value that has the [field value] in it.

Now it seems you want to go through the value list and find a value that is contained in the field. That's a very big difference. The first task can be accomplished simply as shown above. The second task requires looping among the values until a "match' is found:

Set Variable [ $item; Value:YourTable::Sourcefield ] 
Set Variable [ $list; Value:ValueListItems ( Get ( FileName ) ; "MyValueList" ) ] 
Loop 
 Set Variable [ $i; Value:$i + 1 ] 
 Set Variable [ $value; Value:GetValue ( $list ; $i ) ] 
 Exit Loop If [ PatternCount ( $item ; $value ) or $i > ValueCount ( $list ) ] 
End Loop 
Set Field [ YourTable::Targetfield; $value ]