Categories
Uncategorized

Scenario: Fill SharePoint Metadata with Excel File Values on Upload with Power Automate

Scenario: Users want to upload an Excel file to a SharePoint Library, but want to fill in the metadata required for the document in the library with values inside the Excel file itself.

For example, when a business group wants users to fill out an Excel-based form and want them to upload the file to a SharePoint document library, and have the cell values the user filled in inputted as SharePoint library metadata for the file. This is not an uncommon story for users who have relied on older, legacy forms that were never converted and have been in use in field work or did not have an opportunity to convert the experience to InfoPath, Forms, or another online form technology.

Design Logic:

Because the document is being created, SharePoint needs time to “register” the file in the content database. This means if you run a flow on a freshly-uploaded document and the Flow runs too quickly, it might run into a “checked out” or “shared document is in use” error.

To get around this, you will have to use a couple of variables – one to tell the Flow if the file update was successful, and one to increment a “delay” period if SharePoint isn’t done “registering” the file yet.

How to build it:


The whole Flow looks like this:


Other Notes, Thoughts and Considerations

  • You can probably get away without using the incremental value (and reduce a couple of actions), but the incremental minutes will allow less attempts to update the item and work with content databases that may be operating slower. Try it yourself and see what works – as long as it works for you!
  • You don’t need to have the “Parse JSON” step to refer to the values from the Excel table later in the Flow. You can write them in via expressions. Use the “Parse JSON” action if you want to be able to pick the values from the Dynamic data GUI.
  • Consider only using this scenario in a “One item, one table” Excel file. For example, when the file is used as a “Form”. Otherwise you’ll have to use the action filters and possible incrementing ID’s in the “Update Item” step to align each table row with different documents.

Categories
Office 365 Uncategorized

Form Accompli: The rest of the Best for Using Forms in SharePoint (Part 3)

If you didn’t hear enough about forms in SharePoint from my first two blogs, then I promise this is just a trilogy and you’re on Return of the Jedi. We’re close to the finish line.

In my final part of this series on how to use Forms with SharePoint, I focus on the “rest of the best”; the remaining Form solutions that your organization can use in order to meet (or even exceed) your form requirements. And whether you are looking to enhance your current forms or roll out an entire set for a shiny new SharePoint environment, there is something for everyone that doesn’t include Forms or PowerApps.

In case you don’t feel like reading the rest of this, here is a handy TL;DR (too long, didn’t read) table. If you ask me to pick only one forms solution for SharePoint, I’m vouching for PowerApps. It’s the best option going forward that takes the best advantage of the Office 365 stack and subscription.

Forms_Table3
Now, the rest of the Best . . .
Microsoft InfoPath 2013

Ah, the incredulously resilient forms product that seems to have the half-life of Bismuth-209. This thing just won’t go down, as much as your Info Services team wants to see it vanish.

The problem with InfoPath is that it is still useful and still an effective way to edit forms in SharePoint. And it can’t go away quite yet; there is still some blood to be squeezed from this stone, and a surprisingly good amount.

InfoPath has been the gold standard of form editing capabilities in SharePoint for years. I say this because if you already have SharePoint environments (on-premise or in the cloud) and forms have been edited by at least someone for, say complex workflows, chances are InfoPath was the tool used for the job.

InfoPath provides the following benefits for integration with SharePoint:
InfoPath_Forms
  • No code – a major difference maker for users who don’t need complicated IT projects to get better forms (although there is a bit of a learning curve);
  • Adding expanded user inputs to customized workflows in SharePoint (e.g. organization-specific metadata for content approvals, or user-initiated content management);
  • Creating enhanced create/edit forms for SharePoint lists and libraries, improving the user experience and ensuring the right metadata is captured;
  • Custom branding to further extend your intranet, extranet, team site, or website look and feel;
  • There are many tutorials and guides online with good knowledge bases to use and learn from. I suggest taking a look at the Microsoft Infopath Tutorial and Online Training Course from LearnThat, downloading the Advanced InfoPath Form documentation from Microsoft TechCenter, or even taking the InfoPath 2013 Essentials Training by Gina von Courter (if you have access to LinkedIn Learning).

