To Look Up or Not to Look Up: Refactoring SharePoint Relationships in Power Apps

SharePoint Lookup columns are convenient.

They create a visible relationship between lists, display a friendly value, and make the data model feel more structured. For a simple SharePoint solution, that can be exactly what is needed.

The difficulty starts when Power Apps has to filter, join, or reshape records across two or more lists.

That is when a useful SharePoint feature can become a delegation problem.

The Scenario

Consider a travel request solution with two SharePoint lists:

• TravelRequests is the master list.
• TravelRequestDetails is the child list.
• One request can have several related detail records.

The master list is normally filtered by travel date:

Filter(
    TravelRequests,
    TravelDate = dpTravelDate.SelectedDate
)

The child list contains a SharePoint Lookup column that points back to the master request.

A formula may then try to retrieve child records like this:

Filter(
    TravelRequestDetails,
    RequestLookup.Id = ThisItem.ID
)

This looks natural because the relationship already exists in SharePoint. As the formula becomes more complex, however, using Lookup subfields inside Filter, AddColumns, or other table-shaping operations can produce delegation warnings.

The warning matters because Power Apps may evaluate part of the query locally. Once the list grows beyond the app's delegation limit, valid records can be left out of the result.

Why the Lookup Column Becomes Expensive

A SharePoint Lookup is not just a number. Power Apps receives it as a more complex value containing fields such as the lookup ID and display value.

That is helpful for displaying related information, but less helpful when the app needs to perform predictable server-side filtering.

The problem becomes more visible when a formula:

• filters a master list and a child list together,
• compares a property such as RequestLookup.Id,
• adds related child data to every master record,
• repeats LookUp or Filter calls inside a gallery,
• or builds a local join across multiple SharePoint lists.

The formula may work during development while the lists are small. That does not mean it will continue returning a complete result when the data grows.

Refactor the Relationship to a Number

A practical alternative is to store the parent SharePoint item ID in a normal Number column on the child list.

For example, add this column to TravelRequestDetails:

RequestID

When the master request is created, save its generated ID into every child record:

With(
    {
        varRequest: Patch(
            TravelRequests,
            Defaults(TravelRequests),
            {
                Title: txtTitle.Text,
                TravelDate: dpTravelDate.SelectedDate
            }
        )
    },
    ForAll(
        colTravelDetails,
        Patch(
            TravelRequestDetails,
            Defaults(TravelRequestDetails),
            {
                RequestID: varRequest.ID,
                RequestDate: varRequest.TravelDate,
                TravellerName: ThisRecord.TravellerName
            }
        )
    )
)

The relationship is now represented by a simple value:

TravelRequestDetails.RequestID = TravelRequests.ID

Filtering on a Number column is simpler than asking Power Apps to navigate a SharePoint Lookup value.

Filter(
    TravelRequestDetails,
    RequestID = ThisItem.ID
)

Copy the Request Date to the Child List

Replacing the Lookup column solves only part of the problem.

If the screen starts by selecting requests for one travel date, the child list should support the same first-stage filter. Add a Date column such as RequestDate to the child list and populate it when each child record is created.

The child query can then begin with the same date used for the master query:

Filter(
    TravelRequestDetails,
    RequestDate = dpTravelDate.SelectedDate
)

This reduces the number of child records the app must work with before matching them to individual requests.

The date acts as a useful partition:

Travel date
    -> matching master requests
    -> matching child records
    -> exact match by numeric RequestID

The important distinction is that the date is the primary filter, not the relationship key.

Several requests can have the same travel date. RequestID is still needed to identify which child records belong to which request.

Load the Relevant Data Once

Instead of querying SharePoint repeatedly from controls inside a gallery, load the relevant master and child records once:

Concurrent(
    ClearCollect(
        colRequests,
        Filter(
            TravelRequests,
            TravelDate = dpTravelDate.SelectedDate
        )
    ),
    ClearCollect(
        colRequestDetails,
        Filter(
            TravelRequestDetails,
            RequestDate = dpTravelDate.SelectedDate
        )
    )
)

The gallery can use:

colRequests

These are two separate collections: colRequests contains the master request records displayed by the gallery, while colRequestDetails contains the filtered child records.

Controls inside the gallery can retrieve related child records from the already narrowed local collection:

Filter(
    colRequestDetails,
    RequestID = ThisItem.ID
)

The local comparison is safe because the delegable date filter has already reduced the child dataset before it reaches the collection.

This also avoids sending the same query to SharePoint for every visible gallery row.

Index the Columns Used for Filtering

The SharePoint columns used by the initial server-side queries should be indexed.

For this pattern, consider indexing:

• TravelRequests.TravelDate
• TravelRequestDetails.RequestDate
• TravelRequestDetails.RequestID

Indexing does not fix a nondelegable Power Apps formula, but it helps SharePoint process supported filters efficiently as the lists grow.

Keep the Lookup Only When It Adds Value

This does not mean every SharePoint Lookup column should be removed.

A Lookup column can still be useful when:

• users manage relationships directly in SharePoint,
• the list is small and unlikely to grow,
• the relationship is mainly used for display,
• the Power Apps formulas remain delegable,
• or SharePoint relationship behavior is more valuable than query simplicity.

The better question is not whether Lookup columns are good or bad.

It is whether the Lookup column supports the way the app actually retrieves data.

Tradeoffs of the Refactored Design

Copying the request date into the child list is a deliberate form of denormalization. It improves filtering, but it creates another value that must remain synchronized.

If the travel date changes after child records have been created, the app or an automated flow must update the child records too.

That synchronization rule should be part of the design:

ForAll(
    Filter(
        TravelRequestDetails,
        RequestID = varRequest.ID
    ),
    Patch(
        TravelRequestDetails,
        ThisRecord,
        {
            RequestDate: varRequest.TravelDate
        }
    )
)

For larger datasets, perform that update through a delegable query, Power Automate, or another server-side process rather than assuming every related record is available locally.

The numeric ID also does not enforce referential integrity by itself. SharePoint will not automatically prevent a parent request from being deleted while child records still reference its ID. That behavior must be handled by the app, a flow, or governance rules.

A Practical Checklist

When joining SharePoint lists in Power Apps, I now ask:

1. Is the formula delegable against the real production data source?
2. Am I filtering a complex Lookup value when a Number column would be enough?
3. Can both lists be narrowed by the same business field before they are joined?
4. Is that business field copied and indexed in the child list?
5. Is the numeric parent ID still used for the exact relationship?
6. Am I querying SharePoint once, or once for every gallery row?
7. What process keeps duplicated filter fields synchronized?

The Pattern

The final design is straightforward:

• Use the travel date to narrow the master list.
• Store the same request date in the child list.
• Use that date to narrow the child list.
• Store the parent item ID in a normal Number column.
• Use the numeric ID for the exact parent-child match.
• Work with the narrowed collections instead of repeatedly querying SharePoint.
• Index the SharePoint columns used by the server-side filters.

Outcome

The goal is not to avoid Lookup columns at all costs.

The goal is to make the relationship easy for both SharePoint and Power Apps to query correctly.

Using a copied date as the primary filter and a Number column as the exact relationship key makes the data flow easier to understand. It also reduces delegation risk, avoids repeated gallery queries, and gives the app a better chance of behaving the same way with thousands of records as it did with fifty.

To look up or not to look up is not really the question.

The better question is: will this relationship still delegate when the lists grow?