Showing posts with label SharePoint 2013. Show all posts
Showing posts with label SharePoint 2013. Show all posts

2/15/2018

Run SharePoint 2013 and 2016 Search Reports from PowerShell


Updated to include IDs for SharePoint 2016!   Original article here.


Update! Need these reports for every site collection in the farm? See Part 2: http://techtrainingnotes.blogspot.com/2015/04/run-sharepoint-2013-search-reports-from_21.html


In my Search Administration class I stress that admins should dump the search reports on a regular basis as the data is only kept in detail for 14 days and in summary form for 35 months. But who wants to both run these reports at least once every 14 days, even they can remember to do so. So, PowerShell to the rescue… Schedule this script to run each weekend and your work is done.

The following script works for on premise SharePoint 2013. To work with Office 365 you will have to figure out how to include your credentials. The example included here works on premises by using "UseDefaultCredentials = $true".

After lots of hacking, detective work (see below) and just plain trial and error, here's the script:

# This is the URL from YOUR Central Admin Search Service Usage Reports page:
#
# The script will not work unless this is correct!
# $url = "http://yourCentralAdminURL/_layouts/15/reporting.aspx?Category=AnalyticsSearch&appid=ed39c68b%2D7276%2D46f7%2Db94a%2D4ae7125cf567" # This is the path to write the reports to (must exist, but can be anywhere): $path = "c:\SearchReports\" function Get-SPSearchReports ($farmurl, $searchreport, $path, $version) { # TechTrainingNotes.blogspot.com
if ($version -eq "2013")
{ # Report names and IDs $Number_of_Queries = "
21be5dff-c853-4259-ab01-ee8b2f6590c7" $Top_Queries_by_Day = "56928342-6e3b-4382-a14d-3f5f4f8b6979" $Top_Queries_by_Month = "a0a26a8c-bf99-48f4-a679-c283de58a0c4" $Abandoned_Queries_by_Day = "e628cb24-27f3-4331-a683-669b5d9b37f0" $Abandoned_Queries_by_Month = "fbc9e2c1-49c9-44e7-8b6d-80d21c23f612" $No_Result_Queries_by_Day = "5e97860f-0595-4a07-b6c2-222e784dc3a8" $No_Result_Queries_by_Month = "318556b1-cabc-4fad-bbd5-c1bf8ed97ab1" $Query_Rule_Usage_by_Day = "22a16ae2-ded9-499d-934a-d2ddc00d406a" $Query_Rule_Usage_by_Month = "f1d70093-6fa0-4701-909d-c0ed502e3df8" }
else # 2016
{
$Number_of_Queries          = "df46e7fb-8ab0-4ce8-8851-6868a7d986ab"
$Top_Queries_by_Day         = "06dbb459-b6ef-46d1-9bfc-deae4b2bda2d"
$Top_Queries_by_Month       = "8cf96ee8-c905-4301-bdc4-8fdcb557a3d3"
$Abandoned_Queries_by_Day   = "5dd1c2fb-6048-440c-a60f-53b292e26cac"
$Abandoned_Queries_by_Month = "73bd0b5a-08d9-4cd8-ad5b-eb49754a8949"
$No_Result_Queries_by_Day   = "6bfd13f3-048f-474f-a155-d799848be4f1"
$No_Result_Queries_by_Month = "6ae835fa-3c64-40a7-9e90-4f24453f2dfe"
$Query_Rule_Usage_by_Day    = "8b28f21c-4bdb-44b3-adbe-01fdbe96e901"
$Query_Rule_Usage_by_Month  = "95ac3aea-0564-4a7e-a0fc-f8fdfab333f6"
} $filename = $path + (Get-Variable $searchreport).Name + " " + (Get-Date -Format "yyyy-mm-dd") + "
.xlsx" $reportid = (Get-Variable $searchreport).Value $TTNcontent = "&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId%3D" + $reportid # setup the WebRequest $webRequest = [System.Net.WebRequest]::Create($farmurl) $webRequest.UseDefaultCredentials = $true $webRequest.Accept = "image/jpeg, application/x-ms-application, image/gif, application/xaml+xml, image/pjpeg, application/x-ms-xbap, */*" $webRequest.ContentType = "application/x-www-form-urlencoded" $webRequest.Method = "POST" $encodedContent = [System.Text.Encoding]::UTF8.GetBytes($TTNcontent) $webRequest.ContentLength = $encodedContent.length $requestStream = $webRequest.GetRequestStream() $requestStream.Write($encodedContent, 0, $encodedContent.length) $requestStream.Close() # get the data [System.Net.WebResponse] $resp = $webRequest.GetResponse(); $rs = $resp.GetResponseStream(); #[System.IO.StreamReader] $sr = New-Object System.IO.StreamReader -argumentList $rs; #[byte[]]$results = $sr.ReadToEnd(); [System.IO.BinaryReader] $sr = New-Object System.IO.BinaryReader -argumentList $rs; [byte[]]$results = $sr.ReadBytes(10000000); # write the file Set-Content $filename $results -enc byte } # Note: Change the version to 2013 or 2016
Get-SPSearchReports $url "
Number_of_Queries" $path "2013" Get-SPSearchReports $url "Top_Queries_by_Day" $path "2013" Get-SPSearchReports $url "Top_Queries_by_Month" $path "2013" Get-SPSearchReports $url "Abandoned_Queries_by_Day" $path "2013" Get-SPSearchReports $url "Abandoned_Queries_by_Month" $path "2013" Get-SPSearchReports $url "No_Result_Queries_by_Day" $path "2013" Get-SPSearchReports $url "No_Result_Queries_by_Month" $path "2013" Get-SPSearchReports $url "Query_Rule_Usage_by_Day" $path "2013" Get-SPSearchReports $url "Query_Rule_Usage_by_Month" $path "2013"


The Detective Work…

I could not find anything documented on how the reports are called or details on things like the report GUIDs. So here's how I got there:

  • Go the search reports page in Central Admin and press F12 to open the Internet Explorer F12 Developer Tools then:
    • Click the Network tab and click the play button to start recording.
    • Click one of the report links.
    • Double-click the link generated for the report in the F12 pane to open up the details.
    • Make note of the URL (It's the same as the report page!)
    • Note the Accept, and Content-Type Request Headers.
    • Click the Request Body tab.
    • Stare at 3000 characters in that string until your head really hurts, or until you recognize most of what is there is the normal page postback stuff like VIEWSTATE. So we need to find what's unique in the string. (It's the Report IDs.)
    • Click on each of the nine reports and copy out the report IDs.
    • With a lot of trial and error figure out what the minimum string needed is to generate the reports. (It's ""&__EVENTTARGET=__Page&__EVENTARGUMENT=ReportId" plus the report id.)
    • Find out how to do an HTTP POST using PowerShell. (Steal most of it from here: http://www.codeproject.com/Articles/846061/PowerShell-Http-Get-Post.)
    • Find some other needed .Net code and convert the C# to PowerShell.
    • Fill in some gaps with PowerShell putty …….


.

        1/22/2018

        SharePoint Search Weirdness – Part 5: Search REST API Ignores Duplicates


        A continuation of the "Search Weirdness" series!


        If you are a developer, or a SharePoint 2013 workflow designer, then you probably have used the SharePoint Search REST API. Did you know that you are probably not getting all of the results expected?

        Here’s a typical REST search for the word “sharepoint”:

        http://yourSiteUrl/_api/search/query?querytext='sharepoint'

        Or if you would like to be a little more selective:

        http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'

        or you would like to return more than the default number of items:

        http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&rowlimit=1000


        The problem with the above searches is that Search thinks some of your results are duplicates, so it removed them! To solve this problem just add this to your URL:

            &trimduplicates=true

        Your search URLs then might look like these:

        http://yourSiteUrl/_api/search/query?querytext='sharepoint'&trimduplicates=true

        http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&trimduplicates=true

        http://yourSiteUrl/_api/search/query?querytext='sharepoint'&selectproperties='Title,Author'&refinementfilters='fileExtension:equals("docx")'&rowlimit=1000&trimduplicates=true


        .

        1/15/2018

        Adding HTML to SharePoint Columns – Color, Images and More – Round 2!


        Back in June Microsoft announced they were blocking HTML created by Calculated Columns with the June 2017 Public Update (PU) for SharePoint 2013, 2016 and SharePoint Online.

        See here: http://techtrainingnotes.blogspot.com/2017/12/no-more-html-in-sharepoint-calculated.html

        Before the June update:

        image

        After the June update:

        image


        You can turn this new “feature” off using PowerShell… but only for on-prem.

        https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

        $wa = Get-SPWebApplication http://yourWebAppUrl
        $wa.CustomMarkupInCalculatedFieldDisabled = $false
        $wa.Update()

        Repeat for each web application as needed.


        There’s a workaround!

        There's a fairly simple solution that works in all versions, if you don't mind using a workflow.

        1. Edit the Calculated Column with the HTML and change it's "The data type returned from this formula is" back to "Single Line of Text". (Just change the result type... leave the column as a Calculated Column.)
        2. Create a new Multiple Lines of Text column and set it to "Enhanced rich text (Rich text with pictures, tables, and hyperlinks)".
        3. Create a workflow that simply copies the Calculated Column to the new Multiple Lines of Text column. Set the workflow to run on Created and Changed.
        4. Edit your views to hide the Calculated Column and add the Multiple Lines of Text column.

        The workflow is just a single Set Field in Current Item action.

        imageimage

        Set the “field” to the new Multiple Lines of Text column and set “value” to the Calculated column. Publish and test!

        This solution will let you keep the Calculated Column for easy revising of the formula logic. You could also let the workflow do all of the work to create the logic and HTML using a String Builder, and eliminate the need for the Calculated Column.


        Update the Existing Items

        You now need to get the workflow to run on all of the existing items. You can run a PowerShell script to start the workflows, you can run a PowerShell script just to copy the data from the Calculated column the new column, you can manually run the workflows on each item, or if you don't mind the Modified date and Modified By being changed switch to the Quick Edit view and copy all the items in one column and then paste them right back.


        After the workaround:

        image

        What does not work?

        Script tags and Style tags. (and I’m sure there are a few more) Style blocks are emptied and script blocks are completely removed. But, basic HTML for hyperlinks, image tag, etc. still work.

        Before: <style>#test { color:red }</style><script>alert(1)</script> more HTML…

        After: <style></style> more HTML…

        .

        1/11/2018

        SharePoint 2013 Workflow Error: Invalid Text Value


        I got the not so obvious error below from a simple SharePoint 2013 style workflow.

        Invalid text value.
        A text field contains invalid data. Please check the value and try again.

        The problem was actually pretty simple. I was trying to write more the 255 characters to the Workflow Log. You will get the same error writing more than 255 characters to a Single Line of Text column. You can use an Extract Substring from Start of String action to retrieve only the first 255 characters.


        image

        12/23/2017

        No More HTML in SharePoint Calculated Columns!

        Update: Here's a workaround using a workflow: http://techtrainingnotes.blogspot.com/2018/01/adding-html-to-sharepoint-columns-color.html


        Just in case you missed it:
        “Some users have added HTML markup or script elements to calculated fields. This is an undocumented use of the feature, and we will block the execution of custom markup in calculated fields in SharePoint Online from June 13, 2017 onwards. The June 2017 Public Update (PU) and later PUs will make blocking a configurable option for on-premises use in SharePoint Server 2016 and SharePoint Server 2013.”
        https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields

        So, no more of this:
           image
        Or this:
            =REPT("<img src='http://yourPath/yourImage.GIF' style='border-style:none'/>",[Value])
          image

        11/24/2017

        SharePoint: Running JavaScript Code Only When in Page Edit Mode


        One of my old Content Editor Web Part “tricks” would not work when run in a Publishing page. I was hiding a web part when not in Page Edit mode. You can detect this mode by checking for ‘PageState.ViewModeIsEdit != "1"’. The PageState object is automatically created in normal site pages (“Wiki Pages”). It is also created in Publishing Pages, but only when the page has not been published. I.e. when the page is checked out, or in edit mode.

        My code originally looked like this:

          if ( PageState.ViewModeIsEdit != "1" )
          {  …code to run when not in edit mode… }


        As the PageState object does not exist in a Published page, I had to change it to this:

          if ( typeof PageState == 'undefined' || (PageState.ViewModeIsEdit != "1") )
          {  …code to run when not in edit mode… }


        Of course… none of the above works in SharePoint Online “Modern UI” pages!


        .

        10/26/2017

        SharePoint Audiences are not Security!


        In a nutshell… The SharePoint Audience feature is not security… ever. Audiences are used to filter (hide), not secure.

        Some may consider the use of Audiences as “security by obscurity”, but it is not security.



        From my SharePoint Security book…  (on Amazon – 2013/2016 version coming soon.)

        Audiences

        SharePoint audiences are used to target content to specific groups of people by hiding it from those who don’t need to see it. These groups can be SharePoint groups, Active Directory security groups, Active Directory distribution lists and SharePoint global audiences that are based on user profile data.

        Audience targeting can be used with:

        • List and library items, but only when displayed using a Content Query Web Part (part of the Publishing feature).
        • Entire web parts.
        • Top Link and Quick Launch navigation links. (when Publishing features are enabled)

        Note: The Audiences feature is only available with SharePoint Server Standard and Enterprise editions. SharePoint Foundation has no support for Audiences. (Audiences is part of User Profile Services.)

        Audiences are not security!

        Audiences are usually described as being used to "target" content to a selected group. Audiences could also be described as being used to hide content from all users except for the target audiences. This second form sounds like security, but absolutely is not. While a list item or document web part might have a target audience, and non-audience members won't see the web part, if it is not otherwise secured they can still get to the item by using a direct URL or find it from search.

        The Audience feature should be thought of as a filtering option, not security.


        To filter list items using an Audience

        List and library items can be filtered using the Audience feature using the Publishing feature’s Content Query Web Part. While the regular list web parts have an Audience feature, that feature hides the entire web part, not selected items. The Content Query Web Part is added to a site collection when you enable the Publishing features.

        Four steps are required to filter list content using an Audience:

        · Create a publishing site or enable the Publishing Infrastructure feature on a site collection.

        · Enable the Audience feature on the list.

        · “Tag” list items by Audience.

        · Display the list using the Content Query Web Part.

        Step 1: Enable the SharePoint Server Publishing Infrastructure Site Collection feature

        First make sure there are no other reasons to not enable the Publishing features. (Policy, support, governance, etc.)

        1. Go Settings (gear), Site Settings of the top level site of the site collection.

        2. In the Site Collection Administration section click Site collection features.

        3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

        Step 2: Enable the Audience feature on the list or library

        1. Go the list or library, click the LIST or LIBRARY tab in the ribbon.

        2. Click List Settings or Library Settings.

        3. Click Audience targeting settings.

        4. Checkmark Enable audience targeting.
        A new field named Target Audiences will now be displayed in the New and Edit pages for the list.

        5. Click OK.

        Step 3: “Tag” list items by Audience

        1. Edit the properties of a list or library item.

        2. In the Audience area click the Browse button ( ) and select an Audience.

        3. Save the changes to the item.

        Step 4: Display the list using the Content Query Web Part

        1. Move to your page where you want to display the web part.

        2. Edit the page.

        3. Click the Insert ribbon tab and the Web Part button.

        4. Click on the Content Rollup category and then click the Content Query web part. (If the web part is not listed then you do not have the SharePoint Server Publishing Infrastructure site collection Feature enabled.)

        5. Click Add to add the web part to the page.

        6. Click the web part’s dropdown menu and click Edit Web Part.

        7. Expand the Query section.

        8. Select the Source (the scope) for the rollup from one of the following: Show items from all sites in this site collection, Show items from the following site and all subsites, or Show items from the following list.

        9. Select the List Type and the Content Type to select the content to display from the Source selected above.

        10. In the Audience Targeting section checkmark Apply audience filtering.

        11. Optionally add filters or Presentation options.

        12. Click OK to save your web part changes.

        13. Save the page and test the results.

        Note: the Target Audience option in the Advanced section of the web part’s property panel is used to control if the entire web part will be displayed for an audience.

        Search Web Parts vs the CQWP

        Microsoft currently recommends using the Search Web Parts in many of the places that we might have used the CQWP. While search uses cached content, and can be quite a bit faster, the data is only as current as the last search crawl. (I.e. Completed tasks may still display as incomplete.) The CQWP is always using live data and can be Audience filtered.

        Resources for the CQWP:

        Display data from multiple lists with the Content Query Web Part:
        https://support.office.com/en-US/article/Display-a-dynamic-view-of-content-on-a-page-by-adding-the-Content-Query-Web-Part-3e35bd58-d159-43d6-bfc7-77878b4a856d (or just do a web search for “Display a dynamic view of content on a page by adding the Content Query Web Part”)


        To show web parts for an Audience

        You can use Audiences to hide an entire web part from all users except for the selected audiences. Simply edit the web part, expand the Advanced section and select an audience.

        Note: The SharePoint Server Publishing Infrastructure Site Collection feature is not needed for web part Audience filtering.


        To display a Quick Launch or Top Link Bar link for an Audience

        Links in the Quick Launch and the Top Link Bar can be filtered by Audience when the Publishing Infrastructure feature has been activated. Once this feature has been activated the Quick Launch and Top Link Bar options are replaced with a single Site Settings option named Navigation. In the Navigation page Quick Launch is called Current Navigation and the Top Link Bar is called Global Navigation.

        Enable the SharePoint Server Publishing Infrastructure Site Collection feature:

        1. Go Settings (gear), Site Settings of the top level site of the site collection.

        2. In the Site Collection Administration section click Site collection features.

        3. If not already activated, activate the SharePoint Server Publishing Infrastructure feature.

        To filter navigation links:

        1. Go Settings (gear), Site Settings.

        2. In the Look and Feel section click Navigation.

        3. Scroll down to the Navigation Editing and Sorting section of the page.

        4. Add or edit a Heading or a Link.

        5. In the Audience area click the Browse button ( ) and select an Audience.

        6. Set the Title, URL and other options as desired and click OK.

        7. Test! The new navigation item should only be displayed for the selected Audiences.

        4/13/2017

        A SharePoint REST API Tester with an AJAX and Workflow Writer

         

        A JavaScript project to use with a Content Editor Web Part to test SharePoint REST API calls, and create AJAX sample code and SharePoint Designer 2013 Workflow steps. It includes over 40 ready to test samples to query SharePoint and to create and delete items, folders, lists and sites.

        While learning the SharePoint REST API, I created a little REST tester Content Editor Web Part. Later when I explored SharePoint 2013 Workflow REST calls I expanded the tool to include step by step instructions to add the calls to a workflow. After presenting this at the Cincinnati SharePoint User Group and at the Nashville SharePoint Saturday I decided to take the time to clean it up a bit and share it here.

        What you will need:

        You can also download the file from the GitHub project.



        This is the main screen.

        image

        This is partial list of the sample REST calls. A more complete list is at the end of this article, and I’ll be adding more over time.

        imageimageimage

         

        The in the page test of a REST call.

        image

         

        The generated AJAX Code Sample

        image

         

        The SharePoint 2013 Workflow Steps for the Web Service Call

        image

         

        Steps to install to your SharePoint Site
        1. If your master page is not already loading jQuery, download jQuery (just about any version) and upload to the Site Pages library. 
        2. Download the SharePointRESTtester.html file to your PC. 
        3. Edit the file and update the line that loads jQuery to point your jQuery file or CDN.
        4. If your master page already loads jQuery, then delete the <script> block that loads the jQuery file.(the first line of the file)
        5. Upload the SharePointRESTtester.html file to your Site Pages library. (Copy the URL to the file.)
        6. Add a Web Part Page to your project:
          1. In the Site Pages library, click the FILES ribbon, click New Document and click Web Part Page.
          2. Enter a page name like "SharePointRESTtester". 
          3. From the library dropdown select Site Pages
          4. Click Create.
        7. Click Add a Web Part
        8. Add a Content Editor Web Part.
        9. Click the web part's dropdown and click Edit Web Part.
        10. Enter or paste the path to the SharePointRESTtester.html file.
        11. Click OK and then in the ribbon click Stop Editing.
        12. You should now see the tester. Click the dropdown and you should see data in the boxes. If not, then the jQuery library did not get loaded.
        13. Add to your Quick Launch or your Follow list!

         

        To use the tester…
        1. Select a sample from the dropdown, or enter your own URL, Method, Header JSON and if needed, the Body JSON.
        2. Find the Do It! button. The first check box will actually run the code. *** Warning Will Robinson, stuff could get added, changed or deleted! ***
        3. The second and third checkboxes simple hide or show the JavaScript Ajax code and the SharePoint 2013 workflow steps.

         

        SharePoint REST Examples for Queries

        • Get information about the current site collection.
        • Get information about the current web.
        • Get the Regional Settings for the current web.
        • Get the Time Zone for the current web.
        • Get SharePoint's list of Time Zones.
        • Get a list of all webs below the current web.
        • Get the primary site collection administrator (Owner).
        • Get the primary site collection Secondary Contact.
        • Get a web's LastItemModifiedDate
        • Get a list of lists from the current web. (all data)
        • Get a list of lists from the current web. (Just the title)
        • Get a count of items in a library.
        • Get a count of items in a library. (Option #2)
        • Get all items in a list/library.
        • Get all items in a library with filename and path.
        • Get a list folder's properties.
        • Get a count of items in a list folder.
        • Get all items in a list/library filtered by date.
        • Get all items in web level recycle bin.
        • Get selected properties of all items in web level recycle bin.
        • Get all items in a list/library filtered by a range of dates.
        • Search
        • People Search

        SharePoint REST Examples for Lists

        • Create a new list
        • Add a new item to a list
        • Add a new folder to a list
        • Delete an item from a list using ID
        • Delete an item, to the Recycle Bin, from a list using ID
        • Update an item using ID
        • Delete a list
        • Delete a list to the Recycle Bin

        SharePoint REST Examples for Sites

        • Create a new subsite.
        • Delete a site (Warning Will Robinson! Does not go to the Recycle Bin!)

        SharePoint REST Examples for User Profiles

        • Get User Profile info about the current user.
        • Get all User Profile properties for a user.
        • Get User Profile info about a user's manager.

        SharePoint REST Examples for Permissions

        • Get a list of Role Definitions for a site.
        • Get a list of Site Users. The ID is useful when setting permissions.
        • Get a list of Site Groups. The ID is useful when setting permissions.
        • Get a list of Site Groups by name.
        • Get a list of Site Groups where name contains 'string'.
        • Break inheritance on a subsite.
        • Break inheritance on a list.
        • Break inheritance on a list item.
        • Grant permissions (Role Assignment) on a list.
        • Remove permissions (Role Assignment) on a list.

        SharePoint REST Examples for Filter Select and OrderBy

        • Get a list of Site Users who are not Site Collection admins. Get selected fields and sort.

        SharePoint REST Examples for SharePoint 2010 style REST - _vti_bin/ListData.svc

        • Get a list of lists and libraries (EntitySets).
        • Find list items greater than a date.
        • Find list items between two dates.

         

        .

        3/31/2017

        SharePoint Date Search Tips

         

        Applies to SharePoint 2013 and later.

         

        A few SharePoint Search Tips!

         

        Time Zone

        Search internally stores dates in Universal Time. Because of this, a file uploaded at “2/7/2017 10:50 PM EST” will not be found with a search for “Write=2/7/2017” (or “LastUpdateDate=2/7/2017”). That file will be found with a search using “Write=2/8/2017”.

         

        Date Ranges

        You can create searches on date ranges using “..”.

              Example: write=2/1/2017..2/8/2017

         

        Named Date Ranges

        You can also use the names of some date ranges. Quotes are required around any range name that includes a space.

             Example: write="this week"

            image

        The supported ranges are:

        • today
        • yesterday
        • this week
        • this month
        • last month
        • this year
        • last year

        But sadly… no “"last week”!

         

        Comparison Operators

        Note: All of the following operators work with the DateTime, Integer, Decimal and Double data types.

        Operator

         
         

        Equals

        <

        Less than

        >

        Greater than

        <=

        Less than or equal to

        >=

        Greater than or equal to

        <>

        Not equal to

        ..

        Range

         

        .

        11/17/2016

        Creating Random Numbers in SharePoint Calculated Columns

         

        One of my examples for tonight’s Cincinnati SharePoint User Group meeting! See you there!

         

        I wanted to add a "motivational" message to a list of new sales. To be "fair" (i.e. I did not want to think and create a good algorithm!) I wanted the messages to be random. Something like this:

           image

        But… Calculated Columns do not support the Excel RAND() or RANDBETWEEN() functions.

         

        So, how to get a random number???

        Calculated columns do support the =Now() function. This returns a numeric value that represents the current date and time. If formatted as a Date, or Date and Time, then you will see the current date. But, if you format it as Single Line of Text you will see something like: 42,691.3977137731, or a few seconds later: 42,691.3983521875. The last number starts to look like a random number! And if accurate, it changes every .0000000001 of a day, or about every 0.00000864 seconds. Close enough for me.

         

        Get a random number between 0 and 9.

        This one looks easy, just pull off the last digit from NOW()!

            =RIGHT( NOW() ,1)

        But.. there’s one flaw with this… The last digit of a fractional value is never zero!  (I.e. you will never see .111111110 unless custom formatted.)

        So we need to pull off the next to last digit!

          =LEFT( RIGHT( NOW() ,2) ,1 )

        image

        image

         

        Get a random number between 1 and 5

        With just a little math we can limit the range a bit. As we don’t want the zero value we can skip the LEFT function for this one.

           =ROUND( RIGHT( NOW()) / 2+0.5 ,0)

           image

        Here’s a sample:

           image

         

        Get a random number between 0 and 999.

        If you need bigger numbers, just return more digits:

            =RIGHT(NOW(),3)

        As RIGHT creates a string (text), you will get leading zeros (“012”). To remove the leading zeros just do some math!

            = 0 + RIGHT(NOW(),3)

           image

        But… (there’s always a “but”), this will never return a value that ends with a zero. So… back to the LEFT function:

            =LEFT( RIGHT(NOW(),4), 3)

        I.e. get the left three of the right four digits…

        image

         

        Random Messages?

        This little exercise started out to create random messages. All we need to do is combine a random number with the CHOOSE function. As CHOOSE starts with item 1 and not item 0, we will need to add one to the random number.

           =CHOOSE( LEFT( RIGHT( NOW() ,2), 1) + 1, "Good Job", "Wow!", "Good Work", "Thanks!", "Could be better",
                              "Gold star for you!", "a free coffee for you!",":-)", "You are the MAX!","Do it again!" )

        image

         

        Notes

        • These are not guaranteed to be mathematically pure random numbers!
        • The values depend on the exact instant that an item is added to a list and will change with each edit. (But will not change with each view.)

        .

        8/20/2016

        Hide the Windows Explorer Button in SharePoint Libraries

         

        Tested in SharePoint 2013, 2016 and SharePoint Online.

         

        The Windows Explorer view of a SharePoint library has so many issues that I'm often asked to hide it. Turns out that this is pretty easy to do. Two solutions:

        • Create a SharePoint Feature and deploy it to the desired site collections.
        • Add CSS to your master page, or to selected view pages.

         

        Create a SharePoint Feature and deploy it to the desired site collections

        This is the best solution! And it's been documented elsewhere: https://blogs.msdn.microsoft.com/tejasr/2010/07/19/how-to-remove-open-with-windows-explorer-button-from-document-librarys-ribbon-menu/

        I would only add one more step to this solution… make sure the WSP file does not include an unneeded DLL so the solution can be deployed to SharePoint Online. (No code allowed!) The one extra step: In the project's Properties panel click "Include Assembly in Package" and change it to False.

        Once the Feature has been installed in the Site Collection, just visit each subsite and activate the feature. This will impact all libraries in the site.

         

        Add CSS to your master page, or to selected view pages

        Add one little piece of CSS to your master page, or open SharePoint Designer and edit the library's views to add a CSS block and the button will disappear. If added to the master page then this will impact every library in the site. If added to a view page, then it will impact only that view.

        This CSS will not work with the SharePoint Online “New Library Experience”. But then the new “experience” does not currently include a link for Windows Explorer!

        The CSS:

        <style type="text/css">
          #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
            display: none;
          }
        </style>
        

        Note: The backslashes have been added to the ID due to the non-standard naming convention that uses periods.

        If you would like site owners to still be able to see the button then wrap additional CSS in a SharePoint:SecurityTrimmedControl. Note that this control can only be added directly to a page, typically using SharePoint Designer. It will not work if added to a Content Editor Web Part.

        <style type="text/css">
          #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
            display: none;
          }
        </style>
        <Sharepoint:SPSecurityTrimmedControl runat="server" PermissionsString="ManageWeb">
          <style type="text/css">
            #Ribbon\.Library\.Actions\.OpenWithExplorer-Small {
              display: inline;
            }
          </style>
        </SharePoint:SPSecurityTrimmedControl>

         

        .

        8/18/2016

        Auto-populated Choice Columns in SharePoint!

         

        This was tested in SharePoint 2013 and 2016.

        Had a list with 100,000 items with a State column. I found that some of my users did not know their state abbreviations. (KE is Kentucky?) After cleaning up the "nonstandard" states, I decided to convert the column from Single Line of Text to Choice.

        Magic!

        After editing the column and clicking "Choice" I scrolled down and found that the list of choices was already populated!

        image_thumb[1]

        For this to work for lists with more than the List View Threshold number of items (5,000 by default) you will need to be:

        • a server administrator, or
        • an auditor (Configured in Web Application settings, and only for under 20,000 items.), or
        • working with the list during Happy Hour!  (Offically “Daily Time Window for Large Queries”.)

         

        Bonus!

        The State column was now available in my Metadata Navigation Settings options.

        image_thumb[2]image_thumb[3]

         

        Too Easy!

        In the future when importing large amounts of list data I’ll just make the columns that should be Choice as Single Line of Text and then after the import change them to Choice.

        .

        8/11/2016

        Using Relative URLs in SharePoint 2013 Workflow Calls

         

        (For SharePoint 2013, 2016 and SharePoint Online.)

        It's generally a good idea to use relative URLs when creating something that you will want to use in more than one place. While not real obvious, you can easily do this in SharePoint 2013 workflow web service calls.

        Absolute URL:
           http://yourServerName/sites/yourSiteName/_api/web

        Relative URL:
           /sites/yourSiteName/_api/web

        What we would like to have in a workflow web service call:
           _api/web

        Steps:

        1. After adding your Call HTTP Web Service action, click “this”
          image.
        2. Click the "" button to open the String Builder dialog.
           image
          1. Click the Add or Change Lookup button.
          2. For Data source select Workflow Context.
          3. For Field from source select Current Site URL.
          4. Immediately after the lookup place holder (i.e. no spaces) type the rest of the URL for the web service call:
               _api/web

            image
          5. Click OK.
        3. Continue with web service call action configuration…

         

        As you can probably guess… I’m working on a new class that includes workflows!

        .

        6/11/2016

        SharePoint Column Validation Examples

        Now available on Amazon!

        image

        Over 100 Examples!

        A how-to book of formulas would not be too useful without a few examples. I've been collecting these for years. They've come from classroom questions, forum questions, and my own SharePoint sites. Now they are all in one place…

        • Over 60 Calculated Columns examples
        • Over 30 Column Validation examples
        • 11 List/Library Item Validation examples
          (and every one of the Column Validation examples can be used here.)
        • 7 Calculated Column Default Values examples
        • 15 Workflow “workarounds” for things SharePoint formulas can’t do

        Update 11/18/2017… added test for nearest 1/4th, 1/10th, etc.
        Update 11/2/2015… added "Date must be the first day of the month" and "Date must be the last day of the month".

        The following applies to SharePoint 2007, 2010, 2013, 2016 and SharePoint Online/Office 365.

         

        Column Validation

        SharePoint does not include column types for phone numbers or part numbers, nor does it include support for Regular Expressions to test for character patterns. It does support Excel style functions that we can use to create useful column validation formulas.

        Below you will find column validation examples for:

        • OR
        • AND
        • Length (LEN)
        • Pattern matching using SEARCH and FIND
        • Date testing


        General Validation Formula Rules:

        • Formula must return True or False.
        • Column validations can only be added to Single Line of Text, Number, Choice (Drop-Down menu or Radio buttons, but not Checkboxes), Currency and Date and Time columns.
        • Expressions are generally Excel compatible, but not all Excel functions can be used.
        • Field names without special symbols can be entered as is or in square brackets
                = Price * [Qty]  > 100
        • Field names with spaces or symbols must be enclosed in square brackets
                =OR( [Sales Region] = 1, [Sales Region] = 1)
        • The text comparisons are not case sensitive.
                =OR( status = "a", status="c")     is true for either "A" or "a" or "C" or "c".
        • In a column validation the formula cannot refer to another column.
        • In a list / library validation the formula can refer to other columns in the same item.


        Examples using "OR":

        The OR function accepts two or more Boolean tests that each return True or False. OR returns True if any one of the tests is True.

        =OR(YourFieldName="A",YourFieldName="C",YourFieldName="E")

        =OR(State="OH", State="IN", State="KY", State="MI")

        =OR(Qty=5, Qty=10, Qty=20)


        Examples using "AND":

        The AND function accepts two or more Boolean tests that each return True or False. AND returns True if all of the tests are True.

        =AND(YourFieldName>"A", YourFieldName<"M")     YourFieldName value must be between A and M.

        =AND(Qty>5, Qty<100, Qty<>47)      Qty must be between 5 and 100, but not 47.


        Examples using "LEN":

        As an example, if your part numbers are always 9 characters long:
            =LEN(YourFieldName) = 9

        If the part numbers can be 9 or 12 characters long:
            =OR( LEN(YourFieldName) = 9, LEN(YourFieldName) = 12 )


        Examples for Pattern Matching

        The SEARCH function:  (online help)

        • Matches a pattern using "*" and "?". "*" equals zero more characters and "?" equals exactly one character.
        • To match an asterisks or question mark character prefix the symbols with "~". 
          Example: "a~?b?c" matches "a?bxc" but not "axbxc". 
        • An "*" is assumed to be appended to the end of the match pattern. To limit the length use the AND and LEN functions.
        • The comparison is not case sensitive.
        • If there is a match, the function returns the position of the match. If the every character is to be matched you would typically test for "=1" or maybe ">0". 
        • If there is no match, the function returns ERROR, therefore it must be wrapped inside of an ISERROR function. As we will have a match if there is no error, the ISERROR must be wrapped inside of a NOT function. (online help for ISERROR)

        Examples:

        Must start with an "a" or "A" and the third character must be a "c" or "C":
           =NOT(ISERROR( SEARCH("A?C",YourFieldName)=1 ))

           Matches: abc   AbC  aXc  a6c aBcDEF
           Does not match:   bbb   abb  ac  a

        Match a phone number pattern of xxx-xxx-xxxx: (note: user could type letters or digits or type extra characters.)
           =NOT(ISERROR( SEARCH("???-???-????",YourFieldName)=1 ))

           Matches: 123-123-1234    aaa-aaa-aaaa   123-123-12344444

        Match a phone number pattern of xxx-xxx-xxxx and limit the length:
           =AND( NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))), LEN(YourFieldName)=12 )

           Matches: 123-123-1234
           Does not match: 123-123-12345


        Match a phone number and make sure only digits have been used:

        The first example here is not a true pattern match. It just extracts the characters we think should be digits and tries to multiply them by any number. If that fails, then one or more of the characters is not a number. (online help for CONCATENATE and MID)

        =NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4))))

           Matches: 123-123-1234    123x123x1234   123-123-1234xxxxx
           Does not match: abc-123-1234

        The second example combines the earlier pattern match with a numeric test:

           =AND(NOT(ISERROR(SEARCH("???-???-????",YourFieldName,1))),LEN(YourFieldName)=12, NOT(ISERROR(1*CONCATENATE(MID(YourFieldName,1,3),MID(YourFieldName,5,3),MID(YourFieldName,9,4)))))


        The FIND Function:  (online help)

        The FIND function is similar to the SEARCH function with two differences;

        • FIND is case sensitive.
        • FIND does not support wild cards.


        Examples for Numbers

        Validate if a number ends in either .25, .50 or .5 or .75 or is a whole number.

             =ROUND([Activity Effort]*4,0)/4 = [Activity Effort]

        If you wanted the number to the nearest 10th then divide by 10, round then multiple by 10.
             =ROUND([Activity Effort]*10,0)/10 = [Activity Effort]
        This works all of the time for numbers with non-repeating digits. I.e. It will not work for 1/3 as 0.333333333333... can't be truly represented as a fixed set of digits. (It will actually work for 1/3 if you know the right number of digits to type! it looks like it's 15 significant digits: 0.333333333333333, 0.666666666666667 and 1.33333333333333 will work for 1/3, 2/3 and 1 1/3 with =ROUND([Activity Effort]*3,0)/3 = [Activity Effort] as the validation.)


        Examples Using Dates

        You can create rules to limit date ranges by using the TODAY() function or the DATEVALUE() function.

        Date must be in the future:
            =YourFieldName>TODAY()

        Date must be in the future by "x" days:
            =YourFieldName>TODAY() + 3
        I.e. If today is the 7th, then valid dates start on the 11th.

        Test against a particular date:  (online help for DATEVALUE)
            =YourFieldName>datevalue("1/1/2015")

        Date must be between now and the end of the current year:  (online help for YEAR)
            =YourFieldName < DATEVALUE( "12/31/" & YEAR(TODAY()) )
        This example calculates a DATEVALUE by building a string to represent a future date.

        Date must be within the next 30 days:
            =AND(YourFieldName >= TODAY(),YourFieldName <= TODAY()+30)

        Date must be a Monday:   (1 = Sunday, 2 = Monday, 3 = Tuesday, …)   (online help for WEEKDAY)
            =WEEKDAY(YourFieldName)=2

        Date must be the last day of the month:
        =DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn)+1,0)

        Date must be the first day of the month:
        =DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),DAY(yourDateColumn))=DATE(YEAR(yourDateColumn),MONTH(yourDateColumn),1)

        Note: Some of the more "fun" Excel date functions like WEEKNUM, NETWORKDAYS and EOMONTH are not supported in SharePoint.


        Not so useful tests!   Smile

        Value must be greater than PI.  (3.14159265358979 more or less…)
            =YourFieldName > PI()

        And some square roots:
            =YourFieldName > SQRT(2)

        And of course you need a little trig:
            =TAN(RADIANS(YourFieldName)) > 1


        .

        5/31/2016

        SharePoint: Search for People using Properties (LastName:smith)

         

        The typical SharePoint end user knows less than 1% of the SharePoint Search feature set. In other words, they know how to do a Google search. They just type a word or two, press Enter and hope for the best. Just think what they could do if they just knew a few search properties!

        You can really improve search results by adding a Search Administrator to your team and letting them invest an hour or three a week in improving the end-user search experience. Train your end users, add tips to your search pages, create cheat sheets - there’s lots of options.

        Just adding a few search tips to the search pages will do wonders:

           image

        So, let’s take a look at how users can do a better People search…

         

        Searching for People

        Let’s say I do a search for people using the keyword “training”. I could find people in the Training department, people with the word “training” in About Me, and even people with the last name of “Training”. If you would like to focus your search then you can use the predefined search Managed Properties. For example:

           image

        While a few of the properties can be used with the equals operator (“=”), most will only return results with the contains operator (“:”). For example, searching for a work phone number using “=” returns nothing. Using “:” will return the person with that number.

           image

         

        Managed Properties for People Searches

        Most of the User Profile properties are searchable in a People search by just typing a keyword in the search box. You can also perform a People search using some of the out of the box Managed Metadata properties that are linked to the User Profile Services properties. In the table below you will find most of the User Profile properties along with the matching search Managed Property names. A few of the properties below have obvious names. A few are wrapped up in a single search property named “ContentHidded”. Some are “indexed” (crawled), but do not have the needed matching Managed Property. For most of those that do not have a matching Managed Property you can manually add a property to the search schema.

        I will follow up with an article on adding the missing search Managed Properties.

        Property for search

        Property name found in the user’s profile

        Notes

        AccountName Account Name Example: accountname:contoso\samc
        AboutMe About Me  
        Interests Interests  
        Responsibility Ask Me About  
        FirstName First name  
        LastName Last name  
        PreferredName Name This is the full name. “Mike Smith”
        WorkPhone Work Phone To find all users in the same area code or a partial number, use wild cards: workphone=513*
        JobTitle Title  
        WorkEmail Work Email  
        MobilePhone Mobile phone  
          Home phone Mapped to ContentsHidden. Can be mapped to a new Managed Property.
          Fax Not mapped, but can be. (People:Fax)
             
        Department Department This is a String property. This department maps to “Department”
          Department This is a Managed Metadata property. Not mapped, but can be. (People:SPS-Department)
        Schools Schools  
        If you see this list anywhere but on TechTrainingNotes.blogspot.com, then it was “stolen” and used without permission.
        OfficeNumber Office Generally used for “room number”.
        BaseOfficeLocation Office Location  
        PastProjects Past Project  
        Skills Skills  
          Manager Not mapped, but can be. (People:Manager) (returned as domain\username)
          Assistant Not mapped, but can be. (People:Manager) (returned as domain\username)
          Birthday Not mapped, but can be. (People:Birthday) (returned as “2000-03-01T00:00:00.0000000Z”)  All birthdays are set for year 2000.
          Hire Date Not mapped, but can be. (People:SPS-HireDate)
        ContentsHidden (many)

        This maps to several crawled properties as a single merged property:
        People:Office
        People:SPS-PastProjects
        People:SPS-School
        People:WorkPhone
        People:CellPhone
        People:Fax
        People:HomePhone
        People:SPS-MemberOf
        People:AboutMe
        People:OrganizationParentNames

         

        .

        5/17/2016

        SharePoint Folders Are Not EVIL!

         

        image
        It seems that everyday I run across another blog article, forum post or social media that says “Never Use Folders!” While one of the common analogies for SharePoint is the Swiss Army Knife, a better one might be a tool box, and one with a lot of tools. Saying “Never Use Folders” is kind of like saying never use an adjustable wrench because we have box wrenches. Tools are tools and you need to select the correct tool for the job.

        The following is not an excuse to not create a formal taxonomy and use a pure metadata approach to content management. It is a description of one of your many SharePoint tools in your toolbox. Remember everything is not a nail, and your only tool is not just a hammer.

         

        Sometimes You Just Can’t Afford Metadata

        Not an excuse so much as a reality.

        You just built your new SharePoint farm. You have hundreds of thousands of documents to migrate to SharePoint. Who’s going to add all of the metadata? You employees (in their free time?), summer interns, contractors?

        If you maintain the folder structure during your migration from network shares then your users can still find content as they always have. And, when you have added all of your metadata you can then either hide the old folders in your views, or move the content into one giant folderless library.

         

        Folders are metadata!

        In fact, Folders are “instant metadata”. Just upload or drag the document to the right folder and everyone will know something about it. If it’s in the folder named “Chlamydoselachidae” then it must be something about “Frill Sharks”!

        (I’ll give anybody at Microsoft a couple of dollars if they will add the folder name property to the available columns in a view. It would then be true metadata!)

        Folders can have custom metadata

        A folder is a Content Type. You can create new Content Types that inherit from Folder and then add metadata columns. While a search on the metadata does not return the files in the folder, it will return the folders.

        Here’s an article I wrote back in 2007 that still applies to SharePoint 2010, 2013 and 2016: http://techtrainingnotes.blogspot.com/2007/08/sharepoint-how-to-create-links-from.html

           image

           image

           image

         

        Want really smart folders with metadata that shares their metadata with their contents?

        Take a look at Document Sets. Not the out of the box example, but rather a custom one that you create by inheriting from the Document Set Content Type. If you add a Site Column named “Product Category” then every document in that Folder / Document Set will be findable from search on that property. If you move a document from one Document Set to another Document Set, the document’s inherited metadata is updated to match!

        https://technet.microsoft.com/en-us/library/ff603637.aspx

        https://support.office.com/en-us/article/Introduction-to-Document-Sets-C49C246D-31F1-4BFE-AFE2-E26698B47E05

        https://support.office.com/en-us/article/Create-and-configure-a-new-Document-Set-content-type-9DB6D6DC-C23A-4DCD-A359-3E4BBBC47FC1

         

        Folders can be nested more than two levels deep

        Using views and metadata you can create two levels of grouping. If you have SharePoint 2007 or 2010, you can use SharePoint Designer to create views that are up to 16 levels deep. But for SharePoint 2013 and 2016 they have changed (broken) SharePoint Designer so you can only group deeper than two levels by hand crafting XLST and HTML.

        You can nest folders as much as needed, up to the maximum URL limits of Path to Library + Folders + Filename.

         

        Folders are ideal for a rigid taxonomy

        If the primary way of accessing content is by a single hierarchy then a folder structure may be the better choice. While still limited to the maximum length of a URL, it clearly supports more than the two levels offered by a grouped view.

           image

        Want a full crumb trail like we had in SharePoint 2007? See here: http://techtrainingnotes.blogspot.com/2015/11/add-crumb-trail-to-sharepoint-2013.html

         

        Folders can be navigated using a Tree View

        There are actually two tree views available, one out of the box, and one that is hidden.

        The Quick Launch Tree View (Settings, Site Settings, Navigation Elements):

           image

        The hidden SharePoint 2010 “Navigate Up” button:

        (See: http://techtrainingnotes.blogspot.com/2014/06/sharepoint-2013-restoring-2010-navigate.html)

           image

        Note: Currently neither Tree View is available in the “new library experience” for SharePoint Online, and one day for SharePoint 2016 on premises.

         

        Metadata is not always searchable as a property

        Unless you have created Site Columns, and configured them as friendly search Managed Properties, then as far as seach is concerned, all of those columns of metadata might have just been typed into a single “Keywords” column.

         

        Search Likes Folders

        Search includes several managed properties to make finding folders and content in folders easy to do. Unlike Site Columns, these folder properties do not require any Search Service setup to work.

        Path:    path:https://yourServer/sites/site/library/folder
                    path:"https://yourserver/sites/taxonomy/Fish/Agnatha and Lampreys/Myxini/Myxiniformes"

        Searching with Path works, and is very precise, and returns all of the content in that path. The negative is typing the full path to the folder.

        contenttype:folder     contenttype:folder Myxiniformes

        contenttype finds all folders and all content types that inherit from Folder. (This will also return folders that have a column with the keywords being searched. In the example above you will get folders with “Myxiniformes” in the folder name and folders with a column with “Myxiniformes” in its name.)

        IsContainer:true        IsContainer:true Myxiniformes

        IsContainer returns Sites, Libraries and Folders that have the keyword in their name or metadata. IsContainer also returns Team Site Notebooks (OneNote files) and content stored in Asset libraries (The thing you click on in an Asset library is a folder, not the actual picture or video.) as they are represented as folders.

        Library search box

        The search box at the top of each library assumes you only want to search the content in the current folder! (You can then click “Some files might be hidden. Include these in your search” to search the rest of the library.)

           image

         

        Microsoft / SharePoint Really Likes Folders!

        Take a look at OneDrive for Business… you can’t even add metadata columns or use Content Types. “Name”, “Modified”, “Modified by”, “File Size” and “Sharing” are all you get. The only “metadata” I can add is by using folders.

            image
            (Yes, I really have a folder named “junk”!)

        In my OneDrive I have to embed metadata in the filename and/or the folder structure. Kind of like network shares!

            image

         

        The New Library Experience likes folders!

        The new library experience in Office 365 makes it easy to arrange and rearrange documents by folder. (Seems to encourage the use of folders!)

            image

         

        Sync Only Sync’s Folders

        All three of the sync clients only sync folder structure, not metadata. If you want any obvious classification of your local sync of the content then you have to use folders. The only metadata you can add from client side is in the filename and the location/folder.

        image

         

        Security and Folders

        Remember when Microsoft’ advise was to never use item level permissions? At least until SharePoint 2013 where they gave everybody a “Share” button. Now SharePoint 2013 and 2016 encourage users to break inheritance everywhere!

        See here for what can happen with unlimited use of the Share buttons: http://techtrainingnotes.blogspot.com/2015/10/trick-or-treat-day-in-life-of.html

        For a simple example consider:

        1. We create a site for Sales Managers. We create a library for their files.
        2. The sales managers start clicking the Share links on various documents, most to share with the “Summer Interns” group and the “Marketing Team” group. Over time there are 500 items with broken inheritance.
        3. Management asks you to add Regional Sales Managers to the site, with their own group.
        4. You create a SharePoint group and add the Sales Managers and grant it access to the site.
        5. The Regional Managers visit the site and complain that they can’t find all of the files the Sales Managers have told them about.
        6. You now have to:
          1. Find the 500 files with broken inheritance.
          2. Grant permissions to each of the files to the Regional Managers group.

        So what can you do? Use folders for permissions.

        1. Create the library.
        2. Add a folder for “Everyone”. (Optional as the files in the root of the library will be available to everyone by default.)
        3. Add a folder for “Sales Managers Only”. Break inheritance and grant permissions to the Sales Managers group.
        4. Add a folder for “Visible to Marketing Team”. Break inheritance and grant permissions to the Sales Managers group and to the Marketing Team group.
        5. Add a folder for “Visible to Interns”. Break inheritance and grant permissions to the Sales Managers group and to the Interns group.
        6. Create a new view named “Sales Files”:
          1. Make it the default view.
          2. In the Folders section hide the folders.

        Users will now see a single list of content, which can also be grouped using metadata, but they will only be able to see the content they should see. The users who maintain the content use the AllItems view so they can quickly upload documents into the correct folder, and automatically apply the correct permissions. (Now all you have to do is hide those pesky Share buttons! http://techtrainingnotes.blogspot.com/2015/08/hiding-evil-sharepoint-2013-share.html)

         

        So which should you use?
          Folders or
            Metadata+Views or
              Folders+Metadata+Views?

        Use the best tool for the job!

         

        .

        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.