Note: this was only
tested in Excel 2019. Office 365 Pro Plus has something called Compare sheets
similar to this function
I had the need the other day to compare two excel files that had some information in them from exported SharePoint lists. They were very similar, but I didn’t know of a good way to compare each of them to the other since there were about 200-ish rows in each list, so sorting through them would take a while.
That is until I ran
across the compare functionality in Excel that could be turned on my making the
Inquire add-in visible.
If you have spent
much time working with SharePoint, you know that once a group of users likes it
to store information in libraries or Lists, you can certainly tell.
happens is you get an email (or call, or service ticket) one day that says
“Hey we are getting some kind of error that says we have exceeded the list
view threshold…”. No problem, you think, I’ll just go into central admin
and raise the threshold above 5000 and that should fix that.
Well, it does fix
the issue, but only until that threshold gets crossed again a week/month/year
And the story
repeats over and over again until everything is slow and everyone hates
SharePoint because its so slow… am I right???
What I’d like to
recommend today is a method I have used many times before that solves this
problem and hopefully teachers your users a more efficient way to store their
content in SharePoint.
For this example, lets say I have a list with 30,000+ items in it.
I see after looking
at the content in this list that we have about 5-6 year’s worth of information
in this list.
And that there is the kicker. The solution I want to recommend in this case is that you take your content from this list of too many items and break it into several smaller lists by year.
Now this structure
is much more manageable and it also allows you as an admin to go into central
admin and lower that threshold to something that wont cause SharePoint to move
as slow as the day at work before you get ready to go on vacation (Anyone ever
experienced that as well?).
Probably the easiest
way to accomplish this would be to use a third party tool that your admin group
might already have. There are bunches of them and you can read more about it
If you are more
developery and want to try and tackle the task using PowerShell, you can have
at it, but your mileage may vary. Especially when dealing with item level
permissions, notifications, and workflows (beware and double check for these).
Also you can do
things like set indexes on columns and other strategies like Metadata and such
that you can do to also help with scenarios like this, but those are topics
that could be their own posts in the future.
So going forward,
please I beg you don’t just increase that view threshold and leave it, you will
eventually have to come back and deal with the consequences. It brings to mind
something about an ounce of prevention equals a pound of cure or something along
those lines I’ve heard before…
Thanks for reading
and I hope this helps prevent any future pains in SharePoint!
Today’s post I thought I would include in order to find anything needed to create a working demo of SharePoint locally. Nowadays, Azure or other cloud providers make this much easier, but if you want more control you can spin up your own locally.
Today I had a problem with a group of users that were trying to work with a very large PowerPoint file in SharePoint and didn’t realize until I downloaded a local copy of the file that it was too big to work with through Office Web Apps.
So I did some digging around to see if there was a way to possibly shrink the files, so I ended up finding something here:
I had an interesting error come across today when I was working on a new Nintex form list. I have been doing some power user training in my company lately and a few of my users have taken the plunge and started creating their own electronic forms in Nintex.
Which is awesome! It’s a great feeling when you demonstrate something to someone in a training session and then see what they are able to go and create on their own.
In this case, though the user had designed a great looking form, but when they went to submit the form once it was complete it gave the following error:
Which was very odd as I had never seen an error that gave all zeros for a correlation id on and error in SharePoint.
So I started to do some digging online and I noticed this was a common question that has come up before for several users in the past, but no one online I had seen had dealt with this in the context of creating and submitting forms.
After some more research I had a hunch that this was being caused from a required field on the form that was missing data when the form was being submitted. So I checked the form in Nintex form designer, and sure enough!
I saw that the “Title” column was not on the form, but in the list settings was still set to the required option like it is out of the box in SharePoint.
So I went into the list settings and turned off the required setting for this column and then went back to the form and tried submitting it again.
Everything submitted as it was supposed to and now I can move onto more form troubleshooting.
I hope this helps anyone else who runs into this error again and if you have any thoughts or comments, please feel free to add them into the blog.
If you work like I do trying to setup test environments in SharePoint often, the experience is great when you finally get all the bells and whistles activated for the features you need to test, but then what?
Now you have a great looking environment with no data! A SharePoint farm with no data in it is like a chocolate chip cookie without the chocolate chips in it. You might as well throw it away and go get something else to eat.
Never fear though as I have recently discovered a great tool that can auto generate much of the content needed to flesh out a SharePoint farm and it happen to be an open source project on GitHub created by the same fine folks that created SPDocKit!
It’s called SPDG (short for SharePoint Data Generator) and it’s another awesome tool to add to your arsenal if you don’t already use it.
Just head over to Github at the following link and check it out here!
Below is an example screenshot to give you an idea of what it looks like:
What is even better about this tool is that you can use it to generate as much content or as little as you need to flesh out your test box. It works with SharePoint Online, and even older versions of SharePoint like 2010!
This tool is so great and I can see so many uses for it, I may just have to do a demo video of this sometime down the road, so stay tuned! I’d love to see more folks talking about this because it definitely saves a lot of time and effort to fill up an empty farm when you need to give a demo!
Many thanks again to the guys over at SysKit for developing something awesome like this!
For many people who work in development and IT there tends to be one universal truth that I have seen time and time again. People setup and spend lots of time and money creating great software systems for businesses and other organizations, but when it comes time to document where things are and how things are configured, documentation falls in line sometimes with the same considerations as system security (hello Equifax?).
I don’t normally write product reviews or give endorsements, but for this one, I have taken an exception. If you need to figure out what all is in your SharePoint farm right now and don’t know, or even if you “THINK” you know about everything in your SharePoint farm and don’t have it on paper, please check out SPDocKit from the great folks at Syskit.
Here’s a great summary if you have never heard of them!
Not only will this product document the configuration settings of your farm, it can regularly take snapshots of your environment to help you know how things change over time. And you can even easily publish reports of your farm config at any time! Just take a snapshot (which is like a backup type of job), and then you can report on all kinds of things!
If you want to put your own company logo or labeling on the reports that get output, you have that option as well!
One other thing to know is you can try this out for 30 days and see if you like it, but I can already tell you that once you set this product up and run it once and actually see all of the information that is gathered about your farm in a small amount of time, that you will literally be telling the folks at Syskit “Shut up and take my money!”.
I hope you get to check it out! It’s also very affordable for what the product does!
I had an interesting request by someone the other day. I was helping them set up a new site template for SharePoint 2013 and they wanted to display a couple of reports on their site that they had in pdf format in a way to showcase the numbers of the report to others in the company that would visit this site.
There are multiple ways actually to do this very method, but the one I ended up going with is referenced in the following article:
After doing this one the page it gave a pretty good layout on the page. My only concern going forward is having to update this manually and having to possibly retrain someone to keep this showing the most up to date info on the page.
There are other things I would like to research as part of this like putting in zoom or other capabilities in the code to modify the view of the pdf for the users.
I hope this gives you more ideas for displaying info on sites and pages and should work regardless of what version of SharePoint you are working with.
If you have interesting ways to display reports or other objects on pages, feel free to add to this in the comments below.
Beau Cameron just posted a fantastic article about how to do this in O365. Here’s the link:
It’s been a while since we talked about KB2775353 and how it made our SharePoint 2010 servers where I work extremely sad and kept them from correctly displaying our BI dashboards in 2010 (see here).
I was originally told when I found this bug that the fix would be put into a patch around the August 2013 CU timeframe, and sure enough with the newest round of patches released I went and got my grubby little hands on it. It took a few days for us to properly test it here, but I can confirm from our internal testing here that this patch corrects our display issue with our dashboards! Yay!!!!
Just to be clear, this is my personal scenario of how things went, YMMV.
We originally patched our farm to the April 2013 CU for SharePoint Server 2010 due to an incorrectly applied patch that came in from windows update, so we were originally forced to move to the April 2013 CU. For the meantime we have been working around this issue by displaying our dashboard items in a different way. We notified MS support of our issue and verified it was indeed a bug and they went to work on correcting it.
So when the August 2013 CU for SP2010 came out a few days ago, I had a couple of options on how to bring my farm up to the latest patch level. What I ended up doing was patching my farm with SP2 and then after checking to make sure everything applied correctly from this patch, applied the August 2013 CU for 2010.
After this, we had to redeploy our dashboards back to how they were before the April 2013 CU mess and verified that everything looks good and is back to normal.
Now I can put all of this mess behind me and work towards upgrading everything to SharePoint 2013. I hope this helps anyone else who runs across this scenario with their BI setup in SharePoint 2010.
Thanks for all the twitter questions and replies to my previous post everyone! I think with 2013, I will be more conservative in my patching going forward…