How to Add Metadata to SharePoint Document Subgrid in Dynamics 365 / Model-driven Apps

As mentioned in the Dynamics CRM Tip Of The Day Tip #1185, you can customise the SharePoint document subgrid for server-side integration. At the time of publishing that post, the view was not customisable via solution explorer yet but in this forum answer, the Document Associated Grid of the SharePoint Document entity can now be editable via solution explorer.
 

Default Document Subgrid

These are the list of columns that you can add to the default Document Associated Grid of Model-driven Apps in Dynamics 365.
SharePoint document columns


But what if you want to add another SharePoint column that is not available in the list? (e.g. Version of the document)
What if you have created a new custom column in the SharePoint library and you want to show that that in the documents subgrid?

The good news is that you can show those columns by adding a new column to the Sharepoint Document table. I got this tip from one of my colleagues, Chadi, who shared with me how he added the Version column to the SharePoint document subgrid.

In the following sample SharePoint library, I have added the Version column and two other custom columns. One of the custom columns is a Single line of text column and the other one is of Number data type.
Sharepoint Columns

To add these columns to the SharePoint document subgrid, create a new field in the Sharepoint Document entity with the same Display name and add it to the Document Associated Grid view.
New Field for Document


🛈 Important 

The Display Name of the new field needs to be exactly the same as the column name in the SharePoint library. The logical name or prefix of the field can be anything. The Data Type of the fields needs to match with the SharePoint column data type except Choice and Yes/No data types (refer to the Update 3).

This is the final result of the SharePoint document subgrid after adding two Text fields (Custom Column & Version) and one Whole Number field (Custom Number).

Document Subgrid with Custom Columns


Update 4: The Lookup data type SharePoint columns are not supported in the Documents subgrid.
As a workaround solution, create a "Single line of text" column in the SharePoint document library, copy the lookup column text value into that new column using a Power Automate cloud flow, and show that new column in the Documents subgrid by adding a new column to the Sharepoint Document table with the same name.

Update 3: To display Choice and Yes/No data type SharePoint columns in the Documents subgrid, create the columns with the "Single line of text" data type instead of the equivalent data type. Otherwise, the subgrid loading fails with an "Invalid Argument" error.


Update 2: (Solution for the Update below) Jahangir has commented that he also encountered the same problem for translations against custom columns in SharePoint and he resolved with the following solution.
Export the translation against alternate language from SharePoint, you will get a .resx file. Change the labels accordingly. Also, export the translation from Dataverse against SharePoint document entity and add values for your alternate language over there. One thing to be very careful of is to name the display name of the field as your column name in SharePoint.


Update: One of the readers had a question that "What if my environment is used in different UI languages? Will it only work for the base language?"
Refer to the solution in Update 2 above.
I tried it out in my environment by installing the German language and added the German translation of a couple of custom columns. When I change the user's language to German and opened the Document Associated Grid, those columns with translated Display Name no longer shows the SharePoint custom column anymore (as highlighted in red in the image below). That is because the display name of the custom column is not the same as SharePoint column anymore. (it might be working if you can also localise the SharePoint custom column too but I have not tried that yet). Otherwise, the suggestion is not to add the translation for the custom columns and just show them in the base language.


