Document Integration for Access Web Apps in SharePoint Online

This blog post is part of a series.  The next two posts are coming soon:
Part 2: Accessing SharePoint resources using OAuth
Part 3: Bringing it together: serve SharePoint documents in an Access Web App
Table of Contents

Part 1: Extending Access Web Apps and the JavaScript API

Access Web App

I never was a fan of the old-school Access (Desktop) databases.  They were (and still are) a nightmare to manage because of their decentralized nature and because they gave business users the idea they didn’t need IT to deploy critical business applications. In many cases, Access databases became a liability, where no-one knew who wrote it, and no-one could rewrite it or fix bugs inside of it, while it contained a fair bit of business logic and critical business data.

SharePoint 2013 and SharePoint Online Wave 15 introduced a new app: the Access Web App. An initial scan of its functionality early 2014 gave me a positive impression of this new tool. It allowed easy table creation and had a very intuitive user experience.

One of the most successful features to me is the automatic view creation with dependent views that show entities from other tables that have a lookup to the current table. A simple example is shown below where each Product has a lookup to a Supplier.

products-suppliers-relationshipThe Supplier View shows the list of related products (by default):

access-web-app

Last year, one of our customers was interested in managing their construction contracts in their newly acquired SharePoint Online environment. Focus was on low cost and high usability. As I was fiddling around with Access Web Apps, I suggested this to our customer.

After several deep-dive workshops and some homework, we came up with an information model that suited our customer. We implemented it in Access Web App and got good feedback. But already in the analysis phase, the integration of unstructured information (i.e. documents and e-mails) was of great importance for the success of the contracts database.

Linking documents to structured contracts

Even though there are great platforms out there that allow unstructured processes like contracts management to be automated quickly (like Ground lion for example), our customer concisely chose a simpler approach and just wanted the demo we gave with Access to have a link to documents.

In Access Web Apps, you have two ways of incorporating documents out-of-the-box:

  1. Use the “Image” field type. Only 1 image is allowed, which doesn’t server much of a purpose for documents.
  2. Add a SharePoint document library as a table. This however is hardly flexible as we can’t search through it. We could maybe use Data Macros as filters to get documents out of it, but there is no flexibility in displaying them as we’d like.

Moreover, we would like to link the documents using a business key, like a contract reference number and not the URL’s which are more volatile technical keys.

The way we imagined it, we could just “tag” documents with the correct business keys and have them “magically” show up in the corresponding contract in the Access Web App. Moving the documents won’t break the link and we are not limited to one document library this way.

But, Access Web Apps cannot deliver this functionality. Hence, enter Apps for Office.

Apps for Office

The Office team at Microsoft has made a big leap forward by introducing Apps for Office the last couple of years.  Away with ActiveX and COM plugins, enter cross-platform web-technology based extensions. Allowing developers to integrate loosely with Office much like SharePoint apps is a great idea because:

  • The code runs anywhere (Desktop client, mobile client, browser)
  • The code is deployed to one location (web)
  • The code is managed and upgraded in one location (easy upgrade and licensing)
  • It runs in a sandbox so it cannot compromise the client system’s stability

An App for Office is merely a webpage that interacts with whichever document it is bound to using a client-side JavaScript API. This API allows the app developer to set up bindings between the document it is shown in and the app logic. Simple scenario’s like looking up highlighted words in a dictionary come to mind, but we are going to dig a bit deeper on the Access Web App.

There are different types of Office:

  • Content Apps
  • Task Pane Apps
  • Mail Apps

We are focusing only on Content Apps, as that is the only type that is currently available for Access.

From the MSDN documentation Types of Apps:

Content apps integrate web-based features as content that shown in line with the body of a document. Content apps let you integrate rich, web-based data visualizations, embedded media (such as a YouTube video player or a picture gallery), as well as other external content.

What’s in a name

Unfortunately in this time and age, everyone is talking about “apps”. In our case, apps are clearly not smartphone apps, but additional functionality inside Office.

Access comes in 2 flavours these days: Desktop databases and web databases. The latter was called a “Web Database” in 2010, but is now referred to as a “Custom web app”. In SharePoint, it’s called an “Access Web App”.

