Categories
Metadata Microsoft Office SharePoint

How to ensure Word Quick Parts display Lookup value, not ID

Sometimes embedding SharePoint metadata in Word shows the ID instead of the value. This is why and how to fix it.

Microsoft Word has a fantastic feature that lets you embed SharePoint metadata in the document. This really encourages users to fill in the important metadata and lets them do it in the context of the document as they are working on it. You can also do neat things like embed a client or project name throughout a template and have all users of that be updated automatically.

All you have to do is choose Insert|Quick Part|Document Property in Word and it shows a list of all the metadata columns in the associated SharePoint library.

There is a bit of a problem with this however. Frequently your field shows an apparently random number and not the metadata you were expecting. This happens if you choose a field in SharePoint which is a Lookup column.

What’s actually happening is that your SharePoint library actually stores the ID for the item in the lookup list and that’s the number you are seeing instead of the value (Client, Project etc) that you wanted. And here is the reason why…

When you choose this type of Quick Part, Word goes to SharePoint and the Library for the full set of options for that metadata column. The Library then gets this from the Lookup List. However, it does this based on the default view for the List. If your default view in the List does not contain the column you are trying to lookup it can’t complete the action and so just shows the item ID instead.

The fix is relatively easy, once you know this. Just make sure that the default views in the List has any column headings that you might want to embed in your document. The good news is that it’s retroactive – so you can always go back and add extra columns to the view when you need it.

 

So that’s it – just check your lookup lists have the correct columns in the default view. Then crack on and get everyone to fill in the document metadata as they create the document. Of course, they need to have saved the document into the SharePoint library first (even better if they create the documents from within the library in the first place).

By Simon Hudson

Interests: Knowledge management; Information Architecture; Flexible working technologies

Passions: Physics, music, classic cars

Aspirations: To drive a V8 Vantage to the Amalfi Coast; to play guitar to a crowd of 1000+; to ski more than once a year; to make a difference

Background: From teaching to quality assurance, technical development to international marketing and from business development to business start-ups, Simon has flitted, butterfly like, learning from each experience and bringing that breadth to his client facing and business development activities. Simon is articulate, opinionated, understanding and suffers from an insatiable curiosity.

9 replies on “How to ensure Word Quick Parts display Lookup value, not ID”

Sorry, but this just doesn’t work in Word 2016 and SharePoint Online/365. Even with a default list view with only the single column you want to populate the word document property field with, still results in Word recovering the list item ID instead of the desired item column content.

I have the exact same problem – I used this fix and it worked for about a week. Now regardless of the columns in the default view it reverts back to showing the ID.

Any Update please regarding to this subject, i am usinf word 2013 and i gave the same issue, it was resolved and come back again

Same problem here but with MS Office 365 Pro Plus and SP Online – intermittent failure, feature is very flaky. Occasionally works, but more often than not it breaks.

The specific proposed solution does not work.

Hi there, want a fix?

Go to the lookup list from which the lookup field originates.

Find the default view… edit the view.

Scroll to the bottom and increase the item limit to a number that is more than the no of items in your lookup list (up to a max of 5000).

Next time you create a document it should show the Lookup Value, not the ID.

I think this is what the OP was trying to convey. It’s not the list view, it’s the amount of items shown in the list view. Word looks at the default view (which could be set as low as 30 items if you haven’t changed it) and if it doesn’t see it, it blurts the ID number instead.

As for whether or not this works in all versions, we used it on Word 2016 with SharePoint Online and it seems to be fine.

Thanks, Andy

Thanks Andy – that fix works, at least for the moment, while the list remains below 5000 items. Any suggestions for a solution when the list goes over that limit?

Hi Liam, The options in a Lookup Column cannot exceed 5000 items because of SharePoint Online’s view limit, although I know that indexing the particular column can resolve this, it is flaky and I think has not been solved in terms of the database model.

I believe the Modern list view can view more than 5000 items because it uses paging, it’s perhaps worth trying the modern view of the list to see if Word responds and reads over 5000 items (but I doubt it will as Quick Parts are a ‘classic’ technology).

Personally I make sure my architecture doesn’t require a lookup to access more than 5000 items, by either splitting the lookup into a cascade between two separate columns and then only looking up the ‘child’ column in my word document. You can try this or populating a text column with the lookup value using Power Automate.

There aren’t any easy answers to this as I don’t think the designers ever envisaged Lookups and Quick Parts being such a vital technology. The other option is to deploy a custom Javascript solution to pull an external library in like DocXTemplater and make some custom templates to read the list data instead of relying on content type templates.

There is also the possibility that newer technologies like Power Automate and the new Microsoft Lists will incorporate the document templating facilities that have been lacking in SPO but I’m guessing they will be pretty far off in terms of roadmap as we are only just starting to see Lists coming in now.

Hope this helps in some way.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s