Creating data centric and business automated solutions in Office 365

So, you are using Office 365 for your content now, your users love it, they access each day but in reality, it is only being used as document collaboration, maybe publishing and discussion platform. Now that this is all working, then next step is to build solutions that enhance business and data solutions.

Firstly, if you need to build a solution that is really just data driven and is about presenting data, then of course the service of choice within Office 365 is Power BI.

https://powerbi.microsoft.com/en-us/

Outside of that you are then looking at using things such as workflows, forms and some data storage, all of which can be done by writing custom add-in’s and code. However, if you want to stay out of the box as such then you are now into the PowerApps and Flow space.

Let’s start with PowerApps, to do this you will need to have registered over at the PowerApps site, or have access already via your Office 365 tenant subscription. Once in the site we are going to access the “Common Data Service” from the left navigation and then select the “Entities” link.

Next click the “Create database” link, once prompted set the access you need for your users.

Setting it to “Restrict access” will allow you to control user by user who has access to the database. While creating the database it will display the blue bar at the top.

Once completed it will then prompt you to either “Browse entities”, “Create an app” or “Try a sample app”. Now before we choose lets really understand what we just created and why we should use it.

Image Source: https://pwrappsdps.blob.core.windows.net/media/powerapps.microsoft.com/en-us/documentation/articles/learning-common-data-service/20161212105937/architecture.png

Think of the Common Data Service as a hierarchy. At the bottom level, the service stores data in a scalable and reliable way, and makes the data available so that multiple applications can use it. The next level is the common data model that includes many entities used in applications and business processes: entities like Account, Contact, Product, and Sales Order. You can extend the standard entities and create custom ones to meet your business needs.

An entity is just a combination of the metadata that describes it (field names, data types, and so on) and the data that you store in it. If you know Access or another database, an entity is very much like a table. Some of the core benefits of working with entity data in the Common Data Service are:

Easy to manage: Both the metadata and data are stored in the cloud. You don't need to worry about the details of how they're stored.

Easy to share: You can easily share data with your colleagues because PowerApps manages the permissions.

Easy to secure: Data is securely stored so that users can see it only if you grant them access. Role-based security allows you to control access to entities for different users within your organization.

Rich metadata: Data types and relationships are leveraged directly within PowerApps. For example, defining a field type URL will present your data as a hyperlink within your app.

Productivity tools: Entities are available within the add-ins for Microsoft Excel and Outlook to increase productivity, and ensure your data is accessible.

Picklists: Include picklists from a rich set of standard picklists to provide quick drop downs within your entities and apps.

So, the question is still, why would we use it?

In reality you don’t have to, but the Common Data Service provides the base data structure that is used for the most common data that most organizations use. Of course, you can augment that with custom entities too, for your specific needs.

Let’s create an app now and see how this all ties together. Simply click the “Create a new app” link and select to create it using the web interface. Next choose the “Common Data Service”, “Phone layout” tile.

Next select “@Foundation.Contact” from the entities lists for the new app, then press “Connect”.

The default app should now be built based on this initial set of data. Though that was great, that still doesn’t make it super useful as the data to be useful needs to be exported. However, there are some great examples of what you can do with the “Common Data Service” at the link below:

https://powerapps.microsoft.com/en-us/blog/5-new-powerapps-templates-for-the-common-data-service/

To use one of these instead of selecting the “Common Data Service”, “Phone layout”, you need to choose from the “App templates” tile instead. From the list select the “Invoice Management”. These templates are built on the “Common Data Service”, this one specifically uses the “Account”, “Sales Invoice” and “Sales Invoice Line” entities.

As a tip, sometimes when the templates are used, you end up with a mismatch of fields and data sources. 

To fix this you will need to select the affected form and controls and set them to the correct values. For this sample, you just need to set the value “Sales invoice line” to the actual dataset value you have, for me it is “@Sales.SalesInvoiceLine”

Once done for all the forms, the sample app will work as expected. Now we have that working we have an app that is based on the “Common Data Service”, with the data being stored in the cloud, and available to export locally.

The data is then available in excel format to view as needed, modify and then import back, allowing this data to always be available online. Now the real win is to then tie Microsoft Flow events to this data and the app to create the best business app that you needed. All in all, using these standard entities, allows you without writing code, too extend the data model with custom entities, thus opening up a new world of possibilities for building even more useful PowerApps and Flows. In this new world, non-developers have the ability to create a data model to support their needs. Common Data Service has the ability to provide a single data model that sources data from multiple systems, relates the data, and also allows users to view and update data across several applications.

Highly recommend looking at the Common Data Service and using to create applications using the new feature of PowerApps and then Flow.