Centralizing Certification Expiry Tracking in Microsoft 365

The Business Situation

Kestrel Ridge Testing Services is a fictional 120-person environmental testing and field inspection business. Its employees include laboratory analysts, field technicians, sample coordinators, quality specialists, supervisors, and administrative staff. Many roles require current training, professional licenses, task authorizations, safety qualifications, or annual competency evidence.

Note: This case study is provided as a representative example of the types of AI integration and digital transformation solutions Intelligex designs and delivers. Actual engagements are tailored to each client’s goals, constraints, existing systems, timeline, and available resources, so the approach, tools, and outcomes may vary.

The People Operations coordinator maintained employee and manager information. A Quality Systems manager owned the requirement catalog and reviewed most evidence. A Compliance manager approved statutory licenses and higher-risk authorizations. Department managers followed up with employees, while a Microsoft 365 administrator supported accounts, permissions, and automation connections.

The business tracked approximately 540 active employee-requirement assignments. About 60 certification renewals, new training completions, role changes, and evidence updates were processed each month.

The existing process used several Excel workbooks stored in SharePoint, email attachments, individual calendar reminders, and Teams messages. Different workbooks covered laboratory competencies, field safety training, professional licenses, and organization-wide courses. The same employee could appear under different names or identifiers in several files.

The immediate concern was not simply sending expiry emails. The business needed to know which requirements applied to each employee, whether the evidence had been reviewed, who owned the next action, and whether an expired qualification required management attention. It also needed to produce defensible audit evidence without manually reconciling several spreadsheets before each review.

The Existing Process

The original workflow followed these steps:

1. People Operations added a new employee or role change to an HR spreadsheet.
2. A Quality employee interpreted the person’s role and copied applicable requirements into one or more certification workbooks.
3. Employees emailed certificates or training records to a shared mailbox, a manager, or a Quality team member.
4. A coordinator opened the evidence, found the correct workbook, and entered issue and expiry dates manually.
5. Quality or Compliance reviewed the document through email and replied with approval or questions.
6. The coordinator copied the approval result into a notes column and moved the attachment into a SharePoint folder.
7. Spreadsheet formulas highlighted approaching expiry dates.
8. Quality employees sent manual reminder emails or Teams messages to employees and managers.
9. Before an audit, several employees reconciled employee lists, evidence folders, approval emails, and workbook rows.

Spreadsheet conditional formatting helped users notice dates, but it could not reliably assign work, capture approvals, maintain evidence links, prevent duplicate events, or record failed notifications. The process also had no consistent exception queue. If a document was unreadable or an automation-like manual step failed, the issue remained in email until someone remembered it.

What the New System Needed to Do

The implementation team defined the operating requirements before selecting a platform. This prevented the project from becoming a simple spreadsheet migration.

The system also needed to distinguish between a qualification being expired and a decision about whether an employee could perform a task. The automation would notify the responsible manager and control owner, but it would not suspend an employee, revoke access, or make a legal determination automatically.

Implementation Approaches Considered

Improving the spreadsheets

A consolidated workbook could remove some duplication and add better formulas. However, spreadsheets would still require custom handling for approvals, item-level ownership, evidence links, duplicate events, reminder history, and error recovery. This option was suitable only as a short-term stabilization measure.

Google Workspace

Google Forms, Sheets, Drive, and Apps Script could implement the process. Apps Script would provide flexible validation, notifications, and scheduled expiry checks. The drawback was organizational fit. Kestrel Ridge already used Microsoft 365 identities, Teams, and SharePoint. Adding a second productivity suite would complicate access removal, support, document governance, and user adoption.

Microsoft 365

Microsoft Lists provided structured records with choice fields, indexes, unique values, version history, and SharePoint-backed permissions. Microsoft Forms provided controlled intake. Power Automate connected intake, approvals, lists, files, and notifications. Teams gave employees and managers a familiar notification channel. This approach retained the existing identity and collaboration environment.

Airtable

Airtable would provide a strong relational interface and flexible views. It was a credible option if the business already used it or needed a more application-like front end. In this scenario, it added a new data processor and required separate security, retention, integration, licensing, and administrator reviews.

Dedicated learning or compliance software

A learning management system would be preferable if the business needed course delivery, assessments, content authoring, continuing education credits, external learner portals, or training commerce. A compliance credential platform could also offer advanced credential rules. Kestrel Ridge’s immediate scope included many external licenses and locally generated competency records, so implementing a full learning platform was not the first step.

The Selected Solution

The selected implementation used Microsoft Forms, Microsoft Lists, Power Automate, Microsoft Teams, and a SharePoint document library. These tools were already aligned with the organization’s Microsoft 365 identity and collaboration model.

The existing employee directory and Microsoft 365 accounts were retained. The old workbooks were preserved as read-only migration evidence but were removed from daily processing after reconciliation.

Manual copying between spreadsheets, forwarding evidence by email, creating calendar reminders, and searching approval threads were removed. Quality and Compliance still decided whether evidence was acceptable. Managers still decided how to handle an employee whose requirement had expired. Compliance personnel still interpreted legal and regulatory obligations.

System Architecture and Data Flow

The system separated master data, transactional requests, current certification assignments, evidence documents, and automation logs. This separation allowed a rejected request to remain in history without overwriting the current approved certification.

