SharePoint Designer Workflow : Set the Custom List Field using LookUp Feild in SPD Workflow

Here, we need to set the Custom list Feild using Lookup item Value.

For that we used Sharepoint Workflow 2010 version and as we got some error to getting the Lookup value as a Text in the SharePoint Workflow 2013.

The Workflow is as below, this is the final workflow :

To set the  lookup item , we need to set the values as below.

And to set the Find the list Item section, we need to set the value as below  :

Then, we need to save and publish the workflow.
And also need to do some settings when we need to fire the workflow as per our requirement.

Filter the Lookup feild data based on some condition

We may have some requirement that we do not want to show all data of the lookup to every one.

I have some scenario as below :

I have the Master custom list named OfficeMaster and I use the lookup using this in Child list 'FacilityStatus'.

OfficeMaster Custom also had one more column named OfficeAdmin , which have the office Admin name, who can add data for this office.

In that, I just want to show that Office To admin , which office is assigned to him in the OfficeMaster List.

For that, I used the SPServices named "SPFilterDropdown()".

So, when Office Admin Login into the site, he can only see that offices whom Admin he is.

$().SPServices.SPFilterDropdown({
                                                  relationshipList: "OfficeMaster",
                                                  relationshipListColumn: "OfficeLocation",
                                                  columnName: "OfficeName",                                                                                                
                                                                                             
                                                  CAMLQuery: "<Eq><FieldRef Name='OfficeAdmin' /><Value Type='User'>"+username+"</Value></Eq>",
                                                  completefunc: null,
                                                  debug: true
                                                });
 
 

Here , username parameter is used which uses the users username. I am using the ECMA script for that and put that code above that.

this.context= SP.ClientContext.get_current();

            if (this.context!= undefined && context!= null) {

//assume we have a client context called context.

var web = context.get_web();

var user = web.get_currentUser(); //must load this to access info.

context.load(user);

//alert('start In Middle');

context.executeQueryAsync(function(){

  // alert("User is: " + user.get_title()); //there is also id, email, so this is pretty useful.

   

   username=user.get_title();

}, function(){alert(":(");});

}

ECMA Script : Can we pass some parameter in the function ?

Yes, we can pass some parameter in the ECMA Script function as normal as we are doing in the javascript function.


<script type="text/javascript" >

$(document).ready(function() {

$("Select[Title='OfficeName']").change(function() {

getLookUp($("Select[Title='OfficeName']").val());

});




});

</script>

<script language-="ecmascript" type="text/ecmascript">

 function getItemById(OfficeName)
{
        // get the Item for using the OfficeName.
}

</script>

ECMA Script : Call the ECMA Script funcation when page loads

If we want to call the ECMAScript function on page load , we can get the error like
"Unable to get property 'get_current' of undefined or null reference"

Because , before sp.js loads which is required to run our ECMAScript function, was not loaded and our ECMA funcation calls.

So, we need to wait untill our sp.js loads.
So, we need to call that function as below :


$(document).ready(function(){                                            
   var reslt= ExecuteOrDelayUntilScriptLoaded( getUserTitle, "sp.js");
});
                 

                });


function GetUserTitle()
{
          // function defination
}          

          

ECMA Script : Get list Item Count

In the below code, using ECMA Script , we get the data from some custom list and after that we can count the number of items in ECMA Script.




function retrieveListItems() {
var clientContext = SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('FacilityStatus');
 
    var camlQuery = new SP.CamlQuery();


    camlQuery.set_viewXml('<View><Query><Where><And><Eq><FieldRef Name=\'Process\' /><Value Type=\'Lookup\'>Physical Space Allocation</Value></Eq><Eq><FieldRef Name=\'FacilityName\' /><Value Type=\'Lookup\'>Training Room</Value></Eq></And></Where></Query></View>');

    this.collListItem = oList.getItems(camlQuery);
           
    clientContext.load(collListItem);
   

    clientContext.executeQueryAsync( function () {  count= collListItem.get_count(); } ,function(){alert(":(");});              
}

</script>

Append Data on Choice Field by getting the data using ECMASCript in SharePoint 2013

Here we are getting some data from the custom list and show that data in the choice filed in the list form.
To Get the data from the custom list, we are using ECMAScript and then we store that data in some collection and then we append that data one by one to the Choice filed using javascript.

