Tag Archives: lists

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!

Advertisements

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!

Showing users’ profile pictures in a SharePoint list

Special thanks to Joann for the question!

A common use case is to have a list of people in a list in SharePoint, maybe they’re members of a project, contacts for a department, your favorite people to talk to, whatever it may be, you have a list. This example is a listing of my teammates:

slalom people list

Now I’d like to spice it up a little and see their pretty little faces. In my example, I’m just using the Title field to store their names (well, the Title field renamed to Name, regardless, same scenario). This won’y fly. We need to add a people picker to the list. I added one called Account Details. You could name yours Photo, Pic, Face, etc.

Add a people picker to the list, and add the correct values:sharepoint list with names

You can edit this view in Quick Edit mode, and copy/paste the Name column into the Account Details column.

Now let’s configure it to show their picture! (yes you could’ve done this with the above step, but for illustration purposes I add more steps)

When you edit the People Picker Field, check out the Show Field property.

edit people picker

See the few options at the bottom? Picture Only, select one. In my example, I selected 72×72

people pictures in sharepoint

BLAM! Profile pictures!!

Now I’ll let my team know I’m sharing their pretty faces.

‘Til next time, Happy SharePointing!

We all know what IsDlg does… wait, what the…??

Here’s a little nugget I came across, on SharePoint 2010 and 2013 (including O365), and was exceptionally frustrated with. There’s a difference between IsDlg and isdlg. See it? Case sensitivity. Come to find out that means the world to SharePoint.

I was tasked to setup a basic Page Viewer web part to consume a page from another web. Simple enough. I threw the URL into the web part, including IsDlg=1, and blamo, I got my page. However, I couldn’t scroll the web part. The page I was using was longer than the web part, which would normally cause the page to scroll, but alas, not in this web part.

Enter the IsDlg parameter. Don’t know what IsDlg is? It’s a little query string parameter that SharePoint uses to hide elements on your page using CSS. Have you noticed that if you enable the dialog on a list, and the forms then appear in the dialog window, that the header is missing? The left nav too?

Dialog Window in Use

It’s the same page as if you didn’t use the dialog setting, Microsoft isn’t crazy enough to have two versions of a page that does the same thing. Instead when the IsDlg parameter is sent to the page as a parameter in the query string, it hides everything on the page with a CSS class of s4-notdlg.

Here’s what the URL may look like:


/sites/lozzi/Lists/Events/DispForm.aspx?ID=4&IsDlg=1

Note the IsDlg=1 at the end. I’m a big fan in questioning everything, so let’s try it out for ourselves.

Go to a display form of a list item, which is not in a dialog.

A Normal Display Form

and add &IsDlg=1 (case sensitive) to the end of the URL, hit enter.

Display Form with IsDlg=1

See how we lost the top header and left nav? Looks like the dialog up above, right? Pretty cool eh? So, as you may be thinking, “this is awesome, I can use this in the page viewer web part too”. You would be correct in your thinking! Here’s where things get a little fishy.

When you use IsDlg (again case sensitive), SharePoint does NOT scroll the window if the window requires it. Again, give it a whirl. Take your window you used above, and resize it so it’s nice and small, put the Close button below the bottom of the window.

IsDlg missing scroll bars

See? The close button is below the window, but there’s no scroll bars. If you change the IsDlg to isdlg, then you get a happier page.

Scroll bars come with isdlg

Whew, there’s our trusty little scroll bar, now I can press Close.

Let’s dig deeper: core.js

Why is this happening? Well I dove into the JavaScript that handles it, in the core.js file, there are several lines of code which look similar to:


isdlg = (ajaxNavigate.get_search()).match(new RegExp("[?&]IsDlg=1"));

JavaScript is very case sensitive, so it’s actually, and quite specifically, looking for IsDlg, not isdlg. Weird right? So if you send the page IsDlg, SharePoint forces the size of the window and disables the scrolling. If you send isdlg, SharePoint does nothing with it except for hiding the CSS elements (as we discussed earlier). Your browser’s native capabilities kick in and scrolling occurs.

This is great for the dialog interface SharePoint is use to as they will size with the page. Now the user is scrolling the entire page and not just the page within the dialog. This is annoying for us if we want to use IsDlg feature elsewhere and we want to scroll just inside the web part. We have to use the isdlg option instead.

Oh yeah, I confirmed this occurs in Internet Explorer, FireFox and Chrome too ;)

‘Til next time, Happy SharePointing!

SharePoint 2013 Script: Hide or Disable your fields

This one has been on my list for a while. I’ve used a collection of JavaScript methods to help streamline customizing forms in lists. I have created and compiled this little collection starting probably 5-6 years ago, and over time I’m constantly updating, tweaking, enhancing it, especially more so as of late working with Office 365.

I have a lightweight JavaScript file which allows you to hide or disable (set to read-only) fields in a new or edit form. It’s pretty basic but is a big value add. Also, you can set a field to read-only for only certain groups, meanwhile allowing other groups to edit the field. You can also hide a field completely, as well as hide a field and only show it for certain groups. Pretty sweet right?

UPDATED 2/28/15 to fix a few bugs as noted in the comments.

UPDATED 10/22/15 to fix disableWithAllowance and people pickers.

UPDATED 10/23/15 to fix disable and disableWithAllowance and metadata fields.

UPDATED 5/24/16 to fix issue with disabling Lookup Fields, now will show correct value.

UPDATED 8/1/2016 to fix issue with disabling choice fields as radio buttons, now will show the single selected value.

The script is here: download from CodePlex.com.

How to use it

Include the script along with jQuery on your page. Ideally, throw this nugget into your master page and you can use it anywhere in your site.

&lt;script src=&quot;//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;
&lt;script src=&quot;/SiteAssets/Lozzi.Fields.js&quot; type=&quot;text/javascript&quot;&gt;&lt;/script&gt;

Once that’s in, get using it! It’s really simple, I threw this on my new form for my task list:

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

This will change our New form for a typical task, from:

New SharePoint Task Form

to

New Task Form with Hidden Disabled Fields

See the difference? It’s subtle, but when working with end users, it can provide a huge benefit to help streamlining your business flow.

From here, you could have the edit form disable or hide a lot of the fields for normal users, only allowing your Project Managers, or if this is a support request list, your Support Team access to modify fields as needed. The options are endless.

More details

Lozzi.Fields.disable(fieldname)

Simply disables the field, for all users. It hides all controls in the field and displays the value instead.

Lozzi.Fields.disableWithAllowance(fieldname, groups)

Disables the field, but enables it for the users in the groups specified. Also, Site Collection Administrators are included automatically, so they can always edit the field. You can send the groups in an array, like [“Group One”, “Group Two”].

Lozzi.Fields.hide(fieldname)

Simply hides the field, for all users.

Lozzi.Fields.hideWithAllowance(fieldname, groups)

Hides the field, but shows it for the users in the groups specified. Also, Site Collection Administrators are included automatically, so they can always edit the field. You can send the groups in an array, like [“Group One”, “Group Two”].

Some other important notes

  • Currently, this script does not work on list views, meaning a user could edit the data in datasheet/quick edit view.
  • This script should work just as well on SharePoint 2010 if you so desire.

Download this script here: download from CodePlex.com.

Til next time, Happy SharePointing!