Forum Discussion

JenniferJuhasz's avatar
JenniferJuhasz
Qrew Cadet
3 years ago

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 = C...
Relationships
JenniferJuhasz's avatar
JenniferJuhasz
Qrew Cadet
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
------------------------------
LauraThacker's avatar
LauraThacker
Qrew Captain
3 years ago
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
------------------------------