SharePoint URL Shortcut on Form and Generic Dataverse Update Step in Power Automate (Part 5 of 8)

This is the fifth post in a series of Automating SharePoint Integration with Dataverse using Power Automate. You can check out the other posts via these links (Part 1, Part 2, Part 3, Part 4, Part 6, Part7, Part 8)

SharePoint integration in a model-driven app is easy to use and the user can simply view/upload the related documents in the Documents associated grid under the Related tab. But the Documents associated grid in a model-driven app has some limitations comparing the SharePoint site such as drag-and-drop to upload, uploading multiple files, downloading multiple files as a zipped file, etc. For such scenarios, the user would have to click on Open Location to open the document folder in SharePoint for more functionality.

To open the SharePoint site from the form, it takes x4 clicks for the user to expand the Related tab, choose Documents associated grid, click on the Open Location menu and choose the document folder. If the users have to go to the SharePoint site most of the time, we can enhance the user experience and reduce the number of clicks by placing the SharePoint folder shortcut on the form.

Basically, the shortcut is just the read-only text field of URL data type so that the user can click on the globe icon to open the SharePoint folder in one click.

In this post, you will learn about how to automatically populate the SharePoint document location URL of a row (record) in the custom URL column. Additionally, you will also learn how to create a generic Dataverse action step with JSON properties to dynamically update (or create or delete) a row from different types of tables with a single step.

These are all the steps included in the cloud flow for this solution. The flow will be triggered when the Document Location is created and populate the complete SharePoint folder URL to the custom URL column.

The red highlighted steps (all steps except the last one) and exactly the same as the flow in my previous post (Part 4). All those steps are required to assemble the complete SharePoint URL based on the relative URLs of the Document Location rows for both Direct Folder Structure and Subfolder Structure (read more details in steps 1-6 of the Part 4 post).

7. Update a row with SharePoint URL

Now that we have SharePoint Site, library name and the folder path, all we need is to update the custom URL column of the related row (record) that the Document Location was created. First of all, check the table type of the Regarding column of the Document Location from the trigger using the following property which returns the Entity Set Name (plural version of the logical name) of the table. (Do not use "Regarding (Type)" from the dynamic value list because it is not working for the trigger).

triggerOutputs()?['body/_regardingobjectid_type']

Then, the SharePoint URL value can be updated to the table based on the table type of the Regarding.

But if there are dozens of tables enabled for the SharePoint document integration, we need to create dozens of Update a row steps in each Case step. To simply the process, create the custom URL column in all tables with the same logical name (e.g. lzw_documentlocation) and update with a single step using dynamic table name.

This is similar to how we set the custom value in Action Name of "Perform a bound action" and "Perform an unbound action" steps instead of choosing from the list. By setting the custom value of the Table Name in "Add a new row" or "Update a row" steps, the flow designer does not recognise the table name and instead of showing all columns of the table, there will be only one single multiline textbox (Row Item) and you need to populate it with a JSON object.

The value of the Table Name needs to be an Entity Set Name (plural) and we can simply use the _regardingobjectid_type from the trigger since the value of that property is Entity Set Name too. The Row ID is the same as the normal update step. In the Row Item, populate the value in JSON. For our scenario, we only need to update one column (lzw_documentlocation) so that the JSON object will contain one property with one value (concatenated SharePoint URL + Library + Folder).

To create/update a row with multiple column values, you can provide the JSON object like this. More samples can be found in Microsoft Docs.

{
    "name": "Sample Account",
    "creditonhold": false,
    "address1_latitude": 47.639583,
    "description": "This is the description of the sample account",
    "revenue": 5000000,
    "accountcategorycode": 1
}

This approach might not be citizen developer-friendly but understanding a JSON object is part of building advanced cloud flows.

Summary

Setting the SharePoint URL to the custom column of a related row is simple but it can be simpler by using generic Dataverse action step and dynamically updating a row from different types of tables with a single step.