Forum Discussion

JenniferJuhasz's avatar
JenniferJuhasz
Qrew Cadet
4 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
4 years ago
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
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    You have done it exactly correct.  You might want to consider a form rule to populate the 2nd relationship with the same clue as the first selection when the second is blank, assuming that for the most part most attendees re from within their own local community.  Then the users can change it if that was not the case.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • JenniferJuhasz's avatar
      JenniferJuhasz
      Qrew Cadet
      4 years ago
      Hi Mark,

      Thank you!  I appreciate your time and confirmation, and the suggestion is great.  I will do that.

      Take care,

      Jen

      ------------------------------
      Jennifer Juhasz
      ------------------------------
  • LauraThacker's avatar
    LauraThacker
    Qrew Captain
    4 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
    ------------------------------