SharePoint Dates are Always Date AND Time

A Quick SharePoint Date Tip!

When you create a Date column you have the choice of Date and Date & Time.

   

Note the keyword “Format” in that option. Even if you select “Date Only”, your users can still type, or copy and paste, a date and a time and it will be stored as a date and time. But… only the date will be displayed.

Times are a Fraction of a Date

Times are represented as parts of a day.

   

Converting a Date and Time to a Date

As you saw above, SharePoint dates are always dates and times. Even if you set a column to be “Date”, users can still type, or copy and paste, Date and Time values. Columns formatted as “Date” may display as just a date, but they will still be filtered and calculated as a Date and Time.

If you are calculating the number of days between two dates you might write “[Date2]-[Date1]”, which will produce the expected result if both dates are true Date values. If Date2 is “1/16/2018 6:00 AM” and formatted as “1/16/2018”, and Date1 is “1/14/2018” subtracting the two will return 2.25 days, not the hoped for 2 days.

There are several ways of converting a Date and Time to a Date:

• Use the DATE function:
      DATE( YEAR( [SomeDate] ), MONTH(( [SomeDate] ), DAY( [SomeDate] ) )
• Use the ROUND function:
      ROUND( [SomeDate], 0) (This will round up or down depending on the value)
• Use the FLOOR (round down) function:
      FLOOR( [SomeDate], 1)
• Use the CEILING (round up) function:
       CEILING( [SomeDate], 1)
• Use the INT function (which also rounds down):
      INT( [SomeDate])

I prefer the INT as it is the least typing and I most often need to round down to remove the fractional part of a Date and Tim   

The Mysterious Semicolon in SharePoint Search

Discoveries:

• A semicolon in a search is the same as the keyword "AND".
• You cannot uniquely find text that contains a semicolon. (such as ABC;DEF)
• A semicolon used as data in a checkbox enabled Choice column causes data and search problems.

If you have more info, or know of an escape character for “;” please post a note to this article!

";" equals "AND"?

If I were to search a SharePoint list for "111 AND 222", I would find items where "111" might be in one column and "222" might be in another, or both are two words in the same column. I was trying to find data that looked like this, "111;222", and was getting wrong matches. At first I thought the semicolon was behaving as a wildcard, and that would have been really cool as SharePoint does not support anything other than an "*" at the end of a word.

From testing, it appears that a semicolon is identical to the keyword "AND". In the example below, note that all of the sample text entries are using "word delimiters" that SharePoint recognizes as search word breakers. As "data" a semicolon is treated as "white space" or a word breaker, and is ignored by search!

Here is a search for "111" AND "44" and note that no items were found.

You can't uniquely search for a semicolon!

If your data contains symbols in the middle of text, you will have a problem when searching for text that contains a semicolon. In the example below, note that the search for "555;777" also finds other items.

The addition of quotes will solve this problem for other special characters, but not the semicolon.

Semicolons and Choice Columns

A discussion of the semicolon would not be complete if we don't talk about what it does to a Choice column!

Consider Choice column with these choices: (third choice has a semicolon)

And for this Choice column we selected Checkboxes.

Now let's select some data. Notice the display when I select two items in a QuickEdit view. The semicolon is part of one item's data, and is also there as a delimiter between items.

When I click off of that row, note that the two selected items are being treated as three items!

When I attempt to edit that column again, the item with the semicolons has been lost because there are no "aaa" or "bbb" items in the list.

So…

• Don't put semicolons in your data if you plan to search for it.
• Don't put semicolons in multi-choice (Checkbox) columns.

Adding Fractions (1/4) to SharePoint Calculated Columns

(The following works SharePoint 2010, 2013, 2016, SharePoint Online, and most likely, 2019!)

0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

This is a sample from my soon to be published book “SharePoint Calculated Columns and Validation Formulas”. If you would like to see a lot more about what you can do with Calculated Columns, and maybe win a free book, attend my session at SharePoint Cincy 2018 on April 10th!

In Excel you can get fractions using =TEXT(yourNumberColumn,"# ?/?"):   

The same function in SharePoint will round off the number, and for each of the above values, return 5. If you need fractions then you will need to write your own function to create them. Below we have several versions of this function depending on the fractions and formatting needed.

  

Function to return the fractions 0, 1/4, 1/2, and 3/4

Note: Read to the end to see a shorter version that uses CHOOSE instead of nested IFs.

Just a couple of nested IFs…

=IF( yourNumberColumn - INT(yourNumberColumn) = 0.25,
          INT( yourNumberColumn) & " 1/4",
   IF( yourNumberColumn - INT(yourNumberColumn) = 0.5,
           INT( yourNumberColumn) & " 1/2",
     IF( yourNumberColumn - INT(yourNumberColumn) = 0.75,
              INT( yourNumberColumn) & " 3/4",
         INT(yourNumberColumn) ) ) )

The above formula only works if the entered values end in exactly 0, .25, .5 and .75. If you need your function to round off to the nearest 1/4^th, replace the first value in each IF with:

    ROUND( yourNumberColumn * 4, 0 ) / 4

=IF( ROUND( yourNumberColumn*4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " 1/4",
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " 1/2",
     IF( ROUND(yourNumberColumn * 4 , 0 ) / 4 - INT(yourNumberColumn) = 0.75,
         INT( yourNumberColumn) & " 3/4",
         ROUND( yourNumberColumn,0) ) ) )

If you would like to see ¼ instead of 1/4 then use the CHAR function to get those symbols. While the ¼, ½, and ¾ characters are available, 1/3 and 1/8 are not.

• Replace " 1/4" with " " & CHAR(188)
• Replace " 1/2" with " " & CHAR(189)
• Replace " 3/4" with " " & CHAR(190)

=IF( ROUND( yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.25,
     INT(yourNumberColumn) & " " & CHAR(188),
   IF( ROUND(yourNumberColumn * 4, 0 ) / 4 - INT(yourNumberColumn) = 0.5,
       INT(yourNumberColumn) & " " & CHAR(189),
     IF( ROUND(yourNumberColumn * 4, 0 ) / 4  -INT(yourNumberColumn)=0.75,
         INT( yourNumberColumn) & " " & CHAR(190),
         ROUND( yourNumberColumn, 0 ) ) ) )

Function to return the fractions 0, 1/8, 1/4, 3/8, 1/2, 5/8, 3/4, and 7/8

To add support for eighths we need to add four more IFs. If we change the number used in our rounding code from 4 to 8, and add a few lines, then web can display values to the nearest eighth.

=IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.125,
       INT(yourNumberColumn) & " 1/8",
   IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.25,
         INT(yourNumberColumn) & " 1/4",
     IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.375,
            INT(yourNumberColumn) & " 3/8",
        IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.5,
              INT(yourNumberColumn) & " 1/2",
           IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.625,
                 INT(yourNumberColumn) & " 5/8",
              IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.75,
                    INT(yourNumberColumn) & " 3/4",
                 IF( ROUND(yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) = 0.875,
                      INT(yourNumberColumn) & " 7/8",
    ROUND(yourNumberColumn, 0 ) ) ) ) ) ) ) )

