Cascading of Lookup Dropdown Fields on SharePoint 2013 and Office 365

Welcome to an article on the ‘Cascading of Dropdown fields on SharePoint 2013 & Office 365’. This is a major approach when we build forms and when we have to use multiple look up fields on the form and they should be related to each other as a parent and child relationship.

So what happens is, when you select a parent, other child items are related to it. So if you select a particular parent, all the child items related to the parent will appear.

Let’s see it.

• Create a list named “Company”.

• Click on Add an app,
  

• Choose the Custom List App.

  

• Click on it and create a ‘Company’ List.

• Once the list is created, add in the names of the companies.
  
  
  
• As per the screenshot below. 

• Create another list named as “Employee”
  
  
  
• Once the list is created, Create a look up column named as Company from our Company List.
  
  

• As you can see below, Clock on Create Column and the choose Lookup.
  
  
  
• Select the list as Company and choose the Title field while configuring the lookup column.
  
  
  
• So here we can see our look up column has values from another list.
  
  
  
• Fill in your required values; as for mine, I have filled in the name of the employees under the Title and have selected the company names from my look up field.

 

• Now create a third list as Database.
  
  
  
  
  
• Add the two look up columns, Company from the company list, and select title as the source of the column as per the screen shot below.
  
  
  
• Add the second look up columns; Employee from the employee list as per the screen shot below, selecting title as the field of source column.
  
  
  
• So this will be your blank list view with all the columns,
  
  
  
• When you click on New Item as you have added both the column as lookup they should display all the values but no, we are controlling their values as parent and child relationship as per the screen below.
  
  - If you select Company as “CTS”, the employee sees Manpreet as the value.

• 
  
  
  
  - Here when you select “Infy” as the Company name, Baghel is displayed as the Employee.
  
  
  
• How was it done?
  
  - Go to the Database list.
  
  - Under the list tab select “Default New Form”.
  
  
  
  - Add a ‘Script Editor’ web part and paste the code below.
  
  
  
  Code:
  
  1. <script src="https://site name/jquery.min.js"></script>  
  2. <script src="https://sitename/jquery.SPServices.min.js"></script>  
  3. <script type="text/javascript">  
  4. $(document).ready(function ()  
  5. {  
  6.     $().SPServices.SPCascadeDropdowns(  
  7.     {  
  8.         relationshipList: "Employee",  
  9.         relationshipListParentColumn: "Company",  
  10.         relationshipListChildColumn: "Title",  
  11.         parentColumn: "Company",  
  12.         childColumn: "Employee",  
  13.         debug: true  
  14.     });  
  15. });  
  16. </script>  
• Click on OK and come back to the Database List and click on New Form.
  
• Your Cascading will start functioning.

Here we saw today an amazing functionality of Cascading of lookup Dropdown fields on SharePoint 2013 & Office 365. Keep reading more articles and keep learning.

Query String (URL) Filter Web part

The web part is very useful to display the list results based on filters applied. You can create a page in Site Pages library. Add list view web part to the page and below that add 'Query String (URL) Filter ' Web Part below that. In Web Part settings for Query String (URL) Filter Web part, specify the column for which you need to apply filter.

Follow the below links
https://support.office.com/en-us/article/Connect-a-Query-String-URL-Filter-Web-Part-to-another-Web-Part-8fd0107f-4889-451b-a7f7-d1996e12a8d1?ui=en-US&rs=en-US&ad=US&fromAR=1

Read value of controls in SharePoint form

Hi folks,

I got a pre-defined function which helps to read value of controls in SharePoint form. To find a control in SharePoint from client side Javascript we normally use the getTagFromIdentifierAndTitle function.

function getTagFromIdentifierAndTitle(tagName, identifier, title) {
   var len = identifier.length;
   var tags = document.getElementsByTagName(tagName);
    
   for (var i = 0; i < tags.length; i++) {
  var tempString = tags[i].id;
 if (tags[i].title == title && (identifier == "" || 
          tempString.indexOf(identifier) == tempString.length - len))
        {
           return tags[i];
 }
   }
   return null;
}

Tag is the html tag used to define the control, Identifier is the type of the control and title is the name of the control. Below are basic tag and identifiers of the server side controls.
Control                 Tag               Identifier
Panel                 div  
Label                 label  
Button                 input               button
Link Button         href  
Hyperlink         href  
Image Button         input               image
Textbox                 input               text
Textbox (Password) input               password
Textbox (Multiline) Input               textarea
DropDownList         select  
Listbox                 select  
RadioButton         input               radio
Checkbox         input               checkbox
File                 input               file

