I have a free text of employee number in task form (Linked to form), when employee number is entered, I want to auto populate name and employee department. I can have source data in excel or separate list. I don't have access to SharePoint designer or InfoPath. This is to ensure employee is not entering his employee code wrongly. If he enters it wrongly, that will affect my reports in Power BI.
2 Answers
The following example for your reference.
1.Create a custom list "Employee" to store the employee data.
2.Modify the default "Title" field name to "Number" and add two text field "Name", "Department".
3.Add the code below into script editor web part in task form page.
<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
var empNumField="employee number";
var empNameField="employee name";
var EmpDepField="employee department";
$(".ms-standardheader:contains('"+empNumField+"')").closest("tr").find("input").focusout(function(){
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Employee')/items?$filter=Title eq '"+$(this).val()+"'",
type: "GET",
headers: {"Accept": "application/json;odata=verbose" },
success: function(data){
if(data.d.results.length>0){
var employee=data.d.results[0];
$(".ms-standardheader:contains('"+empNameField+"')").closest("tr").find("input").val(employee.Name);
$(".ms-standardheader:contains('"+EmpDepField+"')").closest("tr").find("input").val(employee.Department);
}
},
error: function () {
//console.log("Failed to get details");
}
});
});
});
</script>
4.Enter the employee number in the "employee number" field, after the field focus out, the "employee name" and "employee department" fields will auto populated.
If you don't want to make the "employee name" and "employee department" fields read only, you can modify the code as below.
<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
$(function () {
var empNumField="employee number";
var empNameField="employee name";
var empDepField="employee department";
$(".ms-standardheader:contains('"+empNameField+"')").closest("tr").find("input").prop("disabled", true);
$(".ms-standardheader:contains('"+empDepField+"')").closest("tr").find("input").prop("disabled", true);
$(".ms-standardheader:contains('"+empNumField+"')").closest("tr").find("input").focusout(function(){
$.ajax({
url: _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('Employee')/items?$filter=Title eq '"+$(this).val()+"'",
type: "GET",
headers: {"Accept": "application/json;odata=verbose" },
success: function(data){
if(data.d.results.length>0){
var employee=data.d.results[0];
$(".ms-standardheader:contains('"+empNameField+"')").closest("tr").find("input:disabled").val(employee.Name);
$(".ms-standardheader:contains('"+empDepField+"')").closest("tr").find("input:disabled").val(employee.Department);
}else{
alert("Please check the employee number.");
}
},
error: function () {
//console.log("Failed to get details");
}
});
});
});
</script>
-
Yes, That works satisfactorily. I want to know if I can make the Name and department to black out that it cant be edited by users after it is populated?. Sep 19, 2019 at 15:06
-
If you don't want to make the fields be edited by users, you can check the updated code above.– LZ_MSFTSep 20, 2019 at 1:23
-
Sure. Let me try updated one. Can there be a popup if employee code is wrong too? Sep 20, 2019 at 1:49
-
-
Hello!! After dropping this updated code, values are not getting reflected in fields and only name looks not editable. Sep 20, 2019 at 9:37
If you're in SharePoint online, perhaps you have access to PowerApps? This would be a fairly straightforward PowerApps project. You would simply need to add a data connection for the other list, and then use PowerApps' Lookup function to retrieve the desired fields from the list, based on the value of the textbox.
Otherwise, if you're in a classic site, you could add a content editor or script editor web part to the page and write javascript to retrieve and set those values.
-
No, SP Designer, InfoPath are not allowed in my org due to some security issues and Flow and Power Apps roll out in Q4 2019. But I think, I can use classic site. But How? Sep 18, 2019 at 20:43
-
There are "classic sites" and "modern sites". Perhaps do an image search to see the differences to determine what kind of site you have: bing.com/images/search?q=sharepoint+classic+modern+pages– Mike2500Sep 18, 2019 at 20:51
-
Yeah I have seen that... But still im not a developer... So how should I code it? Sep 18, 2019 at 20:55
-
Otherwise, the JavaScript option, while doable, is definitely a developer level skill set. If you're comfortable with that, then here's a doc on getting started with the SharePoint JavaScript object model: blogs.msdn.microsoft.com/sharepointdev/2011/04/14/…– Mike2500Sep 18, 2019 at 21:07
-