Nintex Lookup Function & Repeating Row on SPO

This post is pretty much a follow up from my previous “Web Services in SPD 2013” – HERE. This part of the solution is centred on the user interface for entering multiple entries per list item. The form was built using Nintex forms over SharePoint Online (and works on SharePoint On-premise also). The entire solution was deployed on a team site named “Expenses”.

 1.    External List

 First things first, I’ll be using a list created previously named “Rates” as an external list (shown below in figure 1.1) to lookup values, specifically from the “Target” and “Rate” columns. The “Target” column was renamed from its default “Title” name. This list is permission as “Expenses Owners” with Full Control for administrative users only.

Figure 1.1: Rates list (external list)

2.    The Main List

Secondly, I created a custom list named “Entries” for the expenses entries. This is the main list that users access the form by clicking “Add new item”. This is shown below in Figure 1.2.

Figure 2.1: Entries list (main list)

3.    Building the form

Thirdly, I created the form interface using Nintex Forms. The form contains the columns as shown above in the “Entries” list and additional local form fields that will be used to set values, perform some magic and make the form dynamic as follows;

• Allows the user to Select a “Currency” from an external list using a List Lookup field,
• Populates the “Rate” form field depending on the currency selected via a lookup formula,
• Allows the user to enter the “Local Amount” paid as a local form field,
• Populates the “Sterling” field via a Calculate Value (formula - Local Amount/Rate),
• Allows the user to make multiple entries via a repeating row panel

 3.1       Select a “Currency” from an external list using a List Lookup field

This is a list lookup form field named “Currency”. Using this field, Nintex will look up the “Title” column of the “Rates” external list on the same site as shown below in Figure 3.1.1.

Figure 3.1.1: List Lookup field settings

As you can see above, the name of the local form field is “Currency”, the source list is “Rates” and the list column to extract the values from is “Title” (I’ve renamed it but Nintex/SharePoint understands the default name of a custom list column though the name “Target” will also work).

3.2         Populates the “Rate” field depending on the currency via a lookup formula

The “Rate” field is actually a Calculated Value local form field that contains a formula. There is a structure to the formula. Doing it another way will not work. The formula lookup the “Rates” list, pull the “Title” field, match it or parse Lookup (meaning get its value) the currency lookup value and populate the local form field named “Rate”. Other vital settings are the decimal places and that the formula gets recalculated on view, new and edit modes as shown below in Figure 3.2.1.

Figure 3.2.1: The “Rate” Calculated Value form field settings

3.3         Allows the user to enter the “Local Amount” paid in a local form field

This part is straight forward. The user will enter their amount in the “Local Amount” local form field. It is a single line textbox field and does not have to be connected to a column in the “Entries” list (also a default value doesn’t have to be set) as shown below in Figure 3.3.1.

Figure 3.3.1: The “LocalAmount” form field settings

 3.4         Populates the “Sterling” field via a Calculate Value (Local Amount/Rate)

This is a calculated value field with a runtime function formula which is “LocalAmount” divided by “Rate” to convert it to Sterling. Other settings are the decimal places and that the formula gets recalculated on view, new and edit modes as shown below in Figure 3.4.1.

Figure 3.4.1: The “Sterling” form field settings

3.5         Allows the user to make multiple entries via a repeating row panel

Now, with all the local form fields created, they can now be placed in a Repeating Panel with a “Add Another Expense” link which allows the user to add individual expense that may or may not be the same currency in a repeating row. Additionally, all local fields are populated in runtime.

Figure 3.5.1: The Nintex Expense form with repeating rows

Note: This is a demo so when creating the form, I haven’t left align some of the local form fields.

4.    Rapping Up the Form

There are other fields on the form. They are the fields connected to the columns of the “Entries” list and are self-explanatory. The “Add Another Expense” link at the bottom is exactly what it says on the tin. The “x” to the right of the repeating row allows the user to delete that row. Finally, the “Total” local form field is a Calculate Value with a runtime function formula of “sum(Sterling)” which is the sum of all row entries for the “Sterling” local form field and is connected to the “Total Due” column of the “Entries” list.