Now we have fractions to the nearest 1/8th.

    

A Much Shorter Version that uses CHOOSE

Here is a much shorter version of a formula to display a number as a fraction:

=ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) &
  CHOOSE( ( ROUND( yourNumberColumn * 8, 0 ) / 8 
            - INT(yourNumberColumn) ) * 8 + 1,
          ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8",""
        )

This uses CHOOSE to pick the text to display for “less than 1/8^th”, “1/8^th” … “7/8^th” and “more than 7/8^th”. (Nine possible values.)

To get the fraction:

1. Round the number to the nearest 1/8^th:
       ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8
2. And as CHOOSE starts with 1 and not 0, add one to the result:
       ( ROUND( yourNumberColumn * 8, 0 ) / 8 - INT(yourNumberColumn) ) * 8 + 1
3. Use CHOOSE to pick the text:
       CHOOSE( … , ""," 1/8"," 1/4"," 3/8"," 1/2", " 5/8", " 3/4", " 7/8","")
4. Concatenate that to the rounded down number:
       ROUNDDOWN( yourNumberColumn + 0.062499, 0 ) & …
   The “+ 0.062499” is added to deal with the last 1/16^th after “7/8^th” so we round up to the next higher number.

.

A SharePoint Calculated Column for all 50 States (workarounds for nested IF limits!)

Just in case you might ever need a formula to convert state abbreviations into state names…