1. Maintain employee and role data. People Operations creates or updates an employee in the Employees List. A reconciliation flow matches the employee’s role, department, site, and hazard attributes against the Role Requirement Matrix. Missing employee-requirement assignments are created in Certification Records with a status of Missing. Invalid mappings are sent to Manual Review.
2. Submit evidence. An employee or manager submits Microsoft Forms data. The Forms trigger provides a response identifier, and Power Automate retrieves the complete response.
3. Create the request record. Power Automate creates an Evidence Requests item using the Form response identifier as an idempotency key. Microsoft Lists returns a numeric item ID. The flow converts that ID into a readable request number such as CER-2026-00184.
4. Validate the submission. The flow matches the employee email or employee ID to an active Employees record and matches the requirement code to an active Requirement Catalog record. It validates issue and expiry dates, checks required evidence, and calculates an evidence fingerprint. Failed validation updates the request to Incomplete or Manual Review and notifies the submitter.
5. Store the evidence. Forms initially stores uploaded files in the Microsoft 365 group’s SharePoint location. Power Automate creates a controlled evidence folder, copies the file, records the destination link, and retains the request ID in document metadata. A failed copy stops approval and creates an error record.
6. Route human approval. Power Automate creates a Quality approval and stores its approval identifier. If the requirement is categorized as statutory, professional, or high risk, an approved Quality review is followed by a Compliance approval. Returned and rejected requests update the request record without modifying the approved certification.
7. Update the current certification. After final approval, the flow locates the Certification Record using the unique employee-requirement key. It updates issue date, expiry date, evidence link, request ID, approval outcome, owner, status, and next reminder date. If no assignment exists, the flow creates one only when the role mapping permits it; otherwise it requests human review.
8. Notify the participants. Teams messages inform the employee, manager, and control owner that the evidence was approved, returned, rejected, or placed in manual review. The message includes the request ID and a permission-controlled record link.
9. Monitor expiry and pending work. Scheduled Power Automate flows check certification dates, outstanding approvals, returned requests, and automation failures. They write reminder log records before sending messages so duplicate scheduled runs do not send duplicate notices.
10. Produce operational reporting. Microsoft Lists views display current work. A daily snapshot flow counts required, valid, expiring, expired, missing, pending, and exception records. It writes the counts to a Compliance Snapshots List and posts a weekly summary to the restricted Compliance Operations Teams channel.

• Intake: Microsoft Forms group-owned certification evidence form
• System of record: Microsoft Lists hosted in a restricted SharePoint site
• Automation layer: Power Automate cloud flows
• Document storage: SharePoint Certification Evidence document library
• Notifications: Microsoft Teams approvals, chats, and restricted channel posts
• Reporting: Microsoft Lists operational views and Compliance Snapshots
• AI layer: Optional AI Builder policy-applicability prompt with human approval

Data Structure

The implementation used related Lists rather than one oversized table. Text keys were retained alongside lookup fields because text keys simplify imports, flow matching, and audit exports.

Certification Records fields

Evidence Requests fields

Version history was enabled for the operational Lists and the evidence library. Unique constraints were enabled for EmployeeKey, RequirementCode, ApplicabilityKey, AssignmentKey, SubmissionKey, RequestID, EventKey, and SnapshotDate where appropriate. Status, ExpiryDate, Owner, EmployeeKey, RequirementCode, and AutomationStatus were indexed for filtering.

Workflow Statuses and Ownership

Evidence request workflow

Certification assignment workflow

A record could move backward when evidence was returned, an approval was rejected, a role mapping changed, or a previously accepted document was formally suspended. Closure did not delete history. Old evidence requests remained available, and superseded documents stayed in an archive location according to the retention policy.

Step-by-Step Implementation

Step 1: Prepare the Accounts and Permissions

1. Verify required Microsoft 365 capabilities. Confirm that the tenant permits Microsoft Forms, Microsoft Lists, SharePoint, Power Automate cloud flows, Teams connector actions, and the Approvals connector. Licensing and connector entitlements vary, so the project team must verify the tenant rather than assume a particular subscription includes every feature.
2. Create a restricted SharePoint site. Create a site named for the compliance tracking function. Limit site owners to the Quality Systems manager, Compliance manager, and Microsoft 365 administrator. Grant edit access only to the operational staff who maintain records.
3. Create a Microsoft 365 group-owned Form. A group-owned Form prevents the process from depending on an individual account. File uploads are then stored in the group’s SharePoint environment rather than an employee’s personal OneDrive.
4. Create a restricted Teams channel. Use a Compliance Operations channel for failure alerts, weekly summaries, and escalations that should not be visible to the full workforce.
5. Create an automation account. Use a dedicated, licensed organizational account where the tenant’s governance policy permits it. Grant only the permissions needed to read the Form, edit the tracking Lists and library, create approvals, and post Teams notifications. Add at least two flow co-owners.
6. Create role groups. Use groups such as Certification Tracker Owners, Quality Reviewers, Compliance Approvers, People Operations Editors, and Read-Only Auditors. Managers and employees do not need broad access to the Lists if they submit through Forms and receive controlled links.
7. Prepare test users. Create or select non-production test identities representing an employee, manager, Quality reviewer, Compliance approver, automation administrator, and unauthorized user.
8. Separate testing from production. Build duplicate test Lists, a test Form, a test evidence library, and a test Teams channel. If Power Platform environment variables are available and governed, use them. Otherwise, store non-secret environment values in the Automation Configuration List.

The core implementation does not require API keys, client secrets, or database credentials. Power Automate uses authenticated Microsoft 365 connections. Credentials remain in managed connections and are not written into Lists or flow action bodies.

Step 2: Build the Intake

Create a Microsoft Form named Certification Evidence Submission. Restrict responses to people in the organization, record the responder’s identity, and allow a manager to submit on behalf of an employee.

Use Forms branching so the employee email field appears only for on-behalf submissions and the license reference appears only for applicable credential categories. Forms can enforce required questions, but cross-field validation remains in Power Automate. For example, the flow confirms that the expiry date is not earlier than the issue date.

Microsoft Forms choices do not automatically synchronize with a Microsoft List through the basic native configuration. The Quality Systems manager therefore reviews the requirement choices when the catalog changes. If the catalog changes frequently, a Power Apps intake interface would be a reasonable later upgrade.

The confirmation message should state that submission does not mean approval, identify the expected review period, provide a support contact, and instruct the employee not to resubmit unless requested. Include a privacy notice explaining the purpose, permitted users, evidence storage location, and retention approach.

Duplicate event processing is prevented with the Forms response ID. A separate evidence fingerprint flags likely duplicate business submissions while still allowing a corrected document to be submitted as a new request.

Step 3: Create the System of Record

