Presenting at Boston Code Camp 2017

I’m very excited to be speaking at my very first Code Camp on March 25th at the Microsoft Sales & Technology Center in Burlington. My session is titled: Connecting Salesforce.com & Office 365 using Microsoft Flow & PowerApps which hopefully is pretty straightforward for what I plan on covering. I am a huge fan of Microsoft Flow and am very excited to have the opportunity to “spread the gospel” of some of the amazing possibilities with this service.

I have always been a little hesitant to speak at Code Camp since I self-identify as being somewhere between a no-code developer and an actual developer. Most of the presenters are hardcore developers and I would be afraid of putting my audience off by not spending my session in Visual Studio. However, I am hoping my topic will resonate with attendees as I’m starting to see lots of organizations move towards Office 365 and there’s a mix of Salesforce.com & Dynamics 365 from a CRM perspective. I am also seeing a surge in data orchestration tools such as IFTT, Microsoft Flow, Zapier, etc. which is a good conversation to have especially when it comes to integrating with custom LOB applications which will require RESTFUL interfaces to enable these tools.

So here’s to an awesome event, and check out the website for more details!
https://www.bostoncodecamp.com/CC27

Advertisements

Is Microsoft Flow the replacement for SharePoint Workflows?

I recently had the privilege to co-present a session on Microsoft Flow & PowerApps at the Microsoft Beyond US Roadshow in Hartford. I am a huge fan of Microsoft Flow and have done several sessions on showing how you can orchestrate data across Dropbox, OneDrive, SharePoint & Salesforce with clicks & not code. One of the attendees in my session asked a very common question that I thought would make a good short blog post: “Is Flow the replacement for SharePoint Workflows?”.

Over the past few years I have built dozens of business applications leveraging the SharePoint platform to route requests through approval processes, provided metrics for turnaround time on requests, and automated non-value added steps. These solutions undoubtedly would leverage the SharePoint workflow engine for sending e-mails, assigning tasks, etc. Since Microsoft introduced the Workflow Manager in SharePoint 2013, there has not been any additional enhancements to their workflow engine. Compounded with the fact that SharePoint 2016 did not include an updated version of SharePoint 2016 it would make sense to assume that Flow is the replacement for SharePoint Workflows.

However, I would argue that Microsoft Flow is really positioned as the next generation of business process management applications vs an outright replacement to SharePoint Workflows. From a feature parity perspective not all of the SharePoint Workflow actions are available in Flow (yet they seem to be added all the time). At the time of this blog there aren’t the basic string manipulation actions, or copy items (also not available in 2013 but are in 2010), content approval/publishing, check-in/check-out, and wait for field changes in list items. There is also the caveat that in order to access on-premises data that you would need to setup a Gateway in order to make it accessible to Microsoft’s cloud.

Microsoft Flow provides much more capability than SharePoint does which might initially frighten some Enterprise customers. While there is a lot of value in being able to orchestrate data across both line of business & public clouds there definitely needs to be some up front planning to ensure that you do not jeopardize the integrity of your company’s data. For example it is absolutely possible to develop a Flow to copy files from your OneDrive for Business to your personal DropBox.

Finally, from a licensing perspective Microsoft Flow is a pay by the drink kind of service (technically pay by the Flow run). There’s a bit of  math but essentially you are allocated an allotment of Flow runs per user in your Office 365 tenant based on your plan. Be sure to check out Microsoft’s Flow Pricing page for up to the minute guidance. Whereas with SharePoint Workflows it’s essentially as many workflow runs as what your infrastructure can support.

So getting back to the original question – is Microsoft Flow the direct replacement for SharePoint Workflows? In my opinion – No. Microsoft Flow is the evolution of business process management allowing you to build elegant solutions which have the ability to orchestrate data across various line of business applications leveraging “clicks” and not code. Combined with PowerApps as your mobile/responsive front-end the barrier to creating enterprise applications has absolutely been lowered to where you no longer need a team of developers to create basic applications.

