Today Office 365 went down in Europe and it's down since more than 3 hours at the time of this post.
OK, Microsoft, it's technology - we get it, and you'd still make the numbers in the SLA report due to the high number of users you've got.
But there's something more worrying in today's scenario - communication!
Wouldn't it be nice to put up something on the http://status.office365.com page rather than the bogus message below? And not asking people to login to their tenants and check their dashboards? When in fact they CAN'T login at the time of calling you...
People are relying on this platform for their daily jobs, and a good communication would have been key in user satisfaction with the products. The communications today is absolutely ridiculous.
I even got a call from one of your support reps asking me to do a remote session and see one of our other issues we're experiencing, so I kindly had to inform him everything is DOWN in Europe!
Meanwhile, we had to find the hard way what's working and what's not, e.g;
- OneDrive sync is working
- OneNote sync is working
- Yammer mobile app is fine (if you haven't logged out)
- Everything else is FINE if you were logged in and haven't logged our prior to 11:00 this morning.
I'd say this is a complete communication failure on your end today...
And while you've put this nice little "warning" message on the Azure portal status, it's still unclear and doesn't mention Office 365 (you need to be aware it's dependent on Azure) and it's saying North and West Europe...while NO ONE in Europe can't go to the Login Portal.
Thursday, December 3, 2015
Monday, November 23, 2015
1 TB space in SharePont Online! /not OneDrive/
Update 18/02. The space has reappeared (this time as 1 TB - expected) although the feature on the roadmap is still "In Development".
Update: Microsoft has reclaimed this space and when talking to them, they said this was "a test 2 TB rollout to select tenants", and ours is not a First Release one. Shame.
Anyway, they said by the end of January all tenants (first they claimed Enterprise only) should get upgraded to 1 TB.
On this foggy November morning, I've logged in to the admin panel in one SPO tenant and saw this!
Update: Microsoft has reclaimed this space and when talking to them, they said this was "a test 2 TB rollout to select tenants", and ours is not a First Release one. Shame.
Anyway, they said by the end of January all tenants (first they claimed Enterprise only) should get upgraded to 1 TB.
On this foggy November morning, I've logged in to the admin panel in one SPO tenant and saw this!
Prior to Friday afternoon, that space was ~130 GB, based on the well-known formula 10 GB + 0.5 GB per licensed user.
The O365 roadmap says Office 365 tenants will now get 1TB + (500MB * # of E/A/G users) but this feature is still in development, and our tenant is not a FR one.
Have you already observed the storage increases in your tenants?
By the way, thanks Microsoft! Really generous :)
By the way, thanks Microsoft! Really generous :)
Friday, November 6, 2015
People Directory with CSWP
Today I started to work on a mock-up of a People Directory which is not utilizing anything else, but a Content Search Web Part due to the easy customization Display Templates and the powerful features it offers. It already has som good templates like Large picture for example, that will be all I need + Department and Job Title (which will be managed properties, linked to a term set).
Even though this Web Part is heavily promoted and is supposed to enhance/replace the good old CQWP, it doesn't work right away as you'd expect (at least in SharePoint Online).
Let's imagine you create a blank page and you add the Web Part.
You then configure your query - it'll be very simple "@*onmicrosoft.com" so that I can get all the people from my demo tenant
As you can see, the query is tested and it simply works. Then you save your Web Part and get this:
Some people suggested that this Web Part is not available in SharePoint Online Plan 1 and they've been in touch with Microsoft Support .... which I have to use in production... but I couldn't find an official source stating that. I think that's old information, I think the web part was not even available in SharePoint Online in the beginning. I know it's definitely not available on-premise if you don't have the Enterprise version.
I've also tested this on my demo tenant which is on SharePoint Online Plan 2... and I got the same results. Turns out pretty simple, I just had to enable the Cross Site Publishing feature on the site collection and the web part works.
+ a little bit of CSS = a sample of my desired outcome:
The next thing is to find out which properties we want displayed in these boxes, if they're custom ones they should be mapped to a managed property first before we can stick them in here.
But that will be another blog post :)
Even though this Web Part is heavily promoted and is supposed to enhance/replace the good old CQWP, it doesn't work right away as you'd expect (at least in SharePoint Online).
Let's imagine you create a blank page and you add the Web Part.
You then configure your query - it'll be very simple "@*onmicrosoft.com" so that I can get all the people from my demo tenant
As you can see, the query is tested and it simply works. Then you save your Web Part and get this:
Some people suggested that this Web Part is not available in SharePoint Online Plan 1 and they've been in touch with Microsoft Support .... which I have to use in production... but I couldn't find an official source stating that. I think that's old information, I think the web part was not even available in SharePoint Online in the beginning. I know it's definitely not available on-premise if you don't have the Enterprise version.
I've also tested this on my demo tenant which is on SharePoint Online Plan 2... and I got the same results. Turns out pretty simple, I just had to enable the Cross Site Publishing feature on the site collection and the web part works.
+ a little bit of CSS = a sample of my desired outcome:
But that will be another blog post :)
Thursday, October 15, 2015
Using "#" in SharePoint Navigation items
Morning folks, that's going to be one of my favorite blog post types - a short one :)
The functionality to click on a category (Basic) for example and automatically scroll to the respective section on the page (ul with id #basic) is what I found challenging with the current setup in SharePoint.
Instead of using a custom categories list in the page, my goal is to use the container for the Current Navigation (Quick Launch) which is already there. This will give us consistent look & feel across the whole site.
The problem I've found today is that "#" is actually not working in the links of the SharePoint navigation...if you're using Structural Navigation for your site. No matter where you insert it, on save your links will always default. To see what I mean, here's the link that I insert and save.
In the reality, I get this in the browser (all of them):
So... my solution was a quick win this time - switch to Managed Navigation for this site, create a termset for it and use Simple Links for the navigation terms. Works as a charm.
What you see...
...is what you get:
Monday, October 5, 2015
Injecting JavaScript in a SharePoint Online site collection the proper way
With recent changes in Office 365 branding guidelines, it has become significantly more difficult to insert small bits and pieces of functionality like a 10-line JavaScript, for example. I had to do this in order to bring the same global navigatoin that we use in our main site collection into the Search Center site collection (the requirement is even more difficult if you use Managed Navigation, but that's not the topic of this post).
By default, the navigation is missing from the Search Center in SPO and SharePoint 2013.
A great way to get it back is described in this post by MVP Elio Struyf. But how would you inject the JS in the whole site collection? By default, we have 7 different result pages in this site, and we plan to create more... so here are our options:
1) Insert the script in a CEWP or a Script Editor webpart - manually in each of the pages... time consuming and the need for manual adjustments arises each time you create a new results page.
2) Use custom master page for the site collection - a big No-No for SharePoint Online since a few months.
3) Build a provider-hosted app that can inject your customization :) Great video by the Office 365 PnP team, but it sounds to me like killing the fly with a hammer!
4) Register it through another .js and forget about supportability and removal with no code... Quick & Dirty approach by Tim Ferro that I'd only ever use as a PoC.. (tested it and does the job by the way).
5) Using PowerShell! Wait, what!? Yep. After some head-banging last Friday afternoon, my friend and PowerShell guru Ivan Yankulov has reminded me that there was a pretty good cmdlet from the PnP-PowerShell project.
All you need to do is...
1) Download the project locally
2) Have WiX toolset installed (at least 3.10 if you use VS2015)
3) (Optional) Have Windows Management Framework 4.0 installed if you want to generate the cmdlet Help
4) Install the binaries (\Binaries\PnPPowerShellCommands16.msi) for SharePoint Online
5) Run PowerShell 3.0 as an admin...
6) Have a look at the AddSPOJavaScriptLink documentation.
7) Connect to your SPOnline tenant (Connect-SPOnline –Url https://yoursite.sharepoint.com –Credentials (Get-Credential)
8) Execute the command...
Let's assume I want to inject something called elementshide.js to my whole site collection. So whenever someone creates a site or a page inside, they'll get the JavaScript loaded automatically for consistent site collection-wide user experience. The key parameter is like an identifier for your script - take a note of this if you want to remove the script reference later on :) The url is basically the path to the script (relative URL is always recommended) and then the scope could be Site for a site collection, or web for a subsite in SharePoint Online.
Add-SPOJavaScriptLink -key ElementsHide -url "~sitecollection/SiteAssets/elementshide.js" -Scope Site -Web https://dimitarm.sharepoint.com
9) Verify. Go to your page and view the source code... you'll have the following piece if the injection went through:
if (scriptsSrc.indexOf('/SiteAssets/elementshide.js') === -1) {
var newScript = document.createElement('script');
newScript.type = 'text/javascript';
newScript.src = '/SiteAssets/elementshide.js';
headID.appendChild(newScript);
scriptsSrc.push('/SiteAssets/elementshide.js');
}(function(){
So this is it... in a little bit less than 10 steps we've managed to make our Search Center navigation show up...and we're ready for a lot more customization done this way. Hopefully the guidelines by the O365 team do not change to something even more complicated in the next months... |
Friday, September 18, 2015
Collapsible Current Navigation in SharePoint...and my new job
Big news in the beginning of this week. No, I'm not back to school :) I recently accepted a new job as an internal SharePoin employee for a company which is not yet an enterprise, but not a startup either. It will be very different than all of my past experience at service providers and consultancy engagements, but I am excited as it's a very innovative organization and we met when it's just about time for some SharePoint(ing).
Now to the point... one of my first tasks is to create the organization's navigation structure for an Intranet portal. We will use Managed Navigation after an evaluation and demos of all the possible methods. I will do this on SharePoint Online for the example, but the method is the same for SharePoint 2013 as well.
The decision to use Managed Navigation is not the scope of this post, a very good comparison by Microsoft is published here, which evaluates performance, needs and in fact all pros and cons.
One of the main requirements is for the Current Navigation to become collapsible, imagine a Windows Explorer style...like the following example. Now, if you have users that will be switching from file shares to SharePoint, that type of navigation might come handy. I personally don't really like the fact that it does go very big when you expand a lot of nodes, but there's a solution for that.
If the SharePoint site structure becomes deeply nested e.g. Site Collection -> Site -> Site -> Site ... this will also be a pain to navigate in, and most users would most likely prefer the Search option (I got this feedback after conducting a few user interviews and demoing this type of navigation).
Let's review the options to achieve the closest functionality in the SharePoint Current Navigation.
We assume we'll only have one site collection, which is nowadays common for small to medium sized organizations. Especially if you go the SharePoint Online route.
Option 1: Tree View
The way to enable this is simply going to Site Settings -> Tree View -> Enable Tree View.
The Tree View looks like this in a site collection (left) and respectively in a site (right):
Pros:
So I decided to use the :hover pseudo selector instead of the .selected class to address the need for the click issue.
Now to the point... one of my first tasks is to create the organization's navigation structure for an Intranet portal. We will use Managed Navigation after an evaluation and demos of all the possible methods. I will do this on SharePoint Online for the example, but the method is the same for SharePoint 2013 as well.
The decision to use Managed Navigation is not the scope of this post, a very good comparison by Microsoft is published here, which evaluates performance, needs and in fact all pros and cons.
One of the main requirements is for the Current Navigation to become collapsible, imagine a Windows Explorer style...like the following example. Now, if you have users that will be switching from file shares to SharePoint, that type of navigation might come handy. I personally don't really like the fact that it does go very big when you expand a lot of nodes, but there's a solution for that.
If the SharePoint site structure becomes deeply nested e.g. Site Collection -> Site -> Site -> Site ... this will also be a pain to navigate in, and most users would most likely prefer the Search option (I got this feedback after conducting a few user interviews and demoing this type of navigation).
Let's review the options to achieve the closest functionality in the SharePoint Current Navigation.
We assume we'll only have one site collection, which is nowadays common for small to medium sized organizations. Especially if you go the SharePoint Online route.
Option 1: Tree View
The way to enable this is simply going to Site Settings -> Tree View -> Enable Tree View.
Now, I'll share my view, based on experience around this functionality.
- Sites are automatically added, no admin overhead
- OOB solution. No custom master page need, hence sticking to the Office 365 best practices.
- Displays lists and libraries in addition to the sites
- Easy to use, nice icons on the left, indicating the type of each node - that's all OOB again.
Cons:
- NOT security trimmed!
- Displays the Apps (add-ins) as well, no way to hide those. The regular user might not need them.
- Can take a large part of your screen and becomes unusable if you need too much scrolling
- Enabled per site level only, e.g. you need to save your site as a template and create new sites based on that if you need Tree View enabled by default
- NOT consistent accross sites. E.g. does not show the parent navigation when you're in subsites.
- NO way to customize it OOB, needs custom CSS (not a big deal, you still don't need to modify the master page for that and the OOB view might suit you just fine in some scenarios.)
Option 2: Managed Navigation + custom CSS
The Managed Navigation requires some planning, manual work and a lot more maintenance then the Tree View. The way to enable it will be out of the scope of this post, but just don't forget to enable the Publishing feature on your sites before you switch to this navigation. It's a prerequisite.
The way it looks in a very quick and simple demo is this:
But it's not collapsible by default!... Even though there are some articles online suggesting the use of fly-out menu I personally didn't feel it's good enough (e.g. a lot of levels will take a lot of space oon the right of the designated navigation menu control)
There's another solution which uses jQuery and provides the "accordion" feeling. But this will generally work if your items that you want to expand don't have a hyperlink. E.g. if in the above screenshot Operations doesn't point anywhere, then it would make sense to expand it with a click and see its children. But in our scenario the 1st level items would be links to actual sites...the solution still works... but in order to expand the children, you'd need to wait for the parent link to load. Not good, could be annoying for users and the link might point you to a site which doesn't inherit the same navigation.
So... to just quickly review the Managed Navigation against Structured Navigation and the alternative Tree View option... read further.
Pros:
- Consistent across all sites if you inherit navigation in the subsites
- Very good for public sites or extranets, where you don't want it to definitely reflect your site structure.
- Better performance than the structured navigation
- More compact than the Tree View and very good looking when customized
Cons:
- Sites are not automatically added, it requires some maintenance
- Not security trimmed.
- Not collapsible OOB for use in Current Navigation. Needs custom CSS and potentially JavaScript.
- By default it's set to display only 2 levels of hierarchy - e.g. Projects site -> Project X subsite. If you want to display libraries and lists for example, or anything else... you need to touch the master page.
Now on the collapsing functionality... you can achieve that by using some simple css. Take the example a few lines below and save it somewhere on your SharePoint site. For the demo I've used a file, called sideNavBox.css and I've put it in the Style Library.
I got the original idea from Dércia Silva at broculus, but that solution seems to work well only for small navigation structures. I don't see it working with more than 2 levels and it requires you to click on a menu item (e.g. a site) to see its children.By default SharePoint will load the site if there's a valid link behind the navigation menu item. Not too good, you might have clicked on it by mistake, and you might not want to wait the load time (imagine it's a heavily customized site). It also uses jQuery, while I think this functionality can be achieved by CSS only.
So I decided to use the :hover pseudo selector instead of the .selected class to address the need for the click issue.
/* Do not show nested ul by default */
#sideNavBox .ms-core-listMenu-verticalBox ul ul {
display: none; !important
}
/* Show nested ul on hover only */
#sideNavBox .ms-core-listMenu-verticalBox ul li:hover ul {
display: block; !important
}
Now you need to refer your site to the custom CSS file. Go to Site Settings -> Master Page -> Alternate CSS URL (preferred solution):
...or if for some reason you can't do the above, then as a last resort insert it in a custom Master Page directly by putting this line in the <head>. (change the path to your CSS). Please have in mind that custom Master Pages in SharePoint Online should be used if no other customization option exist. The reason behind this is that you''ll end up not getting regular updates on master page functionalities released by Microsoft (they only deploy these to the oslo and seattle master pages).
If you're interested in the way Office 365 Sites branding is going forward, I recommend this great explanatory session by Vesa Juvonen. Then you can decide... to brand... or not to brand :)
If you do decide to ignore the recommendations, here's the control you need to insert in your master page...
<SharePoint:CssRegistration Name="Style%20Library/en-us/Themable/sideNavBox.css" runat="server" />
So, with the hovering trick and some more customizations I've added the nav menu looks like this. Currently I am hovering over the PMO link which is a site in a site collection (could be anything) and Project 1/2/3 are sites under PMO (again could be anything you add in the Managed Navigation through the term store management tool)
The extra styling is just for some similarity to the Tree View menu (the icon is a shameless steal from there)... The "Edit Links" item is hidden and colors/sizes are slightly changed. Some cheeky icons added on the left to make it look more intuitive, too. Pretty basic for the means of our example today:
Here's the additional CSS I applied:
/* Any style you want for the left-hand nav control*/
#sideNavBox {
background-color: #DBE8F3;
}
/* Making some room for the icons on the left */
.ms-core-sideNavBox-removeLeftMargin {
margin-left: 5px; !important
}
/* Insert the plus icon for the ul */
#sideNavBox ul {
list-style-image: url('/Style%20Library/Images/Expand_01.gif'); !important
}
/* SP treeview expand icon for the nested ul */
#sideNavBox ul li ul{
list-style-image: url('/_layouts/15/images/tvclosed.png'); !important
}
/* Hide the Edit Links button in the current nav */
.ms-listMenu-editLink {
display: none; !important
}
And now to the levels... we have the requirement to display more than the two level of hierarchy on the screenshot above. Now if you get to this, and you definitely want to use Current Navigation (rather than Global Navigation) for this (regardless of the fact if you use Structured or Managed Nav in the background) then you need to edit your Master Page e.g. use a custom Master Page in SPO.
So, in a bit more details:
1. Create a blank master page
2. Open your seattle master page and copy all the code to the new one.
3. Go to the V4QuickLaunchMenu control.
<SharePoint:AspMenu id="V4QuickLaunchMenu" runat="server" EnableViewState="false" DataSourceId="QuickLaunchSiteMap" UseSimpleRendering="true" Orientation="Vertical" StaticDisplayLevels="3" AdjustForShowStartingNode="true" MaximumDynamicDisplayLevels="0" SkipLinkText="" />
To properly achieve your goals, you might need a bit of clarifications on this above piece of code.
StaticDisplayLevels are the hierarchical items that will show in the navigation control. You would imagine that this solution will work like a charm and display the navigation representing the term store hierarchy. The problem is, if you increase that to 4, for example, you'll get this due to the lack of space in the OOB control. To visualize what I mean I've shown a small part of the term store managed navigation and the way it was represented after I've increased the StaticDisplayLevels.
So the expectation would be to see the same structure as on the left hand-side.
Let's look at the solution for this:
MaximumDynamicDisplayLevels are the hierarchical items that will show outside of the control.
Increasing that number will give you the items, but outside of the Quick Launch menu. Not too bad.
This functionality works OOB in the Global Navigation, so it's a good idea to consider that as well.
So... just a slight tuning here, in order to achieve the same styling as on the regular list items, I've had to apply the below CSS (not needed if you didn't use custom styling for this at all).
/* customize the dynamic ul */
#sideNavBox .ms-core-listMenu-verticalBox .dynamic {
background-color: #DBE8F3; !important
}
/* Any style you want for the left-hand nav control*/
#sideNavBox {
background-color: #DBE8F3;
}
/* Making some room for the icons on the left */
.ms-core-sideNavBox-removeLeftMargin {
margin-left: 5px; !important
}
/* Insert the plus icon for the ul */
#sideNavBox ul {
list-style-image: url('/Style%20Library/Images/Expand_01.gif'); !important
}
/* SP treeview expand icon for the nested ul */
#sideNavBox ul li ul{
list-style-image: url('/_layouts/15/images/tvclosed.png'); !important
}
/* Hide the Edit Links button in the current nav */
.ms-listMenu-editLink {
display: none; !important
}
And now to the levels... we have the requirement to display more than the two level of hierarchy on the screenshot above. Now if you get to this, and you definitely want to use Current Navigation (rather than Global Navigation) for this (regardless of the fact if you use Structured or Managed Nav in the background) then you need to edit your Master Page e.g. use a custom Master Page in SPO.
So, in a bit more details:
1. Create a blank master page
2. Open your seattle master page and copy all the code to the new one.
3. Go to the V4QuickLaunchMenu control.
<SharePoint:AspMenu id="V4QuickLaunchMenu" runat="server" EnableViewState="false" DataSourceId="QuickLaunchSiteMap" UseSimpleRendering="true" Orientation="Vertical" StaticDisplayLevels="3" AdjustForShowStartingNode="true" MaximumDynamicDisplayLevels="0" SkipLinkText="" />
StaticDisplayLevels are the hierarchical items that will show in the navigation control. You would imagine that this solution will work like a charm and display the navigation representing the term store hierarchy. The problem is, if you increase that to 4, for example, you'll get this due to the lack of space in the OOB control. To visualize what I mean I've shown a small part of the term store managed navigation and the way it was represented after I've increased the StaticDisplayLevels.
So the expectation would be to see the same structure as on the left hand-side.
MaximumDynamicDisplayLevels are the hierarchical items that will show outside of the control.
Increasing that number will give you the items, but outside of the Quick Launch menu. Not too bad.
This functionality works OOB in the Global Navigation, so it's a good idea to consider that as well.
So... just a slight tuning here, in order to achieve the same styling as on the regular list items, I've had to apply the below CSS (not needed if you didn't use custom styling for this at all).
/* customize the dynamic ul */
#sideNavBox .ms-core-listMenu-verticalBox .dynamic {
background-color: #DBE8F3; !important
}
That's it folks, hope you enjoy your new collapsible current navigation. If not, you can always switch to the Global Managed Navigation - it will collapse by default, it kind of uses the space on the screen in a more rational way and could be customized to the same extent. It's the user preference -horizontal or vertical menus... you could even go for the trendy mega menus in the Global Navigation (more suitable for public websites in my personal opinion anyway).
Friday, August 21, 2015
SharePoint Online: Can't save a site as template if you have Nintex apps installed
Today I tried to save a SharePoint Online site as a template when it had the Nintex Forms and Nintex Workflow apps added. That's obviously not supported (I hope yet). In that specific case, I'll have to use on-premise infrastructure...
Monday, August 17, 2015
Automating group assignment for external users in SharePoint Online
I have this requirement as part of a site that will be built specifically for external users in SharePoint Online.
It's basically an automation need for users to be granted access to certain areas of the site by adding them to appropriate SharePoint Online groups. Users should also be automatically created by a workflow when their details are picked up from a list.
I've done an attempt to do this in SharePoint Designer, but it turns out there's no such action in there.
A couple of alternatives exist - Nintex Workflow which I have in place and Plumsail Workflow Action Pack which I don't have in place but a quick overview can be found on their website.
With Nintex, there is the handy action "Office 365 add user to group", which is usually working well. For external users, however, I found it impossible to resolve them like any Office 365 user.
I understand this might be due to the licensing part of the whole picture (external users can't be assigned a SharePoint Online license), but I am hoping that Nintex would address this issue in any of the future releases.
The only alternative I found so far is to create this user in the Office 365 tenant, assign a license to it (all automated through the workflow) and then add it to the group. Unfortunately, my requirements involve no licensing for such users, so I'll have to find another possible automation approach.
Has any of you managed to accomplish this? Would be happy to hear oppinions.
It's basically an automation need for users to be granted access to certain areas of the site by adding them to appropriate SharePoint Online groups. Users should also be automatically created by a workflow when their details are picked up from a list.
I've done an attempt to do this in SharePoint Designer, but it turns out there's no such action in there.
A couple of alternatives exist - Nintex Workflow which I have in place and Plumsail Workflow Action Pack which I don't have in place but a quick overview can be found on their website.
With Nintex, there is the handy action "Office 365 add user to group", which is usually working well. For external users, however, I found it impossible to resolve them like any Office 365 user.
I understand this might be due to the licensing part of the whole picture (external users can't be assigned a SharePoint Online license), but I am hoping that Nintex would address this issue in any of the future releases.
The only alternative I found so far is to create this user in the Office 365 tenant, assign a license to it (all automated through the workflow) and then add it to the group. Unfortunately, my requirements involve no licensing for such users, so I'll have to find another possible automation approach.
Has any of you managed to accomplish this? Would be happy to hear oppinions.
Thursday, August 13, 2015
Sync Excel Tables to a SharePoint List
Update: This doesn't work anymore with the release of Excel 2016. All previous versions of Excel still support it.
I have this requirement as part of a large process automation project for an international company operating in construction. OK, well the requirements never come in that clear - "sync" is not used. The goal is to have a SharePoint 2013 list, populated with data from an Excel 2007 spreadsheet.
Then part of the data would be picked up by a workflow and some of the data will be edited with human interaction during the process. At the end, the data should be in a compatible format to use with a reporting system such as SSRS or PowerPivot for SharePoint Server. That's another topic.
There are several approaches that we can take to achieve the first and most important goal - get the spreadsheet data into a SharePoint list. While the data can be easily exported from Excel (2013) to a SharePoint list quite quickly, there's no way to update it through Excel afterwards. It's a one-off solution which won't add value to this project at all.
I'll just show how that simple option works in Excel 2013 (it works in previous versions, too).
Before starting anything, please have in mind that you can only get the data into a SharePoint list if it's formatted as a table, that won't work for just about any spreadsheet. But we'll start with "any spreadsheet" :) In our scenario, we have a group of employees that might be new hires and we need to get them in a list, so we can possibly start a workflow that will do all the things associated with the new hires - e.g. assign them a telephone number, create an account in Active Directory, etc. The workflow is another topic which we won't discuss, but the main point is that's a common scenario.
Organizations usually transition from paper-based process through Excel spreadsheets to an automated software solution at the end.
Today we are lucky to have 7 new hires across different departments. We got this nice little spreadsheet from HR and we want to create a pilot list called "Employees" in our brand new SharePoint Online environment. It will work in the same way if the environment was SharePoint Server 2013 on-premise anyway.
I have this requirement as part of a large process automation project for an international company operating in construction. OK, well the requirements never come in that clear - "sync" is not used. The goal is to have a SharePoint 2013 list, populated with data from an Excel 2007 spreadsheet.
Then part of the data would be picked up by a workflow and some of the data will be edited with human interaction during the process. At the end, the data should be in a compatible format to use with a reporting system such as SSRS or PowerPivot for SharePoint Server. That's another topic.
There are several approaches that we can take to achieve the first and most important goal - get the spreadsheet data into a SharePoint list. While the data can be easily exported from Excel (2013) to a SharePoint list quite quickly, there's no way to update it through Excel afterwards. It's a one-off solution which won't add value to this project at all.
I'll just show how that simple option works in Excel 2013 (it works in previous versions, too).
Before starting anything, please have in mind that you can only get the data into a SharePoint list if it's formatted as a table, that won't work for just about any spreadsheet. But we'll start with "any spreadsheet" :) In our scenario, we have a group of employees that might be new hires and we need to get them in a list, so we can possibly start a workflow that will do all the things associated with the new hires - e.g. assign them a telephone number, create an account in Active Directory, etc. The workflow is another topic which we won't discuss, but the main point is that's a common scenario.
Organizations usually transition from paper-based process through Excel spreadsheets to an automated software solution at the end.
Today we are lucky to have 7 new hires across different departments. We got this nice little spreadsheet from HR and we want to create a pilot list called "Employees" in our brand new SharePoint Online environment. It will work in the same way if the environment was SharePoint Server 2013 on-premise anyway.
First we'd format this as a table, in order to be able to even get to the "Export" button:
We now have the Export button, under the "Table Tools" section on the ribbon and we can choose the "Export Section to SharePoint List" option.
You get prompted for the URL of the SharePoint site and the name of the list that would be created for the table data:
A summary of the fields that will get imported.
Nice to know is that only one the following types will be assigned to the columns in SharePoint:
- Text (single line)
- Text (multiple lines)
- Currency
- Date/time
- Number
- Hyperlink
If a column has cells with different data types, Excel applies a data type that can be used for all of the cells in the column. For example, if a column contains numbers and text, the data type in the SharePoint list will be text.
Here are the results when that sync finishes. A nice little message in Excel:
A newly created list in our target SharePoint site:
Our employees are ready to jump into the complex workflow process to follow :) More on that will follow in one of the next posts likely if/when we get this project rolling out live. Now it's only a PoC.
More on this basic one-way sync can be found on this Excel 2007 Support article
Now let's talk about the second solution... which will enable us to store the Excel data... and sync it from Excel anytime at a later stage. Out of the box that's not available in Excel or SharePoint.
But there's a good add-in for Excel 2007 (great as our customer is on Excel 2007, and I've found it working well on Excel 2013, too). The add-in was available for Excel 2007 and later on deprecated.
You can still find it here and use it (for free of course) if it matches your organization's needs.
When you download and extract this, you simply get a macro-enabled Excel 2007 workbook called SynchronizeWSSandExcel.xlam. Start that one and enable macros. Then get your table in. Don't forget to format it as a table, as in the previous approach, the same requirement is valid here.
Then go to the Design tab under Table Tools and you'll find a brand new button - "Publish and allow Sync":
When you click it, the dialog box looks similar to the previous one, but it's a 1-step process this time. Doesn't get any sweeter than this (I'll just call my list New Hires as the Employees is already taken):
The outcome is the same as solution 1:
But we now have the ability to change the data... let's say Jack D needs to move from the IT department to Developers, we can change that and then synchronize the changes to the SharePoint list from Excel without the need to even open SharePoint. You just change the cell data, use the "Synchronize with SharePoint" option and your list will be updated.
In one of the next blog posts I'll talk about a couple of other approaches.. which are in fact the a lot more sophisticated and, but involve a significant cost that involves the need of SharePoint Server Enterprise Edition and a 3rd party workflow product or some custom coding to call the Excel Web Services.
Tuesday, August 4, 2015
Issues when requesting the Search Results page with no query in SharePoint 2010
After some vacation time in July I'm back to share some of the experiences with SharePoint, this time from the backlog I have for posting here. Although SharePoint 2010 mainstream support will expire soon (Oct 13, 2015) there are still a number of customers using it.
For one of our clients, we've had to design a custom big shiny button that leads to their Search Center (read: Results page) but without the need for users to type in any query before hitting the button. Something similar to this, in the middle of a Publishing page where we've removed the default Search Box webpart on the client's request.
That's a quick and easy thing, if we don't count the numerous design iterations that we've done of course. Once implemented in the test environment though, we have faced some issues.
Whenever you try to search for a keyword, nothing happens. Your Search Query box is reset to empty. So you try again... and again. This was a very intermittent behavior, after some time... it will eventually work. It was also working always for admin accounts. The ULS logs would show:
System.Runtime.InteropServices.COMException: The security validation for this page is invalid. Click Back in your Web browser, refresh the page, and try your operation again. at Microsoft.SharePoint.Library.SPRequestInternalClass.ValidateFormDigest(String bstrUrl, String bstrListName) at Microsoft.SharePoint.Library.SPRequest.ValidateFormDigest(String bstrUrl, String bstrListName) 7ee02ec8-2297-4816-b3c8-b31f0150d81d
Initially one would think to disable the Web Page Security Validation on the web application... which is totally not a good idea and brings more issues when trying to save site as templates, etc.
After a lot of digging, I've found no way of fixing this, so we've changed the behaviour of the Search button to not go to the results page directly with no query, but instead require the user to input a query before that.
Now that works fine and everyone's happy. Not sure if this is by design, but out of the box Search Webparts would require you to type your query before you can go to the results, so it might be.
Monday, June 29, 2015
Web Part Error when trying to view the Nintex Workflow Administration reports
Today I had to do a brand new installation of Nintex Workflow 2013 over SharePoint 2013 SP1.
After following the guide from Nintex, all worked well, except viewing the Administration reports.
This page is part of the Workflow Report Center and contains 10 different reports which are quite useful and they pertain to all sites within the entire SharePoint environment.
So the issue is that whenever you go to the highlighted link in the Nintex Workflow Management section in Central Admin:
Solution:
1. Open IIS Manager on the SharePoint server.
2. Navigate to the SharePoint Central Administration v4 Site and go to Explore.
3. Open the Web.config file after backing it up and add the two lines below before closing the tag </SafeControls>
4. Save web.config and refresh the Administration reports page.
<SafeControl Assembly="Nintex.Workflow.ServerControls, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12" Namespace="Nintex.Workflow.ServerControls" TypeName="*" Safe="True" />
<SafeControl Assembly="Nintex.Workflow.ServerControls, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12" Namespace="Nintex.Workflow.ServerControls.WebParts" TypeName="*" Safe="True" />
You should be able to deep dive into the Nintex Reporting functionalities now.
After following the guide from Nintex, all worked well, except viewing the Administration reports.
This page is part of the Workflow Report Center and contains 10 different reports which are quite useful and they pertain to all sites within the entire SharePoint environment.
So the issue is that whenever you go to the highlighted link in the Nintex Workflow Management section in Central Admin:
You'd end up getting this:
Solution:
1. Open IIS Manager on the SharePoint server.
2. Navigate to the SharePoint Central Administration v4 Site and go to Explore.
3. Open the Web.config file after backing it up and add the two lines below before closing the tag </SafeControls>
4. Save web.config and refresh the Administration reports page.
<SafeControl Assembly="Nintex.Workflow.ServerControls, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12" Namespace="Nintex.Workflow.ServerControls" TypeName="*" Safe="True" />
<SafeControl Assembly="Nintex.Workflow.ServerControls, Version=1.0.0.0, Culture=neutral, PublicKeyToken=913f6bae0ca5ae12" Namespace="Nintex.Workflow.ServerControls.WebParts" TypeName="*" Safe="True" />
You should be able to deep dive into the Nintex Reporting functionalities now.
Friday, June 19, 2015
The Query String (URL) Filter Connections
That is a quite useful webpart... as MVP Laura Rogers blogged in a series of posts a while ago.
I'll just mention what happens when you try to move it over, as it simply doesn't work as you'd expect it to work.
Today I've tried to bring accross a Team Site from SharePoint 2013 On-premise to SharePoint Online by using ShareGate Migration. *Almost* everything went well and I decided to do some sanity checks on basic functionalities of the site.
The one thing that's heavily used is a Client List solution that is implemented in the following way:
Page A: A web part page, containing a number of List View webparts, each of them containing different pieces of information related to specific clients, let's say client documents and key contacts.
Client Documents and Key Contacts, for example are separate lists, each of which has an identical column, called "Client" of type "Choice" Each item in those lists is associated with one client only.
Page B: A page with only one list view webpart, visualizing a list called "Clients", with a search box and some groupings which are collapsed so that the whole list of clients is not displayed by default.
The "Clients" lits has a column called "Link", of type guess what (Yes, Hyperlink or Picture of course) which supposed to take you to information for *only* that client on Page A by filtering all webparts on Page A, based on their "Client" column's value - it should be matching the value inserted in "Link". That is achieved through the Query String (URL) Filter webpart.
The format of the link is: http://intranet/sites/teamA/clientlist.aspx?Client=NameofTheClient.
When this is clicked (that's what people do when they find the needed result), they're taken to Page A (clientlist.aspx) and view all info related to the client, with all other clients' data filtered out. Great, isn't it?
So, that was working well on the SharePoint 2013 on-prem, but not in Office 365 as I've tested it now post-migration.
The Query String (URL) Filter webpart is inserted on Page A. It's only visible in Edit Mode and it was visible on the page when I went to edit it. But the functionality that we used to have on premise was not working, when clicking on the "Link" column on Page B, I was taken to Page A, but totally unfiltered and seeing a bunch of data for a number of clients - not what I needed in order to find something quick. And the reason struck me immediately:
Now we've got to add the connections. In our case those are 10+ lists but for the example, let's say we'd just need to apply the value to the Client Documents list:
I'll just mention what happens when you try to move it over, as it simply doesn't work as you'd expect it to work.
Today I've tried to bring accross a Team Site from SharePoint 2013 On-premise to SharePoint Online by using ShareGate Migration. *Almost* everything went well and I decided to do some sanity checks on basic functionalities of the site.
The one thing that's heavily used is a Client List solution that is implemented in the following way:
Page A: A web part page, containing a number of List View webparts, each of them containing different pieces of information related to specific clients, let's say client documents and key contacts.
Client Documents and Key Contacts, for example are separate lists, each of which has an identical column, called "Client" of type "Choice" Each item in those lists is associated with one client only.
Page B: A page with only one list view webpart, visualizing a list called "Clients", with a search box and some groupings which are collapsed so that the whole list of clients is not displayed by default.
The "Clients" lits has a column called "Link", of type guess what (Yes, Hyperlink or Picture of course) which supposed to take you to information for *only* that client on Page A by filtering all webparts on Page A, based on their "Client" column's value - it should be matching the value inserted in "Link". That is achieved through the Query String (URL) Filter webpart.
The format of the link is: http://intranet/sites/teamA/clientlist.aspx?Client=NameofTheClient.
When this is clicked (that's what people do when they find the needed result), they're taken to Page A (clientlist.aspx) and view all info related to the client, with all other clients' data filtered out. Great, isn't it?
So, that was working well on the SharePoint 2013 on-prem, but not in Office 365 as I've tested it now post-migration.
The Query String (URL) Filter webpart is inserted on Page A. It's only visible in Edit Mode and it was visible on the page when I went to edit it. But the functionality that we used to have on premise was not working, when clicking on the "Link" column on Page B, I was taken to Page A, but totally unfiltered and seeing a bunch of data for a number of clients - not what I needed in order to find something quick. And the reason struck me immediately:
It seems the webpart connections are not brought over! I've tried exporting the webpart and importing it manually - still the same thing. The only thing that came over is the "source" of the filter parameter. Let's imagine that one was gone too, so we'd start nice and clean by readding our webpart:
Go to "Add Web Part" and locate the Query String (URL) Filter under the Filters category:
That's how the vanilla version looks like:
Open the tool pane to configure it and choose a meaningful name of your choice for the filter and the parameter that you'll be passing in the query (in our case Client):
On the next screen, choose "Get Filter Values From" and press the "Configure" button:
Choose the target list column that you'd like to use for filtering:
Done. The webpart will now show you the connection(s) rather than the annoying "Unconnected" warning:
Hope that was useful for you if you're tasked with migrating some solutions based on these webparts in your daily work. The interesting outstanding question is where are these connections stored when they're not coming over with the webpart itself? I'll be glad if someone posts a comment with an answer on that mistery.
Subscribe to:
Posts (Atom)