Forum Discussion

JeremyLahners's avatar
JeremyLahners
Qrew Cadet
3 years ago
Solved

Querying from many-to-many table to populate field value

I have a many-to-many relationship of employees to campaigns.  Each employee can serve one of three roles within a campaign.  In my scenario I will only ever have one employee serving each role.  Table design shown below:


Within the Campaigns table, I would like to be able to see the Employee name in each of the three distinct fields.  So Campaigns should have three fields called Assistant, Strategist, and Setter.  

The query seems fairly easily - basically something like "Select Employee Name from Assignments where Campaign = [Record ID#] and Role = 'Strategist'".  Unfortunately I cannot figure out how to make this happen...  Any suggestions on how to accomplish this?  Should I just abandon the many-to-many relationship and create three independent relationships?

TIA

------------------------------
Jeremy Lahners
LeadBaller
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    3 years ago
    The combined text summary field only works on field types of text.  Your employee name field is actually a user field.

    So just make a text Formula Field called [Employee Name (text)] with the following formula

    UserToName([Employee Name])

    and  summarize that field.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------

3 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    The many to many is fine as is.

    You can make a Combined Text Summary field on the relationships between Campaigns and Assignments to get the name of the person(s) filtered by the role = "Strategist".   If you don't like the "bubble" or "pill" appearance of that Strategist Name on the campaign record you can convert it to text 
    ToText([Combined Text Strategist])

    Then duplicate that summary field and filter on on Role = Assistant, and then duplicate again for Role = "Setter"

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

      I've been trying to do that but keep getting the message "That summary function cannot be applied to this field...".  I've tried with both the Related Employee field and the Employee Name field.




      ------------------------------
      Jeremy Lahners
      LeadBaller
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        The combined text summary field only works on field types of text.  Your employee name field is actually a user field.

        So just make a text Formula Field called [Employee Name (text)] with the following formula

        UserToName([Employee Name])

        and  summarize that field.

        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------