Category: SharePoint

Here’s a quick follow-up to my previous post on dealing with SharePoint list view thresholds. I just bumped up against another case where I had to change some code to deal with it.

To recap the project a bit, I am writing a console app that will import a bunch of data into a SharePoint site. Since I’m human, and I make mistakes, the first step of this importer is to delete any existing data in those lists (which would be leftover from the previous test run).

I had a simple solution for doing that, deleting records in batches of 100, based somewhat on this example. I assumed that would work OK, even with larger lists, but I didn’t take into account that the first step in my process was to get all items in the list. That, of course, fails for a very large list. So I had to change my code to initially get only the first 4000 records in the list. (That can be done with the RowLimit clause in CAML.) Then, I delete from that subset in batches of 100. Then, I just repeat that until there are no more records left.

As far as I can tell, there’s no SharePoint CSOM equivalent to SQL’s “truncate table”, which would have made this much easier. And I feel like I’m probably still not doing this in the most efficient way. If I was creating a process that needed to do this repeatedly, instead of just a few times, I’d dig into it some more. And if I was retrieving items instead of deleting them, I’d probably do something with ListItemCollectionPosition.

I’ve been learning a lot while working on my current SharePoint project. There’s been a lot of “trial and error” stuff, where I try something, get an error message, Google the message, then spend an hour or two puzzling out what I did wrong.

Today’s issue was related to the default 5000-item list view threshold. I was already aware that it existed, and that I’d need to take certain measures to avoid bumping up against it. The main list for my project is going to have about 20,000 items in it, when it’s in production, so I knew I had to watch out for it.

The list has two fields, company and vendor number, that, combined, form a unique key. In SQL, I would put them together into a single two-field unique index, and that would be sufficient to allow lookups to work well. In SharePoint, it’s a little more complicated. It’s theoretically possible to create a two-column compound index, but I can’t do that on my list, for some reason. (At some point, I’ll have to do some research and figure out why.) So I’ve got two single-column indexes.

One of the things I need to do in my code is pull up a single record from my list, given company and vendor number. I’m using something like the code below to do that. (This is CSOM code.) This code should only ever return a single record, since the combination of the two fields is always unique, and they’re both always specified.

The original version of this code had the ‘CompanyName’ field first, and the ‘VendorNo’ field second. That version caused a crash with the message “The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.” That didn’t make any sense to me, since I was specifying values for both indexed fields, and the result should have always been zero or one records.

Some background first: The distribution of the data in my list is a bit lopsided. There are about a half-dozen company codes, and about 6000 unique vendor numbers. There are more than 5000 vendor numbers in the main company and less than 5000 in the others. In SQL, this wouldn’t matter much. Any query with “where VendorNo=x and CompanyName=y” would work fine, regardless of the ordering of the ‘where’ clause.

In CAML, I’m guessing, the order of fields in the ‘where’ clause DOES matter. My guess is that, with the ‘CompanyName’ field first, SharePoint was first doing a select of all items with ‘CompanyName=x’, which in some cases would return more than 5000 rows. Hence the error. By switching the order, it’s searching on ‘VendorNo’ first, which is never going to return more than a half-dozen items (one for each company, if the vendor exists in all of them). Then, it does a secondary query on the CompanyName which whittles down the result set to 0 or 1 records. I’m not entirely sure if I’ve got this right, but I do know that switching the order of the fields in the CAML fixed things.

So, lesson learned: SharePoint isn’t nearly as smart as SQL Server about query optimization.

Another side-lesson I learned: I initially didn’t have my CAML query specified quite right. (I was missing the “<View><Query>” part.) This did NOT result in an error from SharePoint. Rather, it resulted in the query returning ALL records in the list. (It took a while to figure that out.)

I suspect that I’m going to learn even more lessons about SharePoint’s quirks as I get deeper into the testing phase on this project.

Useful Links:

• Creating Performant SharePoint apps with Large Lists
• Resolving the List View Threshold when Migrating to O365
• Demystifying The SharePoint Lists Threshold
• Query Throttling and Indexing
• Manage lists and libraries with many items

