FormsAndSharePoint

Forms and SharePoint: Excellent question! 

The new kid in town

There is a brand new feature in Excel which allows you make good looking surveys superfast. And this new addition to your favorite workbook tool is placed front and center in your OneDrive (in my case the OneDrive for Business) and it will appear in your SharePoint document library as soon as you switch to the new look and feel (Read more about the new experience in document libraries here). When selecting new the Excel Survey pops up in the drop down and as soon as you create one, you quickly enter a name for your document and then you are building your survey straight away.

How does it work?

When you create an Excel survey you basically create a workbook and a nice interface for data entry. This was always possible for the Excel experts among us, but it is readily available out of the box to all and way faster than building it yourself in an ordinary workbook. The survey builder lets you pick a title and a description (or delete the placeholder if your survey doesn’t need them) and then you start defining the questions.

Forms and SharePoint: Excel Survey Blog
Forms and SharePoint: Excel Survey Blog

Every question has an additional settings menu where you can define the question, the subtitle, the response type, if it is required or not and a default answer. And off course you will find the add and delete buttons in this panel as you are used from Microsoft. There is no validation (a part from making questions required) and no special formatting or anything, just plain and simple, and above all superfast, survey building. And when I say fast I mean fast. The survey that I show below contains a title, description, five questions and was built in under ten minutes. And that was timed including the time to take screenshots of every step.

Once you are done with building your survey you simply hit the “Save and View” button to get a glance of how the survey will look to the people who you will be asking to take it. And if you like what you see you simply hit the “Share Survey” button and a link is generated that you can send to your audience.

What has happened in the background is that for each question that you created a column was made in your workbook. This is where the response will be stored of the people taking your survey. And the columns have properties that match the kind of questions they are linked to. So a text column is made to store the answers for a test question, a number column is made for a number question and a choice column is made for a choice column. Again, I not saying that this was not possible before in Excel, but this just makes it so much easier.

In light of being totally honest, I will add in the note that Microsoft also includes on their support page, which is that “Columns in the spreadsheet are built as you add questions to the survey form. Changes you make to the survey form are updated in the spreadsheet, unless you delete a question or change the order of questions on the form. You'll have to update the spreadsheet manually in those cases: delete the columns that go with the questions you deleted, or cut and paste columns to change their order.” (from: Office Support)

Why should I use Excel surveys?

For me this new feature really shows that Microsoft has a sense of what their customers are doing with their product. Because as stated earlier, building surveys in Excel has been done before. And also for other survey tools that Microsoft brought to life, for example the SharePoint Survey App, the data is stored in a list and usually analyzed in Excel. So building a survey feature into the product of Excel makes sense.

And there are some additional benefits next to the fact that your survey building time will become far shorter with this new feature. First, as explained, you only share a link with your audience. This means that you do not have to give the people who fill out the survey, access to the data in the workbook. This separation of data entry and data storage fits the security driven world we act in today. Second, since you share a link to a webpage with your audience, the survey is easily accessible from any device (desktop, laptop, tablet, mobile, etc.) as long as you have a browser and an internet connection. A colleague of mine opened the email I sent out on his phone and could fill in the survey straight away.

If have to put the excel survey in line with the other survey tools that Microsoft offers, I would put it as an equal weight and possible replacement of the SharePoint survey app. For the quick and dirty poll, you can use a third party poll app or even the Outlook voting buttons and for the structured business processes you can use InfoPath forms or a third party form builder like Nintex. But Excel surveys fits nicely in between to fit scenarios where you do have multiple questions to ask but it is an ad hoc or onetime thing that doesn’t need or justify developing a custom form.

This blog post is part of the series Forms and SharePoint. More on this Topic can be found HERE

Forms and SharePoint: InfoPath is here to stay

light-man-hand-pen.jpeg

Forms? Why should I care?

For most people the word “forms” brings to mind bad memories about bureaucratic procedures with lengthy enrollment sheets that require you to enter data that you know for certain you have entered before. I know of these procedures, been forced to go through a whole bunch of them and I too can think of at least one form that made me give up to even start the process it was for. But instead of yelling out “destroy, destroy!” whenever I come across a form I am still a huge fan.

The reason for my everlasting love is simple: garbage in is garbage out to any process and forms provide me with the weapon to ensure data quality on the point of entrance. This focus on data quality was taught to me at a very early stage in my career in light of the single source of truth principle along with the master data management mantra: “Create once, validate once, use many”. A line that is among my favorite quotes from the day that I first heard it.

But hasn’t InfoPath left the building already?

Well actually, no. There has been a lot of talk all over the place, including official statements from Microsoft itself, that InfoPath is on the way out. And while these statements where very strong and decisive in 2014 when Microsoft first came forward with the topic, “…there will not be a new version of InfoPath and support will continue through April 2013…[1], the 2016 release officially included InfoPath which means that by the rules of the Lifecycle Support Model support is continued until 2021 (2026 with extended support)[2]. And Microsoft themselves have updated their earlier strongly formulated statement about InfoPath with a friendlier “…InfoPath Forms Services will be included in the next on-premises release of SharePoint Server 2016, as well as being fully supported in Office 365 until further notice. Customers will be able to confidently migrate to SharePoint Server 2016 knowing that their InfoPath forms will continue to work in their on-premises environments, as well as in Office 365.[3]

And while Microsoft keeps saying that InfoPath 2013 will be the last version of InfoPath that will be released, I see no immediate reason to move away from a tool that works and will remain to work in your latest on-premises and online environments.

