What follows are two connected issues with link columns:
For a specific application, I need users not to be able to link to existing records in another table, but to create a new one. Therefore, I want 1 pure 1:1 relationship.
First and primary issue: enforce 1:1 table relationships
I create a link column A=>B (with the “link to multiple records” option off) in table A, which creates the reverse link column in table B (correct). The latter column has “link to multiple columns on” set by default, which is also understandable.
However, when I turn off that feature in table B, the system does not enforce the 1:1 link:
- I can set only one link from A to B in any record of A. Likewise, I can only set one link from B to A in any record of B
- However, I can set any number of links from multiple records in A to one record in B. This violates the “Record in B only links to one record in A” restraint
- This is a suspected bug, because the GUI in table B does not deal with this correctly.
- The same happens from B to A
As a possible solution, the link picker (i.e. from A to B) should only present records in B which are not already been linked from / to A
My personal workaround was to create a view that only shows records from B that have no incoming links from A. Nevertheless, I think this should be enforced by the system.
Second issue
In order to force people to create a new record in the target table, I created a view that is always empty, and restricted the record picker to that - works! They type something in the search box, and the link “create new record” appears.
However, the linking table des not update properly. Hard to describe, therefore I just wanted to mention that i case somebody sees this while looking into issue #1
