Post
Ask Question

How To Save Lookup Field Values In SharePoint Using Rest API

Introduction

 
In this article, we will learn how to insert a record into the SharePoint custom list having a lookup column. In this example, first, we will create the Department List and then create the Employee list with lookup column having column name DeptName. We also cover how to upload pictures in the picture library and how to make a relation between Employee list (Custom List) and EmployeePicture (Picture Library). We will also cover the jquery DataTable concept. Using Jquery DataTable we will display the list of records available in Employee (Custome list). We will also perform the Delete operation using Rest API, on click of the delete of delete hyperlink a confirmation popup message will be displayed. Depending on the conformation we will delete the record and rebind the list of the item available in the Employee list (Custom List )
 
A Lookup field is a field type that allows you to connect a custom list to a field from another custom list. In this article, we refer DeptName field from the Department list (Custom List) as a lookup field. In Employee List we will add one column DeptName as a lookup field from the Department list.
 
In the case of Picture Library first, we will insert a record into EmployeeList. In EmployeeList having one column EmpProfilePicture, we will insert EmpProfilePicture value (EmpName + yyyyMMddhhmmss + picture extension ) and finally upload the picture into the EmployeePicture library with the same name as inserted into Employee List.
 
After inserting the record into the Employee list we will upload the Employee profile picture into Picture Library (EmployeePicture) and finally display the Employee in Jquery DataTable.
 

Scenario

 
In this example, we will create a custom list called "Department" and in the list, we will create a DeptName field. (ID & Title is the default field). Now create the Picture Library list called "EmployeePicture" with the default field and finally create the custom list "Employee" and in this list, we will create an EmpName field (Single line text), Gender field (Multi Selection), EmpDOJ (Date Time), EmpProfilePicture (Single line text), DeptName (lookup field from Department List ), City (Single line text).
 
In the Employee custom list, we will insert the record and DeptName we will refer from the Department custom list and name of the picture used during the EmployeePicture upload in the picture library.
How To Save Lookup Field Values In SharePoint Using Rest API
Employee List
 
A column stores information about each item in the list. The following columns are currently available in this list
 
Column 
Type
Title
Single line of text
EmpName
Single line of text
Gender
Choice
EmpDOJ
Date and Time
EmpProfilePicture
Single line of text
DeptName
Lookup
City
Single line of text
Modified
Date and Time
Created
Date and Time
Created By
Person or Group
Modified By
Person or Group
 
Department List
 
A column stores information about each item in the list. The following columns are currently available in this list
 
Column (click to edit)
Type
Title
Single line of text
DeptName
Single line of text
Modified
Date and Time
Created
Date and Time
Created By
Person or Group
Modified By
Person or Group
 

Implementation

 
Step 1
 
Create Department List with the field DeptName, field ID & Title as Default field 
How To Save Lookup Field Values In SharePoint Using Rest API 
Step 2
 
Create Employee List with the below field detail. In the Employee list, DeptName is a lookup field and we used EmpProfilePicture name  during the picture upload in the Picture library.
 
How To Save Lookup Field Values In SharePoint Using Rest API
Step 3
 
Create Picture Library called "EmployeePicture" with the default field name. After the data is inserted into the Employee List we will upload the picture into EmployeePicture library.
 
How To Save Lookup Field Values In SharePoint Using Rest API
Step 4
 
Create an HTML form with the below field and finally add two-buttons, the "Insert Employee" and the "Get Employee" button.
 
On click of the employee button, we will insert the record into the Employee List and upload the picture into the picture library.
 
On load of the HTML form we will bind the Department dropdown list from the Department List with ID and Value (Text > 'IT', Value > 1). 
 