Reference: http://ift.tt/2dsbOZB

 

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

How I did “Lazy Approval” on SP Online

The ability to approve or reject a task in SharePoint via an email is known as Lazy Approval.

I was asked to incorporate Lazy Approval as part of our Travel Request solution after a task has been assigned to a Trip Approver. A Trip Approver is a person selected when completing a request to go on a trip. Lazy Approval also allows the approver to approve on any device.

In creating a Lazy Approval, I used the following applications; SharePoint Designer, installed locally on my laptop, free “E-Mail Services” app added from the Office 365 app store. One cannot email from/to a list or a document library on O365 (can only be configured on premise). Please see the section “Email enabled lists and libraries“, for SharePoint Online sites on – http://ift.tt/2aZFIAU.

I had installed a stable third party free app that allows me to email to one list or library per site. E-Mail Services consists of a set of services that handles e-mails within Office 365. It must be said that you may not want to use this app if you are dealing with super sensitive data.

E-Mail Services allows you to transfer e-mails from any mailbox to any list or document library in SharePoint Online. This service brings e-mail enabled lists and libraries to Office 365. Incoming E-Mail is available as a free, fully featured plan with 100 e-mails per month included. If you need more you can choose from a series of plans. For more details please visit http://ift.tt/2bk8a4l.

Moving forward, I created an announcement list named “LazyRequestsAnnouncements” to receive and the handle the emails redirected by the app. After that, I configured the app for the site and the announcement list. I created a site workflow named “LazyRequestsAnnouncements” to listen for an email and perform an action. The action being, when an email contains the word “Approved” the associated task list is updated as approved and vice versa.

How do the user approve or reject via email? This part is after a task is assigned to an approver. The approver would action a task by clicking the “APPROVE” or “REJECT” link (not replying or forwarding) embedded in the task email. They should also not alter or replace the subject of the task email. This is because the subject is set to the task name which is also a unique reference.

 1.    Create the Announcement List

1. On the site where you want to add an app, go to Settings Office 365 Settings button and then click Add an app.
2. On the Your Apps page, do one of the following:

• In the search box, type “Announcements” to find Announcements
• Click the Announcements app to add it
• Leave all the default fields as they are

 

2.    Install the Email Service App

1. On the site where you want to add an app, go to Settings Office 365 Settings button and then click Add an app.
2. On the Your Apps page, do one of the following:

• In the search box, type the name of the built-in app you want to add
• Click the app to add it, and then provide any required information.

 

3.    Configure the E-Mail Services to receive emails

Go to the E-Mail services App, Under Incoming E-Mail, click the “Subscription and settings” button. This will take you to the Incoming E-Mail settings showing;

1. Your configuration redirect E-Mail
2. Set the SharePoint list or document library the incoming emails should be saved to
3. Edit your field mappings
4. Additional settings

This is shown below in Figure 3.1.

Figure 3.1: E-Mail Services

 

In the travel solution, I copied the redirect email address for later. In the above window, I selected the list within the SharePoint site. I mapped the E-Mail message fields (Subject and Body) to the SharePoint columns (Title and Body respectively). You don’t need to worry about anything else. Make sure you save your changes. That’s it you’re done.

 

4.    Creating the Approved/Reject Link

Now to create Lazy Approval, click the link for the “Assign a Task…” in your workflow. Under the heading Task Options, we are interesting in the open email editor for the Task Creation and Task Overdue emails. You would do the same for both email options. Figure 4.1 shows the Assign a Task window and the email editor buttons under Email Option.

Figure 4.1: Assign a Task

 

For the Task Creation, Click the “Open email editor” to access the contents of the task email. In the “Define E-mail Message” dialog window, you can see the contents of the email. Type APPROVE and REJECT. As the words suggests, the formal will approve a task via an email and the latter will reject the task via an email. This is shown below in Figure 4.2.

