List Views based on calculate column containing difference in days between two dates

If you need to find difference in days between two dates suppose "Date 1" & "Date 2" then we use below formula -
=DateDif(Date 1, Date 2,"d") and set return type as Date and Time

But in case you want to set up list view based out of that calculate column we created above then SharePoint will not allow you to do that since return type in above case is Date & Time and it cannot be compared with numerics.

In order to do that, you need to use below formula -
=Date 1 - Date 2 and set return type as number


Now you can set up views based out of this column like if that column value is greater than 60 or less than 100 or any thing like that.

Run SharePoint Workflow on Button Click in List Views using jsLink and JSOM

*This is for SharePoint Online

Sometimes there are requirements from business where they need a button in list view against each item and once you click on it, it should run a workflow for that particular list item.

Make sure to make below changes before using this code.

•  Change the name of the workflow to start mentioned in variable "WorkflowName",
•  replace the siteURL
• Change the column name against which you want to show the button. I am overriding default 'Edit' button 

 /*  Code Starts*/

/*Reference Files*/
<script src="https://siteURL/SiteAssets/jquery-3.2.1.min.js"></script>
<script src="https://siteURL/_layouts/15/SP.Runtime.js"></script>
<script src="https://siteURL/_layouts/15/sp.workflowservices.js"></script>
<script src="https://siteURL/_layouts/15/wfutility.js"></script>
/**/

var itemID = '';
var ItemGUID = '';
var WFSubscriptionID = '';
var WorkflowName = 'Meetings WF';


function getItems(ListTitle, Id) {
    var d = $.Deferred();

    var url = "/_api/web/lists/getByTitle('" + ListTitle + "')/getItemById(" + Id + ")";
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
            "content-Type": "application/json;odata=verbose"
        },
        success: function(data) {
            console.log(data)
            d.resolve(data);
        },
        error: function() {
            d.reject('bad request')
        }
    });

    return d.promise();
}


function GetGUID(z, Id) {
    itemID = Id;
    getItems(z, Id).then(function(data) {

        console.log(data.d.GUID);
        ItemGUID = data.d.GUID;
        GetWFSubscriptionID(ctx.listName, itemID, ItemGUID);
    })
}




function StartWorkflowJsLink(overrideCtx) {

    //overrideCtx.listName = "{88E4FF8A-94E8-40AA-89BE-79A98B1CB370}";
    return '<input type="button" value="Start Workflow" onclick="GetGUID(' + "'" + overrideCtx.ListTitle + "'" + ',' + "'" + overrideCtx.CurrentItem.ID + "'" + ')"/>';

}

function registerListRenderer() {

    var overrideCtx = {};


    overrideCtx.Templates = {};

    overrideCtx.Templates.Fields = {

        'Edit': {
            'View': StartWorkflowJsLink
        }

    };


    SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);
}



ExecuteOrDelayUntilScriptLoaded(registerListRenderer, 'clienttemplates.js');




function GetWFSubscriptionID(ListID, ItemID, ItemGUID) {

    showInProgressDialog();

    var listGuid = "88e4ff8a-94e8-40aa-89be-79a98b1cb370";
    var context = SP.ClientContext.get_current();
    var web = context.get_web();
    var sMgr = new SP.WorkflowServices.WorkflowServicesManager(context, web);
    var sservice = sMgr.getWorkflowSubscriptionService();
    var ssubs = sservice.enumerateSubscriptionsByList(listGuid);
    context.load(ssubs);
    context.executeQueryAsync(
        function() {
            var e = ssubs.getEnumerator();
            while (e.moveNext()) {
                var c = e.get_current();
                if (c.get_name() === WorkflowName) {
                    var subId = c.get_id();
                    WFSubscriptionID = subId;
                    StartWorkflow4(WFSubscriptionID, itemID, ItemGUID);
                }
                //alert("Name :" + c.get_name() + " sID: " + c.get_id());
            };

        },
        function() {

        });




}


