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.

2 Answers 2

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

5
  • 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_MSFT
    Sep 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
  • Add Alert("message") when data.d.results.length==0.
    – LZ_MSFT
    Sep 20, 2019 at 2:13
  • 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
1

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.

7
  • 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
    – Mike2500
    Sep 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/…
    – Mike2500
    Sep 18, 2019 at 21:07
  • Sure ill try and let u know. Sep 18, 2019 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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