1. Create the Employees, Requirement Catalog, Role Requirement Matrix, Certification Records, Evidence Requests, Automation Log, Compliance Snapshots, and Automation Configuration Lists.
2. Use single-line text keys instead of employee names as unique identifiers. Names can change, but EmployeeKey should remain stable.
3. Enable unique values on the keys defined in the data model.
4. Enable version history for operational Lists and the evidence library.
5. Create choice fields exactly once and reuse the documented status vocabulary across views and flows.
6. Create indexes on frequently filtered fields, including Status, ExpiryDate, EmployeeKey, RequirementCode, Owner, RequestStatus, AutomationStatus, and NextReminderDate.
7. Set defaults such as AutomationStatus = Pending, RetryCount = 0, Priority = Normal, and ApprovalStatus = Not Started.
8. Create list validation where possible, but leave multi-record validation to Power Automate. A List cannot independently verify that an employee’s role is mapped to a requirement.
9. Import employee and requirement data before certification records. Import Certification Records only after keys and date formats have been standardized.
10. Preserve the old workbooks in a restricted read-only archive and record the migration date.

The AssignmentKey naming convention is EMPLOYEEKEY|REQUIREMENTCODE. The ApplicabilityKey can combine role, department, site, hazard, and requirement values. Use a literal value such as ALL instead of blanks where a rule applies universally. This makes comparison deterministic.

Create filtered views for active employees, inactive employees, active requirements, high-risk requirements, unresolved role mappings, active certifications, expired certifications, upcoming expiries, missing evidence, pending approvals, manual review, and failed automation.

Step 4: Connect the Tools

Create Power Automate connections using the automation account. The core connections use Microsoft 365 authentication. No credentials should be pasted into expressions or configuration fields.

For each connection, configure the following failure behavior:

• Use an exponential retry policy for transient SharePoint and Teams failures where the action supports it.
• Do not retry business rejections, invalid dates, inactive employees, or unknown requirement codes.
• Store destination identifiers, including List item ID, approval ID, and SharePoint file link, immediately after each successful action.
• Update the source request to Automation Error if retries are exhausted.
• Create an Automation Log item containing the flow name, run identifier, request ID, failed stage, retry count, and sanitized error message.

Step 5: Build the Core Automation

Automation A: Reconcile employee requirements

• Trigger: An Employees item is created or modified, plus a nightly scheduled reconciliation.
• Conditions: Employee is active and has a valid RoleCode. Applicable matrix rows are active and within their effective dates.
• Actions: Match role, department, site, and hazard attributes; generate AssignmentKeys; create missing Certification Records; flag obsolete assignments for review.
• Fields updated: AssignmentKey, employee details, requirement details, owner, due date, status, and automation timestamps.
• Notification: Quality receives a message when a mapping is missing or an existing requirement may no longer apply.
• Exception: Ambiguous mappings are placed in Manual Review. The flow does not automatically mark a previously required certification as Not Required.

Exact action order:

1. Read the changed Employees item.
2. Terminate successfully if EmploymentStatus is not Active, but create a reconciliation task for existing assignments.
3. Get active Role Requirement Matrix rows.
4. Filter rows using normalized role, department, site, and hazard values.
5. For each applicable row, create the AssignmentKey.
6. Search Certification Records for that key.
7. If none exists, create a record with Status set to Missing and AutomationStatus set to Completed.
8. If one exists, refresh denormalized employee, manager, role, owner, and requirement fields without overwriting approved dates.
9. Compare existing employee assignments with the current mapping output.
10. Create Manual Review tasks for unmatched existing assignments rather than removing them automatically.

Automation B: Process a certification submission

• Trigger: Microsoft Forms reports a new response.
• Conditions: A unique response ID exists, the responder is authorized, the employee and requirement are active, dates are valid, and evidence exists.
• Actions: Create a request, validate it, store the evidence, create approvals, upsert the certification record, and record completion.
• Fields updated: Request status, approval fields, evidence link, current certification dates, next reminder date, automation status, and processing time.
• Notification: Teams messages are sent for approval requests, returns, rejections, completion, and exceptions.
• Exception: Failed records enter Incomplete, Duplicate Review, Automation Error, or Manual Review.

Exact action order:

1. Use the Forms trigger and retrieve complete response details.
2. Normalize the response ID, responder email, employee email, and requirement code.
3. Check Evidence Requests for the SubmissionKey. If it exists, terminate as a duplicate event without sending a second notification.
4. Create the Evidence Requests item with RequestStatus set to Submitted.
5. Generate the readable RequestID from the returned List item ID and update the request.
6. Set RequestStatus to Validating and AutomationStatus to Running.
7. Match the employee. Require exactly one active result.
8. Match the requirement. Require exactly one active result.
9. Confirm that the employee’s AssignmentKey exists or that the active role matrix permits creation.
10. Validate the dates and requirement-specific evidence rule.
11. Parse the upload response and calculate the EvidenceFingerprint.
12. Check recent requests for the same fingerprint. Route likely duplicates to Duplicate Review.
13. Create the destination SharePoint folder and copy the evidence file.
14. Update the request with the evidence link and SharePoint item identifier.
15. Resolve the Quality approver, including any active delegation.
16. Create and wait for the Quality approval.
17. On Return for Information, update the request and Certification Record to show the employee owns the next action.
18. On Reject, preserve the current approved certification, update the request, and notify the employee and manager.
19. On Approve, determine whether Compliance approval is required.
20. If required, create and wait for the Compliance approval.
21. After final approval, locate the Certification Record using AssignmentKey.
22. Update or create the current record, storing the request ID, evidence link, approval outcome, issue date, expiry date, status, and reminder schedule.
23. Update the Evidence Request to Completed and calculate ProcessingHours.
24. Post the completion notification in Teams.
25. Write a successful processing event to the Automation Log.

Automation C: Monitor expiries

• Trigger: Scheduled recurrence every weekday morning.
• Conditions: Certification is Active or Expiring Soon and NextReminderDate is due.
• Actions: Recalculate days remaining, update status, create a reminder log key, post Teams messages, and calculate the next reminder.
• Fields updated: Status, NextReminderDate, NextReminderOffset, LastAutomationRun, and AutomationStatus.
• Notification: Employee first, then manager and control owner as expiry approaches.
• Exception: Notification failures enter the error queue, while the certification status still reflects the approved expiry date.

The default reminder stages were 90, 60, 30, 7, and 0 days. Requirement-specific fields could override these values. Before sending, the flow creates an EventKey such as EMP-0047|FIELD-LIC-01|2027-06-30|30. The unique EventKey prevents duplicate reminders for the same certification, expiry date, and reminder stage.