Hope this helps & happy Flow-ing.

 

 

Creating Google Charts with the SharePoint Search API

Recently I had a client ask me to build them some lightweight BI reports because their enterprise team was too consumed with other priorities. I started to think about pulling in a 3rd party JavaScript charting library since they mostly wanted to report against 1-2 columns of a list. However one of the challenges I faced was this library had close to 10,000 items and there would be views which would contain more than 5,000 items.So instead of leveraging the lists endpoint for the REST API, I decided to get the data via the Search API which would overcome that 5,000 item limit.

Since I can’t disclose client data, I mocked up the same solution using data I found on the CT State Data site. I created a new list from the CSV and let it go until I hit over 30,000 list items:

state-listitems

The list had a few interesting columns – Agency, Department, Job Title, Compensation Type, & Amount.

state-listdata

I thought it might be interesting to build a quick report for how my tax dollars were being spent by agency. Since I would be leveraging Search for pulling in the data, I first needed to map a crawled property to a managed property. I picked one of the out of the box managed properties and mapped it to OWS_Agency:

state-managedproperty

After a few hours the managed property became available as part of the Search index.

Next, was the easy part – I added a Content Editor Webpart to a page and pointed it at an HTML file which would pull together the solution.

The complete details of the HTML file can be found below but I just want to highlight a couple of the important parts.

1. When I get data from the Search API I specify that I want ContentTypes of type Item (would support custom as well) and then say I want to pull back RefinableString102 as part of the refiners set so I can see how many items per State agency
/_api/search/query?querytext=’ContentType=Item’&refiners=’RefinableString102′

The refiners work the exact same was using the Search REST API as they do when you perform an actual SharePoint Search – it returns results that match the criteria you specify. One of the bonus features is by saying you want to pull back refiners, you immediately get a summary of the different values for that refiner. So for our instance, I’m able to report on all the different state agencies by using the RefinableString102 refiner. Within that, it found 67 different agencies and then gave me the count of how many expenditures there are per agency.

In a production environment, you would probably want to also limit the query to only return results from that particular site collection or site, but the purpose of this blog post is to hopefully give you a very easy to follow along with example.

2. When I make the AJAX call the JSON returned back is in a complicated hierarchy. I took a screenshot so you can see the structure:

state-xdata

xData.d.query.PrimaryQueryResult.RefinementResults.Refiners.results[0].Entries.results[0].RefinementCount gives me access to 3999 which is how many expenditures were issued to the Board of Regents agency.

RefinementName – the agency name from the data set
RefinementValue – the number of items that are set to that particular agency

3. Because I’m creating the table from data returned from the Search API – I needed to initiate a new DataTable for Google Charts and then iterate over my JSON data to fill in the cells.I told it how many rows I would have (the length of the results) and then I used a for loop to fill in the table values.

4. I set the title of the chart of “State Budget” but creating an Options object.

5. I then create a new variable called chart which initiates the google.visualization.ScatterChart function and pass it the div on the page where you want to render the chart.

Here’s what the Google chart looks like with all the state data loaded:

state-googlechart

Full HTML file on Github

state-html-file.PNG

 

6 Things You Can Do If You’re Not Attending Microsoft Ignite

If you’re an IT Professional there is a pretty good chance that you have heard that Microsoft’s Ignite event is this week down at Atlanta. Ignite is the consolidation of various other Microsoft conferences such as TechEd, SharePoint Conference, etc. For those of you who are attending, there are plenty of “what to expect” blog posts which give you the usual tips of stay hydrated, wear comfortable shoes, bring battery packs, business cards, etc. However, there aren’t too many posts out there for those who aren’t able to attend.

This year I’m not able to attend Ignite but I have a small checklist of things I plan on doing to ensure that I stay up to speed with all the announcements.