And on top of that, we have the “Apps for Office” that we are discussing here.

So for simplicity’s sake, we’ll call it an “app for Access” as it is referred to in the JavaScript API documentation.

An example: Bing Maps

The most well-known example of an App for Access is undoubtedly the Bing Maps app, which shows a simple but effective example of adding external content to an Access Web App. Below you can see an example of the Bing Maps app for Access that visualizes a bound field from the selected record on a map. In this case, the “City” field is used and Bing Maps listens to selection changed events to update the map location.

bing-maps-example

Apps for Office Concepts

This blogpost won’t tell you step-by-step what to do to create your first Access App.

There is a good Microsoft series on that:

I suggest you read and follow them closely, they’ve helped me getting started with my first app. Read on to find out what the series didn’t mention yet.

Data binding

What the documentation is rather obscure about – probably due to the rapid pace of development – is what data bindings are available for apps for Access. There are 3 types of bindings (see MSDN – Bind to regions in a document or spreadsheet):

  • Text binding
  • Matrix binding
  • Table binding

Only the Table binding can be used for apps for Access (at this time).
Table bindings allow you to bind to all data from a single table.

Supposing that you got started with the starter project, you already have two pages available (Home and DataBinding). The “Bind to existing data” button will trigger the databinding process in JavaScript:

    // Binds the visualization to existing data.
    function bindToExistingData() {
        Office.context.document.bindings.addFromPromptAsync(
            Office.BindingType.Table,
            { id: app.bindingID, sampleData: visualization.generateSampleData() },
            function (result) {
                if (result.status === Office.AsyncResultStatus.Succeeded) {
                    window.location.href = '/Home';
                } else {
                    app.showNotification(result.error.name, result.error.message);
                }
            }
        );
    }

There are numerous interesting things to note on this piece of code that the documentation doesn’t elaborate on.

First off, the Office.BindingType.Table is used to bind as a table binding, the only accepted binding in apps for Access.

The addFromPromptAsync shows a pop-up with a generic dialog allowing the user that configures the app for Access to choose which columns in the current table are to be bound to the app for Access. The popup looks like this:

binding-popup

On the left, under Example, you see some “sample data”. You have to supply both the columns and the row data in the sampleData parameter. The apps for Office sample shows you how to do this (data modified):

    // Generates and returns an Office.TableData object with sample data.
    visualization.generateSampleData = function () {
        var sampleHeaders = [['Contract Reference', 'Variation Order Employer Reference',
					    'Claim Employer Reference', 'Contract Amendment Reference']];
        var sampleRows = [
            ['C1', 'VO1', 'Cl1', 'CA1'],
            ['C2', 'VO2', 'Cl2', 'CA2'],
            ['C3', 'VO3', 'Cl3', 'CA3'],
            ['C4', 'VO4', 'Cl4', 'CA4']];
        return new Office.TableData(sampleRows, sampleHeaders);
    }

The number of columns you can choose in the pop-up is not fixed. You can for example remove 2 columns by clicking the minus sign and only use 2 bound fields. However, you cannot remove only 1 column in the middle. You can only add and remove columns at the end.
Note that the column headers are purely as a reference to the end user that is configuring the app for Access. It has no run-time value, because you get an unnamed matrix when retrieving data.

The binding needs to be configured for each View in the Access Web App that you add the app for Access to. This is important when considering your deployment options.

The rest of the sample code for binding to the Access table just redirects to the correct page to display the results or to display an error message.

Getting the databinding context

Now that we’ve configured the data binding on the DataBinding page, we can continue actually load the data from the Access Web App and respond to changes in data and selections.

Wait! Why would you want to listen to changes? Doesn’t every page reload for a new database record? No, it doesn’t.

Don’t forget you are databinding to the entire table, so when the View you are looking at changes the “selected” record, but stays in the same table, the View is not reloaded, and neither is the Access Web App.

Hence, we must listen to JavaScript events that get fired by the Access Web App.

