Symmetric Lookup

[mweiss]

This question may be identical to caman's post (directly below mine), but I'm not sure I understood his correctly.

I have a 2-table database.

Table 1 is "People". Fields are: last_name, first_name, email, etc.

Table 2 is "Contacts". Fields are: Date, last_name, first_name, email, decription, etc.

Every time I contact one of my "people" it gets logged with a new contact record.

I've formatted my contact entry layout so that both the last_name field and the email field are dropdown lists.

I know that I can set Contacts::last_name to be autofilled in when I enter Contacts::email.

I also know that I can set Contacts::email to be autofilled in when I enter a Contacts::last_name.

What I don't know how to do is make this "symmetric": i.e., I want to have the choice of either entering an email, and having last_name automatically filled in, or entering a last_name, and having email automatically filled in.

This is probably a FAQ. Is there an FAQ somewhere?

[Ender]

In general, you should only use a record ID as a relational key for a lookup like this, but it doesn't look like you have one. I'd recommend that you add a Person ID field (auto-enter serial number) and use that as the primary key in Person. Add a corresponding Person ID to Contact as the foreign key in this relationship.

For creating the related Contact records, you should try doing that through the relationship. Start the user on the Person layout, then use a Portal (whose relationship is set to allow creation of related Contact records). The user can simply type the description of the Contact, and not worry about the Name and Email, since those are already in the Person record and they are probably the same across all of that Person's Contact records.