Single SharePoint document storage place across sales process

Let’s start with the fact that I am not a SharePoint “Guru,” so I still need to learn a lot in that area. I wanted to have a single SharePoint document storage place for the sales process of Padel Vamos. The difficulties I found when working with SharePoint are the following:

• It is “entity-driven”, which means you can store a document against a Lead, Opportunity, or Quote, for example. Which means if you have captured documents against the lead, accessing that later in the process can be challenging since they do not move across. This can probably be resolved with customizations and moving folders.
• The folder name structure is the name of the entity and then the GUID of the record.

For my single SharePoint document storage solution, I was looking for a solution that can meet the following requirements:

• Define the name of the folder, excluding the GUID
• Somehow vary in the sales process depending on the site, location, or region
• Have the option to have different subfolders and their names
• Have the same folder structure within the Power Platform as well as in SharePoint

Concept of a single SharePoint document storage place

The concept of the single SharePoint document storage process is actually very simple, there is a standard sales process in place where colleagues would work from a Lead, Qualify this so it becomes and Opportunity, and then working towards generating a Quote. The solution in this concept is the new table Project Documents that is linked to the individual Lead, Opportunity, and Quote. For now, needs to be created manually, but there could be automation in place like:

• On the creation of a new lead, create the project document automatically
• On the creation of a new opportunity that does not originate from a lead, create the project document automatically
• On the creation of a new quote that is not linked to an existing opportunity, create the project document automatically

On this new Project Document table, there is a lookup to a new table called SharePoint Region. The fact that it is called a region does not mean you need to use it like this. It could be sites, countries, or whatever works for you. For Padel Vamos, it is country-based and also has different requirements for standard subfolders that need to be created.

The SharePoint Region table has two purposes:

1. Storing the SharePoint URL of the site
2. Define a folder structure and the ordering for that Region

In the scenario of Padel Vamos, a Power Automate Flow is triggered on the creation of the record. (details below) The Flow will use this information in order to create the folder structure on SharePoint.

Once the Power Automate Flow is executed, you can find the results in the Documents section of the record, as shown below within the model-driven app.

Which is, of course, based on the structure that is created on the SharePoint side, as shown below.

The Power Automate Flow that is responsible for all the magic

Below is the Power Automate Flow that is doing all the magic needed for a single SharePoint document storage process. I will explain what I have done step by step, but will only provide a few details on the steps that I think are interesting to share. If you do have questions about the other steps, feel free to leave a comment, and I will be happy to share the requested information.

• When a Project Document is created; the trigger of the Power Automate Flow
• Initialize Variable – SharePoint URL Site; a variable to store the URL of the SharePoint site so I can easily re-use it
• List SharePoint Site – Main URL; Retrieve the main URL of the SharePoint setup for your environment
• Get SharePoint Region; This is needed in order to get the name (concat of ID and Region Name) as well as the sub folder setup
• Set Variable – SharePoint Site URL; Set the SharePoint Site URL from the Region
• Create new folder – Project Document Root – This step is explained in the image below. In essence, you connect to your SharePoint site, define the List or Library, which in the example of Padel Vamos will be Project Document, and the folder path, which is a concatenation of ID and Region. The beauty of this is that you can decide what the name of the folder will be, which was one of the requirements.

concat(triggerOutputs()?['body/new_projectdocid'], ' - ', outputs('Get_SharePoint_Region')?['body/new_name'])

• Add a new document location for Project Document. In order to have the folders available on the Documents tab on your form, this is a crucial step. If you do not add the document location, you won’t see the folders.
  • Table Name: Document Locations
  • Name: This can be however you want to name this. I have used the Project Code ID
  • Service Type: SharePoint
  • Parent Site or Location: Retrieve the root parent site for the Project Document table. This blog post from Linn Zaw Win (here) was a massive help for me in order to get this right, as well as the help from my colleague Raghu Chalasani!
  • Regarding: The Project Document table, where I want everything to be linked to
  • Relative URL: Concatenation of the Project Document ID and the Name of the Region
    

concat(triggerOutputs()?['body/new_projectdocid'], ' - ', outputs('Get_SharePoint_Region')?['body/new_name'])

• List SharePoint Region Folder: Get all the folders that are configured to be created for that region
• Create new folder based on SharePoint Region Folders:
  • Site Address: SharePoint Site URL
  • List or Library: Project Document
  • Folder Path: a concatenation of the root and then the list of subfolders to be created

concat(triggerOutputs()?['body/new_projectdocid'], ' - ', outputs('Get_SharePoint_Region')?['body/new_name']) / concat(items('Apply_to_each')?['new_linenumber'], ' ', items('Apply_to_each')?['new_foldername'])

Overall, I am pretty happy with the result, but still there are a few things I would like to explore, so if you have ideas, feedback, or other thoughts on the below, please drop a comment:

1. Since my project document records are linked to the Lead, Opportunity, and Quote records, are there nice and slick ways of showing “SharePoint” related information of the Project Document record?
2. Can I have different SharePoint sites connected in this solution? Currently, I have only found ways, but then I was not able to show the documents on the documents tab on the record anymore.