Automation D: Produce compliance snapshots

• Trigger: Scheduled once per day after expiry monitoring.
• Conditions: Include active employee-requirement assignments; exclude confirmed Not Required records.
• Actions: Count statuses, calculate completion percentage, write the daily snapshot, and post a weekly summary.
• Fields updated: Required count, active count, expiring count, expired count, missing count, pending count, exception count, and completion percentage.
• Notification: Weekly Teams channel summary for authorized HR, Quality, and Compliance staff.
• Exception: A duplicate SnapshotDate terminates successfully; inconsistent counts create a reconciliation task.

Automation E: Reconcile failed and stalled records

• Trigger: Scheduled every two hours during business hours.
• Conditions: Requests remain in Submitted or Validating beyond 30 minutes, or AutomationStatus is Failed.
• Actions: Check downstream identifiers, retry safe actions, increment RetryCount, or assign Manual Review.
• Fields updated: RetryCount, AutomationStatus, ErrorMessage, LastAutomationRun, and owner.
• Notification: Restricted Teams alert after the retry limit.
• Exception: No automatic retry occurs after a business rejection or when replay could create a duplicate approval.

Step 6: Add Approvals, Reminders, and Escalations

Use the approval actions in this order:

1. Create an approval with custom responses: Approve, Return for Information, and Reject.
2. Store the returned Approval ID, approval stage, assigned approver, and requested timestamp in Evidence Requests.
3. Wait for the approval response using the stored identifier.
4. Set a bounded waiting period consistent with the organization’s operating procedure. A seven-day flow timeout was used as a representative configuration.
5. Configure a timeout branch to update the record to Manual Review rather than leaving it pending indefinitely.
6. Capture the outcome, responder, response timestamp, and comments.
7. Require comments for Return for Information and Reject. If comments are blank, route the request to Manual Review for follow-up.
8. Create the second approval only after the first approval succeeds.

A separate scheduled flow sends approval reminders because it can inspect the stored ApprovalRequestedOn timestamp without relying on a long delay branch. At two days, it messages the current approver. At five days, it notifies the configured backup or escalation owner.

The Automation Configuration List contains temporary delegations with the original approver, substitute approver, start date, end date, and approval scope. The submission flow resolves active delegation before creating an approval. If an approver becomes unavailable after creation, an authorized owner cancels the existing approval and launches a controlled reassignment flow that stores both approval IDs.

Rejection does not delete the request or existing valid certification. Return for Information keeps the request open and assigns the next action to the submitter. A corrected submission references the original RequestID so the history remains connected.

Expiry escalation follows the approved date rather than the approval date:

• At 90 and 60 days, notify the employee.
• At 30 days, notify the employee and manager.
• At 7 days, notify the employee, manager, and requirement owner.
• At expiry, update Status to Expired and alert the manager, Quality owner, and Compliance when applicable.
• After expiry, do not automatically revoke access or make a work-assignment decision. The responsible manager follows the applicable policy.

Step 7: Add Documents and File Management

Create a SharePoint document library named Certification Evidence. Configure folders using a stable key rather than employee name:

Certification Evidence/
  EMP-0047/
    FIELD-LIC-01/
      2026/
        CER-2026-00184/
          EMP-0047_FIELD-LIC-01_2027-06-30_CER-2026-00184.pdf

The folder and file naming convention makes evidence understandable outside the automation while avoiding unnecessary personal details in file names.

1. Use the group-owned Form so uploaded evidence lands in the group SharePoint environment.
2. Run a controlled test submission and inspect the actual Forms upload path. Folder labels can vary by Form name, question name, tenant language, and product version.
3. Parse the upload answer as an array.
4. Create the employee, requirement, year, and request folders if they do not exist.
5. Retrieve the source file content and create or copy the file into the controlled library.
6. Apply metadata including EmployeeKey, RequirementCode, RequestID, ExpiryDate, EvidenceStatus, and RetentionCategory.
7. Write the destination link and SharePoint item identifier back to Evidence Requests.
8. Do not delete the original upload until the destination copy has been verified and the retention procedure permits deletion.

Versioning should remain enabled. Corrected evidence is stored as a new request-specific file rather than silently replacing the approved document. When an approved record is superseded, update document metadata to Superseded and preserve it according to the retention schedule.

Restrict library access to authorized People Operations, Quality, Compliance, and audit roles. Disable anonymous links and avoid organization-wide links. Teams messages should point to the controlled file or List record; they should not attach copies that create unmanaged duplicates.

If a file is missing, unreadable, duplicated, unsupported, or larger than the documented operational limit, stop the approval route and return the request for correction. A failed upload or copy creates an Automation Error and preserves the original Forms response for recovery.

Step 8: Add Reporting and Operational Views

The daily snapshot uses Certification Records as its source. The denominator is the number of active required assignments. The numerator is the number with approved current evidence. Pending, missing, expired, suspended, and manual-review records remain outside the numerator.

Microsoft Lists views refresh from current list data. The Compliance Snapshots flow runs after the expiry flow so daily counts reflect the latest status changes. The Quality Systems manager owns metric definitions and investigates sudden count changes. Alert thresholds include any Critical expired record, any failed flow after retries, or a material mismatch between active role assignments and Certification Records.

A more advanced Power BI model could be added later, but it was not required for the first implementation. The selected design kept operational reporting close to the underlying records.

Step 9: Add Security and Governance Controls

• Least privilege: Restrict list and library editing to operational roles. Employees submit through Forms rather than browsing all certification records.
• Role-based permissions: Separate site owners, record editors, approvers, auditors, and automation administrators.
• Sensitive fields: Collect only necessary license references. Do not place full identifiers in Teams messages or broadly visible views.
• Shared links: Disable anonymous sharing and avoid organization-wide evidence links.
• Credentials: Keep Microsoft credentials in managed Power Automate connections. Do not store passwords, tokens, or secrets in Lists.
• Activity records: Enable List and library versioning, retain approval identifiers, and write flow events to Automation Log.
• Access removal: Include site, Team, flow ownership, approval groups, and automation connections in employee offboarding procedures.
• Retention: Set retention periods with Legal and Compliance based on the evidence category and governing obligations.
• Backups: Confirm the organization’s Microsoft 365 backup and recovery approach, and periodically export configuration and schema documentation.
• Privacy: Limit processing to necessary employment and qualification data. Document purpose, access, retention, and correction procedures.
• Regulatory interpretation: Legal or Compliance staff determine which rules apply. The automation enforces approved configurations but does not provide legal conclusions.
• AI restrictions: The optional AI process receives policy text and controlled role taxonomy, not employee names, emails, license numbers, health information, or evidence files.

