Lookup fields have always been a popular feature in SharePoint. Especially in cases where there are lots of choices that need to be frequently updated. These work great, but what if you want a lookup list that can be accessed across all your sharepoint sites? In SharePoint Online, there hasn't been a convenient way to have this kind of functionality which often leads to duplication of data. SharePoint does offer Managed Metadata functionality, but this means not being able to tap into SharePoint lists which we may need for workflows or other updates. In many organizations, Managed Metadata functionality isn't used at all so it might be best to not add another area to your environment which needs to be managed.
Data Connections functionality solves this challenge allowing you to pull back data from other sites in SharePoint or even from external data sources so that you don't have to duplicate data that is stored somewhere else.
Is this article, we'll describe how you can create a cross-site lookup field in which you can have a key SharePoint list called (our example uses Departments) in one site that can be referenced from anywhere in your SharePoint environment across all sites. I suggest you begin by following this practice example and then you can apply these steps anywhere you need in your live lists in SharePoint.
One note before you begin, the data connection step (which can be done quickly) must be done by an M365 Global Administrator.
If you are not a Global Admin, please request their support. Note that once the data connection is created, the Global Admin will not need to make any further updates. You can update the data in the source list as an regular SharePoint user.
Begin by creating a simple list called Departments using the Title field for values as shown below:
If your lookup list contains additional fields, be sure to create a view that shows only the ONE COLUMN which you want to appear in your lookup field to be displayed in a dropdown. In our example, the default All Items view contains just the Title field which is exactly what we want. Now go to an entirely different site and add a practice list called Reports once again with no additional fields added.
We will add the lookup field in the next steps. Open Form Designer, then select Data Connections » Add new data connection:
- Name the Data Connection Departments.
- Select SharePoint for Type.
- Copy and paste the URL of the source site where the list is located. This usually will include /sites/ in the URL.
- Click Connect to authorize the connection. ! Must be done by an M365 Global Admin !
Select your lookup list Departments and the view that has 1 field showing which in our practice example is the default All Items view:
In the second Permitted forms tab, select only the View items option as shown below:
- In the Access control tab be sure to carefully apply all settings below.
- Turn OFF Allow hosted forms. (This isn't for a public form.)
- Turn OFF the reCAPTCHA option. (This isn't for a public form.)
- ADD your ROOT SharePoint URL to indicate that this data connection is allowed across of of your SharePoint environment.
- Click the first Save button, then click the 2nd Save button to save your Data Connection.
Go back to the main Ultimate Forms Settings page and select External data lookup in the Columns section:
- Add a new field named Department using Source type Data connection.
- Select the Data connection which we just created in the previous step.
- Select the four toggle options in the bottom exactly as shown.
+ Select key values from dropdown
+ Preload all key values
+ Hide get button
+ Show values in separate columns
Ensure other options are disabled.
- Now you can save your field and add it to your form in Form Designer.
- Test your functionality to see your new cross-site lookup field in action!
Now you have something handy that allows you to not repeat data in lookup lists on multiple sites. Although the M365 Admin does need to create the initial data connection, regular users can update the lookup list data as needed at any time after the initial setup. There are lots of great opportunities here. Common scenarios for lists that would be useful across all of SharePoint include:
- Departments
- Locations
- Products
- Divisions
- Buildings
- Building Rooms
- Makes / Models of equipment
Enjoy!
Add your comment