Figure 4.2: Define E-mail Message

Now to turn the words APPROVE and REJECT into links that a user can click on to send an email to approve or reject the task depending on which word they click on. To do this you should highlight or select the word APPROVE and click the globe link button to add or edit a hyperlink.

Figure 4.3: Edit Hyperlink (for APPROVE)

 

Next, Click the ellipses to access the string builder. Here you would insert in the Name field, the email address link copied earlier from the configuration tab, in the "Your configuration redirect E-Mail” section of the E-Mail Services app. You would assigned a subject and body for the email.

Figure 4.4: String builder (for APPROVE)

 

I’ve made the email subject matches the Task Name from the “RequestsWF” workflow (which is the Title field renamed to Reference). I also use an URLEncode website to help with the syntax for the email. This populates the body with the “Purpose of Trip”, and the “Trip Approver” fields from the Requests list and the relevant spaces and characters so that it is presentable.

As with the APPROVE link, you would do the same for the REJECT link, changing the word “Approved” to “Rejected” in the string builder. Now the task email has hyperlinks in it to approve or reject that particular task via email. You can say the whole task process looks a little user friendly.

5.    The Task Email

Below is the email sent and received to the task assignee through the Assign a task action constructed in the RequestsWF workflow. The task assigned title which is the subject of the email is the Reference column of the Requests list (renamed from Title). The Reference column is made up of the current user logged in dash created date (in ISO Formatted).

This Reference is also the Task Name which is the blue link in the actual email. Clicking this link will land you on the page of that particular task.

Figure 5.1: The Approved email

 

The approver or task assignee should not Reply, Reply All or Forward the email. Why? Because Reply and a Reply All will start the subject of the email with a “RE:..” and a Forward will start the email with a “FW:..”. This will not work in Lazy Approval because the email subject should match the Task Name. I am using the Task Name as a unique reference to perform various operations.

Figure 4.5 shows the email as it should be with the correct title after the user clicks the APPROVE (or REJECT) link.

Figure 5.2: The Approved email

 

Above is what the email to approve a task looks like. Likewise, the email to reject a task looks the same except it starts the word “Rejected Request…” in it. The user should just Click the Send button and nothing else really. They can type additional information in the body but must not remove the words “Approved” or “Rejected”.

After the approver sends the email, it is delivered through the E-Mail App to the announcement list created earlier. There is a site workflow in the background to handle the email delivery and update the “RequestsWF Tasks” accordingly.

As stated earlier, the Task Name associated with a request is the same as the email subject. This is then mapped to the “LazyRequestsAnnouncements” Title SharePoint column.

6.    LazyRequestsAnnouncements workflow

Firstly, the site workflow is a 2013 workflow linked to the “LazyRequestsAnnouncements” announcements list. This workflow was started manually and is always running.

The first stage of the workflow is “Waiting”. The workflow waits for an item to be added in the announcement list. Remember this item is the lazy approval email (constructed earlier) sent to the announcement list. When the item arrives, a unique variable is assigned to it named “related item”. This can be given any name but I decided to leave it as default.

Figure 6.1: The Approved email

Then I set a local variable named “AnnoucementTitle” to the same as the “RequestsWF Tasks” Task Name when it is equal to the Title column of the email received in the announcement list named “LazyRequestsAnnouncements”, where each email has a unique ID differentiating it from all other emails (before or after).

Figure 6.2: The Approved email

 

In the UpdateTask stage, if the body of the email received in the “LazyRequestsAnnouncements” list contains the word Approved then we want to use the Update List Item to update the “RequestsWF Tasks” list fields. The Task Outcome is set to Approved and the Task Status is set to Completed where the Task Name is the same as the Announcement Title.

Else, if the body of the email received in the “LazyRequestsAnnouncements” list contains the word Rejected then we want to use the Update List Item to update the “RequestsWF Tasks” list fields. The Task Outcome is set to Rejected and the Task Status is set to Completed where the Task Name is the same as the Announcement Title.

