Relating requirements to documents from Microsoft SharePoint Online

Projects like to use a Document Management System (DMS), such as Microsoft SharePoint, to manage documents. Often, the need will arise to link information stored in Relatics to a document or document version in the DMS. This article explains how you can create a webservice interface between Relatics and Microsoft SharePoint Online. You can choose to configure the interface for a document or a document version. In the example of this article,  a document is used. In this way, Relatics users can link a requirement directly to a document in SharePoint and be presented with a selection with which to search in the actual set of documents.

Configuring the Relatics webservice to send a request to SharePoint Online

You can configure your own webservice to SharePoint Online in a RESTful web-service (Receiving Client):

01_relatics_soap_webservice_details1

You can define the URL for the SharePoint site of your project in the RequestURI, which uses the API of SharePoint to request documents. Below is an example which I personally like to use.

Example of RequestURI: Requesting documents from SharePoint

[SubsiteUrl]/_api/Web/Lists(guid'[ListGuid]’)/Items?$select=ID,GUID,Title,FileLeafRef,UniqueId&$filter

=substringof(‘[Search]’,Title) or substringof(‘[Search]’,FileLeafRef)&top=100&orderby=ID

Several operators are specified in the URL to influence the result of the webservice. You can see the available operators on this page. One tip is to first create the URL in your web browser. This will give you a quick result and the assurance that the URL works properly. It is important to realize that you are completely free in how you create the URL and that you can decide which document metadata are relevant to the project.

Further, I like to use RequestURI Replacers. This is very important, as this allows you to influence the webservice through the end application. For instance, a search term entered by an end user in the parameter of a selection is passed on via the RequestURI Replacer ‘Search’ in the operator ‘filter’.

Processing the result from SharePoint Online in the Relatics webservice

Go to the ‘Details’ tab to specify what needs to happen with the data received from the webservice:

02_relatics_soap_webservice_details2

Under ‘XSL transformations (optional)’ I applied a self-constructed XSLT file.

Example of an XSLT file: All-Documents-with-DerivedListID.xslt

<?xml version=”1.0″ encoding=”UTF-8″?>

<xsl:stylesheet version=”2.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” xmlns:xs=”http://www.w3.org/2001/XMLSchema” xmlns:fn=”http://www.w3.org/2005/xpath-functions”>

<xsl:output method=”xml” version=”1.0″ encoding=”UTF-8″ indent=”yes”/>

<!– Select each node –>

<xsl:template match=”@*|node()”>

<!– Create a copy of the current node –>

<xsl:copy>

<xsl:choose>

<!– Check if the current node is the node “entry” inside the node “feed” –>

<xsl:when test=”(name() = ‘uri’) and (parent::__metadata)”>

<!– Select the string that contains the ListID of SharePoint –>

<xsl:variable name=”StringWithListID” select=”current()”/>

<!– Remove “SP.Data.” before the String –>

<xsl:variable name=”ListID” select=’substring-after($StringWithListID,”Lists(guid&apos;”)’/>

<!– Remove “Item” from the String and save the result in the attribute “DerivedListID”–>

<xsl:attribute name=”DerivedListID” select=’substring-before($ListID,”&apos;)”)’/>

</xsl:when>

</xsl:choose>

<!– Apply a template to the current element or to the current element’s child nodes –>

<xsl:apply-templates select=”@*|node()”/>

</xsl:copy>

</xsl:template>

</xsl:stylesheet>

 

The XSLT file ensures that the ID of the SharePoint List is included in the XML as a separate node. The point is that, in the original result of the webservice, this ListID is part of a URL. I recommend including the ListID separately in the result. You will want to save this in Relatics with a document, as you will need this later for other webservices.

Please note that I am referring to XML here, while it actually concerns a RESTful webservice. This is because Relatics converts the result from JSON to XML behind the scenes. This is nice as it allows you to always filter data in the same way.

Under ‘XML Adapter Identifying Field’ you can enter an Xpath Expression to interpret the webservice result of the SharePoint webservice:

Example of XPath Expression: XML Adapter Definition

//item/d/results/item

The example above ensures that all nodes are selected from the webservice result that concern a document in SharePoint.

Under ‘XML Adapter Fields’ you can then create the nodes from the webservice result that you want to use in Relatics. If you look closely, you’ll see that a number of fields are saved in attributes. It is important to do this, as this information is required later to call additional webservices; for example, to request all the details of a single document.

As ‘XML Adapter Identifying Field’, I recommend you use ‘UniqueID’. This field is essential, as it informs Relatics which document corresponds with the same document in SharePoint.

Setting authentication for SharePoint Online in the Relatics webservice

03_relatics_soap_webservice_authentication

For SharePoint Online, you need to set the option ‘By credentials’ as ‘Enabled’ on the ‘Authentication’ tab. You can then enter a Username and Password for a user who has access to the SharePoint site of the project.

Under ‘Domain’, it is important to enter the value ‘SharePointOnline’. This ensures that the right authentication mechanism is used. I furthermore recommend to not use a personal user for the interface between Relatics and SharePoint but a separate user that only has read access rights in SharePoint.

Configuring the information model element in the Relatics webservice

04_relatics_soap_webservice_connections

On the ‘Connections’ tab, in the table ‘primarily retrieves a set of instances of’, you can select the element of the model in which instances of the selected document are stored. It obviously depends on your own information model what the most suitable element is. In this example, the ‘Document’ element type was selected.

Configuring a selection for the end user

05_relatics_sharepoint_webservice_selection

To help the end user to select a document, it is advisable to create a selection. Example:

Note that the root node of the query is the element ‘Project’. It also contains two properties defining URLs for SharePoint. This is an ideal construction when using multiple project workspaces in combination with Synchronization. In this way, per project workspace, you can configure the details of the SharePoint Workspace.

The end result

Let’s have a look at SharePoint Online first. The screenshot below shows that two documents are available in the example project ‘The Connection’:

06_relatics_sharepoint_webservice_result_sharepoint

When an end user wants to relate a document in Relatics, for example a requirement, a selection appears. In this selection the end user can enter a search term (e.g. ‘doc’). After clicking on ‘Search’, the actual set of documents from SharePoint is displayed. This has been retrieved live via the webservice.

07_relatics_sharepoint_webservice_result_relatics

When an end user selects a document, an instance of the document is created in Relatics, including the relation between the requirement and the document. This is a very intuitive way for end users to create relations between information in Relatics and in SharePoint Online.

About Kris de Waal

Relatics Portret-120

After graduating in Business Administration with a specialization in Business Information Management, Kris joined Relatics as a Business Information Consultant. He is eager to learn about new concepts, technologies, IT systems and apply the knowledge in his daily work. In addition, Kris loves to work on new ideas and innovations to get more out of Relatics.