var errorMessage = "Something went wrong. To try again, reload the page and then start the workflow.";
var theForm = document.forms['aspnetForm'];
if (!theForm) {
    theForm = document.aspnetForm;
}

function StartWorkflow(iwa) {
    var elIwaStart = document.getElementById("iwaStart");
    elIwaStart.value = iwa;
    theForm.submit();
}
var dlg = null;

function StartWorkflow4(subscriptionId, itemId, itemGuid) {

    var ctx = SP.ClientContext.get_current();
    var wfManager = SP.WorkflowServices.WorkflowServicesManager.newObject(ctx, ctx.get_web());
    var subscription = wfManager.getWorkflowSubscriptionService().getSubscription(subscriptionId);
    ctx.load(subscription, 'PropertyDefinitions');
    ctx.executeQueryAsync(
        function(sender, args) {
            var params = new Object();
            var formData = subscription.get_propertyDefinitions()["FormData"];
            if (formData != null && formData != 'undefined' && formData != "") {
                var assocParams = formData.split(";#");
                for (var i = 0; i < assocParams.length; i++) {
                    params[assocParams[i]] = subscription.get_propertyDefinitions()[assocParams[i]];
                }
            }
            if (itemId) {
                wfManager.getWorkflowInstanceService().startWorkflowOnListItem(subscription, itemId, params);
            } else {
                wfManager.getWorkflowInstanceService().startWorkflow(subscription, params);
            }
            ctx.executeQueryAsync(
                function(sender, args) {
                    closeInProgressDialog();
                    /*var elWf4Start = document.getElementById("wf4Start");
                    elWf4Start.value = 1;
                    theForm.submit();*/
                    window.location.href = window.location.href;
                },
                function(sender, args) {
                    closeInProgressDialog();
                    alert(errorMessage);
                }
            );
        },
        function(sender, args) {
            closeInProgressDialog();
            alert(errorMessage);
        }
    );
}

function closeInProgressDialog() {
    if (dlg != null) {
        dlg.close();
    }
}

function showInProgressDialog() {
    if (dlg == null) {
        dlg = SP.UI.ModalDialog.showWaitScreenWithNoClose("Please wait...", "Waiting for workflow...", null, null);
    }
}

function HandleCheckinBeforeStartWorkflow() {
    var strError = "Please check this document in before starting a workflow.";
    window.alert(strError);
}

/*Code Ends*/

You would be able to see "Start Workflow" button in the overridden column as shown below.

Clicking on the button will run that workflow on the corresponding item.

SharePoint Designer Workflow 2013 - Access Denied while Email

Issue :

I had a scenario where we have to send email to a SharePoint Group where initiator does not belong to that group.

While triggering e-mail to that group, workflow threw an error saying

 "Access denied. You do not have permission to perform this action or access this resource".

Resolution 1:

1.  Go to Site Settings -> Site Permissions
2. Select Group to which you are sending e-mails.
3. In group settings, set "Who can view the membership of the group ?" to Everyone

Resolution 2:

1. Go to Site Settings -> Site Permissions -> Permissions Levels
2. Open Permission level which is assigned to current user or the group where current user is added.
3. Make sure, user is having "Create Alerts " permission as shown below.

Item level Permission SPD Workflow 2013 Part-3

Workflow Logic

•  Workflow logic would be 

Action	Status	Item Permission
On Item Submit	Pending with Manager	Created by – Read Manager - Contribute
Item Approved by Manager	Closed	Created by – Read Manager – Read

• As shown in below screen shot. Add App Step to the workflow.
• When we assign permission on an item, there are 2 steps for that.

1. Break Role Inheritance: It will stop inheriting permission from parent list and also using Copy Role Assignments =false parameter it will remove all the existing permissions from that item.
2. Assign Permissions: In the second step we will assign permission to the desired users.

Breaking Role Inheritance

• Add below steps to the workflow

• Create a REST URL for breaking role inheritance as shown below

• Add two headers as shown below.
• Next step would be to build a dictionary for headers and name it as “Request Headers”. 
Accept : application/json;odata=verbose
Content-Type: application/json;odata=verbose