This is shown in Figures 5.3 and 5.4 respectively.

Figure 6.3: Update List Item

Figure 6.4: Update List Item

The last and final stage is “Incorrect”. If Task Name and Announcement Title does not match it is logged, the task is either approved or rejected and the process starts again by the going back to the Waiting stage as shown below in Figure 5.5.

Figure 6.5: Incorrect stage

To conclude, this site workflow is always running. The user do not know anything about this. They just have to Click APPROVE or REJECT as appropriate. That’s it, you’re all done.

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

Run Audit Reports in O365 SharePoint Online

Is there a reason why View all Auditing Reports > Run Reports > Customise Report isn't working in SharePoint online? This is the question I’m asking myself as this feature works in On Premise environment.

When you try to save the "Content viewing" audit log report in SharePoint Online you’ll get the dreadful “Sorry, something went wrong” error written in a large font. The next line will say “Report contains no data…” as shown below in Figure 1.           

Figure 1: Sorry, something went wrong

You can see the symptom described here – http://ift.tt/1mZ5aMW.

As stated above, "Content viewing" audit log report is not available with SharePoint Online. You’ll have to use “Security & Compliance” portal, logged in as Global Admin following the steps below; 

• Access http://ift.tt/1l4uXLA

Figure 1: for Step 1

• Click the Tiles icon and Click “Security & Compliance” tile

        Figure 2: for Step 2

• Click “Reports” from the left panel

        Figure 3: for Step 3

 

• Click “View reports” under Reports

        Figure 4: for Step 4

 

• Click “Office 365 audit log report”

        Figure 5: for Step 5

 

• On the “Audit log search” – Select from Activities, Start Date, End Date, Users etc. then Click the Search button

        Figure 6: for Step 6

 

• Under the left pane you have the “Results”. You can Filter results and “Export results”

        Figure 7: for Step 7

 

• Now Download the file by Clicking “Download all results” under “Export results”

        Figure 8: for Step 8

Reference: https://www.youtube.com/watch?v=iV6_0sF4f7E

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

Add your Office 365 Calendar to a SharePoint Page

I was asked to show a user Outlook calendar on a SharePoint page. You can do this using a SharePoint calendar overlay, however I elected to use the Page Viewer web part. 

Accessing the Office 365 Mail web address;

1. Make sure that you (or the user in question) is logged onto Office 365.
2. Go to your Office 365 home (assuming it’s not heavily branded).
3. Click your Office 365 tiles (on the top left of the page).
4. Once you see the tiles menu, Click the “Mail” tile icon.
5. Once in your Outlook Mail page, Click the gear icon at the top right to access it’s settings.
6. Scroll right down to the bottom. Under “My app settings”, Click the “Mail” link.
7. More Options will open on the left panel. Under the Calendar heading, Click “Publish calendar”.

Figure 1: Publish Calendar

Notice that on the Publish calendar page, under “Select a calendar” heading “Calendar” is selected and under “Select permissions” heading “Limited details” is selected. If these options aren’t selected, select them and save the page at the top.

This will produce a HTML link that allows us to view the calendar in a browser. This is the first link starting http://ift.tt/29X9YAi… You can see this by Clicking the link, then;

1. Copy this link (…for pasting later).
2. Go to your SharePoint Online site.
3. Edit the page through the gear settings
4. Click “Add a Web Part” to access the web part gallery categories.
5. In the “Media and Content” category, Select the “Page Viewer” web part.

Figure 2: Adding Page Viewer web part

 

Page Viewer Web Part settings;

1. Once the web part has been added, edit the web part.
2. With the web part in edit mode, Click the ellipses button to access the string builder.
3. Paste the HTML link we copied earlier in the string builder as shown below in Figure 3.

Figure 3: Web part settings

The Outlook calendar on a SharePoint page;

1. Now, just Click OK and you are done. You should see the calendar of the user account in question on the SharePoint page as shown below.

