Finding the correct related record

[jknapp]

I have several databases that are related, and the key field relating the different db's is the Organization Name. I know it is not the best setup to use a natural key, and would be open to suggestions to change that as well if it is the best solution to my problem. (I already have an id# field that auto-generates in the contacts db, but have not yet made it into a primary key field, as we are mainly a text driven organization and the people working in these databases link things based on organization or individual name. The contacts database also grew organically, which means that sometimes we have a record with an organization name, but no individual contact name.)

Here's the problem I'm having.

I have a contacts database, that holds all of the address book type of info. for our organization.

I have several other databases that link to it, either looking up information, or adding new contact records as necessary.

These databases are for monthly listings and other submitted content, and contain the event titles, dates and other information that changes regularly.

I have the listings databases linked via a relationship that looks at both the organization name and the individual contact's name in each related table in the table occurrences (for example Jane Smith at Company X.)This is done with just one table occurrence for each table, not as separate table occurrences.

The relationship then fills in lookup fields and pulls related data into fields created to hold info from the other database. (they're not in a portal)

This setup works fine as long as there is information in both the company and the individual contact fields. It does not work for records that have only an individual or only a company, meaning that one of the two fields is blank.

Why would this be? Shouldn't it find the right related record in the contacts database if the field contents essentially match? The same field is blank in each database: for example we have no individual contact at company Y, because we only have one record for that organization/ company, or because none was provided. When in the listing database, we type in company Y into the organization field, but it does not find or pull the information from the Contacts DB.

I needed the relationship to be based on both fields, not one or the other, because we often have multiple contacts at different organizations- like advertising, marketing, design, etc., and I need to make sure that the correct person's contact information shows up in the listing.

If I just have it based on one field, like company name for example, when working in the listings database and entering the organization name, Filemaker just looks for the first matching record for that company and plunks the rest of that contact information into the related fields.

So, if anyone can explain why the relationship does not work if both fields don't contain some data, that would be helpful.

Secondly, if you have suggestions for how to tidy up the relationships, I'm all ears.

[comment]

Empty fields do not match each other. Your relationship is an AND relationship, i.e. both fields have to match. What you seek is an OR relationship, which can be accomplished by defining a calculation field (on both sides) =

Company & ¶ & Individual

and using it as the matchfield instead.

Of course, matching on names is not a good practice, as you have surmised. This has nothing to do with users or the subject of the database. With a good interface, users need not even to be aware that there are ID fields in the background doing the linking.

[jknapp]

Would it be better then, to use the calculation you suggest, and have this link the two records in the related database via the ID#'s I"ve created already (a contacts id # and a listings id#) and if necessary, use a join table? I just didn't know how to go about finding exactly the right record before.

[comment]

It's hard for me to say what would be better, because I didn't understand the part about "listing databases" in your original post. I am not sure if they are files, or tables, or something else, and I don't know what their exact purpose is.