• Now Insert call a web service action.
  Select URL that we prepared above.
  Method Type : POST

In the response code, create a variable named responseCode.

             In the Properties section as show below, select Request Headers

• Now publish the workflow. Create an item in the list and once the workflow execution completes, check the permissions on that item.

Assign Permissions - Pending With Manager

• Add Steps mentioned above for “Assign Read Permission to Created By”.
• Set Variable URL as mentioned below.

• Build Dictionary same as mentioned in previous step and assign it to Request Headers Properties section of call a web service action.
• Make a Post Call and Publish the workflow.
• Create another item in the list and check the permission on that item after workflow completes execution.
• If I check the item level permissions, now Created by user has read permission and Manager has contribute permission

Assign Permission - Approved/Rejected by Manager

• After above steps, item is now pending with manager for approval.
• After approval/rejection, we need to set permissions on that item as below.
• Read for Created by
• Read for Manager
• Since Read permission to Created by is already assigned so we do not have to modify anything for that user.
• Now to assign read permission to Manager, we need to follow below steps.
• Remove Contribute permission for Manager.
• Assign read permission for manager.
• To remove contribute permission for manager and assigning read permission, add actions as below. 

• For removing manager permission, use below URL and use “DELETE” as the http method

• For assigning read permission to manager. Add actions as specified in above screenshot highlighted as green.
• Use below URL and use “POST” as the http method

• Assign Request Header.
• Publish the workflow.
• In the previously created item, edit the same and in Manager Approval select as approved or rejected and click on save.
• Now check permissions on item

• 
  

Item Level Permission - SPD Workflow 2013 - Part 2

Workflow Creation & Configuration

Full Workflow Screenshot

1.  Below is the screenshot of the workflow that we would be developing.
2. 
   
   

Create a SPD 2013 Workflow

1. Create a SharePoint Designer workflow for “Demo List”. 
2. Since SharePoint Designer workflow runs under current user authorizations so we will not be able to call REST API under normal user credentials. So we will have to user APP STEP.

Enable App Step

1. APP Step is a new feature in SharePoint Designer Workflow. We have to assign permission to workflow just like we do for SharePoint Apps and workflow would be able to do the task even if user is not having access for that action.
2. By default APP STEP action would be disabled.
3. To enable that we need to active a site feature “Workflow can use App Step”.
4. Activate the above mentioned feature.
5. After activating you would see, app step action is activated in SharePoint Designer as show below
   

Assign Permissions to Workflow

1. Go to Site Settings à Site App Permissions
2. Now publish a blank workflow and again go to Site Settings à Site App Permission.
3. You will see new entry for Workflow.
4. Underlined text would be the APP ID for which we need to give Full Control to web level. Copy this APP-ID.
5. Go to  https://sponlinepractice.sharepoint.com/sites/ml/_layouts/15/appinv.aspx
6. Paste the APP-ID copied in previous step in above form and click on Lookup button.
7.  It will auto-populate the information such as Title, App Domain, Redirect URL.
8. Now copy paste below permission xml in Permission Request XML in above form.
   
9. Here we are giving full control to workflow at web level which is defined by scope and Right in above xml.
10. Click on create.
11. It will ask you to trust the workflow access. 
12. Click on trust.
13. Now workflow has enough permission to call REST API and assign proper permissions

                               Proceed to Part 3

Item level Permission - SPD Workflow 2013 - Part 1

Follow below steps to apply item level permission in SharePoint Online using SharePoint Designer Workflow.

Scenario: If an employee visits some other location for officially then expenses would be reimbursed but for that there would be an approval cycle. Once employee submits the expenses then it would be approved by Manager. For that we will follow below steps.

REST API’s to be used

	URL	Method Type