• You will need 50 nested IFs,
• but SharePoint 2007 and 2010 only allows 7, and 2013 and later only allows 19.
• You will need a little more than 1300 characters in the formula,
• but SharePoint 2007 and 2010 only allow 1024. (2013 and later are around one billion!)

The trick for the IFs is to only nest 19 at a time and return a state name, or an empty string (""), and then concatenate another 19 nested IFs that return a state name, or an empty string… repeat until done! If you are using 2007 or 2010, then nest 7 at a time, and then concatenate another 7.

But what about the 2007 and 2010 1024 character limit? Renaming the "state" column to just one letter brings the formula down to 1111 characters, but that's still more than the 1024 allowed. Solution? Three Calculated columns. The first has the formulas for the first 25 states (in multiple IF nestings of 7 or less) that returns a state name or an empty string, The second has the next 25 states and returns a state name or an empty string. The third just concatenates the first two columns.

Here's the formula for SharePoint 2013 and later for a column named "State":

if(State="AL","Alabama",
if(State="AK","Alaska",
if(State="AZ","Arizona",
if(State="AR","Arkansas",
if(State="CA","California",
if(State="CO","Colorado",
if(State="CT","Connecticut",
if(State="DE","Delaware",
if(State="FL","Florida",
if(State="GA","Georgia",
if(State="HI","Hawaii",
if(State="ID","Idaho",
if(State="IL","Illinois",
if(State="IN","Indiana",
if(State="IA","Iowa",
if(State="KS","Kansas",
if(State="KY","Kentucky",
if(State="LA","Louisiana",
if(State="ME","Maine",""))))))))))))))))))) &
if(State="MD","Maryland",
if(State="MA","Massachusetts",
if(State="MI","Michigan",
if(State="MN","Minnesota",
if(State="MS","Mississippi",
if(State="MO","Missouri",
if(State="MT","Montana",
if(State="NE","Nebraska",
if(State="NV","Nevada",
if(State="NH","New Hampshire",
if(State="NJ","New Jersey",
if(State="NM","New Mexico",
if(State="NY","New York",
if(State="NC","North Carolina",
if(State="ND","North Dakota",
if(State="OH","Ohio",
if(State="OK","Oklahoma",
if(State="OR","Oregon",
if(State="PA","Pennsylvania",""))))))))))))))))))) &
if(State="RI","Rhode Island",
if(State="SC","South Carolina",
if(State="SD","South Dakota",
if(State="TN","Tennessee",
if(State="TX","Texas",
if(State="UT","Utah",
if(State="VT","Vermont",
if(State="VA","Virginia",
if(State="WA","Washington",
if(State="WV","West Virginia",
if(State="WI","Wisconsin","")))))))))))

SharePoint 2016 Durable Links

I recently had a question in class about “Durable Links”. I did a search of the Microsoft sites to find anything official on SharePoint 2016 “Durable Links”, and basically only found a beta vintage blog article.

https://blogs.technet.microsoft.com/wbaer/2015/09/22/durable-links-in-sharepoint-server-2016-it-preview/

While I did find a number of other blog articles from the beta period, mostly of the “what’s new in SharePoint 2016” type, I found no TechNet articles. So… I thought I’d share part of one of my courses that has a section on Durable Links. This course is available from many Microsoft Learning partners, and of course, from MAX!

From:
Course 55198A: Microsoft SharePoint Server Content Management for SharePoint 2013 and 2016

SharePoint 2016 Durable Links

Prior to SharePoint 2016, renaming or moving a file would break all of the links and shortcuts that pointed to the file. In SharePoint 2013 you might have had a file named “FinancialStatementFY14Q2.xlsx” that had no spaces in the name. This is both an ugly filename and a name that will cause problems with search. (Users searching for “Statement” or “FY14” would never find it based on the title.) The 2013 URL would look something like this:

http://yourServer/sites/yourSite/Shared%20Documents/FinancialStatementFY14Q2.xlsx

Renaming this file to include spaces in the name would create the following URL. But, users with links to the old file will no longer be able to find it.

http://yourServer/sites/yourSite/Shared%20Documents/Financial Statement FY14 Q2.xlsx

Note the spaces in the URL will be replaced with “%20”.

Durable Links
SharePoint 2016 now appends a “d” query string to the URL with a unique ID that will not change even if the file has been renamed. (But not always… see notes below…)

