SharePoint “Cancel Checkout” permissions

When you’re working with item-level permissions in SharePoint, you will see this message in advanced settings:

“Note: Users with the Cancel Checkout permission can read and edit all items.”

chrome_2018-09-02_08-08-18

Here’s how to check to see if a group or individual has that cancel checkout permission, or add it if needed:

Continue reading “SharePoint “Cancel Checkout” permissions”

Solution: “The user who attempted to complete the task is not the user to whom the task is assigned”

You might run into this issue when running task processes in SharePoint Designer.

“Reason: The user who attempted to complete the task is not the user to whom the task is assigned.”

Assuming the person should actually be able to complete the task, check the following:

  • Make sure the user who was assigned the task does not have multiple accounts. If they do, the task could have been assigned to the account that the person isn’t currently signed in as when attempting to complete the task.
  • If it’s someone completing the task on behalf of another, make sure that individual is either:
    • A Site Owner (site settings, site permissions, people and groups, site owners) or
    • Task process owner (in SPD 2010 workflows, go to the properties for the task step and set task process owner. Click OK and republish workflow.):

Extract date components using the TEXT() function in SharePoint calculated columns

calculated date.PNG

In your SharePoint form submissions and list items, sometimes you’d like date fields to be displayed a different way for workflows, notifications, views, grouping, additional metadata, etc. See video at bottom of post for an example use case and tutorial.

Just create a calculated column (format as single line of text) in your SharePoint list or library. Then use any of these formulas, replacing [Created] with the name of the date field from which you’re extracting a piece:

To Extract: Use Formula: Example
Year =TEXT([Created],”YYYY”) 2018
Fiscal Year Range (4-digit) =IF(MONTH([Created])>6,YEAR([Created])&”-“&YEAR([Created])+1,YEAR([Created])-1&”-“&YEAR([Created])) 2018-2019
Fiscal Year Range (2-digit) =IF(MONTH([Created])>6,”FY ” & RIGHT(YEAR([Created])+1,2)&”-“&RIGHT(YEAR([Created])+1,2)+1, “FY ” & RIGHT(YEAR([Created])+1,2)-1&”-“&RIGHT(YEAR([Created])+1,2)) FY 18-19
Quarter =”Q” & CHOOSE( MONTH([Date of inspection])   ,1,1,1 ,2,2,2 ,3,3,3 ,4,4,4) Q3
Month (2 digits) =TEXT([Created],”MM”) 08
Month (abbreviation) =TEXT([Created],”MMM”) Aug
Month (full name) =TEXT([Created],”MMMM”) August
Week Number =”Week ” & IF(ROUNDUP((ROUNDDOWN([Created],0)-(DATE(YEAR(ROUNDDOWN([Created],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Created],0)),1,1))))/7,0)>52,1,ROUNDUP((ROUNDDOWN([Created],0)-(DATE(YEAR(ROUNDDOWN([Created],0)),1,1))+WEEKDAY((DATE(YEAR(ROUNDDOWN([Created],0)),1,1))))/7,0)) Week 35
Day (2 digits) =TEXT([Created],”DD”) 27
Weekday (abbreviation) =TEXT([Created],”DDD”) Mon
Weekday (full name) =TEXT([Created],”DDDD”) Monday

Next-level Tips

  • Build your own date format combining this logic (space and punctuation friendly):
    • =TEXT([Created],”MMMM DD”) for August 27
    • =TEXT([Created],”MMM-YYYY”) for Aug-2018
  • Ampersand (&) joins any strings together.
    • =[Created] & TEXT([Created],”(DDDD)”) will give you Aug-27-2018 (Monday)
    • =”Fiscal Year ” & IF(MONTH([Created])>6,YEAR([Created])&”-“&YEAR([Created])+1,YEAR([Created])-1&”-“&YEAR([Created])) for “Fiscal Year 2018-2019”
    • =[Student Name] & TEXT([Created],” (MMM YYYY)”) gives you Nate Chamberlain (Aug 2018)
  • Experiment with different date formats. YYYY-MM sorts well in lists. I use YYYY-MM (MMM) for clients a lot so it will sort well and also tell you the month name to be crystal clear:
    • =TEXT([Created],”YYYY-MM (MMM)”) gives you 2018-08 (Aug)
  • To prevent “1899” showing up in your calculated column, use an if/then statement to “skip” blank date values or provide default text:
    • =IF([Due Date]<>””,TEXT([Due Date],”YYYY-MM (MMM)”),”No Due Date”)
  • Brackets ([ ]) are not required in formulas for one-word/no-space date fields. Brackets are only needed for “Due Date”, “Start Date” or other multi-word field names. However, they don’t hurt anything if you already have them.
  • You can also use MONTH() and YEAR() to extract just those pieces, but I find the TEXT() function easiest to be able to get exactly what you want and combine multiple values more efficiently.

 

 

 

5 ways you can use SharePoint list and library view settings to improve the user experience

