cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jzcase
Level 8

Dynamics Sharepoint Integration - Getting documents to talk to CDS records through Power Automate

My organization uses Dynamics and we wanted to use Sharepoint Integration to associate scanned PDFs with CDS records.

That was easy enough to set up the normal way, but we wanted to tag those PDFs in Sharepoint using custom columns, and have the CDS record respond by pulling data from those columns.

I was successful! But it was not straightforward. Let me share with you how I did it and provide some useful JSON expressions to make it happen.

1st Lesson: The "SharePoint Documents" entity doesn't actually store SharePoint properties.

As someone who just implemented SharePoint Integration for the first time, I was at first fooled into thinking that the entity "SharePoint Documents" literally contained a copy of SharePoint document properties. Nope. That entity is useful for customizing views for subgrids, but outside the context of a subgrid, it doesn't contain CDS records of its own and you can't run Flows against it.

Instead you need to run Flows against the SharePoint Document Library directly.

2nd Lesson: Give your Document Library a column to store documents' CDS records' GUIDs.

The "SharePoint Documents" entity has a lookup field, "Regarding," that allows the SharePoint Integration to work. But in SharePoint itself, there is no column with the GUID of the parent CDS Record. So we need to create one.

In the SharePoint Library, create a column called "CDS Record" or something, and use Automate to populate it with it's CDS record GUID. We can pull it from the file's Path property, and do some string functions to put the dashes back into it.

The flow is super simple: When a file is created (properties only) -> Update file properties

Then in your new CDS GUID field, use this expression to take the file path, extract the GUID, and insert the dashes into it.

 

concat(substring(last(split(triggerOutputs()?['body/{Path}'],'_')),0,8),'-',substring(last(split(triggerOutputs()?['body/{Path}'],'_')),8,4),'-',substring(last(split(triggerOutputs()?['body/{Path}'],'_')),12,4),'-',substring(last(split(triggerOutputs()?['body/{Path}'],'_')),16,4),'-',substring(last(split(triggerOutputs()?['body/{Path}'],'_')),20,12))

 

 That's pretty much it! You can now have Flows that take a SharePoint Document and update its CDS Record by simply referencing the GUID column you created.

But there was a problem with this setup, which is that I couldn't easily use it with an Instant workflow trigger.

3rd Lesson: Finding a workaround for SharePoint Instant workflows

I wanted my Flow to do a few things besides just trigger changes in CDS records, like automatically rename the file. Trouble there is that any SharePoint Flow that triggers "When a file is modified" and also modifies its own properties creates an infinite loop. The SharePoint Connector "When a file is modified" trigger doesn't have a "Filtering attributes" option like the CDS connectors do, so it triggers on any change to any property.

My solution to this is not to use "When a file is modified" but instead to use a manually triggered Instant flow.

But that raised a separate problem. SharePoint Instant workflows only work when they're in the Default Environment. That's a problem for me because my licensing will only cover premium workflow connectors if they're in my Dynamics environment.

So somehow I need my Default Environment SharePoint workflow to have no premium connectors, but also to trigger my Dynamics Environment workflow that updates the CDS record.

Here is how I set it up:

  1. User selected a document in SharePoint, sets some fields, and then manually triggers "Update CDS" workflow from SharePoint.
  2. The "Update CDS" workflow does a few things, among them renaming the document, which it does by copying the document and deleting the original.
  3. After it copies the document, it updates a new boolean column, "Submitted to CDS" with a 'true' value.
  4. I have a second workflow in my Dynamics Environment, that triggers "When a file is created". This is the workflow that actually does CDS stuff, but only if the SharePoint Document's "Submitted to CDS" property is true.

It's a little janky, but all my users see is that they can "Update CDS" from a file in Sharepoint, and the file gets renamed to fit conventions, and all of the data they just entered goes to the right places in CDS!

Helpful resources

Announcements
firstImage

Better Together Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,004)