It’s a Microsoft product, and it’s free. And more importantly, it provides a level of form editing capabilities that is effective – which at the core, is what most people want (it “gets the job done”).

Microsoft is not dropping support of InfoPath until 2026, so that gives you quite a bit of time before you have to take the plunge with another solution. That means the forms you built for use in SharePoint 2010 and 2013 can still be put to good use. Heck, Microsoft folks have even confirmed it will be compatible with the upcoming release of SharePoint 2019(huzzah!)

InfoPath_Form2
While these are good reasons why there is no immediate rush, others may advise to not use InfoPath any further, simply because it’s becoming “abandonware” and you should ween yourself off of it. But I will play devil’s advocate and tell you to continue using InfoPath under the following conditions:
  • Your organization or team is currently deriving value out of forms already built in InfoPath and used in production and are not in an immediate need to change over (especially if your tech support teams are able to service the forms). There is already a level of familiarity and expertise inside the organization with it.
  • You do not have any further forms to build that are complicated in nature, and need to be woven into extensive SharePoint workflows and content publishing.
  • Microsoft Forms or PowerApps are not capable of delivering the forms you currently have, or you do not have resources able to make the conversion (e.g. budget, time, skillsets to do it, etc.)
  • You understand that InfoPath is effectively abandonware, and Microsoft will not be giving InfoPath any further updates or extensive support for your existing forms. You are on your own path until InfoPath is fully gone eight years from now.

InfoPath is a legitimate option and should not be discarded so easily. However, if you are keen to make the switch to Microsoft PowerApp and Flow, there is a definitive successor that awaits your future form experience, and it’s a leap you will eventually have to take – even if that leap doesn’t have to technically be made for almost a decade.

Nintex Forms

Nintex Forms for SharePoint offers a no-code, drag-and-drop forms experience for users to build and deploy forms to SharePoint lists and sites. It works with on-premise SharePoint environments and Office 365, and is an industry pillar in the SharePoint third-party market. Some of the notable advantages of the Nintex experience include:

  • High customization on forms with ability to control function, branding, and content source integration (e.g. SharePoint, SQL databases, etc.);
  • Easy to ramp-up on usage with an intuitive GUI; relatively low learning curve compared to InfoPath;
  • Anonymous or Authenticated form submissions;
  • Mobile and Tablet-friendly for phones and tablets (both using forms and creating/editing them);
  • Embed forms with Nintex workflows (this is really the strength of the product).

You don’t have to go far to find excellent review of their product set. They have a strong, reputable product that I have seen in multiple client locations (from just testing purposes all the way to full-blown deployment). Additionally, Nintex is partnered with a number of other SharePoint products and services, including Portal-in-a-Box solutions like Bonzai Intranetand migration specialists like ShareGate. If you’ve invested in one of these, that gives it a leg up (for a full list, you can see all partners listed on Nintex’s website). They have also been around since 2006; their products were the first-to-arrive combination of Forms and Workflows that weren’t out-of-the-box, and before the PowerApps/Flow combination appeared on the scene in very recent history. Bascially Nintex is the grand-daddy of third-party integration into SharePoint to do better than what SharePoint offers on its own (in this consultant’s own opinion).

Nintex_Forms1
There are other major third-party players in the SharePoint forms game, including K2 and Formotus. However, full product reviews can be found elsewhere on the net; Nintex is in large enough circulation (at least in the Canadian and North American market) to warrant itself as it’s own option.

One major prohibiting factor for Nintex is that it is a pricey option, especially for small to medium businesses that will not be using copious amounts of forms or workflows (the latest check has Nintex subscription pricing starting at $625/month for five workflows and 25 forms). This also doesn’t include potential up-sell from consulting services and general long-term expanded usage of the product. Note that Nintex Forms itself does not have its own pricing scheme; there is likely little way around getting the workflow components as part of your package (even if you don’t need them).

Additionally, I have yet to see Nintex deployed in small to medium businesses; these organizations (often with limited budgets) will likely find better value in InfoPath, Forms, or PowerApps/Flow, especially as they are already available in standard business usage of Office 365.