SharePoint has many out-of-the-box (OOTB) ways to improve the way data is displayed in lists and libraries. Many of these can alleviate headache your users experience when adapting to a new way of working with their abundance of ever-growing information. Let’s check out a few things you can do right now, in less than five minutes:

  1. Prevent horizontal scrolling by carefully selecting displayed columns
  2. Sort items appropriately
  3. Filter to relevant info per view
  4. Group items into logical “buckets”
  5. Adjust item limits

Continue reading “5 ways you can use SharePoint list and library view settings to improve the user experience”

Use Microsoft Flow to get the number of items in a SharePoint list or library

Sometimes you need the number of items in a list or library for reporting, notifications, or just curiosity. The following details three methods you can use to get the count of items for different purposes.

  1. Use Microsoft Flow to get the number of items and use in various ways
  2. Add “count” to the top of a classic view SharePoint list for all to see
  3. Quickly find “count” just for your information in site contents or list settings

(Video at bottom of first section)

Continue reading “Use Microsoft Flow to get the number of items in a SharePoint list or library”

Win a $50 Nintendo eShop Digital Gift Card from LSPUG

Asset 6lawrence sp user group3twitter2

The Lawrence SharePoint User Group is about to have its 5th monthly meeting, and we’re so grateful for our amazing speakers and supporters that keep us going.

To celebrate, and in the spirit of continuous improvement, LSPUG has sprouted its own Twitter account, @LSPUGKS. Here you’ll find meeting reminders, recordings, handouts and relevant content. And by helping spread the word, you can be entered to win a $50 Nintendo eShop (Wii U and 3DS) gift card. You can enter multiple times a day between now and the end of September when the drawing will be made.

Enter Here

Don’t forget to join our mailing list to stay up-to-date on LSPUG events and receive the links for live streams and recordings.

 

 

10 reasons putting team/department documents in SharePoint is better than shared drives: Part 2

Asset 1mazeThis post is a continuation of 10 reasons putting team/department documents in SharePoint is better than shared drives.

See part one for information about:

  1. Version history
  2. Approvals/Administration
  3. Check-in/Check-out
  4. Co-editing
  5. Archiving & retention

And below for information about:

  1. Sharing and security
  2. Remote access
  3. Metadata and views
  4. Workflows & alerts
  5. Sync & export

Continue reading “10 reasons putting team/department documents in SharePoint is better than shared drives: Part 2”

10 reasons putting team/department documents in SharePoint is better than shared drives: Part 1

Asset 1mazeYou know that one file, right? The one named “Agenda.docx” in the folder called “November” in the “2008” folder in another folder called “DO NOT Delete” in the “Archive” folder of the “Retired Committees” folder?

Me either. And chances are you don’t need it anymore. But managing team/department documents on traditional shared drives has challenges like this all the time, with management, retention, content ownership, etc. SharePoint, however, can greatly assist in keeping your content current, relevant and organized.

Of course making the switch from shared, common network drives to SharePoint can be intimidating. But the benefits of doing so are well worth the effort to make your team work more efficiently. This post will highlight 10 features in SharePoint you can’t necessarily get from shared network drives:

Part One:

  1. Version history
  2. Approvals/Administration
  3. Check-in/Check-out
  4. Co-editing
  5. Archiving & retention

Part Two:

  1. Sharing and security
  2. Remote access
  3. Metadata and views
  4. Workflows & alerts
  5. Sync & export

Continue reading “10 reasons putting team/department documents in SharePoint is better than shared drives: Part 1”

How to add rows for dates between start and end dates in Power BI date range data

screenshot1
Screenshot from appsource.microsoft.com’s app detail page.

In Power BI there are several custom visuals available, such as Elastacloud’s Calendar Visual, that show the density of events over time. However, if your data includes date ranges (start date with a different end date, such as task/project lists), visuals like these will only understand one of the two dates (whichever you’ve chosen for the data value) and none of the dates in between will be accounted for, making your data visualization incorrect.

You can, however, “expand” these date ranges or durations to create rows for all the dates including start, end and those in between. This way each date that’s part of the range is then graphed appropriately.

Continue reading “How to add rows for dates between start and end dates in Power BI date range data”

Set multiple choices as default values for checkbox fields in SharePoint forms

default checks

I recently worked on a project for a client that needed 2/3 checkbox options checked by default on a new form. While not as straight-forward as other field types, it’s still certainly possible.

  1. Go to List –> List Settings (server/on-prem) or settings wheel –> List Settings (online/O365)
    list settings
    list settings o365
  2. Click the name of your checkbox/choice column to edit its settings or create a new checkbox column
    iexplore_2018-07-26_07-53-51
  3. Change “Default value:” from “Choice” to “Calculated Value”
    iexplore_2018-07-26_07-52-49
  4. Enter a formula like: =”;#Choice;#Choice;#Choice;#”
    Example:
    =”;#Printed Statement;#E-Statement;#Pick Up in Office;#”
  5. Click OK

That’s it! Now on new forms/items, everything you entered in the formula as a default choice will be pre-checked.
pre-checked