[Power Apps] Working With SharePoint Lookup Columns in Lists, Through Power Apps

Level of Difficulty: Intermediate – Senior.

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: ""
            }
        }
    )

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