Stream the keynote & select sessions – so like most big conferences, Ignite’s keynote is going to be streaming on Channel 9. Last year at Ignite SharePoint/O365 were barely even mentioned, but with the return of Jeff Teper I have a strong feeling that things might be different this year. There are also select sessions each day which will be available for streaming. Be sure to check out http://ignite.microsoft.com & Channel9 for links to those sessions.

Follow the #MSIgnite hashtag on Twitter – there are over 18,000 people heading down to Atlanta to attend Ignite and you can be sure that there are going to be tweets both official from Microsoft & unofficial from the attendees. Following the #MSIgnite has tag is a surefire way to get quick micro updates from attendees and companies.

Watch sessions on Channel 9 – for the past few years Microsoft has been posting session recordings on Channel 9 about 24 hours later which often times spurs the debate of why attend in-person when all the session are uploaded the next day. Of course the direct product team interaction, networking activities, and being able to ask your questions after the session are all some of the common examples. But for those who aren’t able to attend in-person, getting access to session recordings is truly amazing.

Attend Ignite recap at you local User Groups – I run a local user group and each year after the major Microsoft/SharePoint conference we always ask someone that attended to provide a recap of some of the highlights. Many user groups are moving their scheduling to Meetup, so that would definitely be a good place to start if you’re not sure what user groups are in your area.

Be on the lookout for “After Ignite” events – in case your local user group doesn’t host an “Ignite recap” session, oftentimes Microsoft will host these events in a few select cities. Talk to your local Microsoft rep after Ignite to find out if there’s going to be an “After Ignite” nearby.

Follow these folks on Twitter – finally, if you are completely overwhelmed by following the #MSIgnite hashtag there are a few folks attending which often times provide their own amateur journalist recaps of the day/event (in no particular order):

Nik Patel – fellow Slalomite, fastest fingers in the mid-west
Naomi Moneypenny – presenting like 5 sessions at Ignite, absolutely brilliant
Benjamin Niaulin – Mr. ShareGate, Tweets a lot and includes pictures
Jeff Teper – Mr. SharePoint (enough said)

So there you have it – hopefully this is helpful for any fellow “remote attendees” that want to stay up to date on the latest from the Ignite conference but aren’t able to make it this year!

The End of the Free SharePoint Saturday Events?

This weekend I had the pleasure to present two sessions at SharePoint Saturday Charlotte at the University of Charlotte. This was my first time outside the airport and I really enjoyed the people & atmosphere of the city. Unfortunately some travel issues prevented me from finding much time to really see the city or mingle with other speaker, but I still had a nice time.

My first session of the day was “Integrating O365 & Salesforce.com using Microsoft PowerApps & Flow” which was very lightly attended. I’m not sure if it was because the keynote ran over it’s allotted time or that rooms were on different floors, but I was surprised that more folks weren’t interested in connecting Salesforce & O365. Regardless of number of attendees, I think the talk itself went pretty well and it gave me one more dry run before I record it for Collab365. My second session, “Modern SharePoint Development Workflow using Node, Bower, Yeoman and more!” had quite a few more attendees but still not the same crowd that I’ve recently seen in Boston, New York, or even Pittsburgh. When I spoke with the organizers it sounded like they had over a 60% no-show rate which is just slightly higher than the average 50% rate that most events see.

As an organizer, I know these events take a tremendous amount of planning from securing a venue, to lining up speakers, coordinating food, to making sure that sponsors are all set with their logistics. Estimating breakfast & lunch as got to the best most difficult & wasteful part of coordinating a SharePoint Saturday. No matter what you do it’s inevitable that there is going to be too much food. As an organizer you always want to ensure that people are properly hydrated, fed, and taken care of. If you have 200 people register for your event, it can be stressful to not purchase food for the worst case scenario (200) attendees. Typically I try to factor in 25% no-show even though I know it is going to be higher. As an organizer there is no worse feeling that running out of food, even for a free event. As an organizer of a smaller user group – the amount of leftover food we had at least year’s SharePoint Saturday event could have paid for half the user group meetings this year.

