Getting more out of SharePoint and the Power Platform by using a library with meta data tagging.
Overview
There are many ways to integrate SharePoint with the Power Platform/Dynamics 365. The most common method I come across in my client work and on the web, involves automatic creation of folders related to a record, like a contact. The idea is rooted in how most professionals grew up using file storage where folders are nested within folders to categorize and sort. This is intuitive and was the natural step taken by earlier developers in the 1980’s who simply digitized real world paper filing cabinets.
While this is familiar, it is greatly flawed in that it requires training and familiarity with the file structure to store and locate documents. Anyone who has ever had to train a new hire on the company filing system, knows that without discipline across the organization, filing records can quickly become an out-of-control mess, slowing response time, and losing historical knowledge.
Enter the properly utilized SharePoint Library. This series of blog posts will outline a structure and series of techniques to store documents in a way that anyone can use search or filtering to find all relevant files, without learning how to save files or properly label them. Using a combination of Power Automate flows and a properly structured library, I will demonstrate a modern way to keep company records in check throughout an organization with minimal effort and maximum speed.
Part 1: Structuring your library
When you first crack open a freshly provisioned SharePoint site, the lack of structure can be intimidating. Too often I see companies simply begin to recreate the traditional nested folder structure, which at first retains some semblance of discipline and eventually digresses to a free for all with every department/staff member growing their corner of the system as they see fit. There will always be a place for folder storage, but this series is about effectively connecting CRM files en masse, not one-off departmental memos or SOPs. Most organizations end up with the bulk of their file storage going to a few types of documents that can be directly connected to something in their CRM, examples include patient records, sales documentation, repetitive reports, invoices, quotes, etc… A typical workflow is that these documents somehow originate from the CRM system and need to be stored in an easily accessible place. Perhaps a quote is generated by a salesperson and stored in connection to the opportunity record, or a contact emails a user an important PDF that should be saved for future reference.
When first setting up SharePoint, I recommend creating a new Document Library plus a folder to hold document templates. But first, it is important to take stock of what types of documents will need to be stored as the company may be best suited to having multiple libraries if there is minimal overlap between different departments. In the following examples, I will refer to a client of mine that is managing patient records with the primary relationship being to a contact record and secondary connections to their account. I created a singular “PDF Library” and another folder to hold document templates. You can create as many Libraries as make sense for the business case, but to keep things simple I will only refer to this one.
Step 1 – Create Template Folder
Creating the template folder is straightforward, it should sit at a top level in the SharePoint site and later on I will discuss creating and keeping Word templates in here so that Power Automate can pass data through them and create nice looking documents to store in the library. Here I just added a new folder called ‘TEMPLATES’ to my SharePoint Documents:
Step 2 – Create Library
Next, add a new Document Library by going to the home area, clicking New > Document Library.
This is where things start to differ from the traditional way I have seen organizations store their records. We will not create any folders, rather this is the first step towards creating a sortable, searchable document database. It will look more like Excel than File Explorer.
Step 3 – Create Human Readable Columns
Next, we will click the “Add column” button so we can store relevant meta data about the documents. In our example of storing patient reports, these columns could include patient name, report type, facility name, doctors name, and date of treatment. These columns are for adding information that a human might know as they are searching for relevant information using the top center search bar. Using the native SharePoint search a user can quickly pull all reports written by a given doctor, or any connected to a particular facility – no folders required.
Step 4 – Create Computer Readable Columns
Second, we need to add columns that will store meta data for the computers to read. Example columns include ‘Contact GUID’ and ‘Facility GUID’. These are critical for retrieving documents accurately and are required for the rest of the technique to work. I also highly recommend adding a column called ‘dataURI’ which we will use to retrieve documents in a canvas app with the PDF viewer.
*When creating columns, pay attention to the type of data and the maximum number of characters. GUIDs are a set length test type and most named fields can reasonably be assumed to be less than 200 characters, but this is up to the judgement of the library creator. The dataURI field needs to be ‘multiple lines of text’ as it will contain a very long text string.
In the example below I have kept it simple by only adding columns for Contact Name, GUID and dataURI. The green highlighting shows where you can add additional columns.
That is it! The document library is ready to go.
Summary
The key to the success of this technique is thinking about document storage in terms of a database, rather than nested files. To that end, a database functions based on filtering columns and sorting the results, so it is critical that we add this information automatically to make for easy retrieval in the future.
The next posts in this series will talk about ways to use Power Automate flows to create and upload documents while filling in these columns so users don’t even have to think about it, but they are creating documents that will be easy for others to find in the future. Later posts will cover techniques to quickly retrieve these documents for your users. Check them out below:
Copying file from CRM field to CRM Library
