SharePoint Calendar Cleanup: Use one workflow to remove Outlook “Copy:” prefixes, standardize location names and delete canceled events

headercalendar.PNG

We’ve all been there. One location on a shared calendar will be referred to by multiple people as 20 different things. Johnson Building Room 214 can be entered as “214,” “Johnson 214,” or “J214” to name a few. Canceled events stay on the calendar, sucking up real estate and waiting for someone to delete it manually. Items copied from another calendar make you pay for the convenience of a simple copy and paste by adding the “Copy: ” prefix to the item.

But with a single workflow, we can fix all of these and make our SharePoint calendars look more professional and polished without making more work for end users. This post will cover how we can use workflow to standardize naming of locations with workflow, delete events once they’ve been canceled and get rid of Outlook’s “Copy: ” prefix. You will need SharePoint Designer and appropriate permissions to create workflows to complete the following steps:

Remove “Copy: ” Prefix

  1. Create a calculated column in your calendar
    1. Go to your calendar
    2. Click the calendar tab in the ribbon
      calendar1.PNG
    3. Click list settings
      calendar2.PNG
    4. Create a new column
      calendar3.PNG
    5. Name it WF-NoCopyVersion (WF is a handy prefix to remember it’s used in a workflow), click the radio button next to “calculated column”, insert the following formula (assuming you’re using the default title field for your calendar items’ titles), and uncheck “add to default view”.

      =RIGHT([Title],LEN([Title])-INT(FIND(” “,[Title])))

      calendar4

    6. Click OK
  2. Open your site in SharePoint Designer
  3. Create a new list workflow on your calendar
    calendar5.PNG
  4. Name it and give it a description, leave platform as 2013 and click OK
    calendar6.PNG
  5. Type “if” and hit enter. Click the first hyperlinked “value”
    calendar7.PNG
  6. Change “Field from source” to “Title” and click OK
    calendar8.PNG
  7. Click “equals” and select “contains (ignoring case)” from the dropdown
    calendar9.PNG
  8. Click the second “value” link and type “copy: ”
    calendar10.PNG
  9. Click where it says “start typing…” and type “update” and hit enter. Click “this list”
    calendar11
  10. Click “Add…”
    calendar12
  11. Change “Set this field:” to “Title” and “To this value:” via the function button to your WF-NonCopyVersion field. Click OK.
    calendar13
  12. Click beneath Stage 1 and then add two more stages using the “Stage” button in the ribbon.
    calendar14.PNG
  13. Name the stages first, then set transitions as follows. Stages help keep the workflow organized and easy to understand.
    calendar15

Delete canceled events

Again with the automatic prefixes, we find items that have been canceled by event owners appear on our shared calendar as “Canceled: Event Name” and toggle from “Busy” to “Free” status. But we simply don’t need them to be on our shared calendar at all once they’ve been canceled. Let’s automatically have them removed.

  1. In the “Delete canceled events” stage, we’ll do another “If/then” statement as we did above, but this time instead of copy, type “canceled:”
    calendar16
  2. Beneath the if, type delete and hit enter. Then select “this list” and click “OK”calendar17

Standardize location names

Finally we’re ready to make J214, Johnson 214, Rm 214 all one consistent name on our calendars. This will vary for each organization, but if you only have one room 455 in all of your spaces, it might be simple. In this example, I have a Clark Conference Room which is also sometimes called Room 455. I’m going to create an if/then looking for “455” OR “Clark” as those are both unique to that room and either could be used by our employees on the calendar. Adjust to fit your needs.

  1. Create an if/then statement using “Location” for the first “value” field, and use “contains (ignoring case)” again for the middle and whatever room number or name people may use for the second “value” field
    calendar18
  2. Select the line in the workflow you just finished so it’s highlighted in blue like this. Copy it (ctrl+c).
    calendar19
  3. Click directly beneath the line, but not where it says start typing. Paste (ctrl+v) so it looks like this:
    calendar20
  4. Click “and” so it toggles to “or” and click “455” so you can put another name for the space someone might use (if applicable). Now click where it says “Start typing…”, type “Update” and hit enter.
    calendar21
  5. Click “this list” to get the following dialog, click “Add” and set “Location” to the value you want all references to that room to be. Click OK.
    calendar22
  6. Add additional, nearly identical if/then statements for each room you want to standardize. Here’s an example of what your workflow might resemble with several rooms:
    calendar26example

Finishing up and publishing the workflow

Here’s what all our stages look like together:
calendar23

  1. Go to “Workflow settings” and change start options to trigger under all circumstances.
    calendar24
  2. Click “Publish”
    calendar25

That’s all! Best of luck to you and your calendar cleanup.