The Core of the CapEx App

At the heart of the CapEx app lies its primary functionality: capturing and managing capital expenditure requests. For this, several SharePoint lists and libraries were built, each serving a focused purpose. Here’s the breakdown of the key components:

1. Main Request List

This list is where individual CapEx requests are stored. It acts as the central location where all data ties back. Each request includes data points like:

• Request title
• Requester details (via People Picker columns)
• Dates and deadlines
• Choices for status or type
• Attachments

The SharePoint modern interface makes it simple to add, edit, and organize these fields. Need to tweak the setup? Go to List Settings, where you can view all columns, add new ones, or make adjustments in a streamlined way.

2. Department List: A Single Source of Truth

One critical principle in building apps is maintaining a single source of truth. The app uses a separate SharePoint list to store all department codes and names. Why does this matter?

Imagine your company has 15 individual department lists floating around in different systems or apps. If one department name changes, you’d need to update it in 15 locations. With a single department list, you update it in one place, and the change automatically reflects across the entire app.

In the app’s form, users select departments through a dropdown powered by this list. This is done by adding a ComboBox that fetches data from the department list, simplifying the user experience while ensuring accuracy.

3. Line Item List for Expenses

CapEx requests often include multiple expense line items. For example, a “Flight Simulator Upgrade” request might have separate items for equipment, labor, software licensing, etc. To keep these details organized, another SharePoint list—Expense Details—was created.

This list includes:

• Item descriptions
• Costs (local and USD)
• Asset classes (Choice columns)
• Quantities and units of measure

Every line item is tied back to the main request through a unique identifier, the Master ID. The Master ID ensures the one-to-many relationship, linking a single request to its multiple expense details.

4. Attachments Library: Why Libraries Are Better

Handling file uploads efficiently is essential in any business app. Rather than attaching files directly to the main request list, a dedicated attachments document library was created. This approach offers significant advantages:

• Files remain easily editable—open them directly in the library, make changes, and they save instantly.
• There’s no need for downloading, editing, and re-uploading files (saving your users a ton of hassle).
• Built-in metadata columns, such as the Master ID, tie files back to the corresponding request.

For instance, if you filter the library by a specific Master ID, you’ll see only the files related to that particular CapEx request. This structure improves organization and doesn’t clutter the main list with large files.

5. Approvals List for Workflow Stages

Approval workflows often involve multiple reviewers across different stages. In this case, the app incorporates three stages:

1. Regional Finance approval
2. Accounting approval
3. Corporate Finance approval

Each stage is handled in a separate SharePoint list designed to capture:

• Approver names
• Approval dates
• Comments, if any
• The Master ID linking back to the main request

By separating approvals into their own list, the app ensures that every stage is tracked individually. If an approval process needs to restart, the history remains intact. This design also allows detailed reporting, as you can use tools like Power BI to analyze approval trends or bottlenecks.

How It All Fits Together

The beauty of this setup lies in its flexibility. Data sources like lists and libraries are connected to the app through SharePoint or Dataverse, depending on your preference. Here’s how they interact:

• Main Request List: Serves as the backbone, housing the primary data.
• Other Lists/Libraries: Referenced dynamically using the Master ID as the common link. Whether it’s line items, approvals, or attachments, everything ties back seamlessly.

Additionally, SharePoint’s modern GUI simplifies managing these data sources. With features like list views, column filtering, and direct editing, it’s easier than ever to maintain and update your app.

Why Scalability Matters

A well-thought-out data structure doesn’t just make your app work today—it sets you up for success in the future. Adding new functionality, tweaking workflows, or scaling for a larger user base becomes much simpler when your foundations are strong. Whether you’re working with SharePoint or Dataverse, applying these principles ensures your app remains efficient and easy to maintain.

Conclusion

Designing the data structure for your Power App is like building the frame for a house—get it right, and everything else will work smoothly. From the main request list to the approval workflows, every piece of this CapEx app was designed with organization, scalability, and user experience in mind.

By using single sources of truth, linking data through unique IDs, and leveraging libraries for file uploads, you can ensure a robust setup that delivers real value to your users.

Ready for the next step? In the upcoming post, we’ll explore how Power Automate integrates with this structure to handle multi-stage approvals efficiently. Don’t forget to share this guide with others in your Power Apps community. Have questions about building your app? Drop them in the comments below!