http://yourServer/sites/yourSite/Shared%20Documents/FinancialStatementFY14Q2.xlsx?d=w780e689061e44dbfb4123fe450f4b957

After renaming, SharePoint will still find the correct document as it looks for the Durable Link ID first to find the document.

http://yourServer/sites/yourSite/Shared%20Documents/Financial%20Statement%20FY14%20Q2.xlsx?d=w780e689061e44dbfb4123fe450f4b957

To find the Durable Link in SharePoint 2016, or the SharePoint Online “Classic UI”, click the “…” next to the filename.

Notes:

• Durable Links are not a feature and cannot be enabled or disabled.
• Durable Links require Office Online Server to be part of the farm.
• Works with Office documents like Word, Excel and PowerPoint (i.e. things displayed in Office Server), but not other files like .jpeg, .png, etc.
• At the time of this writing, the Office 365 / SharePoint Online “Modern Library” pages do not offer a way to copy the URL that includes the Durable Link query string.
• Documents that are moved (drag and drop or cut/paste) will preserve the Durable Link ID. Documents that are copied and pasted will get a new Durable Link ID.
• The Publishing site Content and Structure feature does not preserve the SharePoint 2016 Durable Link. (A new ID is assigned after a Move.)

  .

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:

After the June update:

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.

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:

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…

.

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:
  
Or this:
    =REPT("<img src='http://yourPath/yourImage.GIF' style='border-style:none'/>",[Value])
 

Topic Mapping for Course 20347 to Microsoft Certification Exams 70-346 and 70-347

(updated 10/25/18)

Microsoft’s “20347A: Enabling and Managing Office 365” course is a little different in that it dpes not have a one-to-one mapping to a single Microsoft exam. For example, while course 20483 maps to exam 70-483, 20347 maps to two exams: 70-346 and 70-347.
Here’s a guide for my students who are working on the 70-346 and 70-347 exams…

The course:

• 20347A: Enabling and Managing Office 365
  (Of course, available from MAX Technical Training from yours truly!)

The exams:

• 70-346 - Managing Office 365 Identities and Requirements
• 70-347 - Enabling Office 365 Services

---

For details of each topic see Microsoft’s exam pages:

• 70-346 - Managing Office 365 Identities and Requirements
• 70-347 - Enabling Office 365 Services

Exam 70-346

Plan and implement networking and security in Office 365 (15–20%)

• 20347 Module 1 – Configure DNS records for services (and the DNS info in the Exchange and Skype modules)
• 20347 Module 3 – Enable client connectivity to Office 365
• 20347 Module 11 – Administer Microsoft Azure Rights Management Service (RMS)
• 20347 Module 2 – Manage administrator roles in Office 365

Manage cloud identities (15–20%)

• 20347 Module 2 – Configure password management
• 20347 Module 2 – Manage user and security groups
• 20347 Module 2 – Manage cloud identities with Windows PowerShell

Implement and manage identities by using Azure AD Connect (15–20%)

• 20347 Module 4 – Prepare on-premises Active Directory for Azure AD Connect
• 20347 Module 4 – Set up Azure AD Connect tool
• 20347 Module 4 – Manage Active Directory users and groups with Azure AD Connect in place

Implement and manage federated identities for single sign-on (SSO) (15–20%)

• 20347 Module 13 – Plan requirements for Active Directory Federation Services (AD FS)
• 20347 Module 13 – Install and manage AD FS servers
• 20347 Module 13 – Install and manage WAP servers (Web Application Proxy)

Monitor and troubleshoot Office 365 availability and usage (15–20%)

• 20347 Module 12 – Analyze reports
• 20347 Module 12 – Monitor service health
• 20347 Module 12 – Isolate service interruption

Exam 70-347

Manage clients and end-user devices (20–25%)

• 20347 Module 5 – Manage user-driven client deployments
• 20347 Module 5 – Manage IT deployments of Office 365 ProPlus
• 20347 Module 5 – Set up telemetry and reporting
• 20347 Module 5 – Plan for Office clients

Provision SharePoint Online site collections (20–25%)

• 20347 Module 9 – Configure external user sharing
• 20347 Module 9 – Create SharePoint site collection
• 20347 Module 9 – Plan a collaboration solution

Configure Exchange Online and Skype for Business Online for end users (20–25%)