I think the initial charter for SharePoint Saturday which was to provide a platform for learning with no boundaries (i.e.; free to all attendees) was quite noble. However, after attending dozens of SharePoint Saturdays I can’t turn my eye to the inevitable waste that is associated with the events. From food, t-shirts, handouts, and swag, it can be alarming how much is leftover after an event. I think one of the problems with the free events is that registrants do not always associate a value to the event. When it’s free, there’s nothing lost if they don’t attend. But, if there was a nominal fee – then they would have made that investment and perhaps they would think twice about not attending. I don’t think a charge somewhere between $5-10 would create that much of a barrier to entry.

When speaking with the teams that organize the Cloud Saturday event (which is paid) in Chicago & Atlanta – they typically see a 90% attendance which is phenomenal. With sponsorship money becoming more difficult to secure, I wouldn’t be surprised to see more events change to the nominal charge model. If anything it also helps to alleviate some of the stress of obtaining sponsorship money when many ISV’s are beginning to withdraw from participating in some of these community events.

Leveraging Microsoft Flow to Capture #YammerWins for Knowledge Management

First off – I want to give credit to Naomi Moneypenny for the idea of tagging threads in Yammer with #YammerWin. (Or at least I believe it was her) I think that’s a brilliant idea and helps to demonstrate that you would use a product like Yammer for more than just water cooler gossip. When I started to think about the job though of trolling Yammer for #YammerWins it occurred to me that it’s kind of a copy & paste intensive process. There’s also no quick way of getting a count of how many #YammerWins you have, etc. Sure you can click the #YammerWin hashtag in your group and then see all the different threads which certainly works but it’s not super organized.

I’m a consultant and development is what we tend to do – but to be honest, I don’t have the warm & fuzzies about doing custom development on top of Yammer. T

So then Microsoft Flow came along and I got a really cool idea. What if I could build a Flow that would do the data collection for me and then store it in a SharePoint list? Once it’s in a list I can do anything from kicking off workflows, to building mini-reports for management, etc. Talk about a #MicrosoftFlowWin

So I did & here’s what my Flow looks like:

YammerWin

  1. The trigger is when a new item is created in a Group (point at a Yammer Group)
  2. I include a condition, if the Message Text contains #yammerwin
  3. If yes, it creates an item in a SharePoint list

My SharePoint list has a few extra columns (MessageText, URL, and CreatedAt) and I map the following attributes from the Yammer post so that I can see the message text, when it was created, and the URL to the actual object.

YammerWin2

Once that’s all set I let the flow fire and when someone in the group sends a message which includes #YammerWin, the Flow picks it up and creates an item in SharePont:

YammerWinSharePoint

As many of you know – once the item is in SharePont, you can do anything! You can build custom reports to show management, have a workflow that assigns this to one of your Knowledge Management librarians for curation, etc. The URL also allows your librarian to go ahead and view the thread for context should the message text not contain all the information that they would need to help build the knowledge management record.

For those building Knowledge Management Portals in SharePoint – this is a really great way to start to capture your #YammerWins in a structured fashion.

Cheers to Naomi for the idea, and to the Microsoft Flow team for building the technology!

Well done! #MicrosoftFlowWin!

Quick Tip for creating a view of empty folders in your document library

Without stepping into the holy war of folders vs. metadata I would like to share a quick tip for how to get rid of those pesky empty folders in your document libraries by creating a view which shows you all the folders which don’t have any child items in them. Note this only seems to work for root folders and not subfolders. I’m still trying to come up with a clever way to identify those subfolders as well.

Create a view like normal but under the filter section, you’ll want to set the following condition to be true:

1. Folder Child Count is equal to 0
2. Content Type is equal to folder

. image

Click apply and boom – you have a view of your current document library with just folders that are empty!

image