How To Save Lookup Field Values In SharePoint Using Rest API
Step 5 - HTML code  
  1. <html>  
  2.     <head>  
  3.         <script src="https://code.jquery.com/jquery-3.5.1.js"></script>  
  4.         <script src="https://cdn.datatables.net/1.10.23/js/jquery.dataTables.min.js"></script>  
  5.         <link rel="stylesheet" href="https://cdn.datatables.net/1.10.23/css/jquery.dataTables.min.css" />  
  6.         <script src="EmployeeApi.js"></script>  
  7.     </head>  
  8.     <body>  
  9.         <div>  
  10.             <table>  
  11.                 <tr>  
  12.                     <td>Title</td>  
  13.                     <td>  
  14.                         <input type="Text" id="txtTitle" value="Mr">  
  15.                         </td>  
  16.                     </tr>  
  17.                     <tr>  
  18.                         <td>    
  19.                     EmpName    
  20.                 </td>  
  21.                         <td>  
  22.                             <input type="Text" id="txtEmpName" value="Ronado">  
  23.                             </td>  
  24.                         </tr>  
  25.                         <tr>  
  26.                             <td>    
  27.                     Gender    
  28.                 </td>  
  29.                             <td>  
  30.                                 <input type="radio" id="male" name="gender" value="male">  
  31.                                     <label for="male">Male</label>  
  32.                                     <input type="radio" id="female" name="gender" value="female">  
  33.                                         <label for="female">Female</label>  
  34.                                     </td>  
  35.                                 </tr>  
  36.                                 <tr>  
  37.                                     <td>    
  38.                     EmpDOJ    
  39.                 </td>  
  40.                                     <td>  
  41.                                         <input type="Text" id="txtEmpDOJ" value="01/01/2021">  
  42.                                         </td>  
  43.                                     </tr>  
  44.                                     <tr>  
  45.                                         <td>    
  46.                     Select Deparment    
  47.                 </td>  
  48.                                         <td>  
  49.                                             <select id="ddlDepartment" >  
  50.                                                 <option value="0">Select</option>  
  51.                                             </select>  
  52.                                         </td>  
  53.                                     </tr>  
  54.                                     <tr>  
  55.                                         <td>Upload File</td>  
  56.                                         <td>  
  57.                                             <input type="file" id="getFile" />  
  58.                                         </td>  
  59.                                     </tr>  
  60.                                     <tr>  
  61.                                         <td colspan="2">  
  62.                                             <input type="button" id="btnInsertEmp" value="Insert Empoyee" />  
  63.                                             <input type="button" id="btnSubmitEmp" value="Get Employee" />  
  64.                                             <input type="button" value="Upload Profile" id="btnUploadProfile" style="display:none" />  
  65.                                         </td>  
  66.                                     </tr>  
  67.                                 </table>  
  68.                             </div>  
  69.                             <br />  
  70.                             <div id="divResults" style='width:80%'></div>  
  71.                         </body>  
  72.                     </html>    
Step 6
 
