Forum Discussion

BrianRichardso1's avatar
BrianRichardso1
Qrew Trainee
6 years ago

Summarizing users

I'm wrestling with the lack of an option to use a Summary field to pull together a list of Users as defined on a child table.  I'm hoping QB adds this type of summary field in the future.  In the meantime though...

The Goal:  I want to enable the selection of stakeholders for each project, from a table of stakeholders that includes information including a User field matching them to their account.  

The Setup:  I have a many to many relationship, as follows:      Projects < Stakeholder Assignments > Stakeholders

The Ask:  I need a summary field at the Projects table that shows all User fields that were assigned to that project in the Assignments table.  But there's no Summary field for Users.  I know an alternative would be to report directly from the Assignments table on assigned projects, but the Projects table contains reams of data and multiple other relationships that I don't want to replicate as 100 lookups fields on the Assignments table.  Plus I'd not have the ability to report on other Project child tables like financials and risks without also setting up a ton of other relationships.    

Any tricky ideas?   I was playing with the idea of converting user fields to emails and then to text, summarizing that text, then somehow "back converting" those text email addresses to user accounts.  But I'm not being successful yet.

------------------------------
Brian Richardson
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    You are on the exact right track.

    You would create a text field (which is the only kind of field that a Combined TEXT Summary) can combine combine at this time.  So that would be

    ToText([userid field])

    Then on the parent record you will end up with a combined text summary of the users, which would manifest itself visually in "pill format".  Then we just need a field to convert that string to a List User field, which I belieive is your ask. 

    I have to run now, but I will post back later with a formula.  It will be longish but highly repetitive.

    Mark

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      6 years ago
      OK,  not tested but try this.

      var text value = ToText([My combined text summary field of Users in text format]);

      ToUserList(
      ToUser(Trim(Part($value,1,";"))),
      ToUser(Trim(Part($value,2,";"))),
      ToUser(Trim(Part($value,3,";"))),
      Trim(Part($value,4,";")),
      etc

      ToUser(Trim(Part($value,20,";")))
      )


      // The ToUserList only allows up to 20 entries.

      ------------------------------
      Mark Shnier (YQC)
      Quick Base Solution Provider
      Your Quick Base Coach
      http://QuickBaseCoach.com
      mark.shnier@gmail.com
      ------------------------------
      • BrianRichardson's avatar
        BrianRichardson
        Qrew Member
        6 years ago
        This is awesome, thank you so much!!  I'll try this next week but looks like it makes intuitive sense.  I always forget about Trim.

        ------------------------------
        Brian Richardson (test editor)
        ------------------------------