For big libraries you might want to show more than 30 items by default so you can delete all those empty folders in one shot.

Hope that helps, and happy SharePointing!

SharePoint Saturday Pittsburgh Slides & Code

Thanks to everyone who came out to my session this afternoon, hoping you enjoyed it!

My slides can be found here:

Also here’s the Github project:

https://github.com/JaredMatfess/modernspdev

Microsoft Flow & Salesforce.com Integration

This afternoon I had a quick call with a fellow community leader Adam Levitan who works for Metalogix. We were recording a podcast which will hopefully air later this fall during the Collab365 Conference. We got onto the topic of the future of collaboration and I said that Microsoft is absolutely hitting a home run with their Flow service. For those of you who aren’t familiar with it, Microsoft Flow is a workflow automation solution which allows you to tie systems together through activities. While SharePoint does an amazing job of allowing users to automate business processes, Flow takes it a giant step forward.

One of the use cases that I’m very interested in is the ability to integrate the Office 365 Platform with Salesforce.com. Although I’m a big supporter of Microsoft technologies, many of my clients have decided to go with Salesforce.com instead of Dynamics or the new Dynamics 365 for their CRM platforms. One of the challenges with Salesforce is that the recurring subscription costs are expensive when you start trying to license all the users that may play some role within the sales cycle. The purpose of this post definitely isn’t to cover all the features of Salesforce or the many different clouds they have (Sales, Service, Health, etc.) but rather to focus on the future where you can tie cloud services together and build some really powerful solutions.

To illustrate what I mean, I’m going to walk through building a very simple “Flow” which mirrors a business scenario I’ve personally encountered. So I work for Slalom Consulting and we sponsor many community events like SharePoint & SQL Saturdays, various tech conferences, etc. Typically these sponsorships include a booth where we send a recruiter, sometimes a sales executive, but most of the time just members of that particular Practice / Vertical, etc. During these conferences there often times are potential leads generated based on discussions that happen at the booths, and the usually our folks capture a business card or just take down the person’s information. At the end of the event the team goes back to the office the next week and either sends the spreadsheet, or transposes that information from Excel into the CRM system for the sales team to go work. You might be saying to yourself “there has to be a better way” – and indeed there is!

What if you could generate those Leads from an Excel table and have it imported directly into CRM? Wouldn’t that be cool? Well guess what, that’s exactly what we are going to do.

First, let’s login to our Office 365 tenant which has Microsoft Flow enabled (as of this blog post it is still considered to be a Preview service). And we’re going to search up at the top for available templates to create our Flow from:

image

Let’s pick the Create Salesforce Leads from a Excel table template:

image

From here a new page will be displayed giving an overview of the template we selected:

image

Click the big “Use this template” button to bring up the connection configuration screen. We’re going to need to create connections for both the Excel file which will have the table of sales leads, and the connection to our Salesforce.com instance.

image

First click on the “Create” button for the connection to the Excel file.which will bring up a list of list of choices for where the Excel file can sit. Notice there are also non-Microsoft services such as Dropbox, Box, and even SFTP! For the purposes of this walk-through we are going to pick the OneDrive for Business but feel free to select the data source that best meets your requirements.

image

It will prompt you for credentials for your OneDrive For Business account after clicking the Sign In button:

image

So in truthful blogging, I’m going to tell you that it threw an error:

AzureResourceManagerErrors

I waited a bit, cleared out some of my old connections under my profile and then tried again and this time it made the connection.

Next, I created my connection to my Developer Salesforce instance: I logged into Salesforce.com:

image

Next, I allowed PowerApps access to my Salesforce tenant:

image

Of course I clicked, Allow and then continue on the main page to bring up the workflow canvas:

image

So working from the top, the very first thing you can do is setup recurrence for how often the flow will run. As of this time, this is still just a Preview service and there hasn’t been any real discussion on licensing or pricing. Like any service there are most certainly going to be service levels – ie; being able to guarantee computing power to run the job, amount of data perhaps to be processes, and likely some other elements that I haven’t even dreamed up yet – but you can rest assured that there’s a team at Microsoft figuring out how to price this service. Smile

