4/01/2020

SharePoint vs. Teams vs. OneDrive



A few notes from a recent discussion about where to store files:

 
  • SharePoint Everywhere:
    • SharePoint is behind OneDrive, SharePoint and Teams. Each exposes a different level of functionality.
    • In Teams you can display as “Teams Files”, or click a link and open the backing SharePoint site. In OneDrive, most SharePoint functionality is hidden.
    • SharePoint sites can have multiple libraries (similar to multiple drive letters/network shares) while OneDrive and Teams only expose a single library.
  • Content Location and Ownership:
    • OneDrive is associated with individual users, and the content is “owned” by individuals. (OneDrive is actually SharePoint, but most of the SharePoint functionally is hidden.)
    • SharePoint libraries are associated with a SharePoint Site and the content is “owned” by the Site Owners, who are delegated to that role and can be quickly replaced in the future.
    • Teams file storage is in a disguised SharePoint library. It is “owned” by the Team owners.
  • Content Sharing:
    • OneDrive “sharing” is somewhat “ad hoc” and not driven by corporate policy or best practices. OneDrive users can share with anyone.
    • SharePoint “sharing” can be “ad hoc”, but is usually managed by assigning permissions to users, much like network shares. If done right, SharePoint allows quick and easy auditing of “who has what access to this file”.
    • Teams content by default is shared with the team.
  • Content Sync
    • The default use of OneDrive is via the “Sync” feature. Multiple users editing/deleting sync’d content impacts all users syncing that content.
    • The default use of SharePoint and Teams is closer to network shares. Files are stored there, and downloaded, or viewed/edited online, as needed. Libraries can be mapped to local drive letters. We typically discourage any SharePoint library syncing.


Governance is very important, especially with OneDrive. Some things to consider:
  • Who “owns” a file? Storing the file in OneDrive implies that the user owns it. Storing it in Teams or SharePoint implies the organization owns it. If in OneDrive, what’s the impact if a user leaves the company or the department? If in Teams, what’s the impact if a Team is deleted?
  • Who should grant access to corporate content? A OneDrive user can share with anyone. It’s so easy, the user rarely is thinking about security. In SharePoint or Teams, an “owner” grants and removes permissions for users and can easily update and audit user access.
  • Which copy of a document is the “single source of truth”? If we each have a copy in our OneDrive or local drive, which one is the official version?
  • If data with a legal impact is stored, who should manage it? Each user with their OneDrive, a “content steward” who manages a SharePoint site? A Teams owner (who has been property trained on your governance)?



  •  

7/22/2019

SharePoint Date Validation Formula to Prevent Selecting Holidays


We often want to prevent the selection of certain days when users enter tasks or events into SharePoint lists.

I have over 100 Calculated Column
and Validation formulas in my book


Block weekends


This formula is quite simple and uses the Excel WEEKDAY formula to test the day of the week. WEEKDAY assigns a number to each day using a "scheme". The scheme I'll use here is "2" as it puts Saturday and Sunday as the highest numbers. (You could also use schemes 3 and 11.)

Here's the list of schemes as seen in Excel:
   

Here are two formulas that when used in a SharePoint column validation will prevent the selection of weekend dates. The first eliminates weekend days while the second could be changed to eliminate any pattern of days.

    =WEEKDAY( [yourDateColumn], 2 ) < 6

    =NOT( OR( WEEKDAY( [yourDateColumn], 2 ) = 6, WEEKDAY( [yourDateColumn], 2 ) = 7 ) )

You could also rewrite the last one using ANDs:

    =AND( WEEKDAY( [yourDateColumn], 2 ) <> 6, WEEKDAY( [yourDateColumn], 2 ) <> 7 )


Block Holidays

The ideal solution for holidays would be to create a SharePoint list of holidays. But, lookup columns cannot be used with Calculated Columns or Validation formulas. You can create a workflow to do the lookup, but this will not prevent the selection of the date and will only change a column value after the item has been saved.

So... let's build a formula with a reasonable list of US holidays, say for two years, for a column named [TaskDate]. The OR returns True if any of the date tests are True. The NOT reverses the result because we want to return False as the validation value if the date is a holiday.

Note: Replace [TaskDate] with FLOOR( [TaskDate], 1) if your date column contains dates and times.


=NOT(
     OR(
        [TaskDate]=DateValue("01/01/2019"),
  
     [TaskDate]=DateValue("01/21/2019"),
        [TaskDate]=DateValue("02/18/2019"),
        [TaskDate]=DateValue("05/27/2019"),
        [TaskDate]=DateValue("07/04/2019"),
        [TaskDate]=DateValue("09/02/2019"),
        [TaskDate]=DateValue("10/14/2019"),
        [TaskDate]=DateValue("11/11/2019"),
        [TaskDate]=DateValue("11/28/2019"),
        [TaskDate]=DateValue("12/25/2019"),
        [TaskDate]=DateValue("01/01/2020"),
        [TaskDate]=DateValue("01/20/2020"),
        [TaskDate]=DateValue("02/17/2020"),
        [TaskDate]=DateValue("05/25/2020"),
        [TaskDate]=DateValue("07/04/2020"),
        [TaskDate]=DateValue("09/07/2020"),
        [TaskDate]=DateValue("10/12/2020"),
        [TaskDate]=DateValue("11/11/2020"),
        [TaskDate]=DateValue("11/26/2020"),
        [TaskDate]=DateValue("12/25/2020")
       )
    )


When this formula is added as a Validation formula to a Date/Time column we can now block the entry of holiday dates!





Combine the weekend formula and the holiday formula and your users can only enter weekdays that are not holidays!

=NOT(
     OR(
        WEEKDAY( [TaskDate], 2 )=6,
        WEEKDAY( [TaskDate], 2 )=7,
        [TaskDate]=DateValue("01/01/2019"),
  
     [TaskDate]=DateValue("01/21/2019"),
       
[TaskDate]=DateValue("02/18/2019"),
        [TaskDate]=DateValue("05/27/2019"),
        [TaskDate]=DateValue("07/04/2019"),
        [TaskDate]=DateValue("09/02/2019"),
        [TaskDate]=DateValue("10/14/2019"),
        [TaskDate]=DateValue("11/11/2019"),
        [TaskDate]=DateValue("11/28/2019"),
        [TaskDate]=DateValue("12/25/2019"),
        [TaskDate]=DateValue("01/01/2020"),
        [TaskDate]=DateValue("01/20/2020"),
        [TaskDate]=DateValue("02/17/2020"),
        [TaskDate]=DateValue("05/25/2020"),
        [TaskDate]=DateValue("07/04/2020"),
        [TaskDate]=DateValue("09/07/2020"),
        [TaskDate]=DateValue("10/12/2020"),
        [TaskDate]=DateValue("11/11/2020"),
        [TaskDate]=DateValue("11/26/2020"),
        [TaskDate]=DateValue("12/25/2020")
       )
    )






.

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.