I’ve been working on a big SharePoint 2013 project at work, and I’m learning a lot about the ins and outs of developing custom applications with SharePoint. (TL;DR: It’s messy.) There have been a bunch of times when I’ve come across something weird and/or interesting and thought “I should write this up in a blog post,” but I just haven’t gotten around to it. Well, this time, I’m going to at least start writing something up. (We’ll see if I get far enough to have something coherent to post or not.)

My application is a farm-level solution using a couple of application pages. There’s a search page and a detail page, basically. The detail page loads data from a few different sources, based on parameters passed on the query string. So, in some ways, standard ASP.NET stuff.

After having gotten most of the stuff on the detail page done, I wanted to add the ability for users to add comments to the page. I looked at a number of possibilities for this. One option that jumped out is the social comment control. This control can be added to a page as a web part, via “Social Collaboration”, “Note Board”. To add it to an application page in Visual Studio, you need to do the following:

1. Add a reference to “Microsoft.SharePoint.Portal” to your project.
2. On your page, register the SharePoint portal controls:
   <%@ Register TagPrefix="SharePointPortalControls" Namespace="Microsoft.SharePoint.Portal.WebControls" Assembly="Microsoft.SharePoint.Portal, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
3. Drop the social comment control on the page somewhere:
   <SharePointPortalControls:SocialCommentControl ID="CommentControl" runat="server"/>

I picked up the basics on this from this article, which also covers user ratings.

This initially seemed to work well. I was curious about where, exactly, SharePoint was storing these comments though. From this article, I learned that you need to go through central admin to get to them:

1. Open the Central Admin home page.
2. Click “Manage service applications” under “Application Management.”
3. Click “User Profile Service Application”.
4. Click “Manage Social Tags and Notes” under “My Site Settings”.

This gets you to a page where you can search for notes, by user and/or URL. You cannot do a wildcard search, or simply pull up all notes, so that’s pretty inconvenient. But it was reassuring to see that the notes are indeed stored by URL, with the full URL, including query string. So, for me, each detail page would have its own set of comments, no problem there.

When I did some testing, posting comments to the same page from two different user accounts, I hit a pretty major snag though. The two users could not see each other’s comments. That led me down a rabbit hole that brought me to this StackExchange page. I followed the advice to set “Security Trimming Options” to “Show all links regardless of permission”, and that (eventually) fixed the problem. I also ran the “Social Data Maintenance Job”, as described in this StackExchange page.

I’m not sure if changing that security trimming setting will have any negative effects, if I change it in production. It’s a Central Admin level setting, so it’s something I’ll need to review seriously.

Assuming I stick with this plan, I’m also going to need to be able to create comments in code, as I’m going to be importing a bunch of them from the old site that I’m replacing. It does seem to be possible to do that, based on info from these pages:

• This one covers reading, not writing.
• This one covers reading and writing.

I haven’t actually tried writing any code for this yet, so I may stumble across some “gotchas,” as I have with a number of other seemingly-straighforward SharePoint tasks.

And, after going through all this, I see that, for SharePoint Online, the Tags & Notes feature has been retired. We’re not using SharePoint Online, but if we ever migrate to it, I’d have to redo this functionality. So that’s a serious knock against it.

A lot of the stuff I’ve been doing in SharePoint has been working out this way:

1. Find a SharePoint feature that looks like it solves my problem nicely.
2. Spend some time setting it up and doing some initial testing. Things look promising.
3. Stumble across a problem. Spend a bunch of time researching it.
4. Either:
   • Find that the problem isn’t really solvable and give up.
   • Or find a workaround that’s acceptable, maybe, but not great. (It relies on something undocumented, or a feature that’s deprecated, or it requires changing farm-level settings that I’m not sure I can change.)
5. Realize that I’ve wasted half the day on this.
6. Give up and go to lunch.

So my solution for commenting is probably going to be a standard SharePoint list that I’ll read and write in code-behind and show in a standard ASP.NET repeater, or something like that.

Well, I guess I’ve succeeded in writing a semi-coherent post about SharePoint programming. I’m not sure if it will help anyone, but it might be mildly amusing, if nothing else.

I signed up for a free three-month Pluralsight subscription, via Microsoft’s Dev Essentials program, a couple of months ago. The main purpose of the subscription, initially, was to learn more about SharePoint programming, for a project at work. So far, I’ve completed Andrew Connell’s “SharePoint 2013 Developer Ramp-Up” series, and I’m most of the way through Sahil Malik’s “Understanding SharePoint 2013” series. So I’m actually using the subscription, and getting some value out of it.