At this stage of the game, I would probably be recommending customers to give the PowerApps/Microsoft Flow combination a try before going to Nintex. The reason for this is that, all things being equal, you’re better off sticking with the Office 365 stack and being able to move content around and conduct information management activities with the same vendor that built the product. Additionally, PowerApps and Flow are very robust and accomplish alot of the same technical objectives that Nintex Forms does – and without having to coordinate two different vendors, and paying more than you already do Office 365 business or enterprise licenses.

The bottom line for Nintex is this: it is a robust, capable and well supported form solution, and it’s powerful. It’s a very good option. But it is an investment. And this isn’t a decision you can just jump in to; you’ll have to consider your current form usage and your larger use of workflows to get the full value of it – and you should consider whether you have custom forms already, and what a transition strategy will look like.

Anything else?

There are few other ways you could edit forms in SharePoint. You can try the many different Form apps offered in the SharePoint Store – KwizCom, PlumSail, Sidekick365 offer forms apps that you can install (but may have to deploy additional code on SharePoint servers). Even the trial version of Nintex Forms are found here from an installation starting point).

Even though these are freely available, I would not recommend any of these, unless your organization can comb through the product, the product code, and conduct relentless sandboxing and testing activities around form usage before actually deploying into a production environment. This includes reviewing the efficacy of the product, the granular controls and functionality, the graphical user interface, business-appropriate scenario testing, and even the troubleshooting components that can make or break a user experience (e.g. form and workflow debugging). questionnaires

others1
Your data is too important and sensitive to compromise with a free SharePoint Store app that might not be built with the level of security and stability you need. Forms (and the content and workflows around them) should never be implemented in a half-baked manner. Put the time in to explore any of these options – KwizCom, PlumSail, Sidekick365, and anything else.
Of course the final option is using Visual Studio. This is the full custom, full development route that should only be used as a last resort. Office 365 is designed to handle most of the business activities that regular, non-developer users will ever encounter (and allow you now to create your own solutions even for what it doesn’t). visual_studio_purple (1)
Going down the custom form development route only adds a level of complexity to your SharePoint environment that will cause extra leg work when trying to do any sort of platform upgrades, migrations, or tool integration. This route should only be used for the most complex of requirements, including integration with other business systems (e.g. CRM, ERP) and/or where user inputs are required on stand-alone SQL environments that the Office 365 can’t immediately handle.
With that said, I will close my soliloquy on forms in SharePoint. I am sure I’ll have to re-visit this series of blog posts in two to three years, when all of this will change when something in the industry bulldozes everything again. In this industry, you can never bet against it.
Check out the Ion Works blog for other advice around Office 365 and contemporary industry musings. We are a collective bunch of industry guys who like to Office 365 (it’s now a verb), and shed some light on ECM and content/collaboration stuff. We are also moving domains – stay tuned on my LinkedIn and Ion Works Twitter!
Categories
Uncategorized

Form Accompli: Editing Forms in SharePoint with PowerApps (Part 2)

In my last blog, I detailed how Microsoft Forms provides a functional and simple method for conducting polls, surveys, and questionnaires, and how it can work inside SharePoint. It is an effective tool for helping day-to-day users collect information from other users, all while using the SharePoint interface (among other goodies it can provide).

sharepoint_list

That said, Forms isn’t for everyone – and it really depends on what you’re trying to accomplish with your content. The second option that is available for Office 365 users is using PowerApps, Microsoft’s budding cloud service that lets users build business-level applications specific to their work activities without (or with minimal) code. It’s also a great app to expand form functionality integrated with SharePoint.

Here’s the main difference between using Microsoft Forms and PowerApps: Forms lets you use a form that is not bound to a SharePoint list. PowerApps is best used to enhance forms used on these lists or libraries.

Because of this, both Office 365 apps can be used for different purposes and by different users. However, both can be considered options for SharePoint.

Why would I use Forms over PowerApps, or vice versa?

Basically it boils down to this:

  • Use Microsoft Forms if you want to enable your day-to-day end users to set up surveys, polls, questionnaires and feedback. This option is ideal if you want content to be quickly setup in a decentralized fashion, do not have much objections to data collection or branding, and need them used on commonly-used areas, such as intranet home pages or team sites. Forms are also not tied to SharePoint lists or document libraries. You can, however, capture Form results in a SharePoint list with Microsoft Flow.
  • Use PowerApps if you need more robust forms in existing Lists; it works great with the data sources you are already using. PowerApps is also a better tool to use if you have a technical support team that can not only assist in building a robust solution, but is also able to provide support and sustainment services around the form too.

