Hi all,

I have an odd situation where I need to write a script that loops through two tables and finds not only records with exactly matching string values in a field but also that exact match AND one or more additional string values. Allow me to explain:

The parent table contains a record with a field named Movie and a record with that field containing "This Movie". The child table contains a record with it's own field named Movie and a record with that field containing "This Movie" also. Easy find there.  But the child also has another record with the field containing "This Movie (2007)". So how can I find both child records? The conditions are always that the additional records always match exactly except for a year within parenthesis, so I think some sort of find that looks for "(" in addition to an exact match would do. 

 

Thanks,

 

Mark

 

You could probably use patterncount.

Thanks, Steve.

I'll look into it and post the results.

Is this a one time thing or a reoccurring thing you have to loop thru and perform?

If it were a one time clean up just use a relationship between the field TableA::Movie = TableB::MovieMatch the field MovieMatch would be a calculation that uses this custom fucntion http://www.briandunning.com/cf/10 

So "This Movie"  would match =   

T
Th
Thi
This
This   (trailing space)
This M
This Mo
This Mov
This Movie
This Movie (trailing space)
This Movie (

Wouldn't searching for Movie provide the same result?

Thanks for the replies guys.

I found a solution. In my script I do a Perform Find with two criteria. The first compares a variable I set in the parent table for the movie name with the movie name in the child record. That covers any child records with identical string values. The second does the same except I add " ("*  So basically it just adds literal text and zero or more characters beyond that. The logic is clearly not watertight, but it fits the conditions I need it for.

 

Thanks again for the help, I'm looking into the suggestions anyway as they are quite clever and may be of use somewhere else in my database.

 

Best,

Mark