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 c...
Forum Discussion
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
------------------------------
- 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;
- 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]) - 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.
-
- 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]) - 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.
- 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-
------------------------------
Josh Hamilton
------------------------------- Josh, this is very helpful! Thanks!
------------------------------
Raymond Sakar
------------------------------
- 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-