Item-level permissions can become difficult to maintain at scale. In this scenario, broad employee access to the Lists was avoided. Employees and managers interacted through Forms, approvals, and targeted Teams notifications. Auditors received time-limited read access to approved views and evidence as authorized.

Step 10: Deploy and Test

1. Build all Lists, fields, views, folders, and flows in the test site.
2. Load synthetic employees, requirements, role mappings, and certification records.
3. Run developer tests for each action, expression, branch, and error scope.
4. Run integration tests across Forms, Lists, SharePoint, Approvals, and Teams.
5. Conduct user acceptance testing with People Operations, Quality, Compliance, a manager, and an employee.
6. Reconcile imported records against the archived workbooks. Each active employee and requirement should have one AssignmentKey.
7. Pilot the workflow with one department and a limited requirement set for two renewal cycles or an agreed period.
8. Document defects, adjust routing rules, and repeat failed tests.
9. Freeze spreadsheet edits immediately before production migration.
10. Import final records, rerun role reconciliation, and obtain Quality approval of the baseline counts.
11. Turn on production flows in this order: role reconciliation, intake, approvals, expiry monitoring, snapshot reporting, then failure reconciliation.
12. Publish a launch message explaining the Form, expected response, support channel, and responsibility boundaries.
13. Monitor every run during the first week and review daily for the first month.

The rollback plan keeps the archived spreadsheets read-only, disables production flows, preserves all newly submitted requests, and routes urgent evidence to the controlled shared mailbox while the issue is resolved. Rollback must not delete records created during the pilot or launch period.

Code and Configuration

No custom application code is required for the core solution. Microsoft Forms, Microsoft Lists, SharePoint, Power Automate, Teams, and Approvals provide the necessary triggers and actions. The following expressions, schemas, and native configuration complete the technical implementation.

Configuration values

Create rows in the Automation Configuration List for non-secret values. Do not put passwords or access tokens in this List.

SHAREPOINT_SITE_URL = YOUR_SHAREPOINT_SITE_URL
EMPLOYEES_LIST_NAME = Employees
REQUIREMENTS_LIST_NAME = Requirement Catalog
ROLE_MATRIX_LIST_NAME = Role Requirement Matrix
CERTIFICATIONS_LIST_NAME = Certification Records
REQUESTS_LIST_NAME = Evidence Requests
LOG_LIST_NAME = Automation Log
EVIDENCE_LIBRARY_NAME = Certification Evidence
COMPLIANCE_TEAM_ID = YOUR_TEAM_ID
COMPLIANCE_CHANNEL_ID = YOUR_CHANNEL_ID
SUPPORT_EMAIL = YOUR_EMAIL_ADDRESS
DEFAULT_REMINDER_1_DAYS = 90
DEFAULT_REMINDER_2_DAYS = 60
DEFAULT_REMINDER_3_DAYS = 30
DEFAULT_REMINDER_4_DAYS = 7
APPROVAL_REMINDER_DAYS = 2
APPROVAL_ESCALATION_DAYS = 5
MAX_RETRY_COUNT = 3

Power Automate expressions

Generate the readable request ID after the Create item action returns the List item ID:

concat(
  'CER-',
  formatDateTime(utcNow(),'yyyy'),
  '-',
  formatNumber(outputs('Create_Evidence_Request')?['body/ID'],'00000')
)

Create the unique employee-requirement assignment key:

concat(
  toUpper(trim(outputs('Employee_Key'))),
  '|',
  toUpper(trim(outputs('Requirement_Code')))
)

Extract the requirement code from a Form choice formatted as CODE | Name:

toUpper(trim(first(split(outputs('Requirement_Choice'),'|'))))

Validate that both dates exist and the expiry is not before the issue date:

and(
  not(empty(outputs('Issue_Date'))),
  not(empty(outputs('Expiry_Date'))),
  greaterOrEquals(
    ticks(outputs('Expiry_Date')),
    ticks(outputs('Issue_Date'))
  )
)

Match an active employee in a Filter array action without relying on a dynamically constructed OData query:

@and(
  equals(
    toLower(trim(item()?['WorkEmail'])),
    toLower(trim(outputs('Employee_Email')))
  ),
  equals(item()?['EmploymentStatus'],'Active')
)

Build a likely duplicate evidence fingerprint:

concat(
  toUpper(trim(outputs('Employee_Key'))),
  '|',
  toUpper(trim(outputs('Requirement_Code'))),
  '|',
  formatDateTime(outputs('Expiry_Date'),'yyyy-MM-dd'),
  '|',
  toLower(trim(first(body('Parse_Upload_JSON'))?['name']))
)

Calculate whole days remaining until expiry:

int(
  div(
    sub(
      ticks(formatDateTime(items('Apply_to_each_certification')?['ExpiryDate'],'yyyy-MM-ddT00:00:00Z')),
      ticks(startOfDay(utcNow()))
    ),
    864000000000
  )
)

Calculate processing hours when a request completes:

div(
  float(
    sub(
      ticks(utcNow()),
      ticks(outputs('Request_Created_Date'))
    )
  ),
  36000000000
)

Calculate the completion percentage while preventing division by zero:

if(
  equals(variables('RequiredCount'),0),
  100,
  mul(
    div(
      float(variables('CurrentApprovedCount')),
      float(variables('RequiredCount'))
    ),
    100
  )
)

Forms upload parsing schema

Place a Parse JSON action after retrieving the Form response. Use the upload question output as the content. If the field can be optional, first test that it is not empty.

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "name": {
        "type": "string"
      },
      "link": {
        "type": "string"
      },
      "id": {
        "type": "string"
      },
      "type": {
        "type": ["string", "null"]
      },
      "size": {
        "type": ["integer", "null"]
      },
      "referenceId": {
        "type": ["string", "null"]
      },
      "driveId": {
        "type": ["string", "null"]
      },
      "status": {
        "type": ["integer", "string", "null"]
      }
    },
    "required": [
      "name",
      "link"
    ]
  }
}

