Search This Blog

Monday, June 12, 2017

SharePoint 2013 Online joining two Lists - JavaScript

Step 1: add script editor
Step 2: Copy and Paste below code
Step 3; Make sure Employee, Department List is available in your site

<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
<style type="text/css">
    table.table-style-two {
        font-family: verdana, arial, sans-serif;
        font-size: 11px;
        color: #333333;
        border-width: 1px;
        border-color: #3A3A3A;
        border-collapse: collapse;
    }

        table.table-style-two th {
            border-width: 1px;
            padding: 8px;
            border-style: solid;
            border-color: #517994;
            background-color: #B2CFD8;
        }

        table.table-style-two tr:hover td {
            background-color: #DFEBF1;
        }

        table.table-style-two td {
            border-width: 1px;
            padding: 8px;
            border-style: solid;
            border-color: #517994;
            background-color: #ffffff;
        }
</style>
<script type="text/javascript">
    var row = "";
    var listId = "1";
    //We need to run our function only after sp.js file loads
    ExecuteOrDelayUntilScriptLoaded(ViewItem, "sp.js");
    function ViewItem() {
        var context = new SP.ClientContext.get_current();
        var web = context.get_web();
        var list = web.get_lists().getByTitle('Employee');
        var list1 = web.get_lists().getByTitle('Department');
        //It will fetch all the items from the list
        var myquery = new SP.CamlQuery.createAllItemsQuery();
        myItems = list.getItems(myquery);
        var myquery1 = new SP.CamlQuery.createAllItemsQuery();
        myItems1 = list1.getItems(myquery1);
        context.load(myItems);
        context.load(myItems1);
        //If the items load correctly then success function will be called
        context.executeQueryAsync(Function.createDelegate(this, this.success), Function.createDelegate(this, this.failed));
    }
    function success() {
        //alert(listId);
        var text = [];
        var text1 = [];
        var text3 = [];
        var newObj = { data: [] };
        var ListEnumerator = this.myItems.getEnumerator();
        var ListEnumerator1 = this.myItems1.getEnumerator();
        var count = 0;
        while (ListEnumerator.moveNext() && ListEnumerator1.moveNext()) {
            var currentItem = ListEnumerator.get_current();
            var currentItem1 = ListEnumerator1.get_current();
            newObj.data.push({
                "ID": currentItem.get_item('ID'),
                "EmployeeName": currentItem.get_item('Title'),
                "DepartmentName": currentItem1.get_item('Title')              
            });
        }
        $.each(newObj.data, function (key, item) {
            if (listId == 1) {
                     row = row + '<tr><td><a onclick="GetListItemsById(' + item.ID + ');" href="#">' + item.EmployeeName + '</a></td><td>' + item.DepartmentName + '</td></tr>';
            }
            else {
                if (listId == item.DocID) {
                    document.getElementById("EmployeeName").value = item.EmployeeName;
                    document.getElementById("DepartmentName").value = item.DepartmentName;
                }
            }
        })      
        if (listId == 1) {
            $("#GetItems>tbody").html(row);
            $("#GetItems").show();
        }

    }
    function failed(sender, args) {
        alert("failed. Message:" + args.get_message());
    }
    function GetListItemsById(id) {
        listId = id;
        $("#GetItems").hide();
        $("#upItems").show();
        ViewItem();
    }
    //----insert
    function createListItem() {

        var context = new SP.ClientContext.get_current();
        var web = context.get_web();
        context.load(web);
        //create list and add column : DepartmentName(text)
        var oList = web.get_lists().getByTitle('NewEmployeeList');
        context.load(oList);
        var itemCreateInfo = new SP.ListItemCreationInformation();
        this.oListItem = oList.addItem(itemCreateInfo);
        oListItem.set_item('Title', document.getElementById("EmployeeName").value);
        oListItem.set_item('DepartmentName', document.getElementById("DepartmentName").value);
        oListItem.update();

        context.load(oListItem);

        context.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
    }

    function onQuerySucceeded() {

        alert('Item created: ' + oListItem.get_id());
    }

    function onQueryFailed(sender, args) {

        alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
    }

</script>

<table id="GetItems" class="table-style-two">
    <thead>
        <tr>
            <td>Employee Name</td>
            <td>Department Name</td>
        </tr>
    </thead>
    <tbody></tbody>
</table>
<table id="upItems" style="display: none">
    <tr>
        <td>Employee Name:</td>
        <td>
            <input type="text" id="EmployeeName" />
        </td>
    </tr>
    <tr>
        <td>Department Name:</td>
        <td>
            <input type="text" id="DepartmentName" />
        </td>
    </tr> 
    <tr>
        <td></td>
        <td>
            <input type="button" value="Insert" onclick="createListItem()" />
        </td>
    </tr>
</table>