Use SharePoint Lists Just Like a Relational Database in Power Apps

Introduction

While SharePoint lists aren't technically relational databases, with the right approach, you can implement parent-child relationships and create connected data structures that work remarkably well in Power Apps. This capability lets you build more sophisticated apps without needing SQL Server or Dataverse licenses.

In this guide, I'll show you how to structure, connect, and manage related data across SharePoint lists. We'll build a simple but practical example: an issue tracking system where each issue can have multiple related actions.

Prefer to Watch the Video?

Head over to my YouTube channel Chino Does Stuff to watch the video tutorial, there you will see a variety of videos with loads of tricks and tips on the entire Power Platform suite.

Understanding the Approach

To create relationships between SharePoint lists, we'll use a classic database technique: adding a foreign key column that references the primary key of another table. In SharePoint terms:

• The automatically generated ID column in SharePoint lists serves as our primary key
• We'll add a custom number column in the "child" list to store the ID of the related "parent" record
• In Power Apps, we'll use this relationship to filter and create related records

This creates a one-to-many relationship, where one record in the parent list can relate to many records in the child list.

Step 1: Setting Up the SharePoint Lists

We'll start by creating two SharePoint lists to store our data:

1. Issues List: The parent list that stores the main issues
2. Actions List: The child list that stores actions related to each issue

Here's how to structure each list:

The key to our implementation is the IssueId column in the Actions list. This number column will store the ID of the related issue, creating our parent-child relationship.

Important Notes About SharePoint IDs

SharePoint automatically creates an ID column for every list:

• IDs are unique within each list and increment automatically
• The ID column isn't shown in the default view, but you can add it
• IDs are not reused, even after items are deleted (similar to database identity columns)
• IDs are always of type Number, making them efficient for filtering and relationships

Step 2: Connecting to SharePoint in Power Apps

Now, let's create a Power App and connect it to our SharePoint lists:

After connecting, you'll have access to both the Issues and Actions lists within your app, including all their columns.

Step 3: Building the Basic App Structure

Our app will need several screens:

1. Main Screen: Shows a gallery of Issues
2. New Issue Screen: Form to create a new Issue
3. View Issue Screen: Shows Issue details and related Actions
4. New Action Screen: Form to create a new Action related to the current Issue

For the main screen, create a gallery connected to the Issues list. Add a plus button to navigate to the New Issue screen, and an edit icon in each item to navigate to the View Issue screen.

Main Screen Example

On your main screen:

1. Add a gallery connected to the Issues list (Gallery_Issues)
2. Display the Title and ID in each gallery item
3. Add a button that navigates to the New Issue screen
4. Add an icon in each gallery item that navigates to the View Issue screen and selects the current item

Step 4: Creating the Issue Detail Screen

The Issue detail screen is where the parent-child relationship becomes visible. This screen should show:

1. The details of the selected Issue
2. A gallery of related Actions
3. A button to add a new Action for this Issue

The key is filtering the Actions gallery to show only Actions related to the current Issue:

This filter checks the IssueId column in the Actions list and only shows records where it matches the ID of the currently selected Issue.

Step 5: Creating Related Records

When creating a new Action, we need to save both the Action details and its relationship to the parent Issue:

This Patch function saves a new record to the Actions list and sets the IssueId field to the ID of the currently selected Issue, creating the relationship between them.

Advanced Techniques

Delegation Considerations

When working with SharePoint lists in Power Apps, delegation is an important concept to understand. Delegation determines whether operations happen at the data source or in the app.

The good news is that filtering by ID and IssueId (both number fields) is fully delegable to SharePoint, meaning SharePoint will handle the filtering server-side. This ensures you'll get correct results even with large lists.

For best performance:

• Always filter using delegable operations when possible (=, <, >)
• Use number fields for relationships as they're fully delegable
• Consider using the Sort function to organize your data (also delegable)

Creating One-to-Many-to-Many Relationships

You can extend this pattern to create deeper relationships. For example, you could add a Comments list related to Actions, where each Action can have multiple Comments.

To implement this, you would:

1. Create a Comments list with an ActionId column
2. Filter the Comments gallery based on the selected Action
3. When saving a Comment, set its ActionId to the selected Action's ID

Cascading Deletes

Unlike true relational databases, SharePoint doesn't support automatic cascading deletes. If you delete a parent record, the related child records will remain orphaned.

If cascading deletes are important for your app, you'll need to implement them manually:

1. Before deleting a parent record, find all related child records
2. Delete the child records using Remove() or ForAll() with Remove()
3. Then delete the parent record

Real-World Application

This pattern can be applied to many real-world scenarios:

• Project Management: Projects and Tasks
• Customer Service: Cases and Notes
• Inventory Management: Products and Stock Levels
• Event Planning: Events and Attendees
• HR Processes: Employees and Training Records

The pattern remains the same: create a column in the child list that references the ID of the parent record, then use filtering in Power Apps to show only the related records.

Limitations and Considerations

While this approach works well for many scenarios, it does have some limitations compared to true relational databases:

• No referential integrity (orphaned records possible)
• No cascading operations (must be implemented manually)
• No joins (relationships must be managed through filtering)
• Performance can degrade with very large lists (>5000 items)
• Complex queries across multiple relationships may hit delegation limits

For complex enterprise applications with many relationships and large data volumes, you might want to consider Dataverse or a SQL Server database instead.

Conclusion

SharePoint lists can indeed be used effectively as a relational database for Power Apps. By creating linked lists with appropriate columns to establish relationships, you can build sophisticated multi-table applications without needing additional licenses or more complex database platforms.

This approach is perfect for departmental apps, team tools, and solutions where simplicity and accessibility are priorities. The ability to create, view, and manage related records gives your users a cohesive experience while leveraging the familiar and accessible SharePoint environment.

With these techniques, you can build powerful, data-driven applications that feel like they're connected to a full relational database, all while staying within the SharePoint and Power Apps ecosystem.

If you'd like to see more Power Apps development tips and techniques, please head over to the YouTube channel and leave a comment there. I actively monitor and respond to all YouTube comments!

About the Author

Marcello is a lead developer at Powerplatform Experts, an expert in, Computer Science, Power Platform integration and founder of the YouTube channel ChinoDoesStuff. If you have any questions, please feel free to get in contact at via the form below.