Build Master-Detail PowerApps Without Flat List Fails

Most developers try to build relational databases in PowerApps like they are writing SQL. It never works. You cannot just throw two flat SharePoint lists into a canvas app and expect them to magically communicate. You need to explicitly build the parent-child relationship. Fix the data structure first. Then let the UI accelerate it.

Think about how real business data flows. It is almost always a one-to-many relationship. Each project has multiple tasks.

Each expense report has multiple costs.

Each company location has multiple employees.

To make this work you need a Lookup column. You add this directly to your child list. If you have an Expense Items list, you add a lookup pointing to the Parent ID in the main Expense Report list.

There is a hard rule here. SharePoint lookups are strictly limited to the same site collection. Do not try to pull data from a completely different site. It will fail.

When you build the UI, you want a master-detail view. Think of a collapsible gallery filtered by a lookup ID. Take Case Management. The main screen shows the parent cases.

Click a case, and you see the child tasks.

The same logic applies to Branch Offices.

Click a branch, see the child audits.

And for our main focus, Expense Reports.

Click the report, see the individual expense items.

Here is where most makers get stuck. PowerApps does not treat lookups like simple text. Lookups, People, Managed Metadata, and Choice fields are complex columns. PowerApps reads SharePoint lookup fields as a small table containing exactly one row. It expects a specific structure: {Id: 1, Value: "Title"}.

When you inspect a complex field in PowerApps, you see a clickable icon. Click it, and you reveal the underlying ID and Value structure.

To read the title of your lookup, your formula needs to explicitly call the Value.

You need a way to track the active parent record. The easiest method is storing the entire SharePoint row in a variable.

When you create new items, the Parent ID needs a default value set to the ID of the parent you just created.

You trigger a new parent form by clearing your variable and calling the new form function.

Then you submit the details to save the new item.

If a user clicks an existing record, you set the variable to that specific item.

Let us build the Expense Report app step by step. You need two SharePoint lists. Connect both to your app.

Drop in a vertical gallery for the parent records.

Set the Items property to your Expense Report list.

Position it cleanly on the left side of your screen.

Next, add an Edit Form for creating new reports. Bind it to the same Expense Report list.

Set the Item property to your variable, varParentItem.

Add a button to save the new report.

You need a way to trigger new entries. Add a plus icon. OnSelect, use NewForm() and clear your variable.

Update your save button to submit the form.

A SharePoint ID does not exist until the record is saved. You cannot link a child item to a parent that has not been created yet. The trick is using the OnSuccess property of your parent form. When the form submits successfully, fetch the newly created record using Form.LastSubmit and save it to your variable.

Now you need the sub-list. Add a second gallery and a second form for the Expense Items.

Update the parent form to create a new child form immediately after a successful submission.

Remove the restriction on the Parent ID data card on your child form.

You must force the lookup field to inherit the parent data. Set the Default property to construct the complex record using your variable.

Add a save button for the child form.

Reset the form OnSuccess so the user can keep logging items.

You only want to see expenses for the active report. Filter the child gallery where the lookup ID matches your variable.

When a user clicks an existing report in the main gallery, update the variable.

Do not show the child form until the parent exists. Use a boolean variable like varShowChild. Set it to false when creating a new parent.

Set it to true OnSuccess of the parent form.

Stop building flat apps. Relational apps handling 42 expense items per report are what actually solve business problems. If you get stuck, run the app and check your variables in the view menu.

Do the boring work of structuring your data correctly. Then let the tools accelerate it. This approach is adapted from an excellent tutorial on PowerApps and related lists.