Please find the link below for more information
https://workflowssimplified.wordpress.com/2011/09/15/gettagfromidentifierandtitle-function/ 

JavaScript Navigation tricks in SharePoint forms

1. Navigate from NewForm.aspx to DisplayForm.aspx in SharePoint after clicking Save button.

Under PreSaveAction function add the below steps

var URL = location.pathname.replace('NewForm.aspx','DispForm.aspx');
if(GetUrlKeyValue('IsDlg')==='1'){
URL+="?IsDlg=1";
}
$("#aspnetForm").attr('action',location.pathname+"?Source="+URL);
return true;

2. Navigate from DisplayForm.aspx in one list to NewForm.aspx in another list with few of the fields populated.

HTML:
<span  style="font-size:x-large">To continue to phase 3, please <a href="#" target="_blank" id="Phase3Link">click here</a>.</span>

JavaScript:
$(document).ready(function(){
var requestID = $("#RequestID").text();

var phase3NewFormURL = "https://accobrands.sharepoint.com/sites/Departments/USOpsSC/OracleISRT/Lists/IMCRP3/P3NewForm.aspx?ReqId=" + requestID + "&Source=https://accobrands.sharepoint.com/sites/Departments/USOpsSC/OracleISRT/Pages/MyReqs.aspx";

$("#Phase3Link").attr("href", phase3NewFormURL);
//SetItemNumber(requestID, itemNumberTCell);

});

In P3NewForm.aspx

$(document).ready(function(){
//$("#imStatus").hide();
GetCurrentUserName(false, false);
/* Parse query string and set Request ID */
var requestIDQueryString = GetUrlKeyValue("ReqId");
var requestIDField = $("input[title='Request ID']");
requestIDField.val(requestIDQueryString);

requestIDField.prop("readonly", "true");

});

FIX: SharePoint 2013 Workflow recursion prevention – Part 2

Following FIX: SharePoint 2013 Workflow recursion prevention – Part 1, this post will walk you through the processes of designing your workflow that creates a list item on another list and invokes the workflow associated with that list using the new REST APIs released through SharePoint 2013 May 2014 CU as pointed out in part 1 of this series.  If you haven’t read through part 1 of this series I suggest you do to get some background that will help you appreciate this post.

The scenario:

List1 has a workflow Workflow1 associated with it.

Workflow1 can be set to auto-start on item adding/updating or set to manually start or both, it doesn’t matter.

Workflow1 is designed to create a list item on List2.

List2 has a workflow Workflow2 associated with it.

Workflow2 should have manually start workflow option enabled. (Allow this workflow to be manually started)

Workflow2 simply logs the title of the list item in workflow history list.

NOTE: Both Workflow1 and Workflow2 are built on 2013 workflow platform.

Publish both the workflows and create an item on List1.  Manually start Workflow1 on the list item and you should see that it completes successfully.

Now browse to List2 and you will see that a new list item was successfully created by Workflow1.

The problem:

But Workflow2 would never start off.  Refer part 1 of this series, check ULS logs and you’ll see Workflow2 didn’t start because of workflow recursion prevention.

Fix:

The fix is to leverage the new REST APIs exposed to start off Workflow2 from Workflow1.  Let’s open Workflow1 in SharePoint Designer’s workflow editor.

We drop a “Build Dictionary” action just above the create item in list action with the following name/value pairs of type string.

accept: application/json;odata=verbose

content-type: application/json;odata=verbose

Choose the dropdown against “(Output to Variable: dictionary)” and create a variable to type Dictionary named “ReqHeader”.

Choose the dropdown against “(Output to Variable: create)” and create a new variable named “NewItemGuid”.  It will be of type GUID.

Drop a “Call HTTP Web Service” action after the create item in list action.

Choose the dropdown against “(Output to Variable: dictionary)” and create a variable to type Dictionary named “ReqHeader”.

Choose the dropdown against “(Output to Variable: create)” and create a new variable named “NewItemGuid”.  It will be of type GUID.

Drop a “Call HTTP Web Service” action after the create item in list action.

Choose the dropdown against “(Output to Variable: dictionary)” and create a variable to type Dictionary named “ReqHeader”.

Choose the dropdown against “(Output to Variable: create)” and create a new variable named “NewItemGuid”.  It will be of type GUID.

Drop a “Call HTTP Web Service” action after the create item in list action.

Choose “Properties” option from the context menu of “Call HTTP Web Service” action.

