Category Archives: Office 365 SharePoint Online

PowerShell isn’t just for Developers, IT Pros can rock the awesomeness in Office 365

I had the pleasure to co-present PowerShell and Office 365: ITPro #Awesomesauce with my friend and colleague Mike Dixon at the Boston Office 365 User Group yesterday. It was a great session with a lot of good questions. It was recorded so I’ll share the link to the video once it’s available. Big thanks to Hitachi Consulting for the sponsorship too!

I really enjoyed this talk because it kind of went against my natural thread. I’m a developer at heart, been a developer since graduating high school in 1997. I went from IT role to IT role but stuck with development. I’m an architect now (I feel all grown up), and I help teams design some pretty amazing solutions spanning technologies. However I won’t let go of the code. I support my team by pushing the best code practices, code reviews and writing my fair share of code. I like to learn new code in my spare time. I just love it.

This session was targeted to IT Pros: the non-developer type, “those guys” that used to make us developers’ lives harder with their governance policies and limitations on servers (I’m dating back to 2000’s, no one does that now, right? :| ). Given the huge push to cloud: SaaS, PaaS, IaaS, etc. the IT Pro’s role has increased dramatically in my eyes. In a lot of cases, when we come into a company to help create a solution on Office 365, it’s usually been lumped on top of the IT team, who already have their full time job. Going to the cloud is sold as easy and cost effective, but it does take dedication and training to do it right. Generally, new solutions are spun up in the cloud, but the legacy systems remain intact, full production ready with the same SLA for months after they’ve moved to the cloud. It’s not fair to IT.

This session helps bridge the gap a little between developer and IT Pro by providing some real value to IT. PowerShell doesn’t have to be scary or looked as a developer “thing”. It’s awesome, and can do some amazing things that once was only for development teams. Any IT Pro (admin, grunt, wire jockey) can type in commands and build something beautiful. That’s what it is, just commands. This is why I enjoyed this talk, enabling the IT Pros to do more and hopefully bolster them up to do their jobs in Office 365 more effectively and less tediously.

By the way, here’s the deck!

 

 

Advertisements

How to work with large lists in Office 365 SharePoint Online

What’s a large list? Anything over 5,000 items.

Why is this such a low limit?

Great question, and Microsoft does a good job explaining it in their help documents:

To minimize database contention SQL Server, the back-end database for SharePoint, often uses row-level locking as a strategy to ensure accurate updates without adversely impacting other users who are accessing other rows. However, if a read or write database operation, such as a query, causes more than 5,000 rows to be locked at once, then it’s more efficient for SQL Server to temporarily lock the entire table until the database operation is completed.

When the whole table is locked, it prevents other users from accessing the table. If this happens too often, then users will experience a degradation of system performance. Therefore, thresholds and limits are essential to help minimize the impact of resource-intensive database operations and balance the needs of all users.

Do you think you’re going to exceed 5,000 items?

There’s a few things you can do before you exceed 5k, which will make life a lot easier.

Create indexes! Indexes are pockets of focused data that render results much faster (very laymen terms here). Searching for ‘bob’ against a name field is faster if that field is indexed.

Office 365 boasts that it will automatically create indexes, sadly I’ve come across some large lists without indexes, nothing created. Reached out to Office 365 support to create some, I’ll let you know if they actually do (requested a few weeks ago!).

Look for fields that you’ll be using to create views and index those. Think broadly like categories, dates, departments, etc. The ID field IS indexed automatically out of the box. Otherwise, create some more indexes.

I’m over 5,000 items, now what?

In the past, working with SharePoint installed on-premises, you could override the limit and go higher than 5k and keep on truckin. If you wanted to stay within Microsoft’s recommendations, there are opportunities to open up maintenance windows to allow bulk operations on lists larger than 5k.

In Office 365, we don’t have that flexibility. Microsoft actually has a list limit of 30,000,000 (30 million) items. 30M!?! That’s far over their definition of a large list. But the list view limit is 5k (.01%). Weird but I guess I understand why.

So what do we do? We have a few options, depending on your needs.

Users need to find records to update or reference

Create smaller collections of data.

If you’re fortunate enough to get your fields indexed, you’ll still need to keep your views under 5k items. You can have hundreds of thousands of items in your list, but you can’t serve up more than 5k items at a time to your users.

