In my opinion you will need either 2 relationships to your [_DBID_REGIONS] table (one for 'event intended for' and one for 'attendee lives in') selections. If you want the conditional-behavior instead, you will need 4.
[_DBID_COMMUNITIES] < CHILD [Related Community - Event Intended For]
[_DBID_REGIONS] < CHILD [Related Region - Event Intended For] (conditional on [Related Community - Event Intended For]
[_DBID_COMMUNITIES] < CHILD [Related Community - Attendee Lives In]
[_DBID_REGIONS] < CHILD [Related Region - Attendee Lives In] (conditional on [Related Community - Attendee Lives In]
You can automatically pull down the Community-lookup field values if you only select a "Region" at the child-table level (which is less data to populate and honestly if your count of Regions isn't that many this might be much simpler for users to see both the Community & Region as a record picker and to select the right one. If you had lots of Communities and many more Regions I would go with a conditional-table selection (4 relationships).
You need to be very deliberate/careful with your field naming convention when using multiple relationships to the same parent-tables in a child-table; so that users building reports are clear what the data values they are displaying represent.
------------------------------
Laura Thacker (IDS)
laura@intelligentdbs.com(626) 771 0454
------------------------------
Original Message:
Sent: 01-19-2022 18:10
From: Jennifer Juhasz
Subject: Relationships Question: 1 Parent Table to 1 Child Table - lookup/relationship to 2 Fields
Just updating this - I've attempted to do this by creating a Second Relationship; The relationship itself is identical except the reference field is a different field. It "seems" to work, but I recall in a training being told that it's not a good idea to have two relationships; but in that instance it was for creating a many-to-many relationship, which is not what I'm doing here.
Can anyone tell me if there is a reason why this would "not" work?
Thank you!
------------------------------
Jennifer Juhasz
Original Message:
Sent: 01-19-2022 16:27
From: Jennifer Juhasz
Subject: Relationships Question: 1 Parent Table to 1 Child Table - lookup/relationship to 2 Fields
Hi everyone,
This feels like it ought to be a simple question, but I can't figure it out.
I have built a Parent table of Communities and Regions (Region A = Communities 1, 2, and 3; Region B = Communities 4, 5, and 6; etc).
This is connected to a Child Table of attendees to online events.
I want to relate to two fields within this single Child Table: To both the Community the Event was intended For; AND to the Community the attendee Lives in.
I can only seem to get it to relate to One of the two fields; not both. Is it possible? Or do I need a secondary table of communities to make this happen?
Thank you for any suggestions you have!
Jen
------------------------------
Jennifer Juhasz
------------------------------