Here we accept that, we put the reference of the jquery file in the page.


<script language-="ecmascript" type="text/ecmascript">
       
       
        var siteUrl = '/';
       
        var ItemContainer = { ItemList: [] };
       
        function retrieveListItems(lookupItem) {
       
        //alert('Hi..RetriveListItems');
        ItemContainer = { ItemList: [] };
        var clientContext = SP.ClientContext.get_current();
        // Get the SharePoint list by giving the display name of the list.
        var oList = clientContext.get_web().get_lists().getByTitle('Process');
        // To retreive all items use the predefined createAllItemsQuery operation.
        //alert(oList);
       
       // var camlQuery = SP.CamlQuery.createAllItemsQuery();

        var camlQuery = new SP.CamlQuery();
        camlQuery.set_viewXml("<View><Query><Where><Eq><FieldRef Name='OfficeType'/><Value Type='Lookup'>"+lookupItem+"</Value></Eq></Where></Query></View>");
       
        this.collListItem = oList.getItems(camlQuery);
        clientContext.load(collListItem);
       
        //alert('Go to call Method');
        this.clientContext.executeQueryAsync(
            Function.createDelegate(this, this.onListDataLoadQuerySucceeded),
            Function.createDelegate(this, this.onListDataLoadQueryFailed));
    }
   
   
     // Callback function if the item retrieval Async call get successful.
    function onListDataLoadQuerySucceeded(sender, args) {
   
   
    //alert('On Success Method');
        var listItemInfo = "";
      var  listItemEnumerator="";
        var listItemEnumerator = collListItem.getEnumerator();
        while (listItemEnumerator.moveNext()) {
            var oListItem = listItemEnumerator.get_current();
            // Fill a json object with Id and Value properties.
            var tempItem = { Id: oListItem.get_id(), Value: oListItem.get_item('Title') };
            ItemContainer.ItemList.push(tempItem);
        }
       
       
        // Fill the drop down with retrieved data.
        fillDropDown();
    }
 
 
    // Fill  the drop down with the retrieved list item data.
    function fillDropDown() {
   
    //alert('in fill dropdown');
    //alert(ItemContainer.ItemList.length);
    $("Select[Title='Process']").empty();
        var ddlCategory = $("Select[Title='Process']");
       
       
       
        if (ddlCategory != null) {
       
       // alert('In If loop .. found the dropdown');
        var newOption="<option value='0'>Choose Process</option>";
        $("Select[Title='Process']").append(newOption);
       
       
      //  alert(newOption);
       
            for (var i = 0; i < ItemContainer.ItemList.length; i++) {
           
                var theOption = new Option;
               
               // alert(ItemContainer.ItemList.length);
                //alert(ItemContainer.ItemList[i].Id);
                theOption.value = ItemContainer.ItemList[i].Id;
               
               
                //alert(ItemContainer.ItemList[i].Value);
                theOption.text = ItemContainer.ItemList[i].Value;
               
                //alert(theOption.text);
               
              //alert(ddlCategory.Items.length);
             
              //alert(ddlCategory.options[i]);
               
               // ddlCategory.options[i] = theOption;
                                               
var newOption = "<option value='"+theOption.value+"'>"+theOption.text+"</option>";

// alert(newOption);


$("Select[Title='Process']").append(newOption);





               
               
                //alert('success for dropdown');
               
                //alert(ddlCategory.options[i]);
            }
        }
    }
   
   
  function   onListDataLoadQueryFailed(sender, args)
  {
 
  //alert('In Fail');
  }
       
       
        </script>

SPSerives : ForEach kind of condition using GetlistItems

We can create the For each kind of looping using SPServies as per the below code.



