I have a two part question. Here's the scenario. We are using what I call bridge tables. They keep track of a “one to many” relationship without duplicating information in the child table. The bridge table usually only contains two or three fields. In this scenario it will contain three, two unique ID fields and a third used to distinguish between the different types of child records.

Let’s say we have a COMPANY, BRIDGE and a CONTACT table. Since a company can have contacts that perform more than one job function, we don’t want to duplicate any of the contact information in the child table, so in the BRIDGE table we store the company ID, contact type, and contact ID. Therefore we have a “one to many” relationship between the COMPANY and BRIDGE table, and a “one to one” relationship between the BRIDGE and CONTACT table. I usually set complicated relationships in the data environment, but in this case I have to set the relationships on the fly depending on certain properties. In this scenario which relationship should be set first programmatically, or does it even matter?

My second question is about adding records in this same situation. All of the tables are optimistically table buffered. When I need to add a new contact, do I add the BRIDGE table record first or the CONTACT record?

I’ve noticed that when I just attach an existing contact record to an existing company, I have to perform a “GO TOP” in the BRIDGE table before the new record appears. Even with a form refresh the new BRIDGE record doesn’t appear in the grid unless I perform a “GO TOP IN BRIDGE” or simply move the record pointer.

TIA,
Mike