For demo purposes, I am just going to have it recur every 15 minutes. So clicking the little ellipses in the corner of the recurrece box brings up the advanced options where I can set that information:

image 

Before proceeding to the next step I’m going to create a sample Excel file with the following column headers:

First Name
Last Name
Company
Phone
Email

image

Click OK and the following Table will be generated:

image

Last, I’m going to save my file as SPSLeads.xlsx and drop it on my OneDrive for Business. Back to the flow I go to the Get rows section of my flow and click the folder expand button on the right, and find my SPSLeads.xlsx file:

image

Next, I select the table which I left as the Excel default of Table1:

image

Next we dig into the Create Object section of the flow which is really the “Create Lead” functionality in Saleforce. This is where you can map the different columns to the fields in a Salesforce Lead Object. I went with the bare minimum of what is required to generate the lead, but there are other fields such as Street and Zipcode that can also be captured and added to the record.

image

Some of the fields can also be defaulted which is what I am going to do for the Lead Source since these leads are being captured at my fictional SharePoint Saturday Connecticut event:

image

Last, I’m going to have it Delete the row after creating the lead in Salesforce, essentially following the same path of picking the Excel file on my OneDrive and Table1 as my table name:

image

Finally I’m going to give this Flow a name:and then click the Create Flow button and let Microsoft Flow work its magic!

image

I’ll click the Done button and then see that I’m all set:

image

When I go to the My Flows, I’ll now see that there’s the Flow I just created:

image

Next, I’ll click on the little information icon to see that my flow is actually running:

image

In reality though – it takes a bit for my Flow to run. I suspect it’s because I both didn’t set a start time and that I also put a recurrence of every 15 minutes. This is definitely still a Preview service so there will probably be a bit more polishing up of the user interface before it goes live.

Then I looked at my Flow to find out that it had errors:

Flow-Error2

Then when I went to edit the Flow I found myself in the spinning page of doom:

image

So then I decided to back it all out and pop my file up on my Dropbox. I created the service exactly the same way and it picked up the file no problems at all!

image

After allowing the Flow to run, I logged into Salesforce.com and noticed that the following leads were generated:

image

I then popped open Spoopy Matfess (my amazing dog) and low and behold all the lead data came over:

image

Then I logged into my Dropbox accoutn and opened my file and noticed that it had been touched by PowerApps:

image

I’m a little curious as to why it would leave that last record with the _PowerAppsId_ at the end, but maybe that is just part of the beta. I would expect that it would leave the table cleared out and then allow for users to continue adding new leads as they come in throughout the day.

So the big question is why did my original attempts using OneDrive for Business fail? I could be wrong but there might be something with my profile since I’ve got multiple profiles, multiple O365 accounts, etc. Now, I did create the connection to OneDrive and authenticated successfully to Azure AD and it was also able to find the Excel workbook and the table. However, when it came time for the job to actually run, it threw some connectivity errors. I also could have possibly hit some ironically timed Azure issue.

Regardless though, flipping over to Dropbox and seeing it run successfully should hopefully give you some ideas as to what’s possible with Microsoft Flow. You can have users working in systems that they are familiar with such as OneDrive, SharePoint, etc. – and then have the output of their work feed into downstream systems.

SharePoint Saturday NYC – Slides, Github & More

First off, a huge thanks to all those that came out to my session, sorry for the packed room! SharePoint Saturday New York is one of my favorite events, and I loved all the great conversations.

Paul Tavares took an awesome panoramic picture from my room, I couldn’t believe how many folks showed up to talk about Modern SharePoint Development.

cooqzveviaepvpd

Here are the slides SharePoint Modern Web Development

Also here’s the Github project – https://github.com/JaredMatfess/modernspdev