I have a multi-select Person column, but this would likely apply to any multi select column in SharePoint. When retrieving data from a SharePoint list, and a column is multi select, in my case a Person column, what is returned varies based on whether the column has 1 or multiple selections. If there are multiple selections it returns a table of those entries, if it's a single it returns a record. When working with that column the functions available will vary. In my example I want to check if the current user is selected in the column. The test conditions I have are as follows:
Set( gblUser = User().Email );
Set( gblApp, LookUp( Applications, 'Name (Title)' = gblAppName ) );
// Test if only a single person is selected in column
Set( gblIsAdmin, If( gblUser in gblApp.Admins.Email, true, false ) );
// Test if multiple users are selected in the column
Set( gblIsAdmin, If( IsBlank( LookUp( gblApp.Admins, Email = gblUser ) ), false, true ) );
The problem is how to test when to use one over the other. Currently the value only has a single selection, which flags the second test condition as an error because gblApp.Admins is an incorrect type for a Lookup. The flag goes away if Admins has multiple selections. The first test only works if there is a single selection otherwise always returns false if multiple selections.
What can I use to test the condition regardless of whether there is one or more selections in the column?
Thanks.
Solved! Go to Solution.
I solved this issue. There maybe a better way but the below worked for me.
Set( gblApp, LookUp( Applications, 'Name (Title)' = gblAppName ) );
Set( gblUser, User().Email );
// The values for Admins and Owners is copied into a Collection.
// *****************************************************************************
// This is done because the Type of these columns will be different depending on
// whether there is one or multiple Persons selected. If only one the data type
// is Record, if multiple the data type is Table. Collection Creation will take
// either as a parameter to create the collection. So in the end you have the
// same data type regardless of what is in the column. This allows the tests
// that follow to be the same.
Collect( gblAdmins, gblApp.Admins );
Collect( gblOwners, gblApp.Owners );
Set( gblMembership,
{
IsAdmin: If( IsBlank( LookUp( gblAdmins, gblUser in Email ) ), false, true ),
IsOwner: If( IsBlank( LookUp( gblOwners, gblUser in Email ) ), false, true ),
IsSubmitter: false
}
);
// This is optional, I clear the collections as they are no longer needed.
Clear(gblAdmins);
Clear(gblOwners);
I hope this helps someone else.
I solved this issue. There maybe a better way but the below worked for me.
Set( gblApp, LookUp( Applications, 'Name (Title)' = gblAppName ) );
Set( gblUser, User().Email );
// The values for Admins and Owners is copied into a Collection.
// *****************************************************************************
// This is done because the Type of these columns will be different depending on
// whether there is one or multiple Persons selected. If only one the data type
// is Record, if multiple the data type is Table. Collection Creation will take
// either as a parameter to create the collection. So in the end you have the
// same data type regardless of what is in the column. This allows the tests
// that follow to be the same.
Collect( gblAdmins, gblApp.Admins );
Collect( gblOwners, gblApp.Owners );
Set( gblMembership,
{
IsAdmin: If( IsBlank( LookUp( gblAdmins, gblUser in Email ) ), false, true ),
IsOwner: If( IsBlank( LookUp( gblOwners, gblUser in Email ) ), false, true ),
IsSubmitter: false
}
);
// This is optional, I clear the collections as they are no longer needed.
Clear(gblAdmins);
Clear(gblOwners);
I hope this helps someone else.
User | Count |
---|---|
204 | |
93 | |
84 | |
47 | |
42 |
User | Count |
---|---|
251 | |
104 | |
104 | |
62 | |
57 |