Will Boyle

Devlog #1 – Building a Power Platform Inventory Tracker (with just the free tier!)

Introduction

Welcome to the first devlog entry for my ongoing Power Platform project, an inventory tracker built for the Faculty of Kinesiology at the University of New Brunswick. This post focuses on Phase 1, which involved consolidating several lab-specific Excel inventories into a single SharePoint list, designing a Power App to interact with that data, and setting up role-based access controls for different user types.

Best part is, this is all done with the standard M365 license! No extra licensing fees required!

Project Background

Before this project, each lab tracked its equipment, consumables, and small tools in separate Excel sheets. That worked… until it didn’t. Updating stock levels, checking statuses across labs, or identifying low inventory was time-consuming, error-prone, and siloed.

The solution? I built a unified system using Power Apps, SharePoint, and Power Automate that lets labs manage their inventory in a single place while enforcing role-specific access and edit rights.

Phase 1 Objectives

1. Consolidate Lab Inventories

The first step was merging all the Excel data into one SharePoint list called InventoryTrackerData. This centralized location now stores every item across all Kinesiology labs.

Each item includes detailed metadata like description, cost, lab ownership, classification (e.g., small tool, consumable), serial number, status, and more. This was all laid out in a format consistent with the project’s functional requirements.

2. Build the Inventory Tracker UI

Using Power Apps, I designed a responsive canvas app with the following screens:

  • Home Screen with navigation buttons
  • Inventory List Screen with search/filtering
  • Item Detail Screen with item-specific info and editable fields based on role (merged with Inventory List Screen on larger devices)

This gave users a clean interface to navigate and interact with the data, all without needing to touch SharePoint directly.

3. Role-Based Access Management

To manage who can do what, I created a second SharePoint list called AccessManagement. This list maps each user’s email to a role like:

  • Admin
  • Lab User (CELLAB, HPL, PEADS, etc.)
  • Generic User

Inside Power Apps, I used Power Fx formulas to fetch the user’s role at app launch and determine what UI elements to show or restrict. For example, hiding the “Edit” button unless the user is an Admin or showing only items from a specific lab.

SharePoint List Design

Inventory List (InventoryTrackerData)

Here’s what the structure looks like:

Key columns include:

  • Description
  • Supplier
  • Lab
  • Cost
  • Count and Required (used to trigger alerts)
  • Classification (Consumable, Tool, etc.)
  • Status (Available, On-Loan, Lost, etc.)
  • Location, Serial Number, Image, Hyperlink

Access Management List

This list controls role visibility within the app. Each row links a user’s email to their lab access or admin rights.

Power App Screens

Home Screen

Home screen of the inventory tracker Power App showing navigation buttons.

The main navigation screen lets users access inventory, requests (future phase), and change log (role-specific).

Inventory List & Item Detail Screen

This screen displays items filtered by the logged-in user’s lab. Search and filters by classification help narrow results quickly. When a user taps an item, they get a full detail view. Admins can edit all fields; lab users can only adjust the Count field.

On mobile, this screen is split into two: the Inventory List Screen (the left sidebar in the image above), and the Item Details (right side of the screen)

Access Control Logic

The core logic for user control is simple but effective:

LookUp(AccessManagement, UserEmail = User().Email).UserType

This line checks the user’s email against the SharePoint list and pulls their assigned role. From there, role-based logic enables/disables features or entire screens.

  • Admin: Full access, including add/delete.
  • Lab Users: Can view and update item counts for their assigned lab only.
  • Generic Users: View-only access.

All edits by non-admins trigger an email alert to the Admin, who can approve or reject the changes — powered by Power Automate.

Challenges & Solutions

  • 📂 Excel Data Inconsistency: Fields didn’t always match, so I used a standardized import template to clean data before upload.
  • 👥 Role Logic Errors: Some roles weren’t mapping due to email casing & email aliases, so I fixed this by enforcing lowercases and adding an “email” column.
  • 🔐 Unauthorized Edits: Added warning messages and disabled buttons if user lacks permissions.

Key Outcomes of Phase 1

✅ Replaced 3+ fragmented Excel files with one authoritative SharePoint list.
✅ Built an intuitive Power App for inventory tracking.
✅ Implemented scalable, role-based access control.
✅ Created the foundation for Phase 2 (equipment loaning, approval workflows, audit logging).

Next Steps (Phase 2 Preview)

In Phase 2, I’ll implement:

  • Request and approval workflows
  • Equipment loan tracking
  • Audit trails and change logs

Conclusion

Phase 1 is a wrap! Consolidating and centralizing lab inventory for the UNB Faculty of Kinesiology has already streamlined how equipment and tools are tracked.

More importantly, this project lays the foundation for scalable expansion — and future phases will only make the system more powerful.

Thanks for reading, and stay tuned for Devlog #2!