Starting to see a few requirements coming up from clients around surfacing documents in sub grids or being able to access documents through Embedded Canvas Apps for SharePoint from Dynamics 365 CE records.
Thought I would try implementing this through the use of Flow and specifically the OOTB SharePoint connectors. The connector I’m going to be using is the “Send HTTP Request to SharePoint” which allows to construct a custom request.
Essentially the process will involve the normal OOTB SharePoint integration with Dynamics 365 and will have the following steps:
- Get GUID of current record through the form integration data
- Query for the relative URL stored on the document location record related to the primary record (using Dynamics 365 connector, as at time CDS didn’t have document locations)
- Construct an HTTP request to SharePoint to retrieve the list of documents
- Return the Array of documents to the PowerApp
I’m using an Embedded Canvas App which is on a Dynamics 365 form. This will take the GUID of the record and pass this to the Flow to retrieve the SharePoint Documents. Below is the completed Canvas App on the form.
Let’s Begin
I’m going to start with the call from PowerApps to Flow and then look at how to get the list of documents from SharePoint.
From the PowerApp I have setup a timer to end after a time interval. This call the Flow in question and pass the GUID. I’m using the form integration data to get the contact GUID and passing that to Flow.
The GUID being passed is used to query back into Dynamics to retrieve the SharePoint URL. This is done using a PowerApp parameter to pass to the GUID to the filter query of the Dynamics 365 Connector.
The above query will return a list of records so I filter out to get only the first. I use the relative URL to build the API call to SharePoint. If you do a google search you can learn more regarding the SharePoint API. Essentially I’m calling a GET to the GetFolderByServerRelativeURL function to return back an array of files in the folder.
This is where it gets a little interesting as you can see below I hard code the contact folder and pass the relative URL retrieved from the document location query using body(‘Get_Loc’)[‘value’][0]?[‘relativeurl’] as parameters. Going forward this would need to be configurable to each entity type.
The API call will return an array of documents to be passed back to the PowerApp using a Response action. Worth performing a test so a sample can be used to generate a schema.
Back in the PowerApp designer it is simply a matter of binding the Gallery to the collection retrieved back from Flow. I have hard coded the SharePoint URL so think about providing a configuration for this.
The Edit button is to implement a way to edit document meta data which I will blog about soon.
Implementing all the above the Embedded Canvas App can now pull in the SharePoint documents onto the form.
Check out an old post on Embedded Canvas Apps: https://the365hero.com/blog/2018/12/24/embedded-canvas-apps-for-dynamics-365/
Hello 365hero,
Were you able to retrieve the lookup value from the contact in the embedded canvas app?
Like the GUID of the parentcustomerid of the contact?
Thanks
Uday
Hi Mate,
From what I can see you might need to do a Lookup formula to the account and contact entities with the guid from the Customer lookup.
Try this:
If(ThisItem.’Customer Type’ = “accounts”, LookUp(Accounts, accountid = GUID(ThisItem.Customer), ‘Account’), LookUp(Contacts, contactid = GUID(ThisItem.Customer), ‘Contact’))
Cheers,
Josh
Noticed the (+) symbol at the top of your app and wondered if you have been able to implement adding documents using the (+) through your canvas app?
Hi Jason,
Late reply but yes that was on my radar to implement. I also experimented with document meta data as well. This is always a requirement to be able to tag documents.
I have followed every step that is mentioned above, but I am not able to get the documents in the collection.
This is the formula which I have used to call the Ms flow:
ClearCollect(AllDocuments, ‘Retrievedocumentforeachrecord.’.Run(BrowseGalleryObjectives.Selected.Objective))
And also I am not able to bind the gallery as I am not getting the ServerRelativeUrl.
Hi Triasha,
Sounds to me like you need to specify a sample payload to generate the schema to enable the binding in the Response action back to the PowerApp. If you run a test and get the list of documents from SharePoint you can use the output to generate the Schema. Here is the response body JSON Schema in my flow. Hope it helps.
{
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“__metadata”: {
“type”: “object”,
“properties”: {
“id”: {
“type”: “string”
},
“uri”: {
“type”: “string”
},
“type”: {
“type”: “string”
}
}
},
“Author”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“CheckedOutByUser”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“EffectiveInformationRightsManagementSettings”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“InformationRightsManagementSettings”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“ListItemAllFields”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“LockedByUser”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“ModifiedBy”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“Properties”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“VersionEvents”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“Versions”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“CheckInComment”: {
“type”: “string”
},
“CheckOutType”: {
“type”: “integer”
},
“ContentTag”: {
“type”: “string”
},
“CustomizedPageStatus”: {
“type”: “integer”
},
“ETag”: {
“type”: “string”
},
“Exists”: {
“type”: “boolean”
},
“IrmEnabled”: {
“type”: “boolean”
},
“Length”: {
“type”: “string”
},
“Level”: {
“type”: “integer”
},
“LinkingUri”: {
“type”: “string”
},
“LinkingUrl”: {
“type”: “string”
},
“MajorVersion”: {
“type”: “integer”
},
“MinorVersion”: {
“type”: “integer”
},
“Name”: {
“type”: “string”
},
“ServerRelativeUrl”: {
“type”: “string”
},
“TimeCreated”: {
“type”: “string”
},
“TimeLastModified”: {
“type”: “string”
},
“Title”: {
“type”: “string”
},
“UIVersion”: {
“type”: “integer”
},
“UIVersionLabel”: {
“type”: “string”
},
“UniqueId”: {
“type”: “string”
}
},
“required”: [
“__metadata”,
“Author”,
“CheckedOutByUser”,
“EffectiveInformationRightsManagementSettings”,
“InformationRightsManagementSettings”,
“ListItemAllFields”,
“LockedByUser”,
“ModifiedBy”,
“Properties”,
“VersionEvents”,
“Versions”,
“CheckInComment”,
“CheckOutType”,
“ContentTag”,
“CustomizedPageStatus”,
“ETag”,
“Exists”,
“IrmEnabled”,
“Length”,
“Level”,
“LinkingUri”,
“LinkingUrl”,
“MajorVersion”,
“MinorVersion”,
“Name”,
“ServerRelativeUrl”,
“TimeCreated”,
“TimeLastModified”,
“Title”,
“UIVersion”,
“UIVersionLabel”,
“UniqueId”
]
}
}
This is the schema which I have used
{
“type”: “object”,
“properties”: {
“d”: {
“type”: “object”,
“properties”: {
“results”: {
“type”: “array”,
“items”: {
“type”: “object”,
“properties”: {
“__metadata”: {
“type”: “object”,
“properties”: {
“id”: {
“type”: “string”
},
“uri”: {
“type”: “string”
},
“type”: {
“type”: “string”
}
}
},
“Author”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“CheckedOutByUser”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“EffectiveInformationRightsManagementSettings”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“InformationRightsManagementSettings”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“ListItemAllFields”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“LockedByUser”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“ModifiedBy”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“Properties”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“VersionEvents”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“Versions”: {
“type”: “object”,
“properties”: {
“__deferred”: {
“type”: “object”,
“properties”: {
“uri”: {
“type”: “string”
}
}
}
}
},
“CheckInComment”: {
“type”: “string”
},
“CheckOutType”: {
“type”: “integer”
},
“ContentTag”: {
“type”: “string”
},
“CustomizedPageStatus”: {
“type”: “integer”
},
“ETag”: {
“type”: “string”
},
“Exists”: {
“type”: “boolean”
},
“IrmEnabled”: {
“type”: “boolean”
},
“Length”: {
“type”: “string”
},
“Level”: {
“type”: “integer”
},
“LinkingUri”: {},
“LinkingUrl”: {
“type”: “string”
},
“MajorVersion”: {
“type”: “integer”
},
“MinorVersion”: {
“type”: “integer”
},
“Name”: {
“type”: “string”
},
“ServerRelativeUrl”: {
“type”: “string”
},
“TimeCreated”: {
“type”: “string”
},
“TimeLastModified”: {
“type”: “string”
},
“Title”: {},
“UIVersion”: {
“type”: “integer”
},
“UIVersionLabel”: {
“type”: “string”
},
“UniqueId”: {
“type”: “string”
}
},
“required”: [
“__metadata”,
“Author”,
“CheckedOutByUser”,
“EffectiveInformationRightsManagementSettings”,
“InformationRightsManagementSettings”,
“ListItemAllFields”,
“LockedByUser”,
“ModifiedBy”,
“Properties”,
“VersionEvents”,
“Versions”,
“CheckInComment”,
“CheckOutType”,
“ContentTag”,
“CustomizedPageStatus”,
“ETag”,
“Exists”,
“IrmEnabled”,
“Length”,
“Level”,
“LinkingUri”,
“LinkingUrl”,
“MajorVersion”,
“MinorVersion”,
“Name”,
“ServerRelativeUrl”,
“TimeCreated”,
“TimeLastModified”,
“Title”,
“UIVersion”,
“UIVersionLabel”,
“UniqueId”
]
}
}
}
}
}
}
Have you tried deleting the flow connection from the PowerApp and adding again?
Thank you for your help. It is working after I deleted the flow connection.
Hi, this is a really interesting topic and I was very excited to test this functionality.
I have created the app, the collection, setup integration with sharepoint on my trial, I created the flow from the app, etc. All seems to be working when I look at the flow history, including the inputs an outputs (which proves that Flow is triggered with right parameters). However, I do not see any data returned to my collection.
The output from the “Answer” step seems to be correct and according to the schemas that you and Trisha discussed, but still I get nothing returned.
Am I missing something? One thing I noticed different from your syntax is that on my trial (in swedish) it is using following syntax:
ClearCollect(Samling1;’PowerApp->SvarapåenPowerAppellerettflöde,Villkor,Listposter…_8′.Run())
A kind of strange name for the Flow, i agree, but nevertheless, it seems to be triggered.
Thanks in advance!
Ozzie
Hallå
Without knowing too much on your flow, I would be expecting you to pass the GUID of the record over. Something like this ClearCollect(Samling1;’PowerApp->SvarapåenPowerAppellerettflöde,Villkor,Listposter…_8′.Run(GUID of Record))
Hi again, in the Run methond, I have my recordid, for some reason it was not displayed here. Actually it takes the content of @ModelDrivenFormIntegration, but for my test purposes I chose a record.
‘PowerApp->SvarapåenPowerAppellerettflöde,Villkor,Listposter…_8’.Run(recordId)
Thanks again
I’m assuming you have the response action all set up with the schema. You might need to delete the Flow connection from the PowerApp and re-add. The PowerApp should read the schema and setup everything for the binding to the collection.
Hi thanks for your answer. Removing and adding the Flow helps to a certain extend.
I get an empty Collection with the columns like Name, url, etc. Teh Flow historey indicates values returned.
I suspect there is a difference in teh Sharepoint step. I see your answer uses d.result from Sharepoint. I do not have that available, in only have “Body” returning from Sharepoint, whose icon also looks different. Maybe updated since this was published?
Thank you!
Hi again,
I finally managed to get my data in the collection. Thank you so much! This is great!
Lots of new possitbilities 🙂
BR
Ozzie
So many possibilities. I also got working the ability to update document metadata from the PowerApp as well 🙂
Hi, this is a great article but do you know if we can do this with a custom entity? I am having trouble even embedding the canvas app on a custom entity form. Have you tried this with a custom entity that does not support showing the documents area in the main form?
Hey, yes embedding should work on custom entity. Are you using UCI?
Thank you for your prompt response.
Yes, I am using UCI. When I click on the “Customize” button on the Canvas App control in the form I am redirected to a canvas app but the canvas app has a form and not a gallery . So I added a gallery, and set the datasource of the gallery to filtered sharepoint DS with the matching condition set to find the name of the CRM record to be a part of the full path of the file. I can see the file populating correctly and I have a next button set for each record returned. But I cannot figure out how to open the word file in a browser directly. If I use the full path, it downloads the file but does not let me edit it.
Not sure what I am missing here.
Nevermind, I figured out how to do it. Launch(ThisItem.’Document ID’) does the trick. 🙂
Hi!
one thing I noticed is that I get files returned, but not folders in the returned collection. Would you happen to know why?
Yes the SharePoint call is for files only. Check out the SharePoint API to return folders something like: getFolderByServerRelativeUrl