Forum Discussion

NathanAllan4's avatar
NathanAllan4
Qrew Cadet
5 years ago

Summary of a User Field

Hi everyone!

I need to create a summary field in my parent table that summarizes a User field in the child table. Is this possible? Seems like I'll be able to do what I need when the new formulas come out later this year, but just wondering if I can do this now.

Thanks!

------------------------------
Nathan Allan
------------------------------
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    Do you mean a combined text summary field and then non the parent turn this into a userlist field type?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • NathanAllan4's avatar
      NathanAllan4
      Qrew Cadet
      5 years ago
      I think that would work. I tried a combined text summary field, but it says that function can't be applied to my User field in the child table.

      ------------------------------
      Nathan Allan
      ------------------------------
      • QuickBaseCoachD's avatar
        QuickBaseCoachD
        Qrew Captain
        5 years ago
        ... I will post back later when i get a chance.

        Mark Shnier
        'Your Quick Base Coach




  • HannahJacobson's avatar
    HannahJacobson
    Qrew Trainee
    5 years ago

    I'm guessing you figured this out since this was from so long ago, but in case anyone else runs into this thread I'll throw this out there:

    I haven't done this with a list of multiple users, but I have worked around this issue when creating a summary field that returns only one user - here is how I've done it:

    1. On the child table, create a formula field that converts the user field you care about to text, e.g. ToText([User])
    2. Create the combined text summary field on that new text field
    3. On the parent table, create a formula field that converts the summary field from a text list to text, then from text to user, e.g. ToUser(ToText([User Summary Field]))

    This method may be able to be tweaked to use for a user list, but I'm not sure exactly how it would differ in that case.



    ------------------------------
    Hannah Jacobson
    ------------------------------
    • JoshHamilton's avatar
      JoshHamilton
      Qrew Trainee
      3 years ago
      Just adding to this thread as I was looking for a solution to a similar problem.
      My goal was to create a way to filter records in the parent table so that a user would only see records in the parent table when they were assigned as a user in the child records. So in this case my result is a true/false value but can be adapted to create a list of users.

      To filter records based on if the user is assigned to a record in the child table;
      1. Create a formula text field in the child table that looks up the user fields on the child record and creates a list of the id's-

        List(";", UserToID([UserField1], [UserField2])
      2. In the parent table I created a formula checkbox field and used the query function to do my look up using the formula below-

        var text u = UserToId(User());
        var bool has = If(Size(GetRecords("{23.EX."&[Record ID#]&"} AND {31.CT."& $u &"}", [_DBID_TEAMS]))>=1, true, false);
        $has

        '23' was the parent related id field in the child table and '31' was the formula text field listing the User ID's and '[_DBID_TEAMS]' is the child table.
      Using the same principle, to create a List - User field;
        1. Create a formula text field in the child table that looks up the user fields on the child record and creates a list of the id's-

          List(";", UserToEmail([UserField1], [UserField2])

        2. Create a formula List-User field in the parent table and use the formula;

          var text has = ToText(GetFieldValues(GetRecords("{23.EX."&[Record ID#]&"}", [_DBID_TEAMS]),31));
          var user uone = ToUser(Part($has,1,";"));
          var user utwo = ToUser(Part($has,2,";"));
          var user uthree = ToUser(Part($has,3,";"));
          var user ufour = ToUser(Part($has,4,";"));
          var user ufive = ToUser(Part($has,5,";"));
          var userlist users = ToUserList($uone, $utwo, $uthree, $ufour, $ufive);
          $users

          Repeat the 'var user' up to 20 as User-List fields cannot exceed 20 entries.


      ------------------------------
      Josh Hamilton
      ------------------------------
      • RaymondSakar's avatar
        RaymondSakar
        Qrew Cadet
        3 years ago
        Josh, this is very helpful! Thanks!

        ------------------------------
        Raymond Sakar
        ------------------------------