How to link lists from Microsoft Lists using a lookup column in Power Apps
This tutorial shows how you can connect two lists with a lookup column in a canvas app.
SharePoint provides two types of lookup column:
- Lookup: links to another list: for example an Orders list may have a lookup column that links to customers in a Customer list;
- Choice: clicking or tapping the column displays a small menu of items that you choose from.
In this tutorial, you build an app that uses these kinds of lookup column.
Why use a lookup column
Data in an enterprise is large and complex. Data in one list often relates to data in another list. Lookup column are the primary way such business data comes together.
For example, you might have an Orders list which has a lookup column that links to a Customers list, to show which customer placed the order. The lookup column in the Orders list lets you get other data from the Customers list as well. You might also use a lookup column to connect the Orders list to a Product list, and bring in information you need about the product ordered, such as product pictures, specifications, manufacturer details, etc.
What are Choice column used for?
Choice column are used for very short lists, but instead of actually creating a separate list, you include the list values in a small menu, which appears when you select Choice column, and you select one of the values.
Examples include data like Customer Status Code, Product Availability, State Codes; basically any fixed list that is relatively short. This data could in fact be implemented as separate lists, and then you would use a Lookup column to link to them, but it is usually easier and quicker to implement them as Choice column.
For more SharePoint and Power Apps integration tutorials, go to SharePoint integration scenarios.
Create the lists in SharePoint
In this tutorial, you link two lists together, Assets and RepairShop. The Assets list is used to track hardware equipment in a team. Since hardware gets broken from time to time, we use the RepairShop list to track the local shops which can fix it.
The lookup column used in this example
The RepairShop list uses the ContactEmail column to identify the shop. This list is defined first so that each row in the Assets list has something to point to.
The Assets list has two lookup column:
- one called RepairShop, of type Lookup, which uses email addresses to point to entries in the RepairShop list;
- one called AssetType, of type Choice, which lists the kinds of hardware that this asset might be.
You most likely would define additional columns, depending on the information you need to track.
Define the RepairShop list and add data
You do this first, so that when you add data to the Assets list, RepairShop entries are available for you to choose from the Assets.RepairShop lookup column.
On your SharePoint site, create a new RepairShop list from blank.
Add a ContactEmail column of type Single line of text.
Add any other columns you need.
Select + New to enter sample data into the list, at least 3 rows with different ContactEmail values. When an asset needs to be repaired, you choose one of these. You can also use Edit in grid view to quickly enter text in grid.
Define the Assets list
On your SharePoint site, create a new Assets list from blank.
Add an AssetType column of type Choice, and fill in the values you want to appear in the choice menu as choices. Then select Save.
Select + Add column > More.
Enter column name as RepairShop. And column type as Lookup.
In additional column settings, select RepairShop as the list to get the information from, and the column for the lookup as ContactEmail.
Add any additional column you want.
Create an app from the Assets list
Add data to the Assets list
Now you can run the app and see how the view details screen looks for the lookup columns.
Press F5 or select Preview ( ).
Select the + symbol in the upper right corner to add an entry.
Enter a Title for this asset.
Select the AssetType dropdown arrow. The values displayed are those you entered when you created this column. Choose one of the entries.
Select the RepairShop dropdown arrow. Choose one of the entries.
In the upper-right corner, select the check mark to save the new entry.
(optional) Repeat this procedure to add as many items to the list as you want.
Press Esc to return to the default workspace.
Save and publish the app.
- Introducing support for lookups and a new sample app
- Performance, Refresh button, ForAll, and multiple column lookups
- Generate an app by using a Microsoft Dataverse database
- Create an app from scratch using a Dataverse database
Submit and view feedback for