Have you ever been trying to migrate documents to Dynamics 365 with SharePoint Online Document Integration? You're going to want to know what folders to put things in, but shocker (and using a badly corrupted metaphor) they're like Schrodinger's Cat, they neither exist or, does, erm, until you look at it... wait that's quantum physics... Anyway.

There's plenty of posts online about creating SharePoint Online folders for a Dynamics 365 entity and then manually creating a record in the sharepointdocumentlocation table record linking it to the newly created folder.

But considering in the normal run of things Dynamics 365 does this all by itself, the common method found online seems a bit 'hacky'. And I've seen situations where, for some reason, Dynamics 365 doesn't recognise the manually created folder and proceeds to make its own alongside it. And if you're looking to programatically migrate a bunch of documents it could get very annoying very quickly.

So how does Dynamics 365 do it?

Short answer is I have no idea. 😅 But I have a theory that's held up to scrutiny so far.

Considering when you create a new record the folder isn't automatically created at the same time, then I'm guessing it's not a workflow/process that gets triggered.

Although some folders do seemingly randomly pop into existence, even without any files in, so what's causing that?

From a bit of trial and error from the intrepid Kirky (hello 👋), simply visiting the 'Related Documents' tab of a record, somehow breathes life into SharePoint Online and folder pops into existence - mazel tov! But I assume this isn't a case of immaculate conception.

Geek Alert!

Knowing that 90% of what Dynamics 365 does, especially in a Model App, is going to be down to a Dataverse Web API call. Jumping into the F12 Dev Tools in Chrome, I decided to snoop around and see what was happening.

And I was disappointed 🙁.

I didn't see anything that to do with creating folders... Although there was a few calls to a call to api/data/v9.0/sharepointdocumentlocations (why not v9.2? - no Graham, don't get distracted) which returns the "Documents on Default Site 1". And as you'd expect visiting a page to show documents, a few calls to /api/data/v9.0/sharepointdocuments which all returned zero documents as you'd expect. Nothing special at all.

But the interesting part that caught my eye was that every time the call was made, a folder popped up if it didn't already exist. The 'Saved Query' it calls was '0016f9f3-41cc-4276-9d11-04308d15858d' - which is the "Document Associated Grid" view, with a description that says "Displays information about related SharePoint documents in the detail form of any associated entity record."

So there's something special about the query that lists the documents, that creates the folder if one doesn't already exist. Makes sense I guess.

I tested this by calling the specific FetchXML on it's own from XrmToolBox, and this time I wasn't disappointed, the folder appeared!

Here's the FetchXML, that somehow does the magic, in all it's glory - just replace the table name and record ID to suit your scenario.

<fetch distinct="false" mapping="logical" savedqueryid="0016f9f3-41cc-4276-9d11-04308d15858d" returntotalrecordcount="true" page="1" count="50" no-lock="false">
  <entity name="sharepointdocument">
    <attribute name="documentid" />
    <attribute name="fullname" />
    <attribute name="relativelocation" />
    <attribute name="sharepointcreatedon" />
    <attribute name="ischeckedout" />
    <attribute name="filetype" />
    <attribute name="modified" />
    <attribute name="sharepointmodifiedby" />
    <attribute name="servicetype" />
    <attribute name="absoluteurl" />
    <attribute name="title" />
    <attribute name="author" />
    <attribute name="sharepointdocumentid" />
    <attribute name="readurl" />
    <attribute name="editurl" />
    <attribute name="locationid" />
    <attribute name="iconclassname" />
    <attribute name="locationname" />
    <order attribute="relativelocation" descending="false" />
    <filter>
      <condition attribute="isrecursivefetch" operator="eq" value="0" />
    </filter>
      <link-entity name="loopup_carrier" from="loopup_carrierid" to="regardingobjectid" alias="bb">
        <filter type="and">
          <condition attribute="### TABLE_NAME ###" operator="eq" value="### RECORD_ID ###" />
        </filter>
      </link-entity>
  </entity>
</fetch>

And you can do that from Power Automate, with a HTTP action by appending the above FetchXML (URL Encoded) to the Dataverse Web API URL, which is nice.

https://org+domain/api/data/v9.2/sharepointdocuments?fetchXml=..

What next?

Well, once you've ran the FetchXML above, you can get the auto-generated relative folder path back out (and the sitecollectionid if you need it).

https://org+domain/api/data/v9.2/sharepointdocumentlocations?$filter=_regardingobjectid_value eq 11111111-1111-1111-1111-111111111111&$select=relativeurl,sitecollectionid

{
  "@odata.context": "https://org+domain/api/data/v9.2/$metadata#sharepointdocumentlocations(relativeurl,sitecollectionid)",
  "value": [
    {
      "sitecollectionid": "22222222-2222-2222-2222-222222222222",
      "relativeurl": "Primary Column Name_GUIDHERE",
      "sharepointdocumentlocationid": "11111111-1111-1111-1111-111111111111"
    }
  ]
}

and then, (if needed) you can get the absoluteurl from the sharepointsites table.

https://org+domain/api/data/v9.2/sharepointsites?$filter=sharepointsiteid eq 22222222-2222-2222-2222-222222222222

{
  "@odata.context": "https://org+domain/api/data/v9.2/$metadata#sharepointsites(absoluteurl)",
  "value": [
    {
      "absoluteurl": "https://prefix.sharepoint.com/sites/SiteNameURL",
      "sharepointsiteid": "1bc432e3-4cc4-ee11-9079-000d3a3355a5"
    }
  ]
}

Stick 'em together and boom.