Applies to:  Crow Canyon NITRO activated sites in:

SharePoint Online and On-Premises 2013/2016/2019/SharePoint Server Subscription Edition

Description

In SharePoint list, it is directly not possible to restrict the number of users that can be selected in multiple selections enabled person or group column. This can be achieved using form event actions in NITRO Forms.

Sample use case:

The configuration explained in this article is performed based on the following scenario:

1. When the ‘Project’ column value (which is a NITRO lookup column) is selected, then the value in ‘Max People Allowed’ column is automatically filled as per the auto-fill lookup data column mappings.
2. When users are selected in the ‘People’ column, the count of these users is automatically filled in ‘People Count’ column using first Form Event Action.
3. The value in ‘People Count’ column is then matched with the value in ‘Max People Allowed’ column using custom JavaScript.
4. Based on the conditions specified in custom JavaScript, background colour of the ‘People Count’ column is changed to Red, Green, or Yellow, using custom JavaScript that is configured in the second Form Event Action.
5. Validation is configured on the ‘People’ column that checks if ‘People Count’ column value matches with the value in ‘Max People Allowed’ column. If the validation fails, the form cannot be submitted.

List schema

1. Custom SharePoint list.
2. Columns:

Column Display Name	Column Internal Name	Column Type
Title	Title	Single line of text
Project	Project	NITRO Lookup
People	People	Person or Group (multiple selections are allowed)
People Count	PeopleCount	Single line of text
Max People Allowed	MaxPeopleAllowed	Single line of text
Max Allowed Resources	MaxAllowedResources	Single line of text column in the lookup list

‘Project’ column settings:

‘People’ column settings:

Detailed steps

Configure two form event actions in the NITRO Form:

• For counting the number of users/people selected in the ‘People’ column.
• For updating the background colour of ‘People Count’ column.

1. Navigate to NITRO Forms designer for the list -> Add all the columns, configured above, onto the form.
2. To configure the Form Event Actions, expand the ‘Advanced’ section from left-hand panel -> ‘Form Event Actions’ -> ‘New Configuration’.
3. Configure the first form event action ‘Count number of people’ to execute on form load and on ‘People’ column value change.

4. Configure ‘Update Form Controls’ action, click ‘New Mapping’ as shown below:

5. Map ‘People Count’ column -> Select ‘Format Value using JavaScript’ as shown below:

Script:

return window.ccs_g_FormUI.fetchColumnValueUI("COLUMN_INTERNAL_NAME", false, true).length;

Note: Replace ‘COLUMN_INTERNAL_NAME’ with internal name of the ‘People’ column. Refer to this article to get internal name of a list column.

6. Similarly, configure the second form event action ‘Assign Colour’ to execute on form load and on ‘People Count’ and ‘Max People Allowed’ column value change.

7. Configure the ‘Execute Script’ action as shown below:

Script:

var PeopleCount = window.ccs_g_FormUI.fetchColumnValueUI("COLUMN_INTERNAL_NAME_FOR_PEOPLE/GROUP_COLUMN") || 0;
var MaxPeople = window.ccs_g_FormUI.fetchColumnValueUI("COLUMN_INTERNAL_NAME_FOR_MAXIMUM_PEOPLE_ALLOWED_COLUMN") || 0;
var color = "";
if (PeopleCount === 0 || PeopleCount < MaxPeople) {
    color = "red";
} else if (PeopleCount === MaxPeople) {
    color = "green";
} else if (PeopleCount > MaxPeople) {
    color = "yellow";
}
$("#CCSPeopleCountStyle").remove();
if (color) {
    var cssText = "#row_PeopleCount .ccs_ColumnControl{background-color: " + color + "}";
    var style = document.createElement('style');
    style.type = 'text/css';
    style.id = "CCSPeopleCountStyle";
    if (style.styleSheet) {
        style.styleSheet.cssText = cssText;
    } else {
        style.appendChild(document.createTextNode(cssText));
    }
    $("head").append(style);
}
functionCallback();

Note:

• Replace ‘COLUMN_INTERNAL_NAME_FOR_PEOPLE/GROUP_COLUMN’ with internal name of the ‘People Count’ column.
• Replace ‘COLUMN_INTERNAL_NAME_FOR_MAXIMUM_PEOPLE_ALLOWED_COLUMN’ with internal name of the ‘Max People Allowed’ column.

Configure validation:

1. To configure validation on the ‘People’ column, select the column -> Expand ‘Validations’ section from the column settings panel -> Click ‘Add’ -> Configure validation as shown below:

Column validation condition:

‘People Count’ “not equal” ‘MaxPeopleAllowed’

Note: ‘Read’ permissions can be configured on the ‘Max People Allowed’ column as it is auto-filled and should not be updated manually. Refer to this article to configure column permissions.

Sample output:

Lookup list: Maximum resources allowed in ‘Task 1’ are ‘2’.

Sample output showing ‘Green’ background colour and successful validation for ‘Task 1’ is as below:

Validation configured on the ‘People’ column fails as the value in ‘People Count’ column is less than the value in ‘Max People Allowed’ column. As a result, the background colour of ‘People Count’ column changes to ‘Red’ as shown below:

If the value in ‘People Count’ column is more than the value in ‘Max People Allowed’ column, then the background colour of ‘People Count’ column changes to ‘Yellow’ as shown below: