SharePoint lists can hold real many-to-many relationships. Customers relate to many products, products relate to many customers, and the connection between them carries its own data. The recipe takes three lists and two Associated Items columns: the two entities, a junction list holding the connections, and a column on each entity showing its slice of the junction. This guide builds the classic example, customers and products linked through support calls, and explains the modeling idea that makes it work anywhere.
Relationships are the whole point
Relationships between entities are the basis of any information system. Customers relate to products. Projects relate to tasks. Employees relate to the projects they staff.
SharePoint handles the simple direction adequately. A lookup column gives you many-to-one: each support call points at one product. The Associated Items column turns that around into comfortable one-to-many: open the product, see its calls as a table inside the form. Invoice lines on an invoice. Tasks on a project. That covers most needs.
Many-to-many is the step beyond, and the step where most builders stall. A customer has calls about many products. A product has calls from many customers. Neither side owns the other. A multi-select lookup looks like a shortcut here, and it is a trap: it can say that customer and product are connected, but it cannot say anything about the connection. When did they call? About what? Resolved or open?
The real answer is older than SharePoint: a junction list. And the junction's secret is that it is not plumbing. It is data.
The junction is the interesting list
Here is the modeling insight worth keeping. In a many-to-many relationship, the connection itself is an entity with attributes.
A support call is not merely a line linking customer to product. It has a date, a description, a severity, a status. An enrollment linking student to course carries a grade. An assignment linking employee to project carries a role and an allocation. The junction list is where that information lives, and it is usually the list your process cares about most.
So the pattern is: two entity lists, one junction list with a required lookup to each entity, and an Associated Items column on each entity pointing at the junction. Each side then sees its own connections, with all their attributes, inside its own form.
Mechanically, the Associated Items column does the bookkeeping. It automatically adds and maintains the parent item's unique identifier within the child items. When a parent opens in a display or edit form, the column scans the child list for matching items and presents them in a tabular view. Relationships work within one site, and between two sites in the same site collection.
The build: customers, products, support calls
Three lists, any types. The example uses:
- Customers: a standard contacts list.
- Products: a custom list.
- Support Calls: a custom list, the junction.
In Support Calls, add two lookup columns, one to Products and one to Customers, and make both required. These two lookups are the relationship; everything else is presentation:
Next, create two views on Support Calls. Customer Lookup shows Title and Product. Product Lookup shows Title and Customer. The naming logic: when you stand on a customer, you already know the customer, so the view shows everything else.
Views matter more than they look. The Associated Items column uses them to control which columns appear inside the parent form, and you adjust the presentation later by editing the view, not the column. Filters and sorting in the view are respected. You can let users switch between several views from the form. And for large child lists, enable paging, which keeps the parent form loading fast.
Wiring the two sides
On Customers, add an Associated Items column pointing to Support Calls. Set its view to Customer Lookup, and enable adding new items in the Display form:
That display-form option deserves a pause. It means a support agent on the customer's card can log a new call without first putting the customer record into edit mode. The parent stays read-only; the children stay live. For busy reference records, that is exactly the right posture.
On Products, repeat the setup with the Product Lookup view. Two columns, one junction, both directions covered.
What using it feels like
Open a product in its display form and add support calls pointing at specific customers. Notice what you do not do: you never select the product. The system fills the product lookup automatically, because the form knows which parent it belongs to. Add calls from the customer side and the customer fills itself the same way.
The payoff shows everywhere the data appears. Every call lists under its product and under its customer simultaneously. List views expand the related calls inline, here with two products showing their call sub-views open at once:
Navigation becomes exploration. Which customers struggle with this product? Open the product. What has this customer called about? Open the customer. Same data, both questions, no reports commissioned.
Growing the model
The skeleton extends naturally once it stands.
Summaries put numbers on the relationship: a count of open calls on each product, a latest-call date on each customer, computed automatically by summary columns over the associated items. The product list quietly becomes a reliability report.
The junction records can flow values from their parents when creating them, as covered in passing values to related forms. And the same three-list shape serves every domain where two entities meet through an event: students and courses through enrollments, employees and projects through assignments, vendors and contracts through engagements, products and suppliers through price agreements. Name the connection, give it a list, hang an Associated Items column on each side.
For the full relationship toolkit, hierarchies, repeating sections, and cross-list design, see Create Relationships in SharePoint Forms. The many-to-many case is the one that makes SharePoint feel like a real application platform, because it is the case where lists stop being tables and start being a model.
Three habits that keep the model healthy
A few practices separate a junction list that ages well from one that decays.
Name the junction for what it means, not how it works. Support Calls beats CustomerProductLink forever. If the connection has a business name, your users already know it, and the list should wear it. When no name comes to mind, that is a hint the relationship may not deserve a list yet.
Keep both lookups required, always. An orphaned junction item, a call with no customer, or no product, is a row that answers no question from either side. Required lookups make orphans impossible at entry, which is far cheaper than cleaning them up at reporting time.
And let the views do the presentation work. When someone asks for extra columns in the related-items table, resist editing the Associated Items column. Edit the view. The column's configuration stays stable for years while the views evolve freely, and different audiences can get different views of the same junction without anyone rebuilding anything.
Follow those three and the model scales quietly. Ten entities and four junction lists later, the pattern still reads the same way it did with three lists, which is exactly what a data model is supposed to do.
Frequently asked questions
Add your comment