Break Role Inheritance	/_api/web/lists/getByTitle('User Access Requests List')/items(itemId)/breakroleinheritance(copyRoleAssignments=false, clearSubscopes=true)	POST
Add Permission of user	/_api/web/lists/getByTitle('User Access Requests List')/items(itemId)/roleassignments/addroleassignment(principalid=UserId,roleDefId=rId)	POST
Remove Permission of User	/_api/web/lists/getByTitle('User Access Requests List')/items(itemId)/roleassignments/getbyprincipalid(userId)	DELETE

List Creation

•   Create a list: I have a demo list namely “Demo List” and with few columns.

Column Name	Type
Title	Single Line of Text
Location Visited	Single Line of Text
Expenses	Number
Manager	User
Status	Choice

•      Status field would be having below options.

a.       Pending with Manager  - Default value

b.      Closed

Proceed To Part 2

Get SharePoint Role Definition IDs (Out of the box + Custom Permission set)

To assign item level permission we need to have the role definition ids. Here are few out of the box ids below.

Role Definition Name	Role Definition Id
Full Control	1073741829
Design	1073741828
Edit	1073741830
Contribute	1073741827
Read	1073741826
View Only	1073741924

Above Role Definition IDs are found using below REST API only.

Role definition ids of custom permission set can be found out using REST API. I have created a custom permission called “BASIC USER”. Now hit below URL in the browser

https://site-url/_api/web/roledefinitions

You can see the custom permission details including Role Definition ID in the browser response as in below screenshot. I am using online xml beautifier tool for good visibility. It will show role definition ids of all the permission sets (out of the box permissions like full control, read etc as well as custom permissions)

SharePoint Designer workflow triggering multiple emails.

I had a list containing 8 people picker columns with allow multiple values set to true. It was having out of the box forms. Whenever user submits an item it sends an email to all the users marked in all the people picker fields. SharePoint Designer workflow was used to send email to users. Workflow was having just “send an email action”.

Issue: Users were getting n number of emails on item creation/updation.

Workflow Status: Workflow was stuck in suspended state and keeps on retrying because of retrying users were getting n number of emails.

Reason: For an item is number of total users in all people picker column reaches approx. 30 or more and where few duplicate users are added in multiple fields. Then the “send an email” action tends to go to suspended state and keeps on sending emails to users marked in people picker column until the workflow is terminated.

Solution: There should not be repeated users in multiple people picker fields. For ex. User1 Should not be present in Field1 & Field2 simultaneously.  For that you can add a client side validation before item submission notifying the users whenever there are any repetition of users. 

Get User Properties using User Profile in SharePoint Online

Below are some important properties which we can retrieve from user profile for a user by passing login id.

• Cell Phone Number
• Manager
• Accountname
• Displayname
• Directreports
• Email
• Extendedmanagers
• FirstName
• LastName
• Department
• PictureURL
• UserName etc..

I would be retrieving it using Login Id of the desired user. Login id by default in SharePoint Online is of below format.

i:0#.f|membership|manjot@xyz.com

If we directly pass this format it will give you “bad request” error since while passing URL to rest calls we need to encode few characters. In above case, we need to encode ‘#’ to ‘%23’. So our login id would be:

i:0%23.f|membership|manjot@xyz.com

Now pass login name as in below code which will give you the required property

var loginName='i:0%23.f|membership|manjot@xyz.com';

GetSPListItems("/_api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v,propertyName='CellPhone')?@v=" + "'" + loginName + "'", function(data) {
  
    console.log(data.d.GetUserProfilePropertyFor); // This will give you cell phone number

}, function(error) {
    console.log(error);

});

function GetSPListItems(url, Success, Error) {
    $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + url,
        type: "GET",
        headers: {
            "accept": "application/json;odata=verbose",
            "content-Type": "application/json;odata=verbose"
        },
        success: Success,
        error: Error
    });
}

If you want to access all properties then simply replace the URL in the above code with below value.
  // in above url we specified propertyName='CellPhone' to get specific property if you want to access all then simply remove that propertyName parameter.

 URL= _api/SP.UserProfiles.PeopleManager/GetUserProfilePropertyFor(accountName=@v)?@v=" + "'" + loginName + "'