Forum Discussion

HannahJacobson's avatar
HannahJacobson
Qrew Trainee
5 years ago

Re: Summary of a User Field

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
------------------------------

2 Replies

  • 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
      ------------------------------