Jump to content

Search through list to find a close match


This topic is 2781 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 ] 

 

Link to comment
Share on other sites

This topic is 2781 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.