Comments

  1. Hi Linn,
    thank you for the hint.
    You write the displayname in CDS must be the same as the column name in Sharepoint to get it working.
    Question:
    What if my environment is used in different UI languages? Will it only work for the base language?

    cheers Lars

    ReplyDelete
    Replies
    1. Hi Lars,

      Sorry for the late reply
      I tried it out in my environment by installing the German language and added the German translation of a couple of custom columns. When I change the user's language to German and opened the Document Associated Grid, those columns with translated Display Name no longer shows the SharePoint custom column anymore (as highlighted in red in the image below). That is because the display name of the custom column is not the same as SharePoint column anymore. (it might be working if you can also localise the SharePoint custom column too but I have not tried that yet). Otherwise, the suggestion is not to add the translation for the custom columns and just show them in the base language.

      https://1.bp.blogspot.com/-XcE2haf4w6k/XwuN8bBk2iI/AAAAAAABrHk/vNpuW-LvV1UgUFADpd_8MUBF7rYidj_xACLcBGAsYHQ/w625-h354/Foreign%2BLanguage.png

      Delete
    2. Thanks Linn for your article. Recently, we encountered a same problem for translations against custom columns in SharePoint. The solution we did is as follows.

      Export the translation against alternate language from SharePoint, you will get a .resx file. Change the labels accordingly. Also, export the translation from DataVerse against SharePoint document entity and add values for your alternate language over there. One thing to be very careful of is to name the display name of the field as your column name in SharePoint. It worked like a charm.

      Delete
    3. Thanks for sharing your experience and the solution that you used to resolve the issue. I will update that solution in my blog post.

      Delete
  2. Hi Linn,
    thank you for the feedback. I'll keep it in mind.

    Best regards
    Lars

    ReplyDelete
  3. Hello Linn,

    Thanks for the post.

    Is there a way to add custom column (field) on the Upload Document popup on Dynamics 365?
    Also, on the custom column (field) on the Edit Document Properties popup on Dymanics 365?

    Cheers,

    ReplyDelete
    Replies
    1. Apologies for the late reply. It is not possible to customise the Document Upload popup. However, it can be achieved with the File Field of CDS and Power Automate. Check out the solution in my latest blog post.
      https://linnzawwin.blogspot.com/2020/09/sharepoint-document-upload-in-dynamics.html

      Delete
  4. Hi, thanks for posting this.

    The SharePoint field I wanted to show in the subgrid was a 'choice' field in Sharepoint (called Document Type). I therefore I assumed I would have to use an Option Set in Dynamics of the same name, however this failed with an error. I changed it to be a text field and it worked. However it's left me with one more hurdle....


    Ideally I need the user to be able to edit the metadata field (Document Type) in the subgrid. The use case I have is that documents will automatically appear in D365 subgrid as a result of an attachment on a tracked email. Users will need to categorise these documents as a particular document type type (e.g. contract, invoice), and this to be reflected back in SharePoint ideally. Is there a way for a user to edit this metadata and it update back in SharePoint?

    ReplyDelete
    Replies
    1. Hi Jules

      It is technically possible but there will be a quite an effort of development.
      You will need
      1. Custom subgird ribbon button to replace Edit Properties button
      2. Dialog/fields to let the user enter "Document Type" of the selected document.
      3. Flow to update the SharePoint file properties based on the input from step 2.
      https://i.imgur.com/UZMu3eX.png

      I've done the POC for it and I will post it as a blog in next few days.

      Delete
  5. this is great, can you tell me if it is possible to get a dialog box where the user needs to enter the Metadata, I have created 2 columns that I would like the user to edit when they upload the information.

    ReplyDelete
    Replies
    1. Hi Craig

      It is possible as my response to the comment above.

      You will need to create the custom subgird ribbon button and replace Edit Properties button of the Document table in ribbon workbench.

      For dialog box, you can use the following approach by Diana to accept the value for 2 metdata columns.
      https://dianabirkelbach.wordpress.com/2020/10/14/transform-forms-into-dialogs-and-more/

      On save of the dialog, it can triggre the clould flow which updates the SharePoint file properties.

      I stared my POC last year but never get to finish it. Hopefully, I will have some more time after the recording for DynamicsCon.

      Delete
  6. you are awesome .. ty so much! You are my hero!

    ReplyDelete
  7. Hi Linn, Thanks for the post it is really helpful. However i have question for the update 1 section. I have added two options column in SharePoint metadata and have created the same field in the sharepoint document entity with the same name but i am getting error as invalid argument when i add the column in the document associated grid. Any ideas that can help me here?

    ReplyDelete
    Replies
    1. Sure... Please message me on LinkedIn or Twitter

      Delete
    2. I have updated my blog post based on your issue.

      To display Choice and Yes/No data type SharePoint columns in the Documents subgrid, create the columns with the "Single line of text" data type instead of the equivalent data type. Otherwise, the subgrid loading fails with an "Invalid Argument" error.

      Delete
  8. Hi Linn,

    I have added Document sub-grid on the main form.

    And I want to remove the Path column from the above sub-grid.

    But after removing the Path column from the Document sub-grid, files are not showing in the grid.

    So is there any dependency on Path column?

    ReplyDelete
    Replies
    1. I guess the grid control for SharePoint documents has some dependencies on the Path column. You can hide the column without removing from the view. Check out my latest blog post on how to do it.
      https://linnzawwin.blogspot.com/2023/04/hiding-column-without-removing-from.html

      Delete
    2. Thankyou for the reply.

      I follow your blogs. They are great.

      Keep up the good work.

      Thanks!

      Delete
  9. Hi Linn,

    Is there any update on Customizing the "Edit Document Properties" popup on dynamics 365?

    Can we add a new column on the popup?

    OR

    Can we lock the fields on the "Edit Document Properties" popup?

    Any help will be appreciated!

    ReplyDelete
    Replies
    1. I want to customize the above popup using OOB.

      Is it possible?

      Delete
    2. At the moment, there is no way to customize the "Edit Document Properties" popup. One of the solution would be hiding the existing "Edit Properties" button , adding a custom page to your model-driven app and create a new "Edit Properties" command button to show the custom page as a dialog window.
      I will try post a blog post for that solution in a couple of weeks.

      Delete
  10. I have created a Column in SharePoint as Lookup type. Source for this Column is an existing List in Sharepoint. What should be the data type of the column in Dataverse. So that column is visible in Document Subgrid.

    ReplyDelete
    Replies
    1. Hi Mahendra

      I do not think the SharePoint lookup column is supported in the model-driven app Documents subgrid view. I tried with a Text column and it is showing an error for this.dataSet.getDataSetCapabilities function.
      As a workaround, please just create a "Single line of text" in your SharePoint document library and create a cloud flow to copy the lookup column Title value into that new Text column, then, show that column in the model-driven app Documents subgrid.

      Delete
  11. Hi Linn,

    Thanks for this post as this has helped me with getting my choice value for each document to appear in the subgrid. What I'm trying to do on top of this is to have different subgrid views based on the value that's being pulled in from the SharePoint column. I've set these views up but there not brining back any documents even though the All Documents view brings back all of them. Any advice on how to accomplish this would be great.

    ReplyDelete
    Replies
    1. I have tried it and it does not work for me either. I just got the error message below. I guess the FetchXml filter of the view for the sharepointdocument table is not supported.

      "You don't have permissions to create folder in this location. Contact your Microsoft Teams owner or SharePoint administrator for access."

      Delete
    2. For me I get the default "An error has occurred while loading documents. Please reload the page" error. I wish this was attainable as this would be a great way to not show all documents.

      Delete
    3. I doubt it is likely to happen because the columns are there for the display purpose and translating the FetchXml filter values to the SharePoint data filter (especially for the Choice and Yes/No data types which are not matching with the data type of the Dataverse column)

      I guess you will have to create multiple subfolders to not show all documents.

      Delete

Post a Comment

Popular Posts