Insert and Update records in External data source using NITRO

Applies To: SharePoint Online and On-Premises

Description

NITRO Forms allow integration of external data with SharePoint list data. Please refer this article for more details on various scenarios of fetching external data and populating in NITRO Forms.

We can also write (insert / update) records in SQL server tables using NITRO Forms and Custom Actions. These operations work independently of fetch operations and need to be configured separately.

Detailed Instructions

Crow Canyon provides an API to perform the write operations
Write operation requires connection string to the SQL server and parameters for the data to write
We can use JavaScript action in Custom Actions and NITRO forms to invoke this API. API will invoke a stored procedure in DB to do the required update

In this article, we take an example to update the status of the employee record in database. NITRO forms are configured to fetch “Employee” data from the database table. This is done using External Data column configurations (refer this article).

Summary of steps

Create a stored procedure in database for the write operation
Prepare JavaScript:
- Specify the connection string
- Specify the parameter values to call the stored procedure
NITRO Configurations to update employee record
- Custom Action
- Forms

1. Create a stored procedure in database

Create a stored procedure in database to update the record as per your requirement. Sample given below updates Employee Status based on the Employee Id.

Sample stored procedure:

USE [DataBaseName] GO

/ Object: StoredProcedure [dbo].[CCS_UpdateStatus] / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CCS_UpdateStatus] -- Add the parameters for the stored procedure here @EmpID int, @Status nvarchar(128) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

-- Insert statements for procedure here

update [ExData_tblEmployee] set [Status] = @Status Where ID = @EmpId

END GO

2. Prepare JavaScript

In below script, modify the connection string and stored procedure name. Also pass the values for the parameters used in above stored procedure.

Below script is different for SharePoint online and On-Premises. Based on your environment, choose the appropriate script.

Sample JavaScript for SharePoint Online

};

var objApiParams = { "name": "UpdateQuery", "parameters": { "dbConn": "Server= ServerName; Database=Database Name;User ID=UserId;Password=Password;Encrypt=True;Connection Timeout=30", "queryText": "CCS_UpdateStatus", "isSProc": true, "params": {} } };

objApiParams.parameters.params["EmpID"] = currentItem.get_item("EmployeeId"); objApiParams.parameters.params["Status"] = "Inactive"; ccWebServiceCall(serviceURL, objApiParams, functionCallback);

In the above script, “EmployeeId” is the SharePoint column which stores unique value of the database record.

Sample JavaScript for SharePoint On-Premises

Make the same changes as described above for On-premises as well.

Script:

var serviceURL = _spPageContextInfo.webAbsoluteUrl + "/_layouts/15/CrowCanyon.CommonUtils/SPDataHandler" + (_spPageContextInfo.siteClientTag.split("$$")[1].split(".")[0] == "16" ? "16" : "") + ".aspx/DatabaseUtils";

var objApiParams = { "name": "UpdateQuery", "parameters": { "dbConn": "Server=Server Name;Database=Database Name;User ID=User Id;Password=Password;Encrypt=True;Connection Timeout=30", "queryText": "CCS_UpdateStatus", "isSProc": true, "params": {} } };

objApiParams.parameters.params["EmpID"] = currentItem.get_item("EmployeeId"); objApiParams.parameters.params["Status"] = "Inactive"; ccWebServiceCall(serviceURL, objApiParams, functionCallback);