Figure 4: Outlook calendar on SharePoint page

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

Auto populate Nintex form fields with a check box

So I was asked to improve the user experience for users filling in a Nintex Form for travel request. The improvement centred on having the return journey or inbound fields auto populate with a tick of a check box with the data entered in the outbound fields. This is not easily done with out-of-the-box Nintex forms features. I had to write a script in JavaScript to achieve this functionality. Firstly, you’ll have to create the following fields.

Create form, adding the following fields to the “Title”; 

1. Outbound Departure – a single line text
2. Outbound Arrival – a single line text
3. Same as Inbound – a tick box (Yes/No)
4. Inbound Arrival – a single line text
5. Inbound Departure – a single line text

I left the Save and Cancel buttons as default.

Figure 1: Simplified Travel form

For each form field control settings;

For Outbound Departure, Click the “Advanced” setting, Select Yes from the dropdown for Store Client ID in JavaScript variable. In the Client ID JavaScript variable name, I entered and assigned “varOutboundDeparture” as the variable name. Leave everything else the same.

Figure 2: Control Settings for Outbound Departure

For Outbound Departure, Click the “Advanced” setting, Select Yes from the dropdown for Store Client ID in JavaScript variable. In the Client ID JavaScript variable name, I entered and assigned “varOutboundArrival” as the variable name. Leave everything else the same.

Figure 3: Control Settings for Outbound Arrival

For Outbound Departure, Click the “Advanced” setting, Select Yes from the dropdown for Store Client ID in JavaScript variable. In the Client ID JavaScript variable name, I entered and assigned “varSame” as the variable name. Leave everything else the same.

Figure 4: Control Settings for Outbound Arrival

For Inbound Departure, Click the “Advanced” setting, Select Yes from the dropdown for Store Client ID in JavaScript variable. In the Client ID JavaScript variable name, I entered and assigned “varInboundArrival” as the variable name. Make sure the “Not connected” dropdown option is selected for the “Connected to” setting. Leave everything else the same.

Figure 5: Control Settings for Inbound Arrival

For Inbound Departure, Click the “Advanced” setting, Select Yes from the dropdown for Store Client ID in JavaScript variable. In the Client ID JavaScript variable name, I entered and assigned “varInboundArrival” as the variable name. Make sure the “Not connected” dropdown option is selected for the “Connected to” setting. Leave everything else the same.

Figure 6: Control Settings for Inbound Departure

Now, in the form settings add the following JavaScript;

To add JavaScript to Nintex Forms, from the Form editor click the Settings button in the Ribbon and then expand the Custom JavaScript section at the bottom.

NWF$('#' + Same).click(function(){     var checkBox = NWF$('#' + varSame);     if (checkBox.is(':checked') == true){     var OutboundDeparture = NWF$('#' + varOutboundDeparture).val();     var OutboundArrival = NWF$('#' + varOutboundArrival).val();     NWF$('#' + varinboundArrival).val(outboundDeparture);     NWF$('#' + varinboundDeparture).val(outboundArrival);     }     else     {     var nullValue = '';     NWF$('#' + varInboundArrival).val(nullValue);     NWF$('#' + varInboundDeparture).val(nullValue);    }  });

 

Figure 7: Control Settings for Inbound Departure

Nintex Forms uses a copy of the jQuery library that you can access through NWF$. So just adding SPServices to your form’s page won’t work, but since jQuery is already there it’s fairly simple to get it all hooked up through NWF$.                                                                                      

Finally, it’s time for testing. Testing is as follows;

1. Enter data for Outbound Departure and Outbound Arrival fields
2. Check the Tick box, the result should be;
   • Inbound Arrival populate with Outbound Departure
   • Inbound Departure populate with Outbound Arrival
3. Uncheck the Tick box, the result should be;
   • Inbound Arrival field is blank
   • Inbound Departure field is blank

Outbound Departure Airport and Outbound Arrival Airport with “Same as Inbound”: Unchecked

