Hi everybody! I'm trying to create a relationship that's based on partial or imperfect matches, and I'm wondering if it's even possible.

In a typical relationship between table A and table B, entries from each table are related when the matching fields in each entry are exactly the same. But what if I want the entries to be related if the text of one matching field exists in the text of the other, even if the fields are exactly identical?

Here's an example... Let's say I have two tables, Disney Movies and Disney Characters. Let's say I first fill out entries in the Characters database, including the movie that each character is in. In most cases, a character is in just one film: Ariel and Flounder are only in "The Little Mermaid," Simba and Pumba are only in "The Lion King," etc. So, if I then go to the Movies table and create a portal to Characters, I'll get a nice list of all the characters in each movie.

The problem is Goofy, who's in "Fantasia" and "The Goofy Movie." In my ideal world, I want to enter these two titles into Goofie's movie field, separated by a space or a comma. I'd want the relationship to identify either title in the field, so that Goofy will show up in the portal on the "Fantasia" entry and in the portal on the "The Goofy Movie" entry in the Movies database.

So, how can I do this? How can I make a relationship that doesn't look for an exact match in the other table's matching field, but looks for just a string within the field? Can I use a calculation as one of the matching fields? If so, what calculation would work here?

I know I could create an additional table to put between Characters and Movies, but for the purposes of what I'm doing, I need to have several items in one field, like the titles in Goofy's movie field.

I'm running FMP 7 in Apple OS X Tiger.

Thanks for your help!

What you describe is not a partial or imperfect match, but an OR match. Just separate your values by a carriage return, and it will work. At least it will work until you want a report - then you'll discover you need that third table.

I know I could create an additional table to put between Characters and Movies, but for the purposes of what I'm doing, I need to have several items in one field, like the titles in Goofy's movie field.

What makes you say that?

You would probably be better off with a join table, maybe called "Roles" where each record contains on MovieID and on CharacterID. This is a standard many to many relation and that's the way to do it.