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.
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.
Subscribe to:
Posts (Atom)