• 20347 Module 6 – Configure additional email addresses for users
• 20347 Module 6 – Create and manage external contacts, resources, and groups
• 20347 Module 11 – Configure personal archive policies
• 20347 Module 8 – Configure Skype for Business Online end-user communication settings

Plan for Exchange Online and Skype for Business Online (20–25%)

• 20347 Module 7 – Manage antimalware and anti-spam policies
• 20347 Module 7 – Recommend a mailbox migration strategy
• 20347 Module 11 – Plan for Exchange Online
• 20347 Module 7 – Manage Skype for Business

Configure and Secure Office 365 services (20–25%)

• 20347 Module 10 – Implement Microsoft Teams
• 20347 Module 10 – Configure and manage OneDrive for Business
• (not covered in 20347!) – Implement Microsoft Flow and PowerApps
• 20347 Module 10 – Configure and manage Microsoft StaffHub
• 20347 Module 11 – Configure security and governance for Office 365 services

.

Office 365 / SharePoint Online – Where’s Site Settings This Week?

SharePoint Online is kind of like the weather in Cincinnati… if you don’t like it, hang around, it will change.

Where’s Site Settings?

It depends… today, 10/23/2017 it’s here:

• Classic UI pages: It’s where it has always been… in the Settings (gear) menu.
• Modern UI pages: It’s…  Settings (gear), Site Info, Site Information, and then at the bottom of the popup panel, click “View all site settings”.
• Modern UI Site Contents page: Top right corner, click Settings

I wonder will it will be next week?

Purpose of the change?

No real clue… If it was to surface the most often used Site Settings, they are not the ones I use most often. Are the most often used options on your list Delete Site, rename the site or change the description or icon.

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:

• A SharePoint 2013 or later site.
• A web part page with a Content Editor Web Part.
• The JavaScript file. (has a .html extension so you can edit it in SPD.)
• Permissions to access the content you want to access using REST!
• Some documentation:
  • A REST Syntax wall poster: http://www.microsoft.com/en-us/download/details.aspx?id=41147
  • REST API reference and samples: https://msdn.microsoft.com/en-us/library/office/jj860569.aspx

You can also download the file from the GitHub project.

---

This is the main screen.

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.

 

The in the page test of a REST call.

 

The generated AJAX Code Sample

 

The SharePoint 2013 Workflow Steps for the Web Service Call

 

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.

 

.

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:

  

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 )

 

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)

  

Here’s a sample:

  

 

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)

  

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…

 

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!" )

 

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.)

.

You Can Now Create "Modern" Pages in SharePoint Online

 

It looks like "modern pages" are now rolling out to the tenants with the preview options on. You can still create Wiki Pages and Web Part Pages in addition to the new "Site Page" type. Here's both the new and classic Site Pages library “New” menus.

  

The new pages have the "warm and fuzzies". Click to enter a page name. Note the Publish button. New pages are left checked out until "published". Major versions are enabled on the Site Pages library.

