In my second post in the series on how you can add an integration with SharePoint to the Employee Self-Service Portal in Dynamics 365 without using server-side code, I’ll describe how we go about getting the SharePoint folder location for the current Case on the Edit Case page.

What We’re After

The goal of this post is to determine the full URL of the folder in SharePoint that is associated with the current Case record, including the SharePoint server URL, and any ancestor folders of the folder itself.

Crafting the FetchXML

Thankfully Dynamics 365 stores the SharePoint folders associated with a particular record using a standard entity called Document Location (schema name sharepointdocumentlocation). This means we can use FetchXML to query it, which we can do in the portal using Liquid markup.

In this situation, we’re going to assume there is only ever one Document Location for any given record, however it would be pretty straight forward to go through all Document Locations records related to the specific case to determine the most appropriate based on your business logic.

Using the FetchXML Builder in XrmToolBox, I created the following FetchXML to get the Document Locations associated with one particular record:

<fetch top="50" >
  <entity name="sharepointdocumentlocation" >
    <all-attributes/>
    <filter type="and" >
      <condition attribute="regardingobjectid" operator="eq" value="3137bd61-2a9b-e811-b96f-0003ffb4f652" />
    </filter>
  </entity>
</fetch>

Next, I want to incorporate this FetchXML into Liquid so that I can retrieve records associated with the current case. Since the current case ID is available from the id query string parameter, I can do the following:

{% fetchxml sharepointdocumentlocations %}
<fetch top="50" >
  <entity name="sharepointdocumentlocation" >
    <all-attributes/>
    <filter type="and" >
      <condition attribute="regardingobjectid" operator="eq" value="{{params.id}}" />
    </filter>
  </entity>
</fetch>
{% endfetchxml %}

{% assign currentLocation = sharepointdocumentlocations.results.entities[0] %}

All I’m doing here is using FetchXML to query the document locations for the current case, and assigning the first entity in the result set to a variable.

I took the above code and used it to create a Web Template entity called SharePointIntegration, and then included the template on the Edit Case entity form by including the following line in the Custom JavaScript attribute:

{% include 'SharePointIntegration' %}

The attribute of the Document Location record I’m most interested is the Relative URL – this gives me the name of the folder in SharePoint. However, this is not the full path. To get that, we’ll have to work a bit harder, and I’ll touch on that shortly.

For now, though, if you were to include the code from above, the currentLocation variable would be null, even if you have Document Locations associated with the current case. This is because we still have to deal with permissions.

Document Location and Entity Permissions

For a record to be returned in a FetchXML query done in Liquid, you need to create an Entity Permission record, with the following values:

• Name: whatever you like
• Entity Name: sharepointdocumentlocation
• Website: Employee Self Service (or whatever your portal is called)
• Scope: We’ll set this to Global, so that we can read any Document Location.
• Privileges: Read
• Web Roles: associate with a web role as per your requirements – typically you could probably associate it with the Authenticated Users web role.

Getting the Full SharePoint Path

As I mentioned, the Document Location record that we’re querying only gives us the folder name for that particular record. We still have to determine any parent folders, and the actual URL of the SharePoint server in order to use the SharePoint REST API.

To do this, we need to look at the Parent Site or Location attribute on the Document Location. This is a composite lookup field that can point to either another Document Location record (which represents a SharePoint folder), or a SharePoint Site record (similar to how a Customer lookup can point to Accounts or Contacts). Essentially, the parent record for any Document Location associated with an entity will typically be related to at least one parent Document Location, and possibly more depending on your folder structure. Eventually, the parent of one of the ancestor Document Location records will be a SharePoint Site, which will give you the base URL of SharePoint. Therefore, by recursively going through the parent Document Location records, keeping track of each Relative URL, until you arrive at a SharePoint Site, you can determine the full URL of the SharePoint folder associated with the record.

The number of ancestor folders depends on how you’ve setup your SharePoint integration – there are two main structures:

1. By Entity: Each record gets a folder within a parent folder specific to the entity type. For example, for the Case record, within the SharePoint site there will be a folder called incident, and within that folder there will one folder per record. In this situation, your Document Location associated with your Case record would have a parent Document Location that points to the Document Location for the incident folder, and the parent of that Document Location would be a SharePoint Site.
2. Account or Contact Centric: Folders are first based on the Customer record associated with the Case, and within that folder are folders for each entity type. For example, if you went with Contact-centric folders, the folder for a Case related to John Smith would be in /Contacts/John Smith/incident/Case123. In this situation, your Document Location would have at least three ancestor Document Locations before you’d get to the SharePoint Site.

In theory you can also create custom folder structures by manually creating your own folders and mapping them to Document Locations. So we’d like our solution to be as flexible as possible.

Below is Liquid code that uses the currentLocation variable we obtained above and iterates over the parent records to determine the full URL for the folder. You’ll notice I’m using a for loop that goes from 0 to 100, since Liquid doesn’t have the concept of a While loop. This won’t work if your folder structure is more than 100 levels deep, but you can always adjust that number – that being said if your folder structure is that deep, you’ve probably got bigger problems, especially since the max size for the folder plus a file name in SharePoint is 400 characters. I’d be curious how other people work around the lack of While loops in Liquid – let me know if you have a creative solution.

{% assign url = currentLocation.relativeurl %}

{% for counter in (0..100) %}
    {% if currentLocation.parentsiteorlocation %}
        {% fetchxml nextlocation %}
        <fetch top="50" >
            <entity name="sharepointdocumentlocation" >
                <all-attributes/>
                <filter type="and" >
                    <condition attribute="sharepointdocumentlocationid" operator="eq" value="{{currentLocation.parentsiteorlocation.id}}" />
                </filter>
            </entity>
        </fetch>
        {% endfetchxml %}        
        {% if nextlocation.results.entities.size > 0 %}
            {% assign currentLocation = nextlocation.results.entities[0] %}
            {% assign url = currentLocation.relativeurl | append: '/' | append: url %}
        {% else %}
            {% fetchxml sharepointsite %}
            <fetch top="50" >
                <entity name="sharepointsite" >
                    <all-attributes/>
                    <filter type="and" >
                        <condition attribute="sharepointsiteid" operator="eq" value="{{currentLocation.parentsiteorlocation.id}}" />
                    </filter>
                </entity>
            </fetch>
            {% endfetchxml %}
            {% assign sharepointsiteurl = sharepointsite.results.entities[0].absoluteurl %}
            {% break %}
        {% endif %}           
   {% endif %}
{% endfor %}

In the above code, I start with the Document Location associated with the current Case record. I then query using FetchXML to get the parent Document Location for that record, append the Relative URL to my variable, and keep looping. If the parent Document Location query returns no records, that means the parent isn’t a Document Location, but instead is a SharePoint Site. In that case, we query SharePoint Sites to get the Absolute URL, which is that last piece we need to determine the full URL for our SharePoint folder. Since we’re using FetchXML in Liquid to query a SharePoint Site, remember that you’ll need to create Entity Permissions for the SharePoint Site entity, or the queries won’t return any results.

Now we have the full path to our SharePoint folder in the url variable. In my next post, I’ll get into how we call the SharePoint REST API to get a list of all of the documents in that folder.