function GetAvabilityStateWise()
{

var Qstring="<table style='border-collapse: collapse;'><tr ><th style='border: 1px solid black;'><b>StateName</b></th><th style='border: 1px solid black;'><b> %Availability of Services </b></th> <tr>";
$().SPServices({
                    operation: "GetListItems",
                    async: false,
                    listName: "StateMaster",
                    CAMLViewFields: "<ViewFields><FieldRef Name='Title'/></ViewFields>",
                    CAMLQuery: "",
                    //CAMLRowLimit: 2,
                    completefunc: function (xData, Status) {
                                   $(xData.responseXML).SPFilterNode("z:row").each(function() {
                                     
  var t  =$(this).attr("ows_Title");                                              
                                     
                                       var total= GetTotalCountForState(t);

  var avlb = GetAvilableTotalCountForState(t);

var per;

if(avlb != 0 && total != 0)
per = (avlb/total)*100;
else
per=0;


 
//Qstring += t + "         " + per  + "%<br/>";
Qstring +=  "<tr><td style='border: 1px solid black;'>" + t + "</td><td style='border: 1px solid black;'>" + per  + "%</td>";
                                                                                                               
                                                });
                                }
                });
               
         // alert(Qstring);
       
        $("#lblReport1").html(Qstring);
       
//document.getElementById('myId').innerHTML                                                                      
       
       

}


function GetTotalCountForState(StateName)
{
    var itemCount=0;
 
    var queryText = "<Query><Where><Eq><FieldRef Name='State' /><Value Type='Lookup'>"+StateName+"</Value></Eq></Where></Query>";
 
    $().SPServices({
        operation: "GetListItems",
        listName: "FacilityStatus",
        async: false,
        CAMLQuery: queryText,
        completefunc: function (xData, status) {

            //alert(xData.responseXML);
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
           // alert(itemCount);

          //  $(".TextBox3").val(itemCount);

        }
       
     
    });
   
    return itemCount ;
           }




function GetAvilableTotalCountForState(StateName)
{


 
    var itemCount=0;
 
//var queryText = "<Query><Where><And><Eq><FieldRef Name='State' /><Value Type='Lookup'>Rajasthan</Value></Eq><Eq><FieldRef Name='ServiceStage' /><Value Type='Lookup'>Services Available</Value></Eq></And></Where></Query>";
   
var queryText = "<Query><Where><And><Eq><FieldRef Name='State' /><Value Type='Lookup'>"+StateName+"</Value></Eq><Eq><FieldRef Name='ServiceStage' /><Value Type='Lookup'>Services Available</Value></Eq></And></Where></Query>";
 
    $().SPServices({
        operation: "GetListItems",
        listName: "FacilityStatus",
        async: false,

        CAMLQuery: queryText,

        completefunc: function (xData, status) {

           // alert(xData.responseXML);
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");
            //alert("Avilable total for OfficeType "+ OfficeType +"is : " +itemCount);

          //  $(".TextBox3").val(itemCount);

        }
       
     
    });
   
    return itemCount ;
           }

SPServices : GetListItmes : Get Total Count of the Items

To Get the Total Number of items from the custom list which satisfy our caml query, the code is as below :

var queryText = "<Query><Where><Eq><FieldRef Name='OfficeType' /><Value Type='Lookup'>"+OfficeType+"</Value></Eq></Where></Query>";

$().SPServices({
        operation: "GetListItems",
        listName: "FacilityStatus",
        async: false,
        CAMLQuery: queryText,
        completefunc: function (xData, status) {          
            itemCount = $(xData.responseXML).SPFilterNode("rs:data").attr("ItemCount");           
        }
     
   
    });
 
    return itemCount ;
           }

Cascading DropDown Using SPServices in SharePoint 2013

For Cascading Drop down, first of all we need to refer two scripts as shown in the code and also need to store that files at the proper path in Sharepoint Site content or in any other library.

If we need to put the cascading dropdown in the List forms, we need to put the below code at the relative NewForm.aspx and EditForm,aspx in the ListForms and put the below code at the "PlaceHolderAdditionalPageHead" .


<asp:Content ContentPlaceHolderId="PlaceHolderAdditionalPageHead" runat="server">

<script language="javascript" type="text/javascript" src="../../JSLibrary/jquery-1.7.2.min.js"></script>
<script language="javascript" type="text/javascript" src="../../JSLibrary/jquery.SPServices-0.7.1a.min.js"></script>

<script type="text/javascript">

$(document).ready(function() {
$().SPServices.SPCascadeDropdowns({
relationshipList: "Facilities",
relationshipListParentColumn: "Process",
relationshipListChildColumn: "Title",
parentColumn: "Process",
childColumn: "FacilityName",
promptText: "Choose Facility...",
debug: true

});

});
</script>

</asp:content>