Relational Data in Power Apps and SharePoint: An Asset Checkout Example

If you open Power Apps Studio and grab data from a single huge SharePoint list, you are building a maintenance trap. Repeating the same employee names, office locations, or asset descriptions across hundreds of rows feels productive at first, but it breaks the moment you need to change a value or build a report.

Relational databases solve this. SharePoint lists are not a full relational engine, but they absolutely support the one-to-many pattern when you link them through a common ID column. The payoff is smaller lists, drastically improved data accuracy, and a data model that plays nicely with Power BI and Power Automate.

In this walkthrough, we are moving beyond the typical "reserve a vehicle" demo. Instead, we are building an IT Asset Checkout system. Employees check out laptops, monitors, and tablets. Every checkout references a specific asset stored in a parent list. No duplication, no ambiguity.

Designing the SharePoint Lists

We need two lists. Keep their names clear and avoid spaces if possible (or use the "space" naming convention consistently).

CompanyAssets

Column	Type	Purpose
`ID`	Auto	Primary key, automatically created
`AssetTag`	Text	Unique barcode like `IT-2034`
`Model`	Text	e.g. "Surface Laptop 5"
`Category`	Choice	Laptop, Monitor, Tablet, Dock
`AssignedOffice`	Text	Default storage location

AssetCheckouts

Column	Type	Purpose
`ID`	Auto	Primary key
`AssetID`	Number	Foreign key referencing `CompanyAssets.ID`
`EmployeeName`	Text	Full name of the borrower
`CheckoutDate`	Date	When the item was taken
`ReturnDate`	Date	Leave blank until the asset is returned
`Purpose`	Text	Why the asset was needed

Design Tip

Never use a SharePoint Lookup column for the relationship. Add a plain Number column named AssetID. This keeps your data portable, avoids throttling issues, and gives you complete control in Power Apps formulas.

Building the Power App

Open Power Apps Studio and create a Tablet app from blank. Add two data sources:

CompanyAssets
AssetCheckouts

Browse and Select an Asset

Insert a Vertical Gallery (gal_Assets) and bind it to CompanyAssets. Use a Title and Subtitle layout. Set the title to Model and the subtitle to AssetTag.

Reposition the right-chevron icon—or swap it for a clipboard icon to enforce the "IT" theme.

Add a label above the gallery: Select an Asset.

Show Asset Details

On the right side of the screen, place several labels and text inputs to display the selected asset's metadata. In the Text property of a label, reference the selected item:

gal_Assets.Selected.Model

For the asset tag and office:

gal_Assets.Selected.AssetTag

Because these fields are for display only, lock them down:

DisplayMode.Disabled

Here is where the relationship shines. Insert a second gallery (gal_CheckoutHistory) below the asset details. Bind its Items property to a filter that pulls only the checkouts belonging to the selected asset:

powerfxFilter checkouts by AssetID

Filter('AssetCheckouts', AssetID = gal_Assets.Selected.ID)

Inside this gallery, add three labels:

EmployeeName: ThisItem.EmployeeName
CheckoutDate: ThisItem.CheckoutDate
ReturnDate: ThisItem.ReturnDate

Place a header row above the gallery with matching column names.

Submitting (and Returning) an Asset

New Checkout Form

Add a form panel below the gallery. Include:

A text input txt_EmployeeName
A date picker dte_CheckoutDate
A text input txt_Purpose

Set the DefaultDate of the date picker to Today().

Submit Button Logic

Drop a Send icon or a button on the screen. In its OnSelect property, use Patch to create a new record in AssetCheckouts. The critical piece is mapping the relationship:

powerfxCreating a new checkout record

Patch(
  'AssetCheckouts',
  Defaults('AssetCheckouts'),
  {
      AssetID: gal_Assets.Selected.ID,
      EmployeeName: txt_EmployeeName.Text,
      CheckoutDate: dte_CheckoutDate.SelectedDate,
      Purpose: txt_Purpose.Text
  }
);
Reset(txt_EmployeeName);
Reset(dte_CheckoutDate);
Reset(txt_Purpose);

Test the app. Every time you click Submit, a new row appears in the AssetCheckouts list with the correct AssetID matching the selected asset.

Editing (Returning an Asset)

Returning an asset means we need to update the existing checkout record, specifically the ReturnDate.