So what is it good for?

To possibly better understand my enthusiasm when it comes to forms, it is probably valuable to share that I am a process minded guy with a strong background in Operational Excellence. And thus I know all too well that a good process does not allow for defects to be created along the way. Because a defect either results in rework or scrap and both are a waste of time.

I am such a big fan of forms, because they allow me to eliminate defects at the very beginning. And InfoPath gives me a big box of tricks to enforce data quality at the entry point. To list my favorite ones:

Data validation

We have all seen form entries which contradict themselves. Sometimes it is an honest mistake and sometimes it is unthinkable stupidity, but it is always annoying. With data validation you can add rules that check if people are making mistakes while entering the form, for example by forcing that the return date of a reservation is later in time than the indicated starting date. And with the ability to include multiple fields in one rule, even the most completed scenarios (if A is higher than B and C is less than D than E cannot be higher that B plus C and not lower than D minus A) can be validated. And let’s face it, we can all name a process that has these kinds of if’s, then’s and but’s.

Formatting

Who hasn’t come across the quote “If you have answered “No” to this question, please continue to question number...”? And off course, it is always nice to be able to skip a question, but wouldn’t it be even better if the questions you do not need to answer are not even asked in the first place? With formatting this is possible, because based on given values you can determine to show a field or even a whole section of fields. So only those questions that need to be asked based on the previous answers will be shown. This also gives the user the feeling that the form is designed especially for him or her, which always wins you sympathy points.

Querying for data

One other great way to enhance data quality and reduce effort for your users is being able to query for data. The scenario comes to mind of an ordering form, which always need to contain information about a product and a customer. You know this information is available somewhere in the organization already and probably stored in a very structured manner. So why not tap into this source. By providing the product code in the form you can fire of a query that gets all the additional information about that product that you need. This has two big advantages: 1) as a user, I only need to type in the product code and 2) as an administrator I only have to keep the source list up to date to ensure data quality for every entered product code.

Cascaded dropdowns

As a user, I always like when a form thinks along with me. Take for example the scenario where I have to provide a shipping location by filling out country, city and building. As soon as I have provided the country I would appreciate it when I can then only pick cities that are within that country. And that as soon as I have picked the city, I only get buildings within that city. With cascading dropdowns this is possible and again it is a way to at the same time enhance data quality and provide user comfort.

Calculated fields

Calculated fields can be of use in two different ways. The first one is obvious because it is right there in the name: to perform a calculation. This can be anything from a simple total order amount calculating based on the quantity and the price to a complex calculation of break-even revenue based on the price, sales per hour, salary per hour, rent per day and overhead cost percentage. Incorporating any formula into your form again enhances data quality and provides user comfort.

The second way that calculated fields can help you in InfoPath is to provide feedback to your user. Take the previous scenario of retrieving data about a product based on the product code. It is not a bad idea to show the user all the information that was gathered based on the entered product code so he or she can check if that is indeed the product information that is needed. However, you also do not want to allow the user to change this info. If there is indeed a fault in the data, then the source needs to be adjusted to solve it for everyone. The nice thing about a calculated field, as you also know from SharePoint lists, is that they cannot be edited. So if we provide the retrieved info in calculated fields, the user gets their feedback and the admin maintain control over the data quality. Again a win-win scenario.

Infopath

So what’s the downside?

Off course InfoPath is not perfect and there will be limits to what you can do with it, but from my experience the vast majority of business processes can the greatly streamlined by pouring the data entry into smart forms build in InfoPath. And if you come across that process or trick that just isn’t doable to dummy proof it with a good form, then please ask yourself this question before going out and buying something else: “does my process really need to be this complex?”. But that’s the Operational Excellence guy in me talking.

One true disadvantage of InfoPath is that it is not included in the standard SharePoint Server offering. You have to go Enterprise[4] to get it and that is a pretty big difference in cost. And while everything has its price tag, you will to sit down and do your homework before acquiring InfoPath from an investment point of view. It will take a cost benefit analysis over multiple areas to weigh of efficiency gains to license costs before you know is the investment of upgrading from a Standard Server license to an Enterprise Server license will pay off.

However, the Office 365 environment offers multiple possibilities to go about your licensing in a whole different way that could significantly reduce the total investment needed. Plus, you get the flexibility of scaling up and down in the cloud. Our licensing experts can certainly help you figure out what would be the best plan for your organization with respect to InfoPath licensing.

Another often heard critique about forms is that they are ugly and you cannot do a lot in terms of design improvement. While InfoPath certainly is built with function over beauty in mind, there are still many possibilities to enhance the look of your forms. I would put it more like this: if you can manage to create nice excel sheets and word document, you will have the tools to present a good looking form.

To make a long story short

InfoPath is definitely not out the door yet. As Microsoft promises its users, it will be included in the latest online and on premise offerings for SharePoint and supported throughout 2026. So if you have InfoPath at your fingertips right now, use it! Build those forms and make your processes more robust and fool proof. The investment will pay itself back in the coming decade and the experience you gain from digging into the details of your processes and determining what piece of information is needed when and what is the best source to retrieve it from will be valuable forever. Because you will need to go through the same steps when building your forms in any other tool.

 

This blog post is part of the series Forms and SharePoint. More on this Topic can be found at http://08b.4d7.myftpupload.com/tag/FormsAndSharePoint/