What would I use PowerApps for?

Before actually “choosing” PowerApps, I recommend teams work to identify the requirements of what you are looking to do establish your content and user objectives, as well as your parameters and restrictions. Decisions on using PowerApps should be made on the tool alone; PowerApps is simply a means to an end; not an end in itself.

That said, some examples of how PowerApps may be used with SharePoint:

  • Project Management app that allows PM’s to request a new project and Directors to approve it (with workflows or Microsoft Flow);
  • User Registration for an upcoming company event (with an existing Contact List);
  • Employee payroll deductions using a source Excel document hosted in SharePoint;
  • Service Request / Service Desk application to track assistance requests from employees, with a SharePoint form available from the company portal;
  • A team tracking budgets, estimates or costs with a SharePoint list

Of course, these are but a few examples of solutions that PowerApps can help provide for a team or organization. However, PowerApps is a powerful tool; one could go down the rabbit hole for a number of use case scenarios. Remember to conduct sufficient business analysis activities and then make the decision if the app is right for your objective – you’ll thank yourself later.

So what about the Forms part of it? Give me the details!

Let’s say you simply want to accentuate the Create and Edit forms on a SharePoint list, because you expect your users to be using a SharePoint list frequently.

PowerApps_buttonOn the user interface, there will be a button that says “PowerApps”. If you click on it, it gives you the option to “Create an app” or “Customize forms.” Once you click on “Customize forms,” PowerApps will open, and provide you with a modal dialogue to add or remove fields (you can skip this and get straight to the editing GUI).

From here, the PowerApps world is kind of your oyster. I don’t want to provide tactical steps on how to use the PowerApps in this blog post; that would turn blow this post out completely (you can find that in Microsoft’s helpful online documentation and tutorials). What I can say is that the GUI isn’t too hard to wrap your head around (SharePoint list/app screens on the right, editing canvas/fields in the middle, and properties on the right). Practice makes perfect with this one.

powerapps_canvas
If you’ve gotten this far, you are probably the one tasked with actually editing the form in the SharePoint list, or at least have some level of experience with PowerApps itself. If you are responsible for doing the form editing, I recommend setting up a hidden list somewhere on your SharePoint tenant and practicing (“sandboxing”) with a basic list before actually touching live data on a production-level list.

List_edititem

Once you are done, you can simply click on “Back to SharePoint” or go to File > Save, which will publish the form edits back to your SharePoint list. Test the SharePoint form (add a new item or edit an existing item), and review the changes you have made.

Note that you can always revert back to the original SharePoint forms at any time. In the List Settings, simply navigate to “Form Settings” and click the radio button for the default forms. You can switch back to the PowerApps form at any time as well. Note there is an InfoPath form option too, if you have previously gone down that route.

Why don’t you just create forms from scratch in PowerApps altogether? Well, Microsoft themselves have a pretty logical answer. Basically if you want your users going through SharePoint, use the customized list form option. If you want to take the user experience entirely out of SharePoint (even if SharePoint is intended to be used), create a standalone app.

Some limitations of using PowerApps

PowerApps is becoming a central application in the Office 365 stack because of it’s ability to interact with data across the entire platform, but like any form building options, there are drawbacks.

First, there is a learning curve in PowerApps. This is not a tool you can pick up and use like a hammer; this is more like a power drill with an extensive set of drill bits. The drill is a better option, but you need to know some details first before you can jump into the user experience. Therefore, I suggest your PowerApps application is assembled and maintained by persons who are technically capable, motivated, and/or is part of their existing job duties to work with Microsoft applications at a support level.

Second, editing forms on SharePoint document libraries in PowerApps is not officially supported yet. Regular users would not see the PowerApps functionality available on their team site libraries for their important documents. Actually, PowerApps doesn’t do a good job with SharePoint document libraries in general (there are alternative ways to connect to them, but not exactly intuitively). This could be a showstopper if the intention is to edit forms on existing SharePoint libraries, which most Office 365 and on-premise users will ultimately be using (after all, SharePoint is a document management system).