I’ve been watching the videos largely at work, but now I’m getting a little self-conscious about it. There’s apparently been some political stuff going on at work regarding streaming video. It’s a long story, but apparently we need to be careful about not watching too much streaming video now. I don’t think anybody would look askance at a few hours of Pluralsight per week, but you can’t be too careful, so I guess I might need to stick with watching the videos at home from now on.

But, either way, I’m getting value out of the free Pluralsight subscription, so I’m thinking about getting a paid subscription. Normally, I’d wait until the free one was up, but Pluralsight does a Black Friday sale every year, so now would be a good time to pay for a year-long subscription. Their regular rate of $300 per year is a bit too steep for me. The Black Friday deal is $200 per year, so that’s a bit better, but still not insignificant, so I haven’t quite decided yet. The deal is good through Monday, so I have some time to make up my mind.

On the subject of SharePoint programming in general, I’m pretty deep into a SharePoint 2013 project right now, and I’m learning a lot of stuff by trial and error. I seem to keep finding weird little quirks that I have to work around. And I keep trying to do stuff that, apparently, isn’t easy to do in SharePoint, or at least isn’t obvious. I’ve been thinking about writing up a few SharePoint-related blog posts, but I’ve been too busy to really get my notes together on anything particularly interesting, to the point where I could write a coherent post. But maybe soon.

I recently started working on a new SharePoint project at work. This project is basically replacing an old SharePoint 2003 solution with a new SharePoint 2013 one, making a number of improvements along the way. The requirements for this project are a bit beyond my current level of expertise with SharePoint. (Which is a fancy way of saying that I don’t know what the hell I’m doing on this.)

When I last worked on a major SharePoint project, I’d bought a few books on SharePoint 2010 and 2013, and read through them. (Or at least the parts that were relevant to that project.) That was more than a year ago, though, and I’m pretty rusty now. And the new project is a lot more complex than that previous one. So I went back and reread some sections of those books, and did some typical internet research, and stuff like that.

I also remembered that Andrew Connell had a series of videos available on Pluralsight covering SharePoint 2013 development, and that you can get a 3-month Pluralsight trial account through the Visual Studio Dev Essentials program. So now I’ve got a free Pluralsight account that will last me through to the end of the year, and I’ve been watching the Andrew Connell videos in my spare time. When I’m through with those, Sahil Malik has a bunch of SharePoint 2013 videos on Pluralsight too.

I’ve been watching the SharePoint videos on my desktop PC at work, but Pluralsight also has iOS apps, including one for the Apple TV. So I need to download that, and see if the developer training videos are at all effective when watched on a regular TV, from my couch. (I was going to do that on Sunday, but my migraine intervened.)

I’ve thought about paying for a Pluralsight subscription occasionally in the past, but I’ve always decided against it, due to the cost: $300/year or $30/month. So, a good bit more expensive than Netflix, though maybe that’s not a fair comparison. There’s a lot of other stuff on Pluralsight that I’d love to watch, but it’s so hard to find the time to start learning anything new. So I don’t know know if I’d really get my money’s worth out of the subscription. Maybe if I could talk myself into watching Pluralsight videos instead of NCIS reruns once in a while, I could finally learn AngularJS.

As a follow-up to my previous blog post about hosting a web browser control in Dynamics AX, here’s a write-up on how I fudged a SharePoint page / InfoPath form to accept multiple field values from a query string parameter. To reiterate some of the background, the idea here was to be able to open up a new request in SharePoint, from Dynamics AX, with a few fields on the form pre-filled, so that the user wouldn’t have to copy & paste a bunch of stuff from AX into SharePoint.

My idea was to pass those values on the query string, which seemed pretty reasonable. I found some information on doing that with a bit of JavaScript, but that didn’t look like it would work well, for a form that had been created in InfoPath. So then I looked to the “query string URL filter web part”. This web part can be added to a SharePoint page, and allows you to pass a single query string parameter to a field on a SharePoint/InfoPath form. The big issue here is that it only supports a single parameter, so my plan to do something normal, like “?SO=S1234&PO=P1234&item=123456…” wasn’t going to work. After reading this blog post, and some other related posts, I came up with a plan to encode all of the values I needed to pass into a single parameter, of a form like this: “?param=SO:S1234|PO:P1234|IT:123456|…”. Not very pretty, but it would get the job done.

