Reference other sharepoint lists into a l

Copper Contributor

I have several sharepoint lists and I am wanting to create a list pulls in columns from other lists. Is this possible?

2 Replies

Hi@ColbyT82,

you have two choices to aggregate data from multiple lists

1) Lookup Fields: If you have two lists with a "1:1" or "n:1" relation.
If you i.e. have a list Orders and a list Products.

tables.png

 


Then you can create a lookup column on the list Orders to the list Products. With that column you can pull additional columns (Here the "Price" column) from the Products table into the Orders table.

tables2.png


2) Search Results: If you have multiple lists with the same columns and you want to aggregate all rows from all lists into one.
tables 3.png
This does not work out of the box. You have to use the SharePoint Search and a quite complicated setup to do so:

a) You need to create all Columns you want to aggregate as "Site Columns" and not "List Columns".
So all columns need to be created through "Gear"->"Site Information"->"View All Site Settings"->"Site Columns"
B) Then go to "Gear"->"Site Information"->"View All Site Settings"->"Site Collection Administration"->"Search Schema".
c) For each of the columns you want to aggregate select a "RefinableString" / "RefinableNumber" / "Refinable..." Managed Property that is not in use
d) Add the Crawled Property for your Site Column to that Managed Property

e) Remember which property you chose for which property
f) Go to the settings of each list "In the list"->"Gear"->"List Settings"->"Advanced Settings" and press "Reindex list"
g) Install the PNP Modern Search Webparts 
h) Create a new Site Page and add a "Search Results webpart"
i) Select "SharePoint Search"
j) Set the Query template to "Path:<URL of your first list> Path:<URL of your second list> Path: <URL of your third list>"

k) Add all Refinable Properties from step c) in selected properties
l) Add all Refinable Properties from step c) in Layout Slots and reference the selected properties. 

m) Go to page 2 and switch the Layout to "Details List"
n)  Select "Manage Columns" and add the columns you want to display in your table
o) Click "Publish"

This is a little bit complicated if you never did it before.

Best Regards,
Sven 


The best way to describe this is a 1 to many list. In Excel you have three tabs. One is your master list and the other two are taking data from that master list to make a smaller more concise list.@SvenSieverding