SharePoint integration in Dynamics 365 (D365) extendable for many ways if you wish to create multiple SharePoint sites and libraries instead of saving all of them into one library. In this business case there was problem since SharePoint library content was so huge that list view threshold were exceeded and query for documents tool many minutes. We could not get nested items in query even if we knew exact location of the folder in SharePoint library.
I have done also solutions where each organization accounts gets it’s own SharePoint site once the first account is created in D365 and each customer gets separate document library. This kind of customization contains proper metadata in SharePoint and default values when dropping files into D365 grid view (which is window to SharePoint). This blog does not tell about that customization, it tells how to tackle the list view threshold problem when query takes multiple minutes and user wants to have the window open in couple seconds. Previous post describes the base solution how to add SharePoint documents to D365 email entity.
Custom page did not show any files even we knew there were files and the folder url was correct
The opening dialog was blank in test environment. I went trough the flow run history, got the manual path for the folder and put into browser. There were multiple documents but why this did not work. Then I figured out that there were more than half million documents and Power Automate flow action did not have paging on. I put it on in test environment – by creating unmanaged layer for managed package just for the time being for testing.
With this solution I got the Power Automate flow return JSON with correct items data. I tried with threshold 5000, 10 000, 50 000 and 100 000. The 100 000 was the biggest number you could give and was fastest to run the flow – but only 20 seconds faster than 50 000 paging threshold.
Running this flow took more than 4 minutes. The user could not wait 4 minutes when adding documents into email entity in D365. Original solution gets the SharePoint items only once when opening the custom page and then navigating in folder structure was handled within Power Apps filtering the collection that was the source of the gallery component.
I needed to turn of getting nested items from SharePoint to get the query work in seconds
Then I created third Power Automate flow which gets folder contents. It gets the library id, folder path and site url. This is why I give them back in the first flow for custom page, so that I don’t need to get them again in the flow since I already did this once when opening the custom page in dialog at first place.
Then I extended the folder navigation functionality to call this third flow instead of filtering the collection inside custom page.
And now finally I had solution that works properly in the D365 UI even if the SharePoint library on the background has half a million items. The original reason to use Power Automate flow in custom page was the nested items that does not work if you add SharePoint as datasource in Power Apps. Anyway, this fix took only 10% of the time for developing the solution and I was happy and customer was happy so end of case.