Applies to:
NITRO activated sites in SharePoint Online, On-premises 2013/2016/2019/SharePoint Server Subscription Edition

Description

Form event actions (FEAs) can be used to add custom functionality on the forms. Please refer this article for more details and examples.

FEAs can be triggered on form load or on a column value change by a user. There are some special scenarios where standard triggers do not work reliably and this article covers one such case.

On form load, logged in user is auto populated in a person and group column and user’s department in a lookup column. Based on this department value, we need to fetch a column value from department list using FEA.

FEA with form load trigger will not work directly in this case as there is some gap in populating user and user’s department after form load. For this we need to write a script to wait until the lookup column loads and then trigger an event for the lookup column.

This article describes FEA for this case by taking an example list schema and configuration. You can adapt this as per your use case.

Requirement

1. List Schema
   1. Departments
      1. Title
      2. Cost
   2. Requests
      1. Title
      2. Requester (person or group)
      3. Department (lookup to Departments list)
      4. Total Budget (Currency)
2. In Requests list new item form:
   1. Auto populate logged in user in “Requester” person or group column using auto-fill settings in NITRO Forms.
   2. Auto populate logged in user’s department (from user’s profile) in “Department” lookup column using auto-fill settings in NITRO Forms.
   3. Based on the “Department” column value, fetch the department item from “Departments” list and update the department cost in “Total Budget” column

Detailed steps:

Below configurations are in NITRO Forms for the ‘Requests’ list described above.

1. Configure Auto-fill User Information

Configure auto-fill user information settings to populate the logged in user and the user’s department in “Requester” and “Department” lookup column.

Go to the list -> List Settings -> Crow Canyon NITRO Forms -> Auto-fill User Information under “Form Settings” and configure as shown below:

2. Configure form event actions

We will configure two Form Event Actions:

1. Form Event Action 1: Wait for the department lookup column to get loaded and value to get auto filled.
   1. This action is not required if user is directly selecting the department column value. In that case FEA 2 is enough as it will trigger when user selects a value.
   2. It is required in this special case to trigger the second FEA as it will not trigger if department value is set with user auto fill.
2. Form Event Action 2: Update total budget
   1. This is the normal FEA to fetch department list item and use it to set value in a column in Request list.
   2. It is triggered whenever department column value is changed.

Configure form actions as shown below:

1. Form Event Action 1: Action to wait for department lookup column to load on the form

This action will execute a script that will check and wait for the “Department” lookup column value to get filled.

Action 1: Wait For Department Load

Configure execute script action as shown below:

Script:

Download the script from below:

Note: In above downloaded script, replace “Department” with actual column internal name.

To know the column internal name, please refer this article

Action 2: Set Department to trigger Event

Configure update form control action to trigger the event for ‘Department’ column value change. For this we need to do a dummy update of the “Department” column value.

Form Event Action 2: Update total budget

Configure form event action as shown below:

Action 1: GetDepartment

Configure fetch item action to get the item from “Departments” list
Value used in query builder condition:

Action 2: Update total budget

Configure update form control action to update the “Total Budget” with “Cost” from the department item.

Value used in column mappings:

CCSQLGetDepartment##[Cost|Cost]