United Kingdom - D365-CRM

Expand all | Collapse all

SharePoint document list for an Opportunity

  • 1.  SharePoint document list for an Opportunity

    SILVER CONTRIBUTOR
    Posted Oct 28, 2019 04:24 PM
    ​Hi all,
    I have successfully set up SharePoint integration for our v8.2 on-prem environment with SP2013 using Server-Side Sync. I now need to have a query that tells me what documents I have for my Opportunities, specifically any that have a defined bit of text in their file name. As I'm on-prem I have full access to SQL db but there is nothing in the 'dbo.FilteredSharePointDocument' view which I anticipated would be filled with lots of lovely references to all the docs that are being saved against the opportunities. The 'dbo.FilteredSharePointDocumentLocation' view has all the folders that have been set up but nothing more.

    what am I missing and how can I get hold of this information out of SQL? Any help in understanding this would be greatly appreciated

    thanks

    ------------------------------
    Robin Marshall
    Dynamics Competency Centre Lead
    Babcock International
    ------------------------------


  • 2.  RE: SharePoint document list for an Opportunity

    Posted Nov 11, 2019 04:57 AM
    Hi Robin,

    To the best of my knowledge this is not possible.  The SharePoint integration is just a view to a SharePoint Library/Folder.  Non of the actual documents are stored within Dynamics or the database.  You need to be exploring the SharePoint API and doing some form of query that manner.

    Peter

    ------------------------------
    Peter Baddeley
    Head of Sharepoint Solutions
    Baddaz Limited
    ------------------------------



  • 3.  RE: SharePoint document list for an Opportunity

    SILVER CONTRIBUTOR
    Posted Nov 18, 2019 04:05 PM
    Hi Peter, thanks for replying. It makes sense but is still surprising that there isn't any reference to the documents, would be really useful!

    Not sure how to do anything with the SharePoint API, but can have a chat to our team that manage it!

    Cheers

    ------------------------------
    Robin Marshall
    Dynamics Competency Centre Lead
    Babcock International
    ------------------------------



  • 4.  RE: SharePoint document list for an Opportunity

    Posted Nov 19, 2019 04:17 AM
    To get to your sharepoint document folders a bit of fetch like this will do it
    <fetch> 
            <entity name="sharepointdocumentlocation"  > 
              <attribute name="absoluteurl" />
              <attribute name="relativeurl" /> 
              <attribute name="sitecollectionid" /> 
              <attribute name="regardingobjectid" /> 
              <attribute name="regardingobjecttypecode" />
              <filter> 
                <condition attribute="regardingobjectid" operator="eq" value="' + currentId + '" />' 
              </filter>
           </entity> 
          </fetch>
    I've got a use case where I want the document to be seen by multiple entities so am spinning up a PCF control to show it on related entities.
    #sharepoint #PCF
    ​​

    ------------------------------
    Phil Kermeen
    Three Legs Software Ltd
    ------------------------------



  • 5.  RE: SharePoint document list for an Opportunity

    Posted Nov 20, 2019 02:30 AM
    Edited by Phil Kermeen Nov 20, 2019 03:05 AM
    Hi Robin,
    As everyday's a school day and I had to get stuck into this a bit more i found the lack of the reference to the document more and more frustrating. Long story short, i found this PCF control - https://pcf.gallery/attachment-manager/ (really cool) and wondered how it was done. Turns out the sharepointdocument entity does hold the answer - turns out it's a virtual entity (I see you're on 8.2 this is all 9.x online). If you just query without a filter (as i'm sure you've done) you don't get any results. But if you use a link entity then you get the document reference (path, etc from sharepoint) back through Dynamics. So no calling the sharepoint rest api (and having  a fight with CORS) or doing multiple retrieves on the surrounding entities. HTH!

    Fetch -
    <fetch distinct="false" mapping="logical" returntotalrecordcount="true" page="1" count="100" 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="myentity" from="myentitytid" to="regardingobjectid" alias="bb" >
    <filter type="and" >
    <condition attribute="myentityid" operator="eq" uitype="myentityid" value="24c06622-62fa-e911-a913-000d3a86d756" />
    </filter>
    </link-entity>
    </entity>
    </fetch>

    ------------------------------
    Phil Kermeen
    Three Legs Software Ltd
    ------------------------------



  • 6.  RE: SharePoint document list for an Opportunity

    Posted Jul 05, 2020 09:35 AM
    Edited by richard gasper Jul 14, 2020 04:49 PM
    i hope they add this soon

    ------------------------------
    richard gasper
    1to1 (Partner Microsoft)
    https://chatiw.website/
    ------------------------------
    -------------------------------------------