Using your indexed columns, create your filtered views. You’ll notice that your columns will be labeled as (Indexed):

indexed columns in a view

PLEASE NOTE that just because you filter on an indexed field does NOT mean you’ll be under 5k items. Also, you can filter on a non-indexed field IF you include a filter for an indexed field, which by itself would return less than 5k items. For example, consider a list with 10k items:

  • Filter State (Indexed) by ‘Connecticut’, and you get back 6k items. Filtering by City (not-indexed) ‘Hartford’ might return 400 items, but because the indexed filter returns over 5k, you can’t perform additional non-indexed filters against the data.
  • Filter State (Indexed) by ‘Massachusetts’, and you get 4,900 items, then you can filter by City ‘Boston’ and get your subset of 400 items.

The magic of the ID. The ID column is automatically indexed, out of the box, even though it doesn’t show it in filtering. Sometimes if you need to find specific data you can create a view with a range of IDs and get your items. You can also create a view that shows all data, sans filter, but sorted by ID, that will work because that column is indexed.

This is great, but if you don’t have indexes…

Search is awesome.

Search can handle your list, and a gazillion of other records of data. Once the item is indexed, you can search for it pretty easily.

For specific business cases, like a call center for instance, you can create a ‘search application’ in SharePoint to allow call agents to find exactly what they’re looking for, by searching for specific data points like casenumber:1234, or callername:Bob. Some of this functionality would usually be done at the list level using column filters and such, but due to the mass amount of data, search is the answer.

I’ll cover more on creating a search application in my next post, I promise. For now, just try searching for your content, should come back for you.

Users need to report on the data

This is the biggest pain in the butt by far. Managers and teams want to see some sort of reporting, like how many calls in the last week, how many from a certain state, etc. Normally we could fudge this with the out of the box list views, but alas, there’s too much data. Instead we can use additional tools to get this data and report on it.

Microsoft Access is an option, though I don’t like it. If you’re looking for a fast solution, connect your local Access database to SharePoint and suck down that list. You can create reports and even export easily to Excel. This option does require your users to have Access installed to view the data (<– the reason I don’t like it)

Power BI is by far the better option (I know, it all depends on requirements). You can point PowerBI to your huge SharePoint list, it will take time to import it all (I imported 300,000 items in about 45 minutes) but once it’s there, you’re golden. You can even setup a nightly refresh so your data is fresh every day when you come in. Awesome sauce right? See below

Reporting on large lists

Sharing PowerBI reports is easy through the standard Office 365 Share experience. According to Microsoft, PowerBI Pro is only available for E5 licenses, but I’m on an E3 and I can do a ton. E5 does offer more functionality but just pulling in data, creating reports and sharing, is doable on E3.

That’s a wrap! Let me know if I’m missing something, I’d love to hear your war stories. ‘Til next time, Happy SharePointing!

Customizing SharePoint’s Modern Lists’ Forms

Ya know that awesome new interface we get in Office 365 called Modern Lists? Ya know how that’s not editable, or customizable, or extensible? Well, ya know it right! It’s not.

However…

modern_list

Purty Modern List

Above is a typical modern list, in all of it’s glory. Pretty right? But I want to add some of my own code to this and do some cool customizations. Currently, that’s only reserved for the classic list view:

classic_list

Yucky Classic List

I just threw up a little in my mouth. 

What if we took the classic view, and persuaded it to be modern, but keep the beauty of customizations? It’s just some good ‘ole CSS, and yes I’ll share.

Quick steps on what to do:

  1. Download this little CSS file (click here for project on github).
  2. Upload the CSS file to SharePoint somewhere, like your Site Assets library
  3. Create a quick HTML file with just a link to the CSS file, i.e. <link rel=”stylesheet” href=”/SiteAssets/classic_modern.min.css”></link>.
  4. Upload the HTML file to SharePoint
  5. In the library where you uploaded the files, right click on the HTML file, Get Link, then select Restricted Link, and copy that URL
  6. Navigate to your list.
  7. Ensure it’s in Classic mode, and will stay that way. Go to List Settings, then Advanced settings. Scroll all the way down to List Experience and select Classic experience.
  8. Navigate back to your list and create a new item.
  9. Use the cog in the top right, and select Edit page.
  10. Add a Content Editor Web Part and set the Content Link to the URL of your HTML file.
  11. Save it all and reload the page, BLAMO
  12. I have a more indepth walk through on how to do this using a JavaScript file here, same applies to a CSS file.