The exact upload object can differ by tenant and connector version. Run a test response, inspect the flow trigger output, and adjust optional properties without changing the required file name and link handling.

Error scopes and run-after configuration

Group each primary flow into three Power Automate scopes:

1. Try: Contains validation, file handling, approval, record update, and notification actions.
2. Catch: Runs after Try has failed or timed out. It updates the request to Automation Error, increments RetryCount, writes Automation Log, and posts a restricted Teams alert.
3. Finally: Runs after Try or Catch completes. It records LastAutomationRun and the flow run identifier.

Configure Catch using run-after conditions for failure and timeout. Configure Finally to run after success, failure, timeout, or skip. Do not expose raw connector headers, tokens, or document contents in the ErrorMessage field.

Deployment and testing

Import or copy the flows into the test environment, replace every placeholder connection and configuration value, and save each flow while it remains turned off. Submit one synthetic Form response and inspect each dynamic value. Confirm the returned request ID, approval ID, SharePoint file link, Teams message, and certification AssignmentKey.

Likely configuration errors include an incorrect Forms upload path, renamed List columns with different internal names, a flow connection lacking library access, a Person field receiving plain text in the wrong format, or a response schema that does not match the tenant’s upload object. Use Power Automate run history and the output of the failed action to isolate the stage. Correct the configuration in the test flow before exporting or copying it to production.

Failure Handling and Operational Reliability

Idempotency is implemented at three levels:

• The Forms response ID prevents the same trigger event from creating multiple requests.
• The AssignmentKey prevents multiple current certification records for the same employee and requirement.
• The reminder EventKey prevents the same reminder stage from being sent twice for the same expiry date.

Transient connector actions can retry automatically. Business decisions cannot. A rejection, ambiguous role mapping, invalid employee, or conflicting expiry date always requires corrected information or human review.

The Automation Log acts as a practical dead-letter queue. Failed events record the object key, stage, retry count, responsible owner, and recovery status. The reconciliation flow compares requests, certification records, evidence links, and approval identifiers to find partial completions.

Staff recover a failed record by opening the Manual Review view, checking the request and flow run identifier, verifying which destination identifiers already exist, correcting the underlying problem, and launching a controlled recovery flow. Recovery actions must reuse existing keys rather than create replacement records blindly.

A Complete Example

Elena Park is a fictional field sampling technician with EmployeeKey EMP-0047. Her role code is FIELD-TECH. The Role Requirement Matrix shows that this role requires FIELD-LIC-01, an externally issued field sampling license requiring Quality and Compliance approval.

1. Elena submits the Certification Evidence Submission Form on July 15, 2026. She selects her own certification, chooses FIELD-LIC-01 | Field Sampling License, enters an issue date of July 1, 2026, an expiry date of June 30, 2027, and uploads a PDF.
2. Microsoft Forms produces response ID 184. Power Automate retrieves the response and creates an Evidence Requests item with SubmissionKey 184.
3. Microsoft Lists returns item ID 184. The flow generates RequestID CER-2026-00184.
4. The employee match returns exactly one active Employees record. The requirement match returns exactly one active requirement. The role matrix confirms that the requirement applies.
5. The flow creates AssignmentKey EMP-0047|FIELD-LIC-01. It finds the existing Certification Record, which expires on July 31, 2026.
6. The date condition confirms that June 30, 2027 is after July 1, 2026. The evidence fingerprint does not match another open request.
7. The flow copies the PDF to EMP-0047/FIELD-LIC-01/2026/CER-2026-00184 and stores the destination link.
8. A Quality approval is created. The Evidence Request stores the approval ID and changes to Pending Quality Approval. A Teams approval notification goes to the designated reviewer.
9. The reviewer verifies the document, selects Approve, and adds a comment. The flow records the responder and timestamp.
10. Because the requirement category is statutory, the flow creates a second approval for the Compliance manager. The request changes to Pending Compliance Approval.
11. Compliance approves. The flow updates the Certification Record with the new dates, document link, request ID, and approval evidence.
12. The existing evidence remains in the library with metadata marked Superseded. The new record status becomes Active.
13. The flow sets the first applicable reminder for 90 days before June 30, 2027.
14. The Evidence Request changes to Completed. Teams messages notify Elena, her manager, and the requirement owner.
15. The Automation Log records successful completion, and the next daily compliance snapshot counts the assignment as current.

If the Compliance approval had been returned, the existing July 31, 2026 certification would have remained unchanged. The request would have moved to Returned for Information, Elena would have received the approver’s comments, and the corrected submission would have referenced CER-2026-00184.

Implementation Cost

All amounts below are representative planning assumptions, not verified client pricing or results. Software entitlements, internal rates, implementation scope, tax, and professional fees must be confirmed for each organization.

An incremental software assumption of zero does not mean the system has no cost. Existing Microsoft 365 subscriptions remain business expenses, and the tracker requires administration, monitoring, testing, training, and maintenance.

Estimated Time and Cost Savings

The following estimates use conservative planning assumptions:

• Monthly workflow volume: 60 certification submissions or updates
• Current handling time: 24 minutes per record
• New normal handling time: 5 minutes per record
• Exception rate: 15 percent
• Additional exception handling: 12 minutes per exception
• Monthly maintenance: 4 hours
• Loaded hourly labour cost: $52
• Recurring incremental core software cost: $0 in this example
• One-time internal implementation cost: $6,448

Current monthly labour hours: Monthly volume × current minutes per record ÷ 60

New monthly labour hours: Monthly volume × new minutes per record ÷ 60, plus exception handling and maintenance

Monthly hours recovered: Current monthly labour hours minus new monthly labour hours

Estimated monthly labour value: Monthly hours recovered × loaded hourly labour cost

Net estimated monthly value: Monthly labour value minus recurring tool costs

Estimated payback period: One-time implementation cost ÷ net estimated monthly value

Recovered time does not automatically reduce payroll. It may provide additional administrative capacity, quicker evidence turnaround, less overtime, fewer follow-ups, reduced dependence on one coordinator, and the ability to handle higher certification volume without proportional administrative growth.

