Relations as search engines

[Aanand]

I'm putting together a log-style database. It has a central table for actual log entries (called log), and a bunch of related tables for certain objects. These work like this: every log entry can be related to zero or one of the following:

• an advertiser
  
• an agency
  
• a particular issue of a publication
  

So I've got a table for each of those bold-text words and everything's got a unique auto-entered serial number. So:

• log::advertiser is matched to advertisers::id
  
• log::agency is matched to agencies::id
  
• log::issue is matched to issues::id
  
• issues::publication is matched to publications::id. issues also has an issue no field, which in combination with issues::publication is unique (I did with the concatenated calculation field method).
  

Advertisers, agencies and publications also have a unique name text-field.

So far so good. What's causing difficulty is making the data entry user-friendly. I want the users, when making new log entries, to be able to type in the name of the advertiser (or agency, the two work identically) to choose it. I initially had a field for log::advertiser that was a drop-down menu linked to a value list of advertiser IDs that displayed only their names. That was perfectly functional, but it had two problems: firstly, once you've selected the advertiser, the field displays its id, not its name, which is unintuitive. Secondly, you can't take advantage of the auto-complete function.

So I did the following:

• I added a text field log::sf_advertiser_name.
  
• This was related to a second instance of advertisers, called candidate_advertisers, by matching on the name instead of the ID.
  
• log::sf_advertiser_name became the value to be entered on the form, with a drop-down list of advertiser names.
  
• log::advertiser became an auto-enter field (set to enable overwriting, natch) that grabbed the ID of the first (and only) record from candidate_advertisers.
  

...essentially, I was using candidate_advertisers as a search engine of sorts.

Now, that works absolutely fine, for both advertisers and agencies. The problem is with issues. Ideally, I want the user to type in a publication name in one box, an issue number in another, and for the database to go and grab the only issue record with that name and number. But these fields are in two different tables (publications::name and issues::issue no)!

How I see it working in my head is:

• log::sf_publication_name is matched with candidate_publications::name, where candidate_publications is another instance of publications.
  
• candidate_publications::id is matched to candidate_issues::id.
  
• log::sf_issue_no is matched to candidate_issues::issue no.
  

...but Filemaker doesn't like this, because it means there are two relational paths from log to candidate_issues.

So I:

• created a calculation field in issues called publication_name, which just grabs publications::name
  
• got rid of candidate_publications
  
• matched log::sf_publication_name straight to candidate_issues::publication_name.
  

But the relationship doesn't work, and I think it's because issues::publication_name has to be unstored, because it references a related value.

So I'm stuck. Is there another way to do this?

Edited September 8, 2005 by Guest

[Aanand]

Additionally: I can't make issues::publication_name an auto-entered field, because it needs to be automatically updated when publications::name changes, and as far as I know an auto-entered field only updates when the record it's in is changed.