Proximity Search

[nelliott]

Is it possible to perform a search that finds records where, in a particular field, one search criteria is within a set number of words (user defined) from another search criteria.

e.g. search criteria 1 = EJB

search criteria 2 = Java

no of words = 3

would find all records where in the particular field EJB is written within 3 words of Java.

Much thanks

Nick

[Vaughan]

I haven't. But my guess would be to create a calc field that parses the text, looking for criteria 1 and criteria 2, and counts the number of words between. Then do a range search on the number of words.

I don't think it'd be easy. Does any program offer something like this?

[BobWeaver]

You need to create a calculated field cMatch (with numeric result) with the following formula:

cMatch=

gProximity -

WordCount(Substitute(Substitute(

LeftWords(

Middle(SearchField, Min(Position(SearchField, gFindWord1, 1, 1),Position(SearchField, gFindWord2, 1, 1)), 64000), gProximity+1),

gFindWord1, ""),

gFindWord2, ""))>0

---

where gFindWord1 and gFindWord2 are global text fields containing the two words that you are searching for, and gProximity is a global number field containing the allowable distance (in words) between the two search words. SearchField is the text field that you are searching in.

Once you have this set up, enter the criteria in the 3 global fields, and then do a search for records where cMatch = 1.

Warning: if the field contains more than one occurrence of either one or both words, this formula can (and likely will) fail. In that situation, I think it's likely to get very complex, will have to be handled by a script.

[nelliott]

Thanks Bob,

The field in question contains candidates' resumes so there is a very high chance that the words would be contained in the field more than once.

Any pointers on writing a script to enable this?

Much thanks

Nick

[BobWeaver]

1. Skip any records where either one or both words are missing.

2. Skip any records where the cMatch field already shows a match.

3. In all remaining records it's necessary to loop through the text field one word at a time and count the distance between the last found search word, and the next found search word.

Having too much time on my hands today, I posted a sample file in the Samples Forum:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=files&Number=40890&page=0&view=collapsed&sb=5&o=31&fpart=1

[nelliott]

Thanks Bob,

Really appreciate all your help

Cheers

Nick