Non-financial benefits include clearer ownership, fewer incomplete records, consistent reminders, stronger audit history, quicker exception detection, more reliable completion reporting, and a more predictable experience for employees and managers.

Readers should replace the volume, handling time, exception rate, maintenance effort, labour rate, software cost, implementation hours, and implementation rate with their own figures. They should also account for internal security review, data cleanup, change management, and any licensing required by their tenant.

Adding AI to the Automation

AI should be added only after the deterministic tracker, approval workflow, expiry monitoring, and exception controls operate reliably.

The core system does not need AI to compare dates, enforce required fields, generate keys, apply approved role mappings, send reminders, or calculate completion rates. Those tasks are more reliable as validation rules, lookups, conditions, and formulas.

A useful AI application arises when a new policy, regulatory bulletin, or internal procedure contains unstructured language. A Compliance employee may need to determine which roles, departments, sites, hazards, and existing requirements could be affected. AI can read the changed text and propose applicability attributes for human review.

The safer design does not send an employee roster to the model. AI classifies the policy against approved role and requirement codes. After a human confirms the proposed rules, Power Automate deterministically matches those codes to Employees and Certification Records. This produces an affected-employee list without asking AI to make final compliance or employment decisions.

The Recommended AI Enhancement

The recommended enhancement uses an approved AI Builder custom prompt called Classify Policy Applicability v1, invoked from Power Automate. Availability, licensing, regional support, model options, and interface labels must be verified in the organization’s Power Platform environment.

• Trigger: A Policy Change Reviews item changes to Ready for AI Review.
• AI input: Changed policy text, approved role taxonomy, department codes, site codes, hazard tags, and active requirement catalog.
• System instruction: Classify possible applicability using only supplied codes. Do not make legal conclusions or final employee decisions.
• User prompt: Supplies the changed policy text and controlled taxonomies.
• Expected output: Strict JSON containing a summary, proposed applicability rules, confidence values, uncertainties, and human-review requirement.
• Validation: Parse JSON, confirm every returned code exists, check confidence range, and reject unknown fields.
• Record update: Store the raw output, prompt version, proposed codes, confidence, and review status in Policy Change Reviews.
• Human review: Compliance confirms, edits, or rejects every proposed rule.
• Low confidence: Any confidence below 0.80 or any unresolved uncertainty requires explicit manual review.
• Prohibited data: Employee names, emails, IDs, license numbers, health information, evidence documents, and confidential personnel notes.
• Failure behavior: If the model fails or JSON cannot be parsed, set AI Status to Failed and use the normal manual policy-review process.

Reusable system instruction

You classify policy changes against a controlled workforce and certification taxonomy.

Use only role codes, department codes, site codes, hazard tags, and requirement codes supplied by the user. Never invent a code.

Do not provide legal advice. Do not decide whether an employee may work, should be disciplined, should lose access, or should be removed from a role.

Identify possible applicability, explain the supporting policy language, state uncertainty, and require human review.

Return one valid JSON object matching the requested schema. Return no prose before or after the JSON.

Reusable user prompt

Analyze the policy change below.

POLICY CHANGE ID:
{{POLICY_CHANGE_ID}}

CHANGED POLICY TEXT:
{{POLICY_TEXT}}

ALLOWED ROLE CODES:
{{ROLE_TAXONOMY_JSON}}

ALLOWED DEPARTMENT CODES:
{{DEPARTMENT_TAXONOMY_JSON}}

ALLOWED SITE CODES:
{{SITE_TAXONOMY_JSON}}

ALLOWED HAZARD TAGS:
{{HAZARD_TAXONOMY_JSON}}

ACTIVE REQUIREMENT CATALOG:
{{REQUIREMENT_CATALOG_JSON}}

Tasks:
1. Summarize the operational change in no more than 120 words.
2. Identify potentially affected role, department, site, hazard, and requirement codes.
3. Cite the relevant policy wording in a short reason.
4. Assign a confidence value from 0 to 1 for each proposed applicability rule.
5. List ambiguities or missing information.
6. Set human_review_required to true.
7. Use only supplied codes. If no supplied code fits, leave the array empty and explain the gap in uncertainties.

Return strict JSON matching the supplied schema.

Structured output schema

{
  "type": "object",
  "properties": {
    "policy_change_id": {
      "type": "string"
    },
    "summary": {
      "type": "string"
    },
    "effective_date": {
      "type": ["string", "null"]
    },
    "affected_rules": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "role_codes": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "department_codes": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "site_codes": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "hazard_tags": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "requirement_codes": {
            "type": "array",
            "items": {
              "type": "string"
            }
          },
          "reason": {
            "type": "string"
          },
          "confidence": {
            "type": "number",
            "minimum": 0,
            "maximum": 1
          }
        },
        "required": [
          "role_codes",
          "department_codes",
          "site_codes",
          "hazard_tags",
          "requirement_codes",
          "reason",
          "confidence"
        ]
      }
    },
    "uncertainties": {
      "type": "array",
      "items": {
        "type": "string"
      }
    },
    "human_review_required": {
      "type": "boolean"
    }
  },
  "required": [
    "policy_change_id",
    "summary",
    "effective_date",
    "affected_rules",
    "uncertainties",
    "human_review_required"
  ]
}

Example model response

{
  "policy_change_id": "POL-2026-014",
  "summary": "The revised procedure may require annual refresher evidence for employees who collect regulated field samples while using respiratory protection.",
  "effective_date": "2026-10-01",
  "affected_rules": [
    {
      "role_codes": [
        "FIELD-TECH",
        "FIELD-LEAD"
      ],
      "department_codes": [
        "FIELD"
      ],
      "site_codes": [
        "ALL"
      ],
      "hazard_tags": [
        "RESPIRATOR"
      ],
      "requirement_codes": [
        "RESP-FIT-ANNUAL"
      ],
      "reason": "The changed clause applies to personnel collecting regulated samples while assigned respiratory protection.",
      "confidence": 0.86
    }
  ],
  "uncertainties": [
    "The policy text does not state whether supervised trainees are included."
  ],
  "human_review_required": true
}

Power Automate configuration

