cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jzcase
Resolver III
Resolver III

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!

0 REPLIES 0

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,856)