Third, PowerApps does not replace InfoPath in every function. There are too many differences between both applications to suggest that PowerApps is an exact replica or successor of InfoPath as a tool. That being said, Microsoft has already determined that PowerApps (along with SharePoint and Flow) is the combination of successor technology to replace InfoPath in the long-run. Luckily, InfoPath will still be in support until 2026, so there is immediate rush to migrate away (however, no new features are being added to InfoPath, and Microsoft is putting all of their elbow grease into making Office 365 the tool of choice). And oh yes, there is no silver bullet method to convert existing InfoPath forms into PowerApps files. You’re going to have to rebuild them in PowerApps . 😦

In the coming days I will be posting Part 3 of this blog series on managing Forms in SharePoint. We will do a quick review of the other options at hand, including InfoPath (yes, it’s still relevant) and third-party solutions with their own unique spins.

Categories
Uncategorized

Form Accompli: Adding Forms in SharePoint (Part 1)

If you’ve ever tried to set up a form in SharePoint for a particular purpose or goal, you know it has historically been a frustrating and dicey experience to just get one set up out-of-the-box.

In SharePoint’s 17-year history, there hasn’t been a bulletproof, easy-to-use form solution that non-technical users can get up and running in a smooth fashion. The conventional experience has generally involved scouring the internet for answers, consulting the colleague who has loose knowledge around InfoPath, calling everyone in the tech department, complaining to your manager,  attending lunch-and-learns hosted by vendors who up-sell the crap out of their products, and even trying to jimmy something together yourself when you’re at your wit’s end. You’re basically ready to call Batman just to get a simple, damn, form on your team site. I just want a poll! Or a survey! Or just a friendly form to provide feedback that looks good!

Finally, SharePoint seems ready for everyday users to set up a decent form experience. We are wrapping up the form building days that includes profuse sweating, teeth-grinding and stress-inducing decision-making that at times make it seem not worth the time or effort.

This blog post is the first in a three part series about form options for using in SharePoint – and covering the forms options that can be part of your larger Office 365 user experience.

The Microsoft Forms Web Part

Picture1

Thankfully, users have access to Microsoft Forms, a handy addition to Office 365 that lets users actually build their own forms in its’ own app.

Microsoft Forms was initially released for Office 365 for Education users, but was expanded to business and enterprise subscribers as an app preview. Microsoft will move it from preview to general release with expanding functionality in the coming months.

How to Add it

When you edit a page in SharePoint, you can add a web part simply called “Microsoft Forms”. It lets you add a new form, or add an existing one you may have already created. Just provide the link to the Form from Microsoft Forms (when you are on the form, click “Share” to get the web link).

form_weblink

What kind of forms can I build?

If this is your first form, it’s really easy to get started. Through an intuitive and easy-to-use interface, you can create simple questionnaires using field controls that include options, short answers, long answers, dates, ratings, rankings, and likerts (scaling responses). Creating surveys, polls, and other types of forms are easily done and don’t require involving an army of super users.

form_builder

You can up-shift your Form experience into next gear by using them with Microsoft Flow (workflows for Office 365 content). However, this blog post is specifically about the SharePoint web part experience (in the meantime, one can find examples off Form workflows on the Microsoft Flow site).

You can also change your theme (background) on the form, target your form to to just your team or anyone with a link, set timed responses, and see basic form metrics (again, beyond the scope of this blog post). Forms can also be accessed from the desktop or from mobile, complimenting the multiple device environments that are becoming more prevalent in today’s modern workplaces.

What are some examples of how to use it with SharePoint?

form_mobile

A few examples you could use Forms for with SharePoint include:

  • If you have an Office 365 intranet and you want to add a daily or weekly poll on your front page;
  • Rate the success of an initiative or program happening in the company;
  • Gauge feedback for a team or department event so you can prepare for the one next year (e.g. christmas parties, golf tournaments, etc.)
  • Collect responses from external partners on suggestions for an upcoming project.

What should I be aware of?