Create a JavaScript EmployeeAPI.js
  1. $(document).ready(function () {  
  2.     $("#btnSubmitEmp").on("click", function () {  
  3.         $('#example').DataTable();  
  4.         getEmployeeListData();  
  5.         $('#example').DataTable();  
  6.     })  
  7.   
  8.     $("#btnInsertEmp").on("click", function () {  
  9.         debugger;  
  10.         InsertEmployeeListData();  
  11.     })  
  12.   
  13.   
  14.     $("#btnUploadProfile").on("click", function () {  
  15.   
  16.         debugger;  
  17.         var files = $("#getFile")[0].files;  
  18.         uploadFile(files[0]); // uploading singe file  
  19.     })  
  20.   
  21.     addDepartment();  
  22.     $('#example').DataTable();  
  23. });  
  24.   
  25.   
  26. function ProcessUploadPic() {  
  27.     if (document.getElementById("fileupload").files.length === 0) {  
  28.         alert("Select a file!");  
  29.         return;  
  30.     }  
  31.     var parts = document.getElementById("fileupload").value.split("\\");  
  32.     var filename = parts[parts.length - 1];  
  33.     var fileInput = document.getElementById("fileupload").files[0];  
  34.     var picReader = new FileReader();  
  35.     picReader.addEventListener("load", function (event) {  
  36.         var picFile = event.target;  
  37.         var div = document.createElement("div");  
  38.         div.innerHTML = "<img class='thumbnail' src='" + picFile.result + "'" + "title='" + picFile.name + "'/>";  
  39.         PerformUploadPic(filename, div)  
  40.     });  
  41.     picReader.readAsDataURL(fileInput);  
  42. }  
  43. function PerformUploadPic(filename, fileData) {  
  44.     var url = document.URL.split('/');  
  45.     url = url[0] + "//" + url[2] + "/" + url[3] + "/";  
  46.     $.ajax({  
  47.         url: url + "_api/web/getfolderbyserverrelativeurl('EmployeePicture')/files/add(url='" + filename + "', overwrite=true)",  
  48.         method: "POST",  
  49.         binaryStringRequestBody: true,  
  50.         body: fileData,  
  51.         headers: {  
  52.             "accept": "application/json; odata=verbose",  
  53.             "X-RequestDigest": $("#__REQUESTDIGEST").val(),  
  54.             "content-length": fileData.byteLength  
  55.         },  
  56.         success: function (data) {  
  57.             alert("Success! Your Picture was uploaded to SharePoint.");  
  58.         },  
  59.         error: function onQueryErrorAQ(xhr, ajaxOptions, thrownError) {  
  60.             alert('Error:\n' + xhr.status + '\n' + thrownError + '\n' + xhr.responseText);  
  61.         },  
  62.         state: "Update"  
  63.     });  
  64. }    
  65. function uploadFile(uploadFileObj, empProfilePicture) {  
  66.     debugger;  
  67.     console.log('Log-1');  
  68.       
  69.     var fileName = uploadFileObj.name;  
  70.     fileName = empProfilePicture+"_" + fileName; // + date.format("ddmmyyyyHHmmss");  
  71.     var webUrl = _spPageContextInfo.webAbsoluteUrl;  
  72.     var documentLibrary = "EmployeePicture";  
  73.     //var folderName = "Folder1";  
  74.     var targetUrl = _spPageContextInfo.webServerRelativeUrl + "/" + documentLibrary + "/" //+ folderName;  
  75.   
  76.     var url = webUrl + "/_api/web/lists/getByTitle(@TargetLibrary)/RootFolder/files/add(url=@TargetFileName,overwrite='true')?" + "@TargetLibrary='" + documentLibrary + "'" + "&@TargetFileName='" + fileName + "'";  
  77.     debugger;  
  78.     console.log('Log-2');  
  79.     uploadFileToFolder(uploadFileObj, url, function (data) {  
  80.         var file = data.d;  
  81.         var updateObject = {  
  82.             __metadata: {  
  83.                 type: file.ListItemAllFields.__metadata.type  
  84.             },  
  85.             Name: 'Test Data',   //meta data column1  
  86.             Title: 'Test Data',    //meta data column2  
  87.         };  
  88.         console.log('Log-3');  
  89.         debugger;  
  90.         url = webUrl + "/_api/Web/lists/getbytitle('" + documentLibrary + "')/items(" + file.ListItemAllFields.Id + ")";  
  91.         url = webUrl + "/_api/Web/lists/getbytitle('" + documentLibrary + "')/items(5)";  
  92.   
  93.         updateFileMetadata(url, updateObject, file, function (data) {  
  94.             debugger;  
  95.             alert("File uploaded &amp; meta data updation done successfully");  
  96.         }, function (data) {  
  97.             debugger;  
  98.             alert("File upload done but meta data updating FAILED");  
  99.         });  
  100.     }, function (data) {  
  101.         alert("File uploading and meta data updating FAILED");  
  102.     });  
  103. }  
  104.   
  105.   
  106. function uploadFileToFolder(fileObj, url, success, failure) {  
  107.     var apiUrl = url;  
  108.     var getFile = getFileBuffer(fileObj);  
  109.     debugger;  
  110.     console.log('Log-2.1');  
  111.     getFile.done(function (arrayBuffer) {  
  112.         $.ajax({  
  113.             url: apiUrl,  
  114.             type: "POST",  
  115.             data: arrayBuffer,  
  116.             processData: false,  
  117.             async: false,  
  118.             headers: {  
  119.                 "accept": "application/json;odata=verbose",  
  120.                 "X-RequestDigest": jQuery("#__REQUESTDIGEST").val(),  
  121.             },  
  122.             success: function (data) {  
  123.                 console.log('Log-2.2');  
  124.                 success(data);  
  125.             },  
  126.             error: function (data) {  
  127.                 console.log('Log-2.3');  
  128.                 failure(data);  
  129.                   
  130.             }  
  131.         });  
  132.     });  
  133. }  
  134. function updateFileMetadata(apiUrl, updateObject, file, success, failure) {  
  135.     $.ajax({  
  136.         url: apiUrl,  
  137.         type: "POST",  
  138.         async: false,  
  139.         data: JSON.stringify(updateObject),  
  140.         headers: {  
  141.             "accept": "application/json;odata=verbose",  
  142.             "X-RequestDigest": $("#__REQUESTDIGEST").val(),  
  143.             "Content-Type": "application/json;odata=verbose",  
  144.             "X-Http-Method": "MERGE",  
  145.             "IF-MATCH": file.ListItemAllFields.__metadata.etag,  
  146.         },  
  147.         success: function (data) {  
  148.             success(data);  
  149.         },  
  150.         error: function (data) {  
  151.             failure(data);  
  152.         }  
  153.     });  
  154. }  
  155. function getFileBuffer(uploadFile) {  
  156.     var deferred = jQuery.Deferred();  
  157.     var reader = new FileReader();  
  158.     reader.onloadend = function (e) {  
  159.         deferred.resolve(e.target.result);  
  160.     }  
  161.     reader.onerror = function (e) {  
  162.         deferred.reject(e.target.error);  
  163.     }  
  164.     reader.readAsArrayBuffer(uploadFile);  
  165.     return deferred.promise();  
  166. }  
  167.    
  168. function DeleteListItemUsingItemId(Id) {  
  169.     var check = confirm("Are you sure you want to Delete ?");  
  170.     if (check == true) {  
  171.         $.ajax  
  172.             ({  
  173.                 url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('Employee')/items(" + Id + ")",  
  174.                 type: "POST",  
  175.                 headers:  
  176.                 {  
  177.                     "Accept": "application/json;odata=verbose",  
  178.                     "Content-Type": "application/json;odata=verbose",  
  179.                     "IF-MATCH": "*",  
  180.                     "X-HTTP-Method": "DELETE",  
  181.                     "X-RequestDigest": $("#__REQUESTDIGEST").val()  
  182.                 },  
  183.   
  184.                 success: function (data, status, xhr) {  
  185.                     console.log("Success");  
  186.                     getEmployeeListData();  
  187.                 },  
  188.                 error: function (xhr, status, error) {  
  189.                     console.log("Failed");  
  190.                 }  
  191.             });  
  192.   
  193.     }  
  194.     else {  
  195.         return false;  
  196.     }  
  197.   
  198. }  
  199.   
  200. function UpdateEmployeeListData(id) {  
  201.   
  202.     var title = $("#txtTitle").val();  
  203.     var EmpName = $("#txtEmpName").val();  
  204.     var gender = $("input[id='male']:checked").val();  
  205.     var EmpDOJ = $("#txtEmpDOJ").val();  
  206.   
  207.   
  208.     $.ajax  
  209.         ({  
  210.             url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Employee')/items(" + id + ")",  
  211.             type: "POST",  
  212.             data: JSON.stringify  
  213.                 ({  
  214.                     __metadata:  
  215.                     {  
  216.                         type: "SP.Data.EmployeeListItem"  
  217.                     },  
  218.                     Title: title,  
  219.                     EmpName: EmpName,  
  220.                     Gender: gender,  
  221.                     EmpDOJ: EmpDOJ,  
  222.   
  223.                 }),  
  224.             headers:  
  225.             {  
  226.                 "Accept": "application/json;odata=verbose",  
  227.                 "Content-Type": "application/json;odata=verbose",  
  228.                 "IF-MATCH": "*",  
  229.                 "X-HTTP-Method": "MERGE",  
  230.                 "X-RequestDigest": $("#__REQUESTDIGEST").val()  
  231.             },  
  232.             success: function (data, status, xhr) {  
  233.                 getEmployeeListData();  
  234.             },  
  235.             error: function (xhr, status, error) {  
  236.                 $("#ResultDiv").empty().text(xhr.responseJSON.error);  
  237.             }  
  238.         });  
  239. }  
  240. function InsertEmployeeListData() {  
  241.   
  242.     var title = $("#txtTitle").val();  
  243.     var EmpName = $("#txtEmpName").val();  
  244.     var gender = $("input[id='male']:checked").val();  
  245.     var EmpDOJ = $("#txtEmpDOJ").val();  
  246.     var Depid = $("#ddlDepartment").val();   
  247.     var files = $("#getFile")[0].files;  
  248.     var date = new Date();  
  249.     empProfilePicture = EmpName+date.format("ddmmyyyyHHmmss");  
  250.   
  251.     $.ajax  
  252.         ({  
  253.             url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/GetByTitle('Employee')/items",  
  254.             type: "POST",  
  255.             data: JSON.stringify  
  256.                 ({  
  257.                     __metadata:  
  258.                     {  
  259.                         type: "SP.Data.EmployeeListItem"  
  260.                     },  
  261.                     Title: title,  
  262.                     EmpName: EmpName,  
  263.                     Gender: gender,  
  264.                     EmpDOJ: EmpDOJ,  
  265.                     EmpProfilePicture: empProfilePicture,  
  266.                     DeptNameId: Depid  
  267.                 }),  
  268.             headers:  
  269.             {  
  270.                 "Accept": "application/json;odata=verbose",  
  271.                 "Content-Type": "application/json;odata=verbose",  
  272.                 "X-RequestDigest": $("#__REQUESTDIGEST").val()  
  273.             },  
  274.             success: function (data, status, xhr) {  
  275.   
  276.                 uploadFile(files[0], empProfilePicture); // uploading singe file  
  277.                 getEmployeeListData();  
  278.             },  
  279.             error: function (xhr, status, error) {  
  280.                 $("#ResultDiv").empty().text(xhr.responseJSON.error);  
  281.             }  
  282.         });  
  283.   
  284.   
  285. }  
  286.   
  287. function getEmployeeListData() {  
  288.     var fullUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('Employee')/items";  
  289.     $.ajax({  
  290.         url: fullUrl,  
  291.         type: "GET",  
  292.         headers: {  
  293.             "accept": "application/json;odata=verbose",  
  294.             "content-type": "application/json;odata=verbose",  
  295.         },  
  296.         success: onQueryEmpSucceeded,  
  297.         error: onQueryEmpFailed,  
  298.     });  
  299. }  
  300.   
  301. function onQueryEmpSucceeded(data) {  
  302.     var listItemInfo = '';  
  303.     var divTableBody = "";  
  304.     var divTableHeader = "<table id='example' class='display' style='width:100%'>" +  
  305.         "<thead><tr><th>Action</th><th>Id</th><th>Title</th><th>EmpName</th><th>Gender</th><th>EmpDOJ</th></tr></thead><tbody>";  
  306.     $.each(data.d.results, function (key, value) {  
  307.   
  308.         divTableBody = divTableBody + "<tr><td> <a href='javascript: DeleteListItemUsingItemId(" + value.Id + ")'>Delete </a> || <a href='javascript: UpdateEmployeeListData(" + value.Id + ")'>Edit </a></td><td>" + value.Id + "</td><td>" + value.Title + "</td><td>" + value.EmpName + "</td><td>" + value.Gender + "</td><td>" + value.EmpDOJ + "</td></tr>"  
  309.   
  310.         //listItemInfo += '<b>Title:</b> ' + value.Title + ' – <b>EmpName:</b> ' + value.EmpName  
  311.         //  + '<b>Gender:</b>' + value.Gender + '<b>EmpDOJ:</b>' + value.EmpDOJ + '' + '<br />';  
  312.     });  
  313.     listItemInfo = divTableHeader + divTableBody + "</tbody><tfoot><tr><th>Action</th><th>Id</th><th>Title</th><th>EmpName</th><th>Gender</th><th>EmpDOJ</th></tr></tfoot></table>";  
  314.     $("#divResults").html(listItemInfo);  
  315.     $('#example').DataTable();  
  316. }  
  317.   
  318. function onQueryEmpFailed() {  
  319.     alert('Error!');  
  320. }  
  321.   
  322. function addDepartment() {  
  323.     var fullUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getByTitle('Department')/items";  
  324.     $.ajax({  
  325.         url: fullUrl,  
  326.         type: "GET",  
  327.         headers: {  
  328.             "accept": "application/json;odata=verbose",  
  329.             "content-type": "application/json;odata=verbose",  
  330.         },  
  331.         success: function onQueryEmpSucceeded(data) {  
  332.             $.each(data.d.results, function (key, value) {  
  333.                 $('#ddlDepartment').append(new Option(value.DeptName, value.Id));  
  334.             });  
  335.   
  336.         },  
  337.         error: onQueryEmpFailed,  
  338.     });  
  339. }  
Output
 
How To Save Lookup Field Values In SharePoint Using Rest API
Note
For the lookup type field, the value will be Integer and must be the ID of Lookup item. In the request body, you have to specify it as,
 
InternalNameOfTheColumn + Id
Meaning that, If your field's internal name is DeptName, in the request, it will be DeptNameId. Value of DeptNameId will be an Integer (Id of the lookup item).
 

Summary

 
In this article, we have seen the step-by-step implementation of the lookup field  and also pictured the  upload.
 
I hope this helps. If this helps you then share it with others.
 
Sharing is caring! :)


Similar Articles