The Trick to Using Lookup Columns with Content Types from the Content Type Hub

File this in the category of things I didn’t think would ever work. This was definitely a ‘necessity is the mother of invention’ situation: I tried a bunch of other approaches before I hit on this one.

Here’s the scenario:

We have multiple sites, where each site represents what we at Sympraxis tend to call a “unit of work.” That sounds a little consultant-y, but it’s useful shorthand. For example:

• A law firm: one site per matter (this is the example for this post)
• A property management company: one site per property
• A manufacturing company: one site per assembly process
• Sympraxis: one site per client or project

Each site has a rich information architecture defined, drawing from centrally managed Content Types in the Content Type Hub, also known as the Content Type Gallery.

The problem: local Lookup Columns, central Content Types

That all sounds lovely until Lookup Columns enter the picture. A Content Type published from the Content Type Hub is supposed to be reusable across sites. A Lookup Column, meanwhile, is a very particular little beast: it points to a specific list in a specific site. It wants to know exactly where its source list lives.

That creates a mismatch. If every matter site has its own supporting lists, then a centrally defined Content Type cannot just point to “the lookup list” in some abstract, hand-wavy way. The Lookup Column needs a real list reference, but each destination site has its own real list.

In practice, there are a few ways this can bite you:

• The Content Type can publish successfully, but the Lookup Column may not resolve correctly in the destination site.
• The destination site may have the right list, but SharePoint may not see it as the same list the column was originally configured against.
• Manually recreating the Lookup Column in each site works once, but it defeats the purpose of managing the information architecture centrally.
• Republishing the Content Type later can overwrite or conflict with site-level fixes, putting you back into cleanup mode.

That is why I assumed Lookup Columns were a bad fit for this pattern. They felt too anchored to the local site to behave well as part of a centrally managed Content Type. I tried a few other approaches, didn’t like any of them, and eventually realized the answer was not to fight the local nature of Lookup Columns, but to change my thinking.

Before getting to the sequence that worked, it’s worth explaining why I still wanted Lookup Columns in the first place.

A Digression about Lookup Columns

I used to use Lookup Columns a LOT, back when we had hierarchical sets of subsites in large Site Collections. I really missed them when we moved to the flat topology of modern SharePoint sites (which are really Site Collections under the covers).

Benefits of Lookup Columns – especially over Choice Columns:

1. If you change the Title of an item in the lookup source list — usually the value displayed by the Lookup Column — that change shows up anywhere the item is referenced. Choice Column values do not behave that way, so changed choices can leave you with “orphan” values.
2. You can “pull through” other column values along with the specific Lookup Column value. As an example, you might have a list with information about your offices. The Lookup Column looks up into the Title of that list, which contains the office name. But you may also want to drag along the office address and phone number, which are separate columns in the list. That’s one of the Lookup Column capabilities.
3. We can use Content Types in the source lists for Lookup Column values, which brings even more rigor into our information architecture.

In modern SharePoint, we end up using Managed Metadata columns instead of Lookup Columns. With Managed Metadata columns, we get benefit #1 above but not benefit #2. I’ve long wanted a way to do #3 in the Term Store. Wouldn’t it be cool if we could mash together the concepts of Content Type and Term Sets so we could have predictable IA and associated values for every term where we need them? But I digress…

The solution: choreograph the local pieces

The breakthrough was simple once I stopped trying to make Lookup Columns behave like something they are not. Lookup Columns are local. Fine. The trick is to make sure the local things they need are there at the right time.

There are really two places where this matters: the Content Type Hub and each destination site. In the Content Type Hub, the source lists have to exist before you can define the Lookup Columns. In the destination sites, matching local lists have to exist before the published Content Types are applied.

The sequence looks like this:

1. Create the lookup source lists in the Content Type Hub. You need these lists in the hub first, because otherwise you cannot define Lookup Columns which point to them.
2. Define the reusable information architecture centrally. Create the Site Columns and Content Types in the Content Type Hub, including the Lookup Columns that belong on those Content Types.
3. Provision the destination site. Create the site that represents the unit of work, such as the matter, property, assembly process, client, or project site.
4. Create matching supporting lookup lists in that destination site. The local lists need to exist before the published Content Types are added to libraries or lists in the site.
5. Publish the Content Types after the local lookup lists exist. Once the lists are present, the Lookup Columns have something concrete to bind to in the destination site.
6. Only then add the Content Types to the target libraries or lists. This keeps the site-level implementation aligned with the centrally managed Content Type definition.

In short: create the lookup lists in the Content Type Hub so the Lookup Columns can be defined. Then create matching lookup lists in each destination site before applying the published Content Types. The hub gives you the reusable model. Provisioning gives each site the local plumbing it needs.

The important part is the order of operations. If the source lists do not exist in the Content Type Hub, you cannot create the Lookup Columns there in the first place. If the matching lists do not exist in the destination site, SharePoint has nothing local to resolve against when the Content Type shows up.

This is where being fussy about information architecture consistency pays off. The supporting lists should be provisioned predictably, with stable names, URLs, and fields. The more deterministic the provisioning process is, the fewer little SharePoint gremlins you invite into the room.

In this case, we’re using Orchestry for provisioning, which helps a lot. Orchestry uses a modified PnP Provisioning under the covers, and I know those mechanics pretty well, so the provisioning side of this does not scare me. The important part is getting the Lookup Columns set up correctly in the Content Types and lists or libraries in the Live Template site. Once that is right, the site provisioning machinery should be able to do what it does best: repeat the pattern consistently.

By the way, if you haven’t looked at Orchestry – especially for provisioning in this kind of scenario – you really should. I’m happy to talk about it with you if you’re interested.

Once I started treating this as an orchestration problem rather than a column-definition problem, the whole thing made a lot more sense: publish the common model centrally, provision the local dependencies first, and then let the Content Type land in a site that is actually ready for it.

Conclusion: central model, local plumbing

The point here is not that Lookup Columns suddenly become easy when the Content Type Hub is involved. They do not. They are still Lookup Columns, and SharePoint will still remind you of that fact if you get too optimistic.

But they can work in this pattern if you respect the boundary between the centrally managed model and the local structures each site needs. The Content Type Hub defines the standard and careful provisioning prepares each site to receive it.

As usual with SharePoint, the magic is not really magic. It is mostly doing things in the right order, with enough consistency that SharePoint does not have to guess what you meant.

Whoever designed the original architecture of sites, lists, libraries, and information architecture syndication was way ahead of their time. This solid architecture has paid off time and time again. Copilot can use it to make sense of our content, but without that great IA structure underneath, it wouldn’t know what it was looking at.