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.
There are a few
things that after I learn them, make me want to immediately share with as many
people as possible. This is definitely one of those moments.
After starting a
migration project where I work involving some cleanup with Nintex workflow
content databases, I’ve run into something that everyone should check in their
SharePoint environments right away.
It’s a setting under
Central Administration for Nintex and the setting I want to warm everyone about
is Automatic Database Mapping.
When you set up
Nintex as a solution in SharePoint the install is pretty straight forward and
not all that difficult in getting set up and going quickly.
One part of getting
Nintex going after you install it is creating a config and content database to
start using it to store information about the workflow history and config of
the service itself in SQL.
Its at this point
when you start that I recommend that you go in and turn OFF automatic database
Why am I urging you
to do this so intently?
Well let’s just say
I am working on a migration now with an environment that’s been using Nintex
for about 3 years now, with a big config database (around 100GB or so) and
multiple Nintex content databases with automatic database mapping enabled… oh
and in this farm, there are about 30-40 site collections using Nintex actively.
Now at this point my
main goal with this migration is to consolidate the larger Nintex sites into
their own content databases.
But with automatic
mapping enabled, this is going to make my job a lot harder to sort out.
Now I hope you see
why this is important to do when starting at the beginning of a setup. 🙂
Here are the steps
to make this change in your SharePoint setup.
Open Central Admin, and go to the Nintex section and look into the Database Management section:
Once in this section, go to the Manage area of databases down at the bottom of this section:
From here you should see the following option where you can make the change to disable automatic mapping here:
Once this is done and you click okay, then you will next need to map your existing SharePoint content databases to your existing (or even better newly created and segregated) Nintex content databases.
Just make sure that as you do this to make sure there is no active work going on for the sites you are working with. Also it wouldn’t be a bad idea to do an IISRESET as well to make sure the settings you changed take effect right away.
Trust me, doing this when you start will save you alot more time and effort than having to take a bunch of big existing databases and map them to newly created blank databases to logically separate the content out.
I hope this helps with anyone starting out with a new Nintex install or if you have to go down the cleanup road like I am as well.
Here lately I’ve been looking into doing some cleanup for Nintex related databases in a SharePoint farm, so I thought I would share a short write up I did for a few other folks on the process of moving content in the context of dealing with Nintex content databases.
First take backups
of Nintex and content databases involved
Go to central admin
under the Nintex workflow section and pick the databases option.
From here scroll
down and check your DB mappings to make sure you know which DB’s will be
involved in these changes
Next go back to the
Nintex Databases section and create a new blank Nintex DB to use for migration
(name ie. Nintex_Content_SiteName)
If you are moving
SharePoint content as well, go into Central admin under Manage Content
Databases and create a new one there, or you can use PowerShell to create the
new SharePoint Content DB and attach it to the farm.
content can be migrated by normal backup-spsite and restore-spsite methods
specifying the new Content DB created to make sure the SharePoint info goes to
the new DB.
Or this can be done
using the Move-SPSite command through PowerShell as well with an IIS RESET as
the last step.
Now to migrate the
Nintex Workflow info you will need to do a few more steps:
Stop the web app that contains the Nintex info being moved
Also stop the SharePoint Timer Service on ALL SharePoint servers in the farm so no actions take place in the background during the move.
Run the nwadmin -o movedata command to migrate the content to the new Nintex DB
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:
Recently I ran across a really tough issue that I had with one of my workflows that kept failing right before the approval step of the workflow was being sent.
Below is an example of the workflow I had with the listing of the error I was getting:
I tried going over this many times and even went and created a whole new list for this and imported the workflow into this new list with a newly build form to make sure I could minimize any other variables in the error.
Well the bad news was even in the new list I created and imported the workflow into, I still got the same error. So, I started digging in with some help, and was ultimately able to find the source of the issue on the step of the set item permissions in the workflow.
It turns out that when this workflow was originally created the set item permissions had several users explicitly defined in it to grant permissions to the item on before an approval would take place.
What I didn’t realize until I looked deeper into who all was being given access to this item was that one user who was specifically designated in the action was no longer with the company.
So, when the workflow would go out and try to match this person with a valid AD account, it would error out and not allow the workflow to go any further.
The location of the error was in the ULS of SharePoint, but only when verbose logging was turned on, of course…
So in the future I will always from now on, check to make sure that all users being granted access to items in workflows have valid accounts.
If you ever run across something like this I hope this tip will save you sometime and allow you to get right to the solution.
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.
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:
Then from there, it’s an even more frantic day moving from session to session.
One great talk I attended was the roadmap update for SharePoint 2016 given by Jeff Teper and several other MVP’s. the link to this presentation is here:
Some great announcements coming out from this talk were for me the great ability now to do zero downtime patching in SharePoint 2016 with a 4 or even a 2 server farm. People will be able to do this with the new min role combo roles that they have coming out in November of 2016.
Another area of interest for me will be all of the new functionality and features given to Power Apps and Flow as well as more features being added to the mobile apps for SharePoint and others.
love the fact that Windows Server 2016 is now released to the public and out of tech preview! This means that everyone will now be able to bring up farms and other resources on all the 2016 products.
There were so many Azure related announcements, it made my head spin, so I will try to find a summary link and post it below.
Another highlight of the day was seeing the future of a lot of technologies in the innovation keynote. The link to that session is below:
They even brought Deon Sanders out on the stage to talk about playing fantasy football with an AI. Now that’s pretty cool.
As the videos and links to other talks become available, I will add them to this post. Now it’s time to start day 2 of ignite.