0

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.

0

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".

enter image description here

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.

enter image description here

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>

enter image description here

| improve this answer | |
  • 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?. – Bhuvana Sundar Sep 19 '19 at 15:06
  • If you don't want to make the fields be edited by users, you can check the updated code above. – LZ_MSFT Sep 20 '19 at 1:23
  • Sure. Let me try updated one. Can there be a popup if employee code is wrong too? – Bhuvana Sundar Sep 20 '19 at 1:49
  • Add Alert("message") when data.d.results.length==0. – LZ_MSFT Sep 20 '19 at 2:13
  • Hello!! After dropping this updated code, values are not getting reflected in fields and only name looks not editable. – Bhuvana Sundar Sep 20 '19 at 9:37
0

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.

| improve this answer | |
  • 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? – Bhuvana Sundar Sep 18 '19 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 – Mike2500 Sep 18 '19 at 20:51
  • Yeah I have seen that... But still im not a developer... So how should I code it? – Bhuvana Sundar Sep 18 '19 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/… – Mike2500 Sep 18 '19 at 21:07
  • Sure ill try and let u know. – Bhuvana Sundar Sep 18 '19 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.