Set “RequestHeaders” to the request header variable you created and create a new variable of type dictionary named “RespContent” for “ResponseContent” as shown below.  Hit OK.

Drop a “Get an Item from a Dictionary” action after “Call HTTP Web Service” action and click “item by name or path” hyperlink.  In the editor type d/results(0).

Click the dictionary hyperlink and choose “Variable: RespContent” from the dropdown.  Click the item hyperlink and again choose “Variable: RespContent” from the dropdown.  End result will be.

There are plenty of articles out there that talk about how to parse the JSON to get to the actual data we need so I am not going to cover that here.  But remember we can use Fiddler to determine the structure of JSON result and we’ll use that knowledge to decide on how we will get to the result we need (for e.g., d/results(0)).

What we did above is that we want the value at d/results(0) from the RespContent dictionary variable we get back from the web service call.  We are then storing the result again back in RespContent because the result is also of type dictionary.

Drop another “Get an Item from a Dictionary” action.  Click on “item by name or path” hyperlink and type Id.  Click “dictionary” hyperlink and choose “Variable: RespContent”.  Click the item hyperlink in “(Output to item)” and create a new variable named “NewItemId” of type integer as shown below.

We did all these to get the ID (the sequence number) value of the list item we just created.  Phew!!!  This is because we need to know the ItemID of the list item we just created as we need to pass it to the StartWorkflowOnListItemBySubscriptionId REST method later.  But we are just 30% into our design 

The other parameter that StartWorkflowOnListItemBySubscriptionId needs is the workflow SubscriptionId.

We drop another “Call HTTP Web Service” action and construct our next REST call.  This time we call/_api/web/lists/GetByTitle(‘List2’).  We set the same “ReqHeader” variable to this call.  And set the same “RespContent” variable as we did before.  The end result you should have in “Call HTTP Web Service” dialog is shown below.

And in designer we should see.

We then add another “Get an Item from a Dictionary” action.  Ask for d/Id from “RespContent” variable and assign the output to a new variable named “List2Guid” of type GUID as shown below.

Now the variable “List2Guid” will have the GUID of List2.  We need this to get the workflow subscriptionId that we can get by calling an already available REST API called/_api/SP.WorkflowServices.WorkflowSubscriptionService.Current/EnumerateSubscriptionsByList.

We now drop another “Call HTTP Web Service” action and set it up get all workflow subscriptions.  Here’s how string builder should look like for this call.

And we set the “HTTP method” option for this “Call HTTP Web Service” action to “HTTP POST” as shown below.

We should now have the workflow design as shown below.

Now we have a tricky situation where we need to get the subscriptionId of the exact workflow that we are interested to start off.  This is where Fiddler is very helpful.  Again I’ll defer to the plethora of articles available online that tells us how to do a GET, POST REST calls using Fiddler.  But I am going to rush on this piece a bit.

Through Fiddler (by submitting a POST request to the same REST method EnumerateSubscriptionsByList, we see the following output.

Workflow2 happens to be the 3rd in the list of workflows associated to List2 so I can get that out using the 2nd index.  So we drop two more “Get an Item from a Dictionary” actions.  In the first action we ask for d/results(2) from “RespContent” and we store the output again in “RespContent”.  In the second action we ask for Id which happens to be the SubscriptionId of Workflow2 on list List2 from “RespContent” again and store the result in a new variable named “SubscriptionId” of type GUID.  Our workflow design should now look like below.

Now we are at the final stage where we will start the workflow.

Drop our last “Call HTTP Web Service” action and call the new/_api/SP.WorkflowServices.WorkflowInstanceService.Current/StartWorkflowOnListItemBySubscriptionId REST method by passing in the SubscriptionId and NewItemId variables.  Our string builder dialog should look like the below.  (Ensure the parameter names of this method is exactly as it is shown below and that the values are enclosed within single quotes).

This will again be a POST request.

After setting the “ReqHeader” correctly using the “Properties” context menu option for the last “Call HTTP Web Service” action, we drop a “Set Workflow Status” action.  The completed Workflow1 should look like below.

That’s it!!

We Save/Publish our workflow, go to List1 and create a new item.  Start off Workflow1 manually on that new item.  It should complete successfully.  Then we go to List2 and we should see that a new item is created and Workflow2 was trigged off too.

If we click on “Stage 1”, we’ll see that Workflow2 logged the message as we designed.

I hope the information in this post was useful.  Please check out the next part SharePoint 2013 Workflow recursion prevention – Part 3 to achieve the above objective from a Visual Studio SharePoint 2013 Workflow.