classic_modern_list

YAY!

The flow and feel of the classic form is a lot more like the modern form, but ’tis classic.

Some Notes

  • Terrifyingly enough, the CSS for the modern lists are on the pages themselves, not in a CSS file I can reference. So there was a lot of copy/pasting of CSS from a modern list to this CSS file.
  • Your classic list form will have the full ribbon, this CSS is primarily focused on the form interface for users.
  • This will not do anything on the list view pages, I haven’t bothered styling those yet.
  • If you wanted to, you could put this CSS into a global CSS file so all classic lists will have similar interfaces.
  • This CSS is not all inclusive, it should get you most of the way there. If you do add to it, please feel free to share on the github project, or send me the changes.

Thanks, and ’til next time Happy SharePointing!

Embedding your JavaScript into a SharePoint page

A very typical approach for client side development in SharePoint is to throw the code onto the page where you need it. You can alternatively put into the master page, but generally speaking, most code doesn’t need to run on each and every page. The following describes my preferred, tried and true, method of handling this.

Upload the Assets

Say you have some great JavaScript code provided by a developer or blogger, and you want to now use it on your page. First things first, get the JavaScript into your SharePoint site!

Upload the JS file into a library. I generally use SiteAssets, with a small folder structure for organization, like SiteAssets\js, or if there is more, sometimes like SiteAssets\webparts\mywebpartname.

Once the JS is uploaded, we now need a HTML file to reference it. This can be pretty simple HTML file. You can create it on your desktop (create a new text file, and rename the extension to .html) or using SharePoint Designer, you can create it directly in SharePoint. Throw it in the same place as your JS file, or however you have your assets structured.

As an example, we’ll take a block of code from one of my blog posts, we’re going to throw the necessary HTML and JS into the HTML file you created, something like:


<script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js" type="text/javascript"></script>
<script src="/SiteAssets/Lozzi.Fields.js" type="text/javascript"></script>

<script type="text/javascript">
$(document).ready(function(){
ExecuteOrDelayUntilScriptLoaded(function(){
Lozzi.Fields.disableWithAllowance("Start Date", ["Project Managers"]);
Lozzi.Fields.disable("Task Status");
Lozzi.Fields.hide("% Complete");
},"sp.js");
});
</script>

This is all the HTML and code needed to run the JS I’m working with. Notice Line 2, the second <script tag. This is the path to the JS file you uploaded into SharePoint. Make sure the path is correct. This block of code above will differ for each and every JS example you work with.

Once the HTML file is saved, navigate to it through your browser and copy the link to the HTML file. You can do this simply by clicking on the file’s ellipses, the …, and copy the URL from there

copy file link

Embed in SharePoint

Ok, now that we have the JS file uploaded, and a HTML file created, let’s embed it in SharePoint! This is the easy part.

Navigate to the page you want to use this code on. Just use your browser and click to go to the page, pretty simple so far right?

Now edit the page: click the cog in the top right and select Edit Page

edit page

Once the page is in edit mode, click Add a Web Part button at the top of a zone, it doesn’t matter too much where. If you don’t have an Add a Web Part button, you’re probably using a wiki page, so click anywhere in the content area where you want add a web part, then click Insert Web Part.

add content editor web part

In the Add web part ribbon, select Media and Content on the left, and then select Content Editor. Click Add to add to your page. You should have something like:

added content editor web part

Notice the Content Editor web part added. Now click the web part, in the top right, and edit the web part.edit web part

In the tool pane on the right, paste in the URL to the HTML file. Click OK.

past html link in content editor

Afterwards, your content editor web part may look empty, or you may see some of your HTML, it depends on what you’re working with. My example, I’m just using JavaScript to hide fields on my page.

save content editor

Save the page and you should be good to go! Your code should fire off and you should see things happening. If not, if you question what’s going on, try using the developer tool bar in your browser, more on how here.