I mapped that one parameter to a hidden field on my InfoPath form, then added a bunch of rules to that field to break down the value and save the parts out to the desired form fields. There aren’t a lot of string-handling functions in InfoPath, but I found that substring-before and substring-after were enough for what I needed to do. A formula like this:

substring-before(substring-after(URL parameters, "PO:"), "|")

can be used to extract the PO # “P1234” given an example like the one in the previous paragraph. This works, but it’s a little tenuous. If I had too much data to cram into the parameter, that would be a problem. Or if I had to worry about having special characters (like the colon or vertical bar characters) in the data fields, then that could confuse things quite a bit. But for my use, it works out pretty well.

I don’t actually do much SharePoint / InfoPath work. Every time I do, I feel like I’ve travelled back in time, to an era when InfoPath seemed like a good idea. (Though I’m not sure it was ever a good idea…) It doesn’t seem to have much of a future, though Microsoft will support InfoPath 2013 until 2023.

I’m currently working on a SharePoint project that’s going to involve a number of fairly kludgy bits, mostly because… SharePoint. I think I may be able to get a few mildly amusing blog posts out of this project. Today’s one is about the !important directive in CSS.

This article titled “When Using !important is The Right Choice” should probably be one word long: never. Understanding specificity in CSS is important, and there are occasions where it’s appropriate to use !important, but only a few. Using it in a site-wide template for a SharePoint site is not one of them, under most circumstances. I had to write a little JavaScript (using jQuery) to hide part of a SharePoint page, and that task was made more difficult than it needed to be due to overuse of !important in the site template.

I’m the first to admit that I don’t know nearly as much about CSS as I should, but I know enough to know when I’m looking at a careful design, and when I’m looking at “let’s just try random stuff until this looks right.” Heck, I probably shouldn’t criticize; the base CSS on a SharePoint site is probably already pretty bad, so someone designing a custom template likely has an uphill battle getting his or her own CSS to work well within the framework provided by Microsoft. SharePoint is old enough that it’s supporting a lot of dead weight from old versions, most of which probably can’t be trashed without breaking too much stuff.

I’m starting to work on a SharePoint project right now that sounds like it should be simple, but is actually kind of complicated. Or at least if seems to me like it is, possibly because I’m still new to SharePoint and I’m not always stumbling across the “right” way to solve the various problems I’m encountering.

The basic problem is to take a purchase requisition, stored as a normal SharePoint list item, and allow the user to save it to a nicely-formatted Word document or PDF, so that it can be sent out to a vendor as a purchase order.

I went through quite a few ideas for this, most of which are likely very wrong. What I’ve settled on, for now, is creating a new ASP.NET application page which will take the ID of the purchase requisition on the query string. I can then link to this page through a custom action on the regular list item display/edit form. The new page will read the fields of the list item via the SharePoint object model, substitute them into a Word document template, then allow the end-user to download that document from their browser.

Previously, I’ve always used third-party libraries, such as EPPlus, to generate Word and Excel files. But, in this case, I wanted to avoid third-party DLLs and see if I could do what I needed with just the Open XML SDK.

I’m discovering a lot of interesting things about Open XML as I go. First, there’s a tool in the Open XML SDK, called the “Productivity Tool”, that can take a Word document and generate the C# code necessary to create that Word document from scratch. When I started working on this project, I was concerned about where I would store the document template; now, it turns out that I don’t have to store it at all! I can just generate it from scratch in code each time. And I can just modify the generated code a bit so that the values I need to substitute in can be variables instead of constants.

The generated code, unfortunately, is pretty messy. My first shot at running my purchase order template through the tool resulted in 10,000 lines of code. I made some changes in the document to simplify things, and I got down to about 7500 lines, which is still a lot.

I also discovered the “Power Tools for Open XML” library, which can be used to simplify the formatting on a Word document a bit. I ran it on my template, then generated new C# code, and now I’m down to 7200 lines. Still a lot, but it’s manageable.

So it seems like I now have a viable solution that doesn’t need to read or write anything to/from the file system, doesn’t have any oddball DLL dependencies, and should be pretty straightforward to code, test, and deploy.