Forms is a great entry-level integration feature to get form usage into SharePoint. As the Office 365 platform deepens integration between apps, there is a natural fit to have Microsoft Forms integrate with SharePoint – especially if you are an education, business or enterprise-level user. This is great for team leads and communicators who don’t want to go down the rabbit hole of investing in InfoPath or third-party vendors like Nintex or Survey Monkey – especially if you’re already paying for an Office 365 subscription and your form requirements aren’t complex.

The downside is that if you do want to take the form to the next level, it’s not the most powerful form builder out there. If you want to do crazy things with forms and do more complicated workflows, functions, or calculation, Forms is not your best bet – InfoPath or PowerApps might be better options. However, this is the point – Forms is meant for normal, day-to-day users that don’t want to go to town on; it’s the “lightweight” choice of the bunch. And if you’re not expecting more than 5000 responses, this option can make sense for your organization.

Additionally, users outside of the U.S. or Europe who collect sensitive data should be aware that responses gathered in Microsoft Forms are stored on servers only in the U.S. or Europe. Therefore, if location-based information storage and regulatory frameworks are your priority, then consider your options for using Forms carefully.

Part 2 of this Forms series will be coming next week . . . We’ll look at PowerApps as a form solution (the upcoming big-boy app for no-code application building).

Categories
Uncategorized

A Familiar Face in Microsoft Teams – The SharePoint Team Home Page

Microsoft Teams for Office 365 is a nifty little tool. Actually, it’s not a tool and it’s not so little anymore. It’s a pretty robust platform for collaboration, and anyone who has even had a chance to tinker with or use it productively would probably say there’s a lot this platform can do. And the tool continues to grow at a dizzying pace. Even just recently, Microsoft announced a slew of new updates including an updated app gallery, the ability to retrieve content from an app and discuss it with the Teams chat function, and a better search bar experience. It was not long ago that, among other things, Microsoft announced that Teams will succeed Skype for Business as the main communication hub for Office 365 customers.

Of course, with all the new bells and whistles, it might seem a bit overwhelming, especially for casual users. Change can be a tough pill to swallow, especially as it relates to technology. Office 365, let alone Teams, is not an exception to this rule.

Turning to new technology is not easily done, like turning on a light switch – people get used to having their content in particular places, knowing the core features of their document areas, and having an information architecture that aligns with the daily business (Internal Communications, I know how valuable news feeds can be!)

Wouldn’t it be great if you could bring some of that familiarity to your new tools? Especially if what you had before isn’t exactly “broken?”

I found that one of the best features of Teams was adding tabs when in a Teams Channel (e.g. General).

teams_page_login2

Tabs allow organizational teams or groups of users to add the right content they need to see. Conversations, Files, OneNote, etc. And there is a lot of different kinds of tabs available to add, and that list is constantly growing.

teams_app_galleryOne neat – and useful – tab is being able to add your SharePoint team home page as a tab itself.

I’m not talking about a SharePoint document library, which is what the “SharePoint” tab makes you pick. I’m talking about the page that everybody has eyes on when they visit their team site in the morning. Newsfeed, document libraries, lists, calendar, etc. The “dashboard page” of your team, so to speak.

To do this, just add a new Website tab, then add the URL (website address) of your team site. Provide a name for the tab too. The main Teams panel will then render the team site home page – the same one you would find in your Office 365 SharePoint site. The page browsing experience stays within the Teams main panel as you would expect (unless the link goes to an external site). You can also adjust it’s position in the tab row (drag-and-drop).

teams_page_homepage5One thing to note, you’ll have to go the actual SharePoint site to edit the page or content of the home page shown in Teams. Also, links to external websites will open up a new browser session.

teams_page_login1You may get prompted to login to Office 365 for the first time accessing the tab (especially if you’re not on the company network at work or at home). However, just put in your username and password like you would normally log into Office 365 (make sure to click Yes on the Stay Signed In message).

Admittedly it is not the most intuitive experience by having to pick the “Website” tab rather than the SharePoint one. However, perhaps Microsoft will fix this up in the not-to-distant future.

This is simply one method to begin bridging your team into the Office 365 juggernaut that is Microsoft Teams – if you can provide a familiar experience for your users, the pain points in adopting Teams as a go-to tool can be assuaged.

For more information on Teams features and updates, I recommend visiting the official Microsoft Teams Blog.