Same reference field, different selections within the form
I have a scenario where I have a parent table with a list of trial numbers.
I have a related table that has a list of subjects.
Each trial has many subjects, and some subjects have more than one trial.
In my form, I want to be able to add a new subject. In one field, I have labeled the primary trial, which is a table relationship picker.
However, I also want to have a second field labeled the secondary trial, which would pick from the same table as the primary trial did, but with different results.
How do I make the referenced selector independent of one another?
If you know that you will only have up to two Trials per subject, you will need to create a second relationship, and thus a second trial relationship picker. However, this can get dangerous because if suddenly a subject has a 3rd trial, you will be creating a 3rd relationship and so on (so this tactic only works if you can guarantee a small number of trials per subject).
Technically speaking, what you actually have is a many to many relationship (Trials have many subjects, and subjects have many trials). In Quickbase this takes a 3rd table, called something like Trial Subject Assignments. Where for any trials you can assign multiple subjects, and thus any subject assign multiple trials.
One Trial has many Trial Subject Assignments and One Subject also has many Trial Subject Assignments.
When on your trial form, you will see an embedded table of all the subject assignments. And on your subject form you will see an embedded table of all the trial assignments for that subject.