1. Create the custom prompt with three or more text inputs for policy text and taxonomy JSON.
2. Test the prompt with synthetic policy content and publish the approved prompt version.
3. Create a flow triggered when a Policy Change Reviews item is modified.
4. Terminate unless ReviewStatus equals Ready for AI Review and AIProcessedVersion differs from the current item version.
5. Get active Requirement Catalog and Role Requirement Matrix items.
6. Use Select actions to remove personal data and retain only approved codes and descriptions.
7. Serialize the selected data as JSON and send it to the published prompt action.
8. Store the unmodified model output in a restricted long-text field.
9. Parse the output using the JSON schema above.
10. Validate every returned code against the source Lists. Unknown codes cause validation failure.
11. Set ReviewStatus to Human Review Required and post a Teams task to Compliance.
12. After human approval, run a separate deterministic matching flow against active Employees records.
13. Create proposed requirement assignments or policy-impact tasks, not final active requirements.
14. Require a second authorized confirmation before modifying Role Requirement Matrix or Certification Records.

Log the policy change ID, prompt version, execution timestamp, reviewer, output validation result, correction reason, and usage information available from the selected service. Do not log prohibited employee data in the prompt record.

Benefits of the AI Enhancement

The core automation already centralizes records, routes approvals, sends reminders, maintains evidence links, and reports expiry status. Those benefits do not depend on AI.

The AI enhancement specifically reduces the time required to read changed policy language and translate it into candidate workforce attributes. It can provide:

• Faster initial summaries of unstructured policy changes
• More consistent use of approved role and requirement terminology
• Quicker identification of potentially affected departments, sites, or hazard groups
• Visible uncertainty instead of an unsupported exact answer
• A structured starting point for Compliance review
• Better reporting on policy changes that may require new training or evidence

The output remains a recommendation. It can omit context, misunderstand terminology, or assign excessive confidence. Human review and deterministic matching remain mandatory.

What Remains Rule-Based or Human-Controlled

Estimating the Additional Value of AI

Assume the business evaluates eight policy, procedure, or regulatory changes per month.

Additional gross time recovered compared with the core tracker: 8 × (35 − 15) ÷ 60 = 2.67 hours

Correction time: 8 × 25% × 10 ÷ 60 = 0.33 hours

Failure fallback time: 8 × 5% × 35 ÷ 60 = 0.23 hours

Net additional capacity: 2.67 − 0.33 − 0.23 = approximately 2.11 hours per month

Additional labour value: 2.11 × $52 = approximately $109.72 per month

Net value after AI usage allowance: $109.72 − $20 = approximately $89.72 per month

These figures are planning assumptions, not expected service guarantees. Correction and failure rates should be measured during a controlled pilot. AI does not eliminate policy review or human accountability.

Testing Checklist

Use synthetic sample data before processing real employee or certification information.

Ongoing Maintenance

The Quality Systems manager is the primary business owner. The Compliance manager is the backup business owner. The Microsoft 365 administrator owns connections, flow health, and technical recovery. People Operations owns employee, manager, role, and employment-status accuracy.

Maintenance documentation should include List schemas, internal field names, flow diagrams, connection owners, configuration values, approval routing, recovery steps, known limitations, test cases, and change history. Changes to status values or List columns must be tested because they can break flow conditions and mappings.

When to Move to Dedicated Software

The Microsoft 365 implementation can remain appropriate while the volume, permission model, and workflow complexity are manageable. Replacement should be based on operational evidence rather than an assumption that a configured platform must eventually be discarded.

Consider dedicated learning, credentialing, governance, risk, compliance, or workforce management software when several of these conditions appear:

• Transaction and employee volumes make flow duration or List maintenance difficult.
• The business needs formal course delivery, testing, content authoring, or continuing education credits.
• Customers, contractors, or external learners require a self-service portal.
• Permissions must vary by legal entity, region, client, project, or individual record.
• Formal regulatory requirements demand validated software controls or specialized audit reports.
• Multiple locations need different regulatory catalogs, languages, and delegated administration.
• Integrations are required with HR systems, identity governance, access control, scheduling, payroll, or external licensing authorities.
• Exception rates or manual reconciliation effort continue to rise.
• Flow maintenance requires excessive specialist time.
• Mobile or offline evidence capture becomes necessary.
• Advanced analytics, forecasting, or regulatory content subscriptions are required.
• The business requires vendor service commitments and specialist product support.
• Security risk increases because sensitive evidence requires more granular controls.

A dedicated platform does not automatically eliminate integration work. Employee master data, identity, Teams notifications, evidence migration, reporting, and downstream access decisions may still require controlled interfaces.

Implementation Checklist

• Confirm the regulated business requirements and decision owners.
• Define the employee, role, site, hazard, and requirement taxonomy.
• Select Microsoft Forms, Microsoft Lists, Power Automate, Teams, and SharePoint.
• Verify tenant licensing and connector availability.
• Create group-owned accounts, Forms, sites, Teams channels, and flow ownership.
• Apply least-privilege permissions and evidence access controls.
• Create the Employees and Requirement Catalog master data.
• Create the Role Requirement Matrix.
• Create Certification Records and Evidence Requests.
• Enable unique keys, indexes, version history, and controlled statuses.
• Build the intake fields, validation, branching, privacy notice, and file upload.
• Document every source-to-destination field mapping.
• Build employee-requirement reconciliation.
• Build submission validation and duplicate prevention.
• Build SharePoint folder creation and evidence linking.
• Build Quality and Compliance approvals.
• Configure return, rejection, delegation, timeout, and reassignment handling.
• Configure expiry reminders and escalations.
• Configure Teams task notifications and restricted error alerts.
• Create operational views and compliance snapshots.
• Configure Try, Catch, Finally, retry, and manual-recovery controls.
• Test normal, invalid, duplicate, rejected, failed, and recovered records.
• Complete user acceptance testing with HR, Quality, Compliance, managers, and employees.
• Reconcile and migrate existing spreadsheet records.
• Document cost and savings assumptions.
• Pilot before full activation.
• Add policy-classification AI only after the core workflow is stable.
• Keep AI output advisory, validate returned codes, and require human approval.
• Assign primary and backup maintenance owners.
• Define review dates and criteria for moving to dedicated software.