Again, the sample code gets us pretty far already:

    // Checks if a binding exists, and either displays the visualization,
    //        or redirects to the DataBinding page.
    function displayDataOrRedirect() {
        Office.context.document.bindings.getByIdAsync(
            app.bindingID,
            function (result) {
                if (result.status === Office.AsyncResultStatus.Succeeded) {
                    var binding = result.value;
                    displayDataForBinding(binding);
                    binding.addHandlerAsync(
                        Office.EventType.BindingSelectionChanged,
                        function () { displayDataForBinding(binding); }
                    );
                    binding.addHandlerAsync(
                        Office.EventType.BindingDataChanged,
                        function() { displayDataForBinding(binding); }
                    );
                } else {
                    window.location.href = '/Home/DataBinding';
                }
            });
    }

If you want to get data from the Access table, you must retrieve the binding first. This is shown in the example above.

Note that most JavaScript calls are asynchronous, which requires callback methods, like in this example.

Once the getByIdAsync returns with the correct binding (in this case we use only 1 binding), the displayDataForBinding is called to render the data from the binding.

Also, and most importantly for a responsive app, we need to register event handlers for selection changes (selecting another record from the same table in the same View) and data changes (when the user saves data in a modified record). This is done using the addHandlerAsync functions.

 Next, let’s take a look at the displayDataForBinding function.

    // Queries the binding for its data, then delegates to the visualization script.
    function displayDataForBinding(binding) {
        //Get the Contract ID from the current row
        binding.getDataAsync({
            coercionType: Office.CoercionType.Table,
            //Specify to get the data from the currently selected data row
            rows: "thisRow"
        }, function (callback) {
            // Check to see if the function was successful
            if (callback.status == Office.AsyncResultStatus.Succeeded) {
                // Get the contract ID
                var ContractID = callback.value.rows[0][0];
                var VariationOrderReference = callback.value.rows[0][1];
                var ClaimReference = callback.value.rows[0][2];
                var ContractAmendmentReference = callback.value.rows[0][3];
                $('#data-display').text("Loading...");

                // Now go and search for documents!
			// ... Your typical async AJAX call here...
            }
        });
    }

To get the data from the binding, just call getDataAsync. Sounds simple, right? In fact, you get the data for the binding as you request, but don’t forget that you bound to the entire table, so not to a single record. In some cases, it makes sense to display data for all rows, like with the Bing app for Access, which shows all locations in the table. But sometimes, you just want the single selected/active row.

