Working With SharePoint Lookup Columns in Lists, Through Power Apps

In today’s dynamic digital workspace, organisations continuously seek versatile tools to enhance data interactivity and streamline workflows. Many organisations use the Microsoft suite which brings together their whole ecosystem which is generally accompanied by a SharePoint-central strategy. Citizen development and business processes largely make use of SharePoint lists and libraries to share data as well as documents.

Enter SharePoint lists with lookup columns – a powerful feature that allows lists to pull data from another list, much like how foreign keys work in relational databases. This capability not only promotes data consistency but also reduces redundancy. When integrated with Power Apps, these lookup columns can significantly elevate app functionalities (or so Microsoft says), providing users with contextually relevant data selections. However, like all tools, they come with their pros and cons. While they offer enhanced data relationships and a more structured data entry experience, they might introduce complexity for new users and could impact performance if not optimised.

Despite these challenges, when used correctly and appropriately, SharePoint lists with lookup columns in Power Apps can be a game-changer for businesses aiming for efficient and interactive data management.

I think as a community of developers, we try to stay as far away from additional complexity in SharePoint when integrating into Power Apps as possible. Lookups are therefore, not often used. So what happens when a citizen developer has created a lovely hierarchy of lists that have more lookups and relationships than a YouTube influencer has followers? You’ve got to make do with what you’ve got. Let’s say there’s a ‘Participant‘ list in SharePoint that has a lookup column from another list named ‘Team‘.

Adding a Record to the List

Here’s how you’d populate the Participant list while linking it up to the correct team:

Patch(
        Participants,
        {
            UserId: Office365Users.MyProfile().Id,
            Participation: "Team",
            Team:
            {
                Id: TeamID,
                Value: TeamName
            },
            Participant: Office365Users.MyProfile().DisplayName
        }
    )

Note: TeamID and TeamName refer to variables that were set with the Team values, retrieved through lookup expressions in Power Apps.

Updating a Record and Removing the ‘Link’ Between Lists

Now let’s say you’ve created a record where a Participant is associated to a team and then life happened, normal human drama… And that participant no longer wants to be associated to a team? What then?

Well, you’d still need to do a Patch, but you’d need to link the participant to the equivalent of “null” or “none” for the Team lookup. Here’s how (use -1):

Patch(
        Participants,
        LookUp(Participants,UserId= Office365Users.MyProfile().Id),
        {
            UserId: Office365Users.MyProfile().Id,
            Participation: "Individual",
            Participant: Office365Users.MyProfile().DisplayName,
            Team:
            {
                Id: -1,
                Value: ""
            }
        }
    )

A Few More Lookup Patterns

Once you're patching lookups, you'll probably need to read them back and filter on them too. A few patterns that come in handy:

Displaying a Lookup Value Safely

If the lookup was cleared (like in the -1 example above), the column will be blank, so it's worth handling that in labels and galleries:

If(
    IsBlank(ThisItem.Team),
    "No team assigned",
    ThisItem.Team.Value
)

Pulling Other Columns From the Looked-Up List

The lookup column only gives you the Id and Value (the display column) of the related item. If you need something else from the Team list, use the Id to fetch the full record:

LookUp(
    Team,
    ID = ThisItem.Team.Id
).Description

Cascading Dropdowns

If one list looks up to another, you can filter a child dropdown based on what was selected in the parent dropdown by filtering on the lookup Id:

// Items property of the Participant dropdown
Filter(
    Participants,
    Team.Id = TeamDropdown.Selected.ID
)

Things to Keep in Mind

• Delegation: Keep an eye on delegation warnings when filtering or searching on lookup columns. If a function can't be delegated to SharePoint, Power Apps will only evaluate the first 500 records (2 000 at most, if you change the data row limit), which can quietly give you incomplete results on larger lists.
• Cache reference data: If a lookup list (like Team) rarely changes, load it into a collection once (e.g. ClearCollect(colTeams, Team) in App.OnStart) and use that for dropdowns, rather than hitting SharePoint every time a screen loads.
• Keep the hierarchy shallow: Every extra level of lookups means more LookUp calls and more complexity in the app. If you have any say in the list design, keep the relationships as simple as possible.

Hope this helps you through the journey of using SharePoint Lists and Lookups with Power Apps!