While web parts are not listed yet, you can add some "widgets" to the new pages. (Hey! There's a Yammer thing there!)

 

So… stay tuned to see what will appear next!

 

.

Get the Version Number of a PowerShell Module

 

When a PowerShell script works for one person, but not for another, sometimes it's because the PowerShell module is a different version.

To find the version number:

Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell" | 
select name, version

 

If you need to deal with multiple versions in your scripts:

if ( (Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell").
  Version.ToString() -eq "16.0.4915.0") 
  { … do this } 
else 
  { … do this }

or maybe

if ( (Get-Module -ListAvailable "Microsoft.Online.SharePoint.PowerShell").
   Version.ToString() –lt "16.0.4915.0") 
   { "Must have 16.0.4915.0 or later"; Return; }

.

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”
   .
2. Click the "…" button to open the String Builder dialog.
    
   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
      
      
   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!

.

SharePoint Online / Office 365 “Modern Library” Blank Pages

 

One of my PCs cannot display any of the new “Modern Lists / Library Experience” pages, including the OneDrive for Business pages. The pages are just blank. The issue turned out to be a URL that was in one of my blocked lists that is used to load a JavaScript library used by these new pages:
<script type="text/javascript" src="https://spoprod-a.akamaihd.net/files/odsp-next-prod_ship-2016-07-18_20160720.003/require-951f856e.js">

Removing akamaihd.net from my blocked list let these pages work again. The weird part is that this was only an issue in Internet Explorer 11. The pages loaded fine when using FireFox.

 

About the “Modern Library” Pages

These pages are almost completely generated from JavaScript, not HTML embedded in the page. If you use IE’s View Source command you will find that these pages are actually a bit weird, at least from an HTML point of view. The W3 validator page (http://validator.w3.org/check) has nothing nice to say about these pages!

 

The page as delivered, before the JavaScript runs, basically looks like this:

• A DOCTYPE directive that says the following is XHTML (but the W3 validator has issues with this):
  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
      "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
• An HTML tag that includes two meta tags and a number of <script> tags:
    <html dir="ltr" lang="en-US"><meta http-equiv="X-UA-Compatible" content="IE=edge"/>
  • A meta tag (outside of the HTML and HEAD tags!):
      <meta name="viewport" content="width=device-width,   …   />
  • A link tag to an icon:
      <link rel="shortcut icon" href="_layouts/15/images/favicon.ico   …   />
  • Seven script blocks, one of which points to an external source:
      <script …. ></script>
  • There are no <HEAD> tags!
• An end HTML tag:
     </html>
• An then an empty <BODY> tag:  (And I thought this had to be between <HTML> and </HTML>!)
    <body>   </body>
• And then several <script> tags (again outside of <HTML> … </HTML>!):
    <script …. ></script>

 

Oh, customizers take note, the traditional SharePoint master pages are not used here! There go all of our customizations… so far anyway.

 

.

Office 365 / SharePoint Online Site Contents Page Changes

 

SharePoint Online Latest Change of the Week / Day / Hour / Minute…

If you use Office 365 / SharePoint Online then you should now be used to the constant tinkering with the user interface. I’m starting to feel like SharePoint Online is kind of like the weather in Cincinnati… if you don’t like it, hang around, it will be different tomorrow.

One of the latest changes is to the Site Contents page. A preview of this page is documented in the link below. But… it’s already out of date! They have since added the Top Link bar back and the site icon. (To see these new pages in advance of general release you need to enable Preview Features in the tenant’s SharePoint Settings page.)

https://support.office.com/en-us/article/The-SharePoint-Site-Contents-page-ba495c1e-00f4-475d-97c7-b518d546566b?ui=en-US&rs=en-US&ad=US

 

The page as of 6/19/2016…

 

Changes to Site Contents:

• This is a “New SharePoint” style page. It is responsive and will somewhat adapt to screen resolution and device size. But like the other new responsive pages, a change of screen resolutions or zoom levels will make well known navigation elements move to new locations, or disappear. (Usually being rolled up into another navigation element.)
  Where did Quick Launch go? (It’s now the three slashes button) Where did the App launcher/waffle button go? (It’s now changed colors and has moved to the right into the middle of the other buttons.)
  
• This is no longer a master page based page or even a typical ASPX page. Right-click the page, select View Source and you will see that there’s basically an empty HTML tag and the loading of a bunch of JavaScript. If you use the F12 developer tools in your browser you will see that everything’s a DIV and there are MANY JavaScript files being loaded. The page is still stored in “_layouts” so there’s no customization through web parts or SharePoint Designer.
• +++ They changed the list of lists and libraries into a list!!! No more ugly blue squares, in no useful order and having to click Next, Next, Next.
  
• +++ The lists are sortable!!! (But not filterable or customizable. It would be really nice to group on list type or especially a custom property!)
• +++ They also changed the list of subsites into a list!!! It’s also sortable!
  
• - - - They added new big ugly blocks that we have to scroll past to get to the list of lists and subsites. These are site activity reports that really should be in their own page somewhere, maybe a “Site Activity” page. The first two big tiles do link to their own report pages.
  
• - - - They gave the page a new “New” button that will confuse the heck out of people.
     
  Click New and then List, you get a “Custom List”. No options. Click New and then Library, you get a generic library. If you want a Tasks list or an Announcements list, you have to click New and App. And then we are back to the ugly big blue tiles. (The New App page would be a great place to replace the blue tiles with a list! Give it two tabs, “Lists and Libraries” and “SharePoint Apps”.  Oops, I should have said “SharePoint Ad-ins”. They did tell us that they renamed these, right?)

 

So…

They cleaned up, and cluttered up, the Site Contents page.

 

Don’t like the new design… hang around!

(Today it’s hot and sunny in Cincinnati…)

 

.