To handle this gracefully, we reuse the checkout gallery. Add an Edit icon to gal_CheckoutHistory. When clicked, the gallery registers a selection.

Set the Default property of gal_CheckoutHistory back to empty so no row is pre-selected:

Defaults('AssetCheckouts')

Now update the Submit button's OnSelect to handle both new records and updates. The standard pattern uses Coalesce combined with LookUp:

powerfxPatch that supports insert and update

Patch(
  'AssetCheckouts',
  Coalesce(
      LookUp('AssetCheckouts', ID = gal_CheckoutHistory.Selected.ID),
      Defaults('AssetCheckouts')
  ),
  {
      AssetID: gal_Assets.Selected.ID,
      EmployeeName: txt_EmployeeName.Text,
      CheckoutDate: dte_CheckoutDate.SelectedDate,
      ReturnDate: dte_ReturnDate.SelectedDate,
      Purpose: txt_Purpose.Text
  }
);
Reset(txt_EmployeeName);
Reset(dte_CheckoutDate);
Reset(dte_ReturnDate);
Reset(txt_Purpose);
Reset(gal_CheckoutHistory);

If a row in the checkout gallery is selected, LookUp returns that record, and Patch updates it. If no row is selected, LookUp returns Blank(), Coalesce falls back to Defaults('AssetCheckouts'), and Patch creates a brand new checkout.

No If statement needed. One formula to rule them all.

Delegation, Performance, and Security

Delegation

The Filter('AssetCheckouts', AssetID = ...) call is your main delegation point. Equality comparisons on single columns are delegable by the SharePoint connector. This query runs on the server and will not slow down as your list grows—up to the SharePoint list view threshold, which defaults to 5000 items.

Important: Index the AssetID column in the AssetCheckouts list. Go to your list settings, find the AssetID column, and turn on indexing. This guarantees that even if you pass the threshold, the server still executes the query efficiently against the indexed column.

Performance

Keep galleries lean. Do not load rich text or image columns in a gallery's Items if you only need text.
Use Coalesce sparingly in delegable queries (it can cause non-delegation warnings in some contexts, though it is safe here inside a Patch).
Turn off auto-refresh on libraries that do not change frequently.

Security

Because Power Apps respects the data source permissions of the user running the app, SharePoint list-level security applies automatically. If a user only has Contribute access, they cannot delete records even if the app tries to call Remove. Always design your app controls with the user's permission level in mind.

Common Mistakes and Troubleshooting

1. The relationship column is blank.

You are likely using a SharePoint Lookup column instead of a Number column. Delete the lookup column in SharePoint, create a new Number column named AssetID, and update your app's data source to see it.

2. Every click creates a new record instead of updating.

Your Patch is receiving Defaults('AssetCheckouts') instead of the existing record. Double check the Coalesce(LookUp(...), Defaults(...)) pattern inside the Patch.

3. The gallery does not refresh after a submit.

Add a Reset(gal_CheckoutHistory) at the end of your submit formula. Alternatively, use Refresh('AssetCheckouts') before the Reset if you need the data source to sync immediately.

4. Delegation warning on Filter.

If you get a delegation warning, your filter is probably too complex. Stick to simple equality (AssetID = or ThisItem.Value). Avoid StartsWith on large lists.

5. The AssetID is hidden or missing.

In SharePoint, ensure the AssetID column is not hidden from the default view. Power Apps picks up columns based on the default view metadata.

Why This Matters for Reporting

The real magic happens when you connect Power BI to AssetCheckouts and CompanyAssets. Because the data is normalized, you can:

Calculate average checkout duration per asset.
Track which assets rarely get returned on time.
Build a utilization heatmap by category.

None of this is possible when everything is crammed into a single flat list with repeated values.

Final Recommendation

Stop throwing every column into one SharePoint list. The ten minutes it takes to create a second list and link it with an ID column will save you hours of technical debt, app debugging, and reporting frustration.

Start with one-to-many. Master the Filter / Patch / Coalesce pattern shown here. Apply it to customers, orders, projects, or any entity that has child records. Your apps will perform better, your data will stay accurate, and your stakeholders will notice the difference.

References

Matthew Devaney, Power Apps & SharePoint List Relationships (Original Article)
Microsoft Learn, Create a relationship between SharePoint lists (link placeholder)
Microsoft Learn, Understand delegation in a canvas app (link placeholder)
Microsoft Learn, Patch function in Power Apps (link placeholder)