AnsweredAssumed Answered

Question: Link a container to more than one table

Question asked by DeShawnJackson on Mar 6, 2011
Latest reply on Mar 22, 2011 by DeShawnJackson

Title

Question: Link a container to more than one table

Post

Set up:

Tables:

Contacts

Inventory

Documents

Preferences: One record

Previous Relationships:

Contacts::k_ID_Record = Documents::kf_ID_Record AND

Contacts::k_1_Contacts = Documents::kf_Type

Inventory::k_ID_Record = Documents::kf_ID_Record AND

Inventory::k_3_Inventory = Documents::kf_Type

etc...

Where k_ID_Record fields are auto-serial unique IDs and k_1_Contacts, etc fields are global calculations (i.e. k_1_Contacts = 1, k_3_Inventory = 3, etc. ) *The global calculation fields are used in the database to distinguish the "type" of document.

Problem:

Some documents need to be linked to both Contacts and Inventory, but some Contacts and Inventory items will obviously have identical k_ID_Record's (i.e. the 1st contact created and the 1st inventory item created). 

Example:

Documents Table:

Document::Container = DocA

Document::kf_ID_Record = 1<p>2

Document::kf_Type = 1<p>3

Instead of DocA being linked to Contact #1 and Inventory #2, DocA will be linked to Contact #1, Contact #2, Inventory #1, and Inventory #2.

Current Solution:

The current solution I have come up with is to separate the kf_ID_Record field into separate fields:

Inventory::k_ID_Record = Documents::kf_Inventory

Contacts::k_ID_Record = Documents::kf_Contacts

etc....

Solution works, but may not be ideal in the future if I need to relate Documents to several tables. Is there a better solution to this?

Outcomes