Figure 8: The form with Same as Inbound Uncheck

Inbound Departure Airport and Inbound Arrival Airport with “Same As Inbound”: Checked

Figure 9: The form with Same As “Inbound” Checked

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

The Future of SharePoint Summarised

New SharePoint sites with modernised team site experience, with an engaging home page personalised by the intelligence of Office Graph.

A revitalised libraries and lists enable immediate productivity with an intuitive user experience and provide rich metadata, content management and functionality.

Bringing the power of SharePoint team sites and Office 365 Groups together, giving every group a team site, and giving team sites the benefit of groups for simple management of membership across Office 365 services.

Microsoft Flow was announced as a new service for automating workflow across the growing number of apps and SaaS services.

SharePoint mobile app, designed for Windows, iOS and Android, to put your intranet in your pocket, with full-fidelity access to company news and announcements, people, sites, content and apps — no matter where you are.

New tools to discover and protect content with data loss protection (DLP) policies, both in Office 365 and in SharePoint Server 2016.

SharePoint Insights, a service which aggregates usage and compliance data from on-premises and cloud into the Office 365 Reporting Centre, so you can get a unified view across your entire organisation.

And of course SharePoint 2016 is out with further releases to come. SharePoint designer will work with the new version.

The InfoPath 2013 remains the last version to be released and will work with new version.

• The InfoPath 2013 client will be supported through April 2023.
• InfoPath Forms Services for SharePoint Server 2013 will be supported until April 2023.
• InfoPath Forms Services in Office 365 will be supported until further notice.

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

The Future of SharePoint Summarised

New SharePoint sites with modernised team site experience, with an engaging home page personalised by the intelligence of Office Graph.

A revitalised libraries and lists enable immediate productivity with an intuitive user experience and provide rich metadata, content management and functionality.

Bringing the power of SharePoint team sites and Office 365 Groups together, giving every group a team site, and giving team sites the benefit of groups for simple management of membership across Office 365 services.

Microsoft Flow was announced as a new service for automating workflow across the growing number of apps and SaaS services.

SharePoint mobile app, designed for Windows, iOS and Android, to put your intranet in your pocket, with full-fidelity access to company news and announcements, people, sites, content and apps — no matter where you are.

New tools to discover and protect content with data loss protection (DLP) policies, both in Office 365 and in SharePoint Server 2016.

SharePoint Insights, a service which aggregates usage and compliance data from on-premises and cloud into the Office 365 Reporting Centre, so you can get a unified view across your entire organisation.

And of course SharePoint 2016 is out with further releases to come. SharePoint designer will work with the new version.

The InfoPath 2013 remains the last version to be released and will work with new version.

• The InfoPath 2013 client will be supported through April 2023.
• InfoPath Forms Services for SharePoint Server 2013 will be supported until April 2023.
• InfoPath Forms Services in Office 365 will be supported until further notice.

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

Adding images to SPD Workflow emails

I work in an organisation where there are a few managers that are afraid of technology which is not directly related to SharePoint Online (or any version). I bare this in mind when I designed a Workflow for a Document Management System (which is a document library with various content types). To start I renamed the “Created By” column to “Owner”.

After the Owner uploads a document and the “Approval Status” is “Draft” an email is sent to them. To make things more user friendly, I added a link in the email that points to the document library. I felt that it would help the user even more if I there was an image showing how to “Publish a Major Version” through the item’s ellipses menus. I did this by accessing the Properties… button of the “Email Current Item: Owner” action as shown below in Figure 1. This is not the Define E-mail Message window where you get to define the “To:”, “CC:”, “Subject:” fields and type the email message though the properties can give you the same options.

Figure 1: Valid stage that sends an email

Once you access the “Send an Email Properties” windows you’ll see the various properties of the email but we are interested in the “Body” property as shown below in Figure 2.

Figure 2: Send an Email Properties

