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 = 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
------------------------------
Relationships
  • JenniferJuhasz's avatar
    JenniferJuhasz
    Qrew Cadet
    3 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
      3 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
        3 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
      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
      ------------------------------