How add “join table” records? (... when a parent MAY be absent)
I have 3 tables : Events —< People-at-Events >— People. (This is a bit oversimplified.)
On the Events form, I want to be able to enter People who attend that event.
Ideally I’d like to easily enter people who are already in the People table, and People who are not yet in it.
For those not yet in, I obviously need to create both a People record and a PatEvent record.
Currently (which seems not ideal), I have 2 portals on the Events form.
#1 PatEvent (for entering existing people)
#2 People - Related (for entering new people)
A) If “Ann” IS already in the People table, I begin typing her name into the (last row of the) PatEvent portal-1; the autocomplete, drop-down field finds her; and she is added.
B) If “Bill” is NOT in the People table, I enter his name and other data in the People portal-2, and (after clicking elsewhere — [to commit the record?]), his name appears in both portal-1 and portal-2.
C) If I MISTAKENLY think Carl is NOT in the People table and begin to enter him (like Bill), I get a “Duplicate Name” warning, and switch to method A).
If there is a better to do this — ie, to be able to enter both existing and new people — I’d like to switch to it in a new DB I’m creating. My current approach does not seem very elegant.
I imagine this issue is faced by many people, because this structure is pretty basic.
Probably irrelevant, but in case it might be useful:
I actually have a 3rd portal on the Events form:
#3 People—ALL (via a Cartesian “X-join” to a 2nd table occurrence of People)
... and an alternative way to enter Ann (who IS in People) is:
D) I scroll down Portal #3 (ALL people), select her record, and trigger a scrip which transfers her data to the last row of Portal-1 similar to “A)” above.)
Thanks for any suggestions.