I spent most time looking for how to get the single selected row from the data binding. This is done through the rows parameter on the getDataAsync function and supplying it with “thisRow“. It is documented however, see MSDN (Binding.getDataAsync method (JavaScript API for Office).

Next, when the callback function gets called, you can access the callback.value.rows object. This is a simple two-dimensional array, which provides you with no idea on what columns were used in the data binding. Therefore, make sure you always use the same number of columns to bind your data to. This ensure that column 2 is always what you expect it to be.

Once you can get the business key from your selected row, things start to get really fun as we can query any external service or repository we want to use data to show in our app for Access. More on that in the next part of this series.

Lessons learned

Following are a collection of things I found out during development, which I thought were neat or good to know.

Foreign keys

Access Web Apps allow you to have “Lookup” fields. Even though these lookup fields look like they contain the text value of the referring table, internally it is of course referring to a primary key, an integer in this case. When accessing foreign keys in data bindings with apps for Access, you only get the foreign key ID.

A simple trick I pulled off to get the text value anyway, is to add a calculated column in the Access Web App and use a marco “On Insert” and “On Update” on the table:

access-toolbarMacroaccess-toolbarDataMacro

Notice how I also created a Data Macro “GetContractReference” to which I pass the foreign key to retrieve the textual reference.

User Identity

There is no way in apps for Office (at this time) to get the identity of the user (except for apps for Outlook). If you think of it in the broader context of apps for all Office suite applications (Word, Excel, PowerPoint, Project), it actually makes sense, as you can’t assume you’re logged into any account in Office and it wouldn’t be secure to just disclose the user’s Windows username. So there is no way of getting the identity. You could have your end users log in again (which is done a lot in Task Pane apps), or you could use OAuth trust dialogs to services the user is already logged in to.

Code behind

By default when creating an app for Office in Visual Studio you get a solution with .html files that contain mostly JavaScript code to connect to the data model. However, when working with OAuth integration, there is a certain level of secure communication that needs to be done. This exchange of access and refresh tokens is best left outside the web browser, and hence requires server-side code.

For this matter, I just removed the default web application that comes from the Visual Studio “App for Office” template, but I kept the html files for future reference. I added a new web application project (Azure-hosted ASP.NET) and copied over the html files and renamed them to .cshtml. That allowed me to run Controller code securely to negotiate OAuth tokens.

Hosting your app vs IE and its Trusted Sites

Debugging your app in Visual Studio launches an IIS Express and binds it to the app. You may find this behaviour easy to use for most use cases. However, for storing cookies (with user ID information for example) and for the cross-domain queries that are required for opening the trust dialogs, using localhost is not ideal. Internet Explorer 11 does some strange things compared to Firefox or Chrome, like not accepting cookies from localhost, not showing cross-domain trust dialogs, …

Instead of testing on localhost, these days with Visual Studio 2013 you can easily deploy to Azure, and each MSDN subscription gets 10 Azure web apps to host for free.

Deploying to Azure is easy:

  1. Right-click on your web project and click Publish
  2. In the “Publish Web” dialog, go to the “Profile” tab in the wizard and click “Microsoft Azure Website”:
    azure-publish1
  3. Now connect using the Microsoft account that is linked to your MSDN subscription (make sure the Azure benefit is activated from MSDN). Create a new web site or connect to an existing one.
  4. Once connected, the credentials are filled in automatically:
    azure-publish2
  5. Click Publish and you’re good to go!

Debugging is just as easy. Open up the Server Explorer (typically on the left side). Click on the Azure icon (tooltip: “Connect to Microsoft Azure Subscription”) and sign in. You can now expand the Azure and Websites nodes:
azure-debug

Right-click the web app you want to debug.  Make sure you published to this web app recently, so the symbols on Azure correspond to your local code. Now click “Attach Debugger”:

azure-debug2

Ok, it takes just a bit more time than going local, but with the limits of Internet Explorer and cross-domain requests, this early deployment to Azure saved my life.

App Upgrade

The documentation for Access Web Apps is not that great, I’ve said that before. But regarding ALM, it seriously lacks details. Specifically app upgrades are problematic. When you first deploy your Web App to a production environment, everything works marvellously. You can do this by saving your app as a deployment package (within the Access desktop client). Of course, you make sure to “Lock” the app, so no one can change your app in production.

Next time you want to deploy a new version, you could do an app upgrade in the UI. Sounds great, but the app upgrades don’t always work. I’ve gotten a lot of errors that app upgrades failed (without any information but a correlation ID, which we all know is rather useless in O365 at this time). This typically happens when data model changes are too drastic to perform without data or integrity loss.

Access “solves” this by allowing you to perform “On Deploy Macros”. However, you need to code every upgrade from each version you every made. That is hardly an easy task, and it’s inside a very limited design environment. So that’s mostly a no-go. Also because there is hardly any easy way of testing this rather delicate code.

access-deploy

My advice? At this time, I would say don’t go to production soon and make sure you get as much right as you can. Don’t lock your app and continue implementing changes in your staging environment and manually copy over the changes. Not the best way to work, I know, but at this time, the alternatives are not much better.

Apps for Office ALM

One more issue with deployment is the fact that apps for Office are added to Office Web Apps with hard coded App Catalog URL’s. That’s right, even though the app has a unique ID that could be found easily in the App Catalog, it still references the app catalog from the development tenant.

I’ve contacted Microsoft support on this issue and they eventually redirected me to the MSDN forums, which they mostly do when any problem starts getting serious… There was no way they could create a fix for this quickly of course.

You can find the conversation here:
Access Web App deployment to different environments not working with apps for Office from the App Catalog

Read on to the next part of the Series to see how we are going to access SharePoint files using the Search API and use the current user’s OAuth token.

Update 21/02/2016 updated broken link to “Bind and manipulate data in a SharePoint Access app” (seemed to have moved do MSDN Code)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s