Now, Click the ellipsis of the body field to review the string builder as shown below in Figure 3. This view is not quit user friendly if you are not used to starring at code. Best thing is not to play around with the tags already there. I just inserted an image tag where I want the image to be display.

Figure 3: Email String Builder

 

The code I used to insert an image is as follows… “<IMG alt="Publish a Major Version" src="https://sitecollection/sites/subsite/screenshots/screenshot-publish_a_major_version.jpg">”. In my case the image is stored in an image library on the same SharePoint site. You can insert images from any location as long as your user base has access to where the image is stored. I didn't use any more image attributes as it wasn't necessary. If you wish, you can find out more information about the <IMG> tag attributes here - http://ift.tt/17mUPMx.

As far as images are concerned, I did try an animated GIF that shows the different clicks, i.e. Click the Document's ellipsis > Preview ellipsis > Advanced > "Publish a Major Version". There is one problem, not every email client supports animated GIFs. Newer versions of Outlook (2007, 2010 and 2013) won’t show the animation. All other image formats are accepted when building in SharePoint Designer and by all other email clients. You can find out more about email client support here - http://ift.tt/1r1gp4E.

You can use animated GIFs if your users view their emails in email clients that can show animated GIFs. The final email will have some text and the image inserted earlier. In my case, the email will look something like the email in figure 4.

Figure 4: The email received with an image inserted via a workflow

That's it you're done. When a user receives the email created earlier, they will see the image inserted in the body of the email.  If they view animated gifs, well that's even better.

by Larry Saytee via Everyone's Blog Posts - SharePoint Community

Sending Reminder via workflow

I was asked to create a workflow for a document library. This is sometimes known as a Document Management System (DMS). The document library has metadata fields. This means that there will be a second popup window after the file upload “Add a document” window. The workflow “Start workflow automatically when an item is created” and “Start workflow automatically when an item is changed”. The workflow has five stages. There are two types of users, an Owner and an Approver.

An owner (which is the “Created By” field renamed) is the user that upload a document and an Approver approves or rejects documents. The stages are Draft, Pending (Valid Owner and Invalid Owner), Approve and Reject. The users are updated by email at every stage of the workflow. In addition to this, the workflow should sends reminders to the Approver to approve or reject a document upload if they haven’t done so within a time period. It also sends emails to the approver in advanced to review a document prior to the review date. To facilitate this, I created three calculated columns named “Due Date”, “Reminder Date” and “Review Date”.  

A reminder email is sent only on the “Due Date” which is 7 days after the document has been uploaded if the approver hasn’t approved or rejected that document. After the document has been approved or rejected, a reminder email is sent 7 days before the “Review Date”, this is the “Reminder Date”. Review Date is “Modified” plus 180 days. Reminder date is Review Date minus 7 days. Seems simple enough right? Well I came across a few issues.

Using the “Pause for 0 days, 0 hours, 0 minutes” action wasn’t a good idea as the workflow did not work. No emails where delivered. It is as if the workflow pauses mid-way through in the pending stage. I tried a few tricks that did not work. I decided to separate the workflows. By that I mean have a second workflow, a 2010 workflow to be exact. In this workflow I had all the reminders. Again, same as the 2013 workflow this one “Start workflow automatically when an item is created” and “Start workflow automatically when an item is changed”.

As shown below, a Due Date email is sent, when it is today (ignoring time) and when Status (a local string variable based on Approval Status) equals 2 which is the Pending Approval Status. An email is sent also when Reminder Date equals today (ignoring time) and Status equals 0 or Approved Approval Status. Finally, an email is sent when the Review Date equals today (ignoring time) and Status equals 0 or Approved.

In conclusion, I had the bulk of my conditions and actions are in one workflow and the reminders are in another. I have seen different methods such as using “Add 0 months, 0 days, 0 hours, 0 minutes to date (Output to Variable: date)”.

However, I prefer using calculated columns to send reminders because it is simple and it works!

Figure 1-1: Reminder stage

by Larry Saytee via Everyone's Blog Posts - SharePoint Community