A couple of notes:

  • You may want to modify the Content Editor Web Part’s Chrome Type setting to hide the title from view
  • If you receive the error “Cannot retrieve the URL specified in the Content Link property. For more assistance, contact your site administrator.”, check the URL you’re pasting in. SharePoint can’t find it, make sure it’s valid. Paste it into a new browser window and see if downloads the file.
  • Make sure to structure your JS and HTML files well, you never know who’s going to look at it next.
  • Consider using the URL to the HTML file as a relative URL instead of a absolute URL. How? Consider the following URL is an absolute URL: https://sharepoint/sites/sitename/siteassets/myfile.js. Make it relative by dropping the first part: /sites/sitename/siteassets/myfile.js. This will keep it a wee bit more flexible and migrations in the future should be a little easier.

‘Til next time, Happy SharePointing!

SharePoint Online’s Public Website Doesn’t Allow Users to View Display Forms

If you’re used to SharePoint, and the display form on lists, you may be thinking you can use that on your SharePoint Online Public Sites as well. Sadly, by default your anonymous users cannot access the display form of a list item. Instead, SharePoint Online prompts the user to login.

Annoying? Yes.

It is due to the ViewFormPagesLockdown feature, which is available in SharePoint on-prem, and appears to be on by default on SharePoint Online. I think the reasoning is that the display form can have more data on it than you’d care to share to your visitors, and frankly, the page is rather dull. So thinking through how to get around this, my initial thoughts were:

  • Disable this silly feature, but alas, you can’t.
  • Use the search web parts with a custom display template, but alas, these aren’t available on the public site #FAIL
  • Write some custom JavaScript to grab the data, meh, too much work.
  • Fudge a few columns, and the view, and get it to work. BING BING BING

We can use the views of a list, since users can access that fine, and manipulate it to look like a form page.

Below I walk through how we did it on the Boston Office 365 User Group site, www.bostono365usergroup.com,

Take the default view of the list:

Boring SharePoint List View

Then let’s change the view style, we used Newsletter, which gives us the nice start to where we want to be:

List view in Office 365 SharePoint

Not terrible right? But let’s make it a wee bit more prettier. I edited the page, added a code snippet web part (embed code) and threw in the following collection of CSS:


<style unselectable="on">
.ms-listviewtable tr td.ms-vb2:first-child {
display:inline-block;
}

.ms-listviewtable td:first-child+td, .ms-listviewtable td:first-child+td+td {
display:inline-block;
width:45%;
font-size:14px;
}

.ms-vh2 {
display:none;
}

table.ms-listviewtable tr td.ms-vb2:first-child {
font-size:1.6em;
width:400px;
}

.registerButton {
padding:4px 5px;
border-top-left-radius:10px;
border-top-right-radius:10px;
border-bottom-right-radius:10px;
border-bottom-left-radius:10px;
border:1px solid #5b5b5b;
width:180px;
text-align:center;
color:#ffffff;
font-weight:bold;
display:inline-block;
text-shadow:none;
background-color:#001a81;
}

@media screen and (max-width: 400px) {
.ms-listviewtable td:first-child+td, .ms-listviewtable td:first-child+td+td {
display:inline-block;
width:100%;
}
.ms-vb-tall {
width:280px;
display:inline-block;
}
}
</style>

Much better right? Almost looks like a normal content page now ;)

SharePoint List View Customized

How do we get users to this page view now? If I had multiple items in the list it would just scroll long. Since this is a list view, we can filter the list view by the ID of the item.

We added a calculated column to use as a link on the home page. So, let’s add the column and the calculation:

=CONCATENATE("<div><a href='/events?FilterField1=Title&FilterValue1=",Title,"'>",Title,"</a></div>")

Make sure to set the Calculated Field to output as Number. Why didn’t I use ID you might ask, well, the ID field doesn’t work as you’d like. The ID isn’t calculated until after the item is added and the calculated column doesn’t get updated accordingly. Very annoying. Fortunately, in our case the title will suffice.

So the above calculation will output a link, sending the user to the events list view, filtered based on the current item. You can see it in action on the home page:

SHarePoint Event List on Office 365

Wait a second, what’s that nifty Register button?

register

That’s another calculated column, reading from the registration URL field, and then spits out some extra HTML for us to format it as needed (in conjunction with the above CSS):

=IF(RegistrationURL="","coming soon",IF(RegistrationURL<>"",CONCATENATE("<div><a id='regLink' class='registerButton' href=",RegistrationURL," target=_blank>Register</a></div>")))

It’s not impossible to have nice looking forms on the public site in your Office 365 tenant, but does take a little extra work.

Til next time, happy SharePointing!