The (small) company I work for has need of a database, and I have been given the responsibility to design it. In learning through trial and error with FileMaker, among the more vexing problems of mine is the exactitude required when displaying related records in a portal. My colleagues are extremely sloppy with data integrity, even with drop-down menus and autocomplete enabled. The company is a literary agency, which takes manuscripts (defined as Title) from our authors (Author Name) and sends them out to various editors at different publishing houses (Editors, House). I currently match Author Name and Title between two different databases, and based on those two factors, it pulls up all of the related information I want.

However, if the author's name is Arthur and someone uses Art, then the Art result will not display due to its difference. Tim vs. Timothy, etc. has made what would be an excellent program into a nightmare of constant double-checking and reminders. Is there a way to make it so that the relationship need not use EXACT values, but values that are close? Such as a margin for error in a search term.

 

Alternatively, do I need to make a list of every possible nickname, somehow link them all to an ID code, and match based on that? Please ask for clarification if you are not sure of what I am asking.

5 minutes ago, Hoytopher said:

 Is there a way to make it so that the relationship need not use EXACT values, but values that are close?

There are methods for fuzzy matching. Most of them require a lot of work (and not at a beginner level). And no method exists that would match Jack to John, for example, except by looking it up from a pre-defined array.

IMHO, your best approach would be to force users to select from value lists (and use IDs as the match fields for the relationship).

 

13 minutes ago, Hoytopher said:

Such as a margin for error in a search term.

That actually is quite feasible. The default search is for words that start with ... so you could "explode" the name "Timothy" to say:

Timothy
Timoth
Timot
Timo
Tim

and use that as the match field instead of the original name field.