Current step: Reminder emails + admin maintenance Updated 14 Aug 2026 Training Tracker only

Training Tracker v3 Fix Guide

The old full build guide has been retired from this URL for now. This page is the new working area for the v3 YAML fixes, starting with the SharePoint schema that must exist before the pasted screens will stop red-lining.

Where you are on this page:

✅ Done — Schema, app setup, and Fixes 1–8 (collapsed below, click to re-open if you need to re-check a step).
✅ Done — Add Mode Buttons build pass (collapsed below).
Done or nearly done — Remote Worker Exclusions, rebuilt with complete verified formulas.
Current focus — Course Expiry Reminder Emails, rebuilt as a separate start-to-finish guide with locked reminder log, double safety switch, duplicate-send protection, rich email, and deep links.
New admin pass — Admin Dashboard Certificate And Reminder Visibility, covering Jackie opening staff certificates from the dashboard and seeing the last live reminder email per person.
New build pass — Admin Data Maintenance, covering certificate library metadata and an admin-only permanent remove-person flow.
Then — Snapshot Dashboard (not started).
Superseded, not part of the current build — Certificate Privacy Upgrade below describes an earlier approach (collapsed). The certificate system that's actually live is a different, later design: Certificate Library & In-App Viewer — Full Guide.

✅ Done: Schema, app setup, and Fixes 1–8 (click to expand if you need to re-check a step)

What This Page Is

This is not a rebuild-from-scratch guide. Use it as a short, follow-down checklist while you patch the existing Training Tracker app.

Do this before chasing screen errors: create the new list, add the new columns, refresh the data sources, then reopen the pasted screen formulas. Errors mentioning TT AuditLog, LastUpdatedByEmail, LastUpdatedOn, LastStatus or AdminNote are schema errors until this page is complete.

What Is `TT AuditLog` For?

TT AuditLog is an append-only history list. It records small events caused by the app: certificate saved, manager upload enabled, person updated, course archived, course restored, and snapshot activity.

It was added in v3 for two reasons:

Activity timeline: the admin dashboard drill-down can show recent activity for a person instead of just static course rows.
Accountability: if Jackie or another admin later asks why something changed, the app has a dated event with the actor email.

The compliance calculations do not need TT AuditLog. The current v3 YAML does need it because several formulas patch that list and the admin dashboard reads it for the timeline.

Fix 1 - SharePoint Schema

Complete this schema work first. Create column internal names exactly as shown, with no spaces.

Create New List: `TT AuditLog`

Open the same SharePoint site used by Training Tracker.
Create a new blank list called TT AuditLog.
Add the columns below.

Column internal name	Type	Notes
`Title`	Single line text, built-in	Short event title.
`EntityType`	Single line text	Example values: Person, Course, TrainingRecord, Report.
`EntityID`	Single line text	ID of the related SharePoint row, stored as text.
`PersonEmail`	Single line text	Lowercase person email for person-specific events. Blank for course/report events.
`CourseID`	Single line text	Course ID for course-specific events. Blank otherwise.
`EventType`	Single line text	Short event category shown by the timeline.
`EventDescription`	Multiple lines of text	Plain text. Human-readable timeline text.
`ActorEmail`	Single line text	Email of the person/admin who caused the event.
`EventDateTime`	Date and time	Include date and time.
`Notes`	Multiple lines of text	Plain text. Optional extra context.

Permissions: the app writes to TT AuditLog from admin actions and certificate saves. For now, give normal app users permission to create items and give admins full control/read access. If your SharePoint permission model is locked down, we can tighten this later after the screens are working.

Add Columns To Existing Lists

Add these four columns to each existing Training Tracker list:

TT Personnel
TT Courses
TT TrainingRecords

Column internal name	Type	Why it exists
`LastUpdatedByEmail`	Single line text	The app writes the email of the last user/admin who changed the row.
`LastUpdatedOn`	Date and time	The app writes `Now()` when a row changes.
`LastStatus`	Single line text	Short status such as Created, Updated, Archived, Restored or Certificate saved.
`AdminNote`	Multiple lines of text	Plain text. Reserved for admin explanation notes.

Internal names matter: create the column with the exact no-space name first. You can change the display name later if needed, but do not create names like Last Updated By Email.

Confirm Course Delivery Columns

The restored course launch feature needs these columns on TT Courses. They existed in the earlier Training Tracker design, but check them before pasting the updated admin/course screens.

Column internal name	Type	Required?
`CourseHost`	Choice	Yes. Suggested choices: Defence Learning Environment, Civil Service Learning, SharePoint, Teams, External provider, Local file, Other.
`CourseURL`	Single line text	Yes. Used by the Open course button.
`CourseDescription`	Multiple lines of text, plain text	No. Hidden on the course screen when blank.

Remote Worker Exclusion Columns

Add these two Yes/No columns for the remote-worker exclusion pass.

List	Column internal name	Type	Default
`TT Personnel`	`RemoteWorker`	Yes/No	No / false
`TT Courses`	`ExcludeRemoteWorkers`	Yes/No	No / false

Create New List: `TT ComplianceSnapshots`

This list stores the historical monthly percentages used by the screenshot dashboard. Without stored snapshots, the app can calculate today's compliance but cannot honestly reconstruct old month-end percentages.

Column internal name	Type	Notes
`Title`	Single line text, built-in	Course title plus month.
`SnapshotMonth`	Date only	Use the first day of the reporting month.
`CourseID`	Number	The related `TT Courses` ID.
`CourseTitle`	Single line text	Stored title at snapshot time.
`RequiredCount`	Number	How many active staff required the course after exclusions.
`InDateCount`	Number	How many of those staff were in date.
`CompliancePct`	Number	Whole-number percentage.
`SnapshotTakenOn`	Date and time	When the snapshot was saved.
`SnapshotTakenBy`	Single line text	Admin email.
`Notes`	Multiple lines of text	Plain text, optional.

Connect And Refresh In Power Apps

Open the Training Tracker app in Power Apps Studio.
Open the Data pane.
Add the SharePoint list TT AuditLog as a new data source.
Add the SharePoint list TT ComplianceSnapshots as a new data source.
Refresh these data sources: TT Personnel, TT Courses, TT TrainingRecords, TT AuditLog, TT ComplianceSnapshots.
Save the app.
Reopen the pasted screen and check whether the schema errors have gone.

Expected result: errors about missing TT AuditLog, LastUpdatedByEmail, LastUpdatedOn, LastStatus and AdminNote should disappear. Any remaining errors are then real screen/control/formula issues and we can tackle them one by one.

App Setup

After the SharePoint schema is in place, check the app-level setup before pasting or troubleshooting the screens.

Data Sources Required

TT Personnel
TT Courses
TT TrainingRecords
TT AuditLog
TT ComplianceSnapshots
Office365Users

App Formulas Dropdown

No named formulas are required for the current v3 YAML. If the app already has old named formulas, leave them alone unless they reference a removed data source or hard-code the old blue theme.

App.OnStart

Paste this into App → OnStart, then run OnStart from Power Apps Studio. This initialises the shared variables the screens read. It does not globally load TT AuditLog; the admin dashboard loads that timeline data when the admin screen opens.

Set(varMyEmail, Lower(User().Email));
Set(varMe, LookUp('TT Personnel', Lower(Email) = varMyEmail));
Set(varIsAdmin, If(IsBlank(varMe), false, Coalesce(varMe.IsAdmin, false)));
Set(varViewingForSelf, true);
Set(varViewingPerson, LookUp('TT Personnel', ID = -1));
Set(varShowArchive, false);
Set(varTab, "People");
Set(varAdminFilter, "All");
Set(varAdminStatusFilter, "All");
Set(varAdminLoading, false);
Set(varHTML, "");
Set(varTTTheme,
    {
        Primary: RGBA(0,78,66,1),
        Success: RGBA(34,139,80,1),
        Warning: RGBA(239,159,39,1),
        Danger: RGBA(163,45,45,1),
        Page: RGBA(247,249,252,1),
        Panel: RGBA(255,255,255,1),
        Border: RGBA(221,227,234,1),
        Muted: RGBA(85,85,85,1),
        Soft: RGBA(232,244,239,1)
    }
);
ClearCollect(colCourses, 'TT Courses');
ClearCollect(colAllPeople, Filter('TT Personnel', Coalesce(Active, true) = true));
ClearCollect(colAllRecords, 'TT TrainingRecords')

Why TT AuditLog is not collected here: admins read the timeline on scrAdminDash.OnVisible. Normal users only write audit entries when saving certificates, so keeping the read out of App.OnStart avoids unnecessary permission pain.

Fix 2 - Date Of Completion Pre-fill

If scrCourseDetail shows the saved certificate attachment but the date picker is blank, fix the existing controls below. You do not need to repaste the whole screen.

Select DateCompleted_DataCard1.
Set its Default property to this formula.

=ThisItem.DateCompleted

Select the date picker inside that card, DataCardValue1.
Set its SelectedDate property to this formula.

=Coalesce(varRecord.DateCompleted, Parent.Default)

Expected result: when a record already has a completion date, the date picker opens with that saved date. Choosing a different date still saves a replacement date when you press Save record.

Fix 3 - Add Course Validation

If pressing Add course with a missing required field clears the text you already typed, replace only the button formula below. You do not need to repaste the screen.

Select btnAddCourse on scrAdminManage.
Set its OnSelect property to this full replacement.

=If(
    IsBlank(Trim(txtNewCourse.Text)) || CountRows(Filter(cmbNewApplic.SelectedItems, !IsBlank(Value))) = 0 || IsBlank(drpNewProvider.Selected.Value) || IsBlank(Trim(txtNewCourseURL.Text)),
    Notify("Course needs a name, staff type, provider and URL.",
           NotificationType.Warning),
    IfError(
        With(
            {
                savedCourse: Patch('TT Courses', Defaults('TT Courses'),
                    {
                        Title:            Trim(txtNewCourse.Text),
                        ApplicableTo:     ForAll(Filter(cmbNewApplic.SelectedItems, !IsBlank(Value)), {Value: Value}),
                        RecurrenceMonths: drpNewRecur.Selected.Months,
                        CourseHost:       {Value: drpNewProvider.Selected.Value},
                        CourseURL:        Trim(txtNewCourseURL.Text),
                        CourseDescription: Trim(txtNewCourseDesc.Text),
                        Status:           {Value: "Active"},
                        LastUpdatedByEmail: varMyEmail,
                        LastUpdatedOn: Now(),
                        LastStatus: "Created"
                    }
                )
            },
            Patch('TT AuditLog', Defaults('TT AuditLog'),
                {
                    Title: savedCourse.Title & " added",
                    EntityType: "Course",
                    EntityID: Text(savedCourse.ID),
                    PersonEmail: "",
                    CourseID: Text(savedCourse.ID),
                    EventType: "Course added",
                    EventDescription: savedCourse.Title & " added to the mandatory course list.",
                    ActorEmail: varMyEmail,
                    EventDateTime: Now(),
                    Notes: ""
                }
            );
            Notify("Course added.", NotificationType.Success);
            Reset(txtNewCourse); Reset(cmbNewApplic); Reset(drpNewRecur); Reset(drpNewProvider); Reset(txtNewCourseURL); Reset(txtNewCourseDesc);
            ClearCollect(colCourses, 'TT Courses');
            true
        ),
        Notify("Course could not be added: " & FirstError.Message, NotificationType.Error);
        true
    )
)

Expected result: if Applies to, provider, URL or course name is missing, the app shows a warning and keeps your typed values. Fields reset only after a successful course add.

Fix 4 - Blank Admin Dashboard

If the admin dashboard opens but every KPI says 0 even though SharePoint has people and courses, this is usually old rows having blank upgrade fields. Treat blank Active as active, and blank course Status as active.

Select scrAdminDash.
Open its OnVisible formula.
Replace the old personnel collection line with this one.

ClearCollect(colAllPeople, Filter(colAllPersonnelRaw, Coalesce(Active, true) = true));

Still in scrAdminDash.OnVisible, find the course requirement filter line.
Replace Status.Value = "Active" && with this safer version.

Coalesce(Status.Value, "Active") = "Active" &&

Select galCompliance.
Open its OnSelect formula.
Replace its Status.Value = "Active" && line with the same safer line above.

Expected result: existing people created before the Active column was added appear on the dashboard again. Existing courses created before the Status column was added count as active unless you explicitly archive them.

Fix 5 - Provider Dropdown Default

If the course provider dropdown works when adding a course, but is blank when you edit an existing course, the edit dropdown is not finding a matching row in its own Items table. Use one shared provider collection, then make both provider dropdowns read from it.

SharePoint check: the TT Courses CourseHost Choice column must include these same values: DLE, Civil Service Learning, On-Site, and External provider.

Select scrAdminManage.
Replace its OnVisible property with this formula.

=If(IsBlank(varTab), Set(varTab, "People"));
ClearCollect(colAllPeople, Filter('TT Personnel', Coalesce(Active, true) = true));
ClearCollect(colCourses, 'TT Courses');
ClearCollect(colCourseProviderOptions, ["DLE", "Civil Service Learning", "On-Site", "External provider"])

Select drpNewProvider.
Set Items and Default to the two formulas below.

=colCourseProviderOptions

=LookUp(colCourseProviderOptions, Value = "DLE")

Select drpEditProvider.
Set Items to the same =colCourseProviderOptions formula.
Set Default to this formula.

=With(
    {providerValue: Coalesce(varEditCourse.CourseHost.Value, "DLE")},
    LookUp(
        colCourseProviderOptions,
        Value = Switch(
            providerValue,
            "Defence Learning Environment", "DLE",
            providerValue
        )
    )
)

Select the edit pencil in the course gallery, icoEditCourse.
Replace its OnSelect with this formula. This makes the dropdown and text inputs re-read their defaults after varEditCourse changes.

=Set(varEditCourse, ThisItem);
Reset(txtEditCourse);
Reset(drpEditRecur);
Reset(drpEditProvider);
Reset(txtEditCourseURL);
Reset(txtEditCourseDesc)

Expected result: when you click the edit pencil, the provider dropdown pre-selects the saved provider. Old records saved as Defence Learning Environment will display as DLE.

Fix 6 - Admin OnVisible Navigation

If scrAdminDash.OnVisible errors on Navigate(scrMyTraining, ScreenTransition.None), remove that immediate navigation guard. Power Apps can reject navigation from a screen's own OnVisible because the screen would always navigate away as soon as it appears.

Select scrAdminDash.
Open its OnVisible formula.
Delete this first line only.

If(!varIsAdmin, Navigate(scrMyTraining, ScreenTransition.None));

Expected result: scrAdminDash.OnVisible starts with Set(varAdminLoading, true);. The normal user route is still protected because the Admin button on scrMyTraining is only visible when varIsAdmin is true.

Fix 7 - Compliance KPI Meaning

The original % compliant card counted staff with Outstanding = 0. That made people with 0 of 0 required courses count as compliant, so the percentage could show as high even when no required training had been completed. These targeted changes make the card item-based and recolour Outstanding as an action chip instead of green.

Select lblStatFullyIn.
Set its Text property to this formula.

=CountRows(Filter(colCompliance, Required > 0 && Outstanding = 0))

Select lblStatPct.
Set its Text property to this formula.

=If(Sum(colCompliance, Required) = 0, "0%", Round(100 * Sum(colCompliance, InDate) / Max(Sum(colCompliance, Required), 1), 0) & "%")

Select lblCapPct.
Change its Text property to this label.

="% items in date"

Select btnAttentionOutstanding.
Change these four colour properties so the outstanding filter reads as action-needed, not complete.

=RGBA(163,45,45,1)

=If(varAdminStatusFilter = "Outstanding", RGBA(255,255,255,1), RGBA(121,31,31,1))

=If(varAdminStatusFilter = "Outstanding", RGBA(163,45,45,1), RGBA(252,235,235,1))

=RGBA(243,218,218,1)

Pill meanings: Expired means completed before but now out of date. Expiring means completed and due within 60 days. Missing means no training record exists. Outstanding means required but not currently in date, so it includes missing, expired, and records that exist but have no completion date.

Fix 8 - Stale Course Names

If you shorten a course name in scrAdminManage, save it, return to scrAdminDash, and the selected person's course list still shows the old name, refresh the SharePoint source before rebuilding the local collections. Do not try to refresh the gallery itself; galleries read whatever is already in their Items collection.

Select scrAdminDash.
Open its OnVisible formula.
Add these four lines immediately before ClearCollect(colAllPersonnelRaw, 'TT Personnel');.

Refresh('TT Personnel');
Refresh('TT TrainingRecords');
Refresh('TT Courses');
Refresh('TT AuditLog');

Select btnSaveCourse on scrAdminManage.
In its OnSelect success path, find these lines near the end.

Notify("Course updated. Expiry dates recalculate automatically.",
       NotificationType.Success);
ClearCollect(colCourses, 'TT Courses');
Set(varEditCourse, Blank())

Replace that ending with this version.

Notify("Course updated. Expiry dates recalculate automatically.",
       NotificationType.Success);
Refresh('TT Courses');
ClearCollect(colCourses, 'TT Courses');
Set(varEditCourse, Blank())

Expected result: when you return to the compliance dashboard, scrAdminDash.OnVisible pulls fresh SharePoint data into colCourses. Click a person again and their detail gallery rebuilds from the current course names.

Build Pass - Add Mode Buttons

The add person and add course forms only show when the edit variable is blank. Add clear mode buttons so an admin can get back to the add form after editing a row.

Add `btnNewPersonMode`

Open scrAdminManage.
Insert a modern button named btnNewPersonMode.
Set these properties.

Property	Value
`Text`	`"New person"`
`Visible`	`varTab = "People"`
`BasePaletteColor`	`RGBA(0,78,66,1)`
`X`	`500`
`Y`	`88`
`Width`	`150`
`Height`	`40`

Set(varEditPerson, Blank());
Set(varConfirmDeactivate, 0)

Add `btnNewCourseMode`

Insert a modern button named btnNewCourseMode.
Set these properties.

Property	Value
`Text`	`"New course"`
`Visible`	`varTab = "Courses"`
`BasePaletteColor`	`RGBA(0,78,66,1)`
`X`	`500`
`Y`	`88`
`Width`	`150`
`Height`	`40`

Set(varEditCourse, Blank());
Set(varConfirmArchive, 0)

Build Pass - Remote Worker Exclusions

This pass lets you mark a person as a remote worker and mark specific courses as excluded for remote workers. When both are true, that person does not see that course, and it does not count against their compliance.

Schema

Confirm TT Personnel.RemoteWorker exists as a Yes/No column.
Confirm TT Courses.ExcludeRemoteWorkers exists as a Yes/No column.
Refresh TT Personnel and TT Courses in Power Apps Studio.

Add Person Checkboxes

Add two modern checkboxes on scrAdminManage.

Two different property names for two different things — confirmed live, not a guess. This control's own properties panel only lists Default (used below to set the checkbox's initial state), with no Checked option anywhere in it. But when reading the checkbox's current value back out in another formula (a Patch record, for example), only .Checked resolves — .Default throws Found type 'Error' there, even though it's the only settable option shown in the panel. Use Default in the table below to set it; use .Checked everywhere else you read it.

Control	Visible	Default	Label	Suggested position
`chkNewRemoteWorker`	`varTab = "People" && IsBlank(varEditPerson)`	`false`	`"Remote worker"`	`X 430 / Y 404 / W 190 / H 32`
`chkEditRemoteWorker`	`varTab = "People" && !IsBlank(varEditPerson)`	`Coalesce(varEditPerson.RemoteWorker, false)`	`"Remote worker"`	`X 430 / Y 308 / W 190 / H 32`

Add Course Checkboxes

Control	Visible	Default	Label	Suggested position
`chkNewExcludeRemote`	`varTab = "Courses" && IsBlank(varEditCourse)`	`false`	`"Exclude remote workers"`	`X 32 / Y 416 / W 240 / H 24`
`chkEditExcludeRemote`	`varTab = "Courses" && !IsBlank(varEditCourse)`	`Coalesce(varEditCourse.ExcludeRemoteWorkers, false)`	`"Exclude remote workers"`	`X 32 / Y 416 / W 240 / H 24`

Patch Person Writes

Do not paste just the one field. An earlier version of this section only gave the single RemoteWorker: ... line to drop into the existing Patch record by hand, and in practice that's exactly how it ends up pasted in the wrong place with no error until it silently doesn't do anything. Delete the entire btnAddPerson.OnSelect formula and paste the complete version below instead.

Select btnAddPerson on scrAdminManage, delete everything currently in OnSelect, and paste this complete formula in full. The only change from what was there before is RemoteWorker: chkNewRemoteWorker.Checked, inserted into the new-person Patch('TT Personnel', ...) record — everything else (reactivation branch, audit log writes, notify/reset calls) is unchanged.

=With(
    {
        newEmail: Lower(If(!IsBlank(cmbNewPerson.Selected.Mail),
                           cmbNewPerson.Selected.Mail, Trim(txtManualEmail.Text))),
        newName:  If(!IsBlank(cmbNewPerson.Selected.DisplayName),
                     cmbNewPerson.Selected.DisplayName, Trim(txtManualName.Text)),
        newType:  drpNewType.Selected.Value,
        newHasM365: tglHasM365.Checked,
        newManager: Lower(Trim(txtNewManager.Text))
    },
    If(
        IsBlank(newEmail) || IsBlank(newName),
        Notify("Email and name are required.", NotificationType.Warning),

        !IsBlank(LookUp('TT Personnel', Email = newEmail)),
        With(
            {
                savedPerson: Patch('TT Personnel',
                    LookUp('TT Personnel', Email = newEmail),
                    {
                        Active: true,
                        LastUpdatedByEmail: varMyEmail,
                        LastUpdatedOn: Now(),
                        LastStatus: "Reactivated"
                    }
                )
            },
            Patch('TT AuditLog', Defaults('TT AuditLog'),
                {
                    Title: newName & " reactivated",
                    EntityType: "Person",
                    EntityID: Text(savedPerson.ID),
                    PersonEmail: newEmail,
                    CourseID: "",
                    EventType: "Person reactivated",
                    EventDescription: newName & " reactivated in Training Tracker.",
                    ActorEmail: varMyEmail,
                    EventDateTime: Now(),
                    Notes: ""
                }
            )
        );
        Notify("Already in system - reactivated.", NotificationType.Warning),

        With(
            {
                savedPerson: Patch('TT Personnel', Defaults('TT Personnel'),
                    {Title:newName, Email:newEmail, StaffType:{Value:newType},
                     IsAdmin:tglNewAdmin.Checked, Active:true,
                     HasM365Access:newHasM365, LineManagerEmail:newManager,
                     RemoteWorker: chkNewRemoteWorker.Checked,
                     ManagerCanEdit:false,
                     LastUpdatedByEmail: varMyEmail,
                     LastUpdatedOn: Now(),
                     LastStatus: "Created"}
                )
            },
            Patch('TT AuditLog', Defaults('TT AuditLog'),
                {
                    Title: newName & " added",
                    EntityType: "Person",
                    EntityID: Text(savedPerson.ID),
                    PersonEmail: newEmail,
                    CourseID: "",
                    EventType: "Person added",
                    EventDescription: newName & " added to Training Tracker.",
                    ActorEmail: varMyEmail,
                    EventDateTime: Now(),
                    Notes: ""
                }
            )
        );
        Notify(newName & " added.", NotificationType.Success);
        Reset(cmbNewPerson); Reset(txtManualEmail); Reset(txtManualName);
        Reset(drpNewType); Reset(tglNewAdmin); Reset(tglHasM365); Reset(txtNewManager)
    )
)

Patch Person Writes (already live — shown for reference)

Checked against your live export: btnSavePerson.OnSelect, btnAddCourse.OnSelect, and btnSaveCourse.OnSelect already have RemoteWorker/ExcludeRemoteWorkers wired in correctly. Nothing to change on these three — they're included below in full only so this guide has the real, current formula on record instead of a fragment.

=Patch('TT Personnel', varEditPerson,
    {
        Title:            Trim(txtEditPersonName.Text),
        StaffType:        {Value: drpEditPersonType.Selected.Value},
        LineManagerEmail: Lower(Trim(txtEditPersonManager.Text)),
        RemoteWorker: chkEditRemoteWorker.Checked,
        IsAdmin:          tglEditAdmin.Checked,
        HasM365Access:    tglEditM365.Checked,
        LastUpdatedByEmail: varMyEmail,
        LastUpdatedOn: Now(),
        LastStatus: "Updated"
    }
);
Patch('TT AuditLog', Defaults('TT AuditLog'),
    {
        Title: Trim(txtEditPersonName.Text) & " updated",
        EntityType: "Person",
        EntityID: Text(varEditPerson.ID),
        PersonEmail: Lower(varEditPerson.Email),
        CourseID: "",
        EventType: "Person updated",
        EventDescription: Trim(txtEditPersonName.Text) & " profile updated.",
        ActorEmail: varMyEmail,
        EventDateTime: Now(),
        Notes: ""
    }
);
Refresh('TT Personnel');
Set(varEditPerson, Blank());
Notify(Trim(txtEditPersonName.Text) & " updated.", NotificationType.Success)

=If(
    IsBlank(Trim(txtNewCourse.Text)) || CountRows(Filter(cmbNewApplic.SelectedItems, !IsBlank(Value))) = 0 || IsBlank(drpNewProvider.Selected.Value) || IsBlank(Trim(txtNewCourseURL.Text)),
    Notify("Course needs a name, staff type, provider and URL.",
           NotificationType.Warning),
    IfError(
        With(
            {
                savedCourse: Patch('TT Courses', Defaults('TT Courses'),
                    {
                        Title:            Trim(txtNewCourse.Text),
                        ApplicableTo:     ForAll(Filter(cmbNewApplic.SelectedItems, !IsBlank(Value)), {Value: Value}),
                        ExcludeRemoteWorkers: chkNewExcludeRemote.Checked,
                        RecurrenceMonths: drpNewRecur.Selected.Months,
                        CourseHost:       {Value: drpNewProvider.Selected.Value},
                        CourseURL:        Trim(txtNewCourseURL.Text),
                        CourseDescription: Trim(txtNewCourseDesc.Text),
                        Status:           {Value: "Active"},
                        LastUpdatedByEmail: varMyEmail,
                        LastUpdatedOn: Now(),
                        LastStatus: "Created"
                    }
                )
            },
            Patch('TT AuditLog', Defaults('TT AuditLog'),
                {
                    Title: savedCourse.Title & " added",
                    EntityType: "Course",
                    EntityID: Text(savedCourse.ID),
                    PersonEmail: "",
                    CourseID: Text(savedCourse.ID),
                    EventType: "Course added",
                    EventDescription: savedCourse.Title & " added to the mandatory course list.",
                    ActorEmail: varMyEmail,
                    EventDateTime: Now(),
                    Notes: ""
                }
            );
            Notify("Course added.", NotificationType.Success);
            Reset(txtNewCourse); Reset(cmbNewApplic); Reset(drpNewRecur); Reset(drpNewProvider); Reset(txtNewCourseURL); Reset(txtNewCourseDesc);
            ClearCollect(colCourses, 'TT Courses');
            true
        ),
        Notify("Course could not be added: " & FirstError.Message, NotificationType.Error);
        true
    )
)

=If(
    IsBlank(txtEditCourse.Text) || CountRows(cmbEditApplic.SelectedItems) = 0 || IsBlank(drpEditProvider.Selected.Value) || IsBlank(Trim(txtEditCourseURL.Text)),
    Notify("Course needs a name, staff type, provider and URL.", NotificationType.Warning),
    Patch('TT Courses', varEditCourse,
        {
            Title:            Trim(txtEditCourse.Text),
            ApplicableTo:     ForAll(cmbEditApplic.SelectedItems, {Value: Value}),
            RecurrenceMonths: drpEditRecur.Selected.Months,
            ExcludeRemoteWorkers: chkEditExcludeRemote.Checked,
            CourseHost:       {Value: drpEditProvider.Selected.Value},
            CourseURL:        Trim(txtEditCourseURL.Text),
            CourseDescription: Trim(txtEditCourseDesc.Text),
            LastUpdatedByEmail: varMyEmail,
            LastUpdatedOn: Now(),
            LastStatus: "Updated"
        }
    );
    Patch('TT AuditLog', Defaults('TT AuditLog'),
        {
            Title: Trim(txtEditCourse.Text) & " updated",
            EntityType: "Course",
            EntityID: Text(varEditCourse.ID),
            PersonEmail: "",
            CourseID: Text(varEditCourse.ID),
            EventType: "Course updated",
            EventDescription: Trim(txtEditCourse.Text) & " course settings updated.",
            ActorEmail: varMyEmail,
            EventDateTime: Now(),
            Notes: ""
        }
    );
Notify("Course updated. Expiry dates recalculate automatically.",
       NotificationType.Success);
Refresh('TT Courses');
ClearCollect(colCourses, 'TT Courses');
Set(varEditCourse, Blank())
)

Compliance Formula Changes (not yet live — still needs doing)

Checked against your live export: this one is genuinely not done yet. Select scrAdminDash, delete everything currently in OnVisible's ClearCollect(colCompliance, ...) statement, and paste this complete replacement. It's only this one statement inside OnVisible, not the whole property — the rest of OnVisible (theme setup, other ClearCollects, colRiskByType, and whatever comes after it) is untouched and stays exactly as it is. Two changes from what you have now: a RemoteWorker field added to each person's row, and the exclusion clause added to req.

ClearCollect(
    colCompliance,
    ForAll(
        colAllPeople As P,
        With(
            {
                req: Filter(
                    colCourses,
                    Coalesce(Status.Value, "Active") = "Active" &&
                    CountIf(ApplicableTo, Value = P.StaffType.Value) > 0 &&
                    !(Coalesce(P.RemoteWorker, false) && Coalesce(ExcludeRemoteWorkers, false))
                )
            },
            With(
                {
                    okCount: CountRows(
                        Filter(
                            req As C,
                            With(
                                {
                                    rec: LookUp(
                                        colAllRecords,
                                        Lower(PersonEmail) = Lower(P.Email) &&
                                        CourseID = C.ID
                                    )
                                },
                                !IsBlank(rec.DateCompleted) &&
                                (
                                    C.RecurrenceMonths = 0 ||
                                    DateAdd(rec.DateCompleted, C.RecurrenceMonths, "Months") >= Today()
                                )
                            )
                        )
                    )
                },
                {
                    PersonID:      P.ID,
                    Name:          P.Title,
                    Email:         Lower(P.Email),
                    StaffType:     P.StaffType.Value,
                    LineManager:   P.LineManagerEmail,
                    ManagerEdit:   Coalesce(P.ManagerCanEdit, false),
                    RemoteWorker:  Coalesce(P.RemoteWorker, false),
                    Required:      CountRows(req),
                    InDate:        okCount,
                    Outstanding:   CountRows(req) - okCount,
                    MissingRecords: CountRows(
                        Filter(
                            req As C,
                            IsBlank(
                                LookUp(
                                    colAllRecords,
                                    Lower(PersonEmail) = Lower(P.Email) &&
                                    CourseID = C.ID
                                ).ID
                            )
                        )
                    ),
                    ExpiredItems: CountRows(
                        Filter(
                            req As C,
                            With(
                                {
                                    rec: LookUp(
                                        colAllRecords,
                                        Lower(PersonEmail) = Lower(P.Email) &&
                                        CourseID = C.ID
                                    )
                                },
                                !IsBlank(rec.DateCompleted) &&
                                C.RecurrenceMonths > 0 &&
                                DateAdd(rec.DateCompleted, C.RecurrenceMonths, "Months") < Today()
                            )
                        )
                    ),
                    ExpiringItems: CountRows(
                        Filter(
                            req As C,
                            With(
                                {
                                    rec: LookUp(
                                        colAllRecords,
                                        Lower(PersonEmail) = Lower(P.Email) &&
                                        CourseID = C.ID
                                    )
                                },
                                !IsBlank(rec.DateCompleted) &&
                                C.RecurrenceMonths > 0 &&
                                DateAdd(rec.DateCompleted, C.RecurrenceMonths, "Months") >= Today() &&
                                DateAdd(rec.DateCompleted, C.RecurrenceMonths, "Months") <= DateAdd(Today(), 60, "Days")
                            )
                        )
                    ),
                    CompliancePct: If(
                                       CountRows(req) = 0,
                                       100,
                                       Round(100 * okCount / Max(CountRows(req), 1), 0)
                                   ),
                    AttentionState: If(
                                        CountRows(req) - okCount = 0,
                                        "Clear",
                                        okCount = 0,
                                        "Critical",
                                        "Needs attention"
                                    )
                }
            )
        )
    )
)

Select galCompliance, delete everything currently in OnSelect, and paste this complete replacement. Only the Filter(colCourses, ...) predicate inside it changed — the timeline ClearCollect at the end is unchanged.

=Set(varDrillPerson, ThisItem);
ClearCollect(colPersonDetail,
    ForAll(
        Filter(colCourses,
            Coalesce(Status.Value, "Active") = "Active" &&
            CountIf(ApplicableTo, Value = varDrillPerson.StaffType) > 0 &&
            !(Coalesce(varDrillPerson.RemoteWorker, false) && Coalesce(ExcludeRemoteWorkers, false))) As C,
        With({rec: LookUp(colAllRecords,
                      Lower(PersonEmail) = varDrillPerson.Email && CourseID = C.ID)},
        {
            CourseName: C.Title,
            DateDone:   rec.DateCompleted,
            UploadedBy: rec.UploadedByEmail,
            ExpiresOn:  If(IsBlank(rec.DateCompleted)||C.RecurrenceMonths=0,Blank(),
                           DateAdd(rec.DateCompleted,C.RecurrenceMonths,"Months")),
            TrainingStatus:
                If(IsBlank(rec.DateCompleted),"Not Started",
                   C.RecurrenceMonths=0,"Complete",
                   DateAdd(rec.DateCompleted,C.RecurrenceMonths,"Months")<Today(),"Expired",
                   DateAdd(rec.DateCompleted,C.RecurrenceMonths,"Months")<=DateAdd(Today(),60,"Days"),"Expiring Soon",
                   "In Date"),
            RecordID: rec.ID
        })
    )
);
ClearCollect(
    colPersonTimeline,
    SortByColumns(
        Filter(colAuditLog, Lower(PersonEmail) = varDrillPerson.Email),
        "EventDateTime",
        SortOrder.Descending
    )
)

My Training Course Filter (not yet live)

This one exception to "paste the whole property": scrMyTraining.OnVisible is a very long formula (theme setup, colMyRecords, colMyTraining, colMyArchive, and more beyond that) and reproducing all of it here risks a transcription error somewhere that has nothing to do with this change. Instead, find this exact Filter(colCourses, ...) block inside it — it only appears once, building colMyTraining — and replace just that block.

Filter(colCourses,
    Status.Value = "Active" &&
    CountIf(ApplicableTo, Value = varMe.StaffType.Value) > 0 &&
    !(Coalesce(varMe.RemoteWorker, false) && Coalesce(ExcludeRemoteWorkers, false))
) As C,

Team Member Course Filter (not yet live — four fixes, not one)

This needs more than the guide previously said, twice over. Checked against your live export: the same course-filter predicate appears three separate times on scrTeamMember — twice inside galMyChain.Items (once for PersonRequired, once for PersonInDate) and once more inside galMyChain.OnSelect (building colViewTraining). All three need the same exclusion clause, or the count badges on the team list and the drilldown course list will disagree with each other. On top of that, all three read P.RemoteWorker/ThisItem.RemoteWorker off colMyChain — and colMyChain is a reshaped collection built with an explicit field list in OnVisible that does not currently include RemoteWorker at all. Without the fix below first, all three formulas below will throw Name isn't valid. 'RemoteWorker' isn't recognized. the moment you paste them.

Do this one first. Select scrTeamMember, delete everything currently in OnVisible, and paste this complete replacement — this is the entire property, not a fragment, since it's short enough to reproduce in full safely. The only change is RemoteWorker: Coalesce(P.RemoteWorker, false), added to all three colMyChain record shapes (chain levels 1, 2, and 3).

=Set(varChainDepth, 3);
ClearCollect(colAllPeople, Filter('TT Personnel', Active = true));
ClearCollect(colAllRecords, 'TT TrainingRecords');

ClearCollect(
    colChainL1,
    Filter(colAllPeople, Lower(LineManagerEmail) = varMyEmail)
);

ClearCollect(
    colChainL2,
    Filter(
        colAllPeople,
        Lower(LineManagerEmail) in ForAll(colChainL1, Lower(Email)).Value
    )
);

ClearCollect(
    colMyChain,
    ForAll(
        colChainL1 As P,
        {
            Email: Lower(P.Email),
            Name: P.Title,
            StaffType: Coalesce(P.StaffType.Value, ""),
            ManagerCanEdit: Coalesce(P.ManagerCanEdit, false),
            HasM365: Coalesce(P.HasM365Access, false),
            RemoteWorker: Coalesce(P.RemoteWorker, false),
            ManagerEmail: Lower(P.LineManagerEmail),
            ChainLevel: 1,
            IsExpanded: false
        }
    )
);

Collect(
    colMyChain,
    ForAll(
        colChainL2 As P,
        {
            Email: Lower(P.Email),
            Name: P.Title,
            StaffType: Coalesce(P.StaffType.Value, ""),
            ManagerCanEdit: Coalesce(P.ManagerCanEdit, false),
            HasM365: Coalesce(P.HasM365Access, false),
            RemoteWorker: Coalesce(P.RemoteWorker, false),
            ManagerEmail: Lower(P.LineManagerEmail),
            ChainLevel: 2,
            IsExpanded: false
        }
    )
);

If(
    varChainDepth >= 3,
    Collect(
        colMyChain,
        ForAll(
            Filter(
                colAllPeople,
                Lower(LineManagerEmail) in ForAll(colChainL2, Lower(Email)).Value
            ) As P,
            {
                Email: Lower(P.Email),
                Name: P.Title,
                StaffType: Coalesce(P.StaffType.Value, ""),
                ManagerCanEdit: Coalesce(P.ManagerCanEdit, false),
                HasM365: Coalesce(P.HasM365Access, false),
                RemoteWorker: Coalesce(P.RemoteWorker, false),
                ManagerEmail: Lower(P.LineManagerEmail),
                ChainLevel: 3,
                IsExpanded: false
            }
        )
    )
)

Now the three galMyChain fixes below will actually resolve. Select galMyChain, delete everything currently in Items, and paste this complete replacement. It fixes both of the first two occurrences at once.

=Filter(
    AddColumns(
        colMyChain As P,
        PersonRequired,
        CountRows(
            Filter(colCourses,
                Status.Value = "Active" &&
                CountIf(ApplicableTo, Value = P.StaffType) > 0 &&
                !(Coalesce(P.RemoteWorker, false) && Coalesce(ExcludeRemoteWorkers, false)))
        ),
        PersonInDate,
        CountRows(
            Filter(
                Filter(colCourses,
                    Status.Value = "Active" &&
                    CountIf(ApplicableTo, Value = P.StaffType) > 0 &&
                    !(Coalesce(P.RemoteWorker, false) && Coalesce(ExcludeRemoteWorkers, false))) As C,
                With({rec: LookUp(colAllRecords,
                        Lower(PersonEmail) = P.Email &&
                        CourseID = C.ID)},
                    !IsBlank(rec.DateCompleted) &&
                    (C.RecurrenceMonths = 0 ||
                     DateAdd(rec.DateCompleted, C.RecurrenceMonths,
                             "Months") >= Today())
                )
            )
        )
    ) As R,
    R.ChainLevel = 1 ||
    (R.ChainLevel = 2 &&
     LookUp(colMyChain, Lower(Email) = Lower(R.ManagerEmail), IsExpanded) = true) ||
    (R.ChainLevel = 3 &&
     LookUp(colMyChain, Lower(Email) = Lower(R.ManagerEmail), IsExpanded) = true)
)

Select galMyChain again, delete everything currently in OnSelect, and paste this complete replacement. It fixes the third occurrence, inside colViewTraining.

=Set(varViewingPerson, LookUp('TT Personnel', Lower(Email) = Lower(ThisItem.Email)));
Set(varViewingForSelf, false);
ClearCollect(
    colViewRecords,
    Filter(colAllRecords, Lower(PersonEmail) = Lower(ThisItem.Email))
);
ClearCollect(
    colViewTraining,
    ForAll(
        Filter(
            colCourses,
            Status.Value = "Active" &&
            CountIf(ApplicableTo, Value = ThisItem.StaffType) > 0 &&
            !(Coalesce(ThisItem.RemoteWorker, false) && Coalesce(ExcludeRemoteWorkers, false))
        ) As C,
        With(
            {rec: LookUp(colViewRecords, CourseID = C.ID)},
            {
                CourseID:    C.ID,
                CourseName:  C.Title,
                CourseURL:   C.CourseURL,
                CourseHost:  C.CourseHost,
                CourseDescription: C.CourseDescription,
                RecurMonths: C.RecurrenceMonths,
                RecurText:   Switch(C.RecurrenceMonths,
                                 0,  "One-off",
                                 12, "Every year",
                                 24, "Every 2 years",
                                 "Every " & C.RecurrenceMonths & " months"),
                RecordID:    rec.ID,
                DateDone:    rec.DateCompleted,
                UploadedBy:  rec.UploadedByEmail,
                ExpiresOn:   If(IsBlank(rec.DateCompleted) || C.RecurrenceMonths = 0,
                                 Blank(),
                                 DateAdd(rec.DateCompleted,
                                         C.RecurrenceMonths, "Months")),
                TrainingStatus:
                    If(IsBlank(rec.DateCompleted), "Not Started",
                       C.RecurrenceMonths = 0, "Complete",
                       DateAdd(rec.DateCompleted, C.RecurrenceMonths,
                               "Months") < Today(), "Expired",
                       DateAdd(rec.DateCompleted, C.RecurrenceMonths,
                               "Months") <= DateAdd(Today(), 60,
                               "Days"), "Expiring Soon",
                       "In Date"),
                CanEdit: ThisItem.ManagerCanEdit
            }
        )
    )
);
Navigate(scrMyTraining, ScreenTransition.Cover)

Next up once this is live and tested: Course Expiry Reminder Emails — the mandatory-course email reminder feature, built with a safety switch so it cannot email your 125 real people while the app and data are still being populated.

Build Pass - Course Expiry Reminder Emails

Superseded by the new full guide. Do not build from this older inline sketch. It only covers a basic 30-day/expired-today reminder and does not include the locked TT ReminderLog, double safety switch, duplicate-send protection, rich HTML email, or deep link routing. Use the new page instead: Course Expiry Reminder Emails - Full Guide.

A scheduled flow that emails a person when a mandatory course is 30 days from expiring, and again when it has expired. Default for everyone, no opt-out — this is a compliance reminder, not a marketing email, and an opt-in toggle would just mean most people never turn it on.

Read this before building anything else in this section. The current database has roughly 125 people in it, and the data isn't fully populated yet — meaning most people currently show as having no completed courses on file. Built naively, this flow would email all 125 of them the moment it first runs, about an app they don't know exists yet. The TestMode switch below is not optional polish, it is the thing that makes it safe to build and test this feature at all before the data is ready. Do not skip Step 1, and do not flip TestMode to false until you have deliberately decided the data is ready and the wording is right.

Step 1 — Build The Flow With The Safety Switch First

Go to make.powerautomate.com, signed in as the same account used for the other Training Tracker flows.
Create → Scheduled cloud flow. Name it exactly TT - Course Expiry Reminders. Set it to run Daily, starting at a time before your working day begins (e.g. 07:00).
Add an Initialize variable action, name TestMode, type Boolean, value true.
Add a second Initialize variable action, name TestModeRecipient, type String, value your own email address.

These two actions must be the first two actions in the flow, right after the trigger, before anything else. Every email this flow sends will read its recipient through these two variables. Leave TestMode as true for every test run until you have deliberately decided the data is ready and you're ready to email real people — then, and only then, edit this one action and change the value to false. That is the entire cutover.

Step 2 — Pull The Data

Add three SharePoint – Get items actions, one after another.

Action name	List	Filter Query
Get Active People	`TT Personnel`	`Active eq true`
Get Active Courses	`TT Courses`	`Status/Value eq 'Active'`
Get Training Records	`TT TrainingRecords`	leave blank — pull all, filtered per-person/course inside the loop below

No Top Count on any of these — leave them all blank so nobody and no course is silently skipped once the list grows past the default 100-item page size.

Step 3 — Loop Every Person, Then Every Applicable Course

Add an Apply to each over the value from Get Active People. Rename it Apply to each Person.
Inside it, add a second Apply to each over a filtered array expression, not the raw list — click the field, switch to expression mode, and paste this so only courses that actually apply to this person (matching staff type, not remote-worker-excluded, and not a one-off course that never expires) are considered:
Apply to each Course — Items expression
```
filter(
    body('Get_Active_Courses')?['value'],
    and(
        greater(item()?['RecurrenceMonths'], 0),
        contains(string(item()?['ApplicableTo']), items('Apply_to_each_Person')?['StaffType']?['Value']),
        not(
            and(
                equals(items('Apply_to_each_Person')?['RemoteWorker'], true),
                equals(item()?['ExcludeRemoteWorkers'], true)
            )
        )
    )
)
```
The action names inside the expression (Get_Active_Courses, Apply_to_each_Person) must match your actual action names exactly, underscores in place of spaces — Power Automate does this substitution automatically when you reference a prior step through the expression editor's dynamic content picker rather than typing it by hand. Build this by picking fields from the picker first, then adjust, rather than typing the whole thing from scratch.

Step 4 — Find This Person's Record For This Course, Compute The Expiry Date

Inside the course loop, add a Filter array action on the value from Get Training Records, condition: PersonEmail is equal to Apply to each Person → Email, AND CourseID is equal to Apply to each Course → ID. Then add a Condition: length(body('Filter_array')) is greater than 0 — this skips anyone who has never completed the course at all, since a course that was never done isn't "expiring," it's simply outstanding (a different notification, not built here).

Inside the Yes branch, add a Compose action named Expiry Date, value:

addToTime(
    first(body('Filter_array'))?['DateCompleted'],
    items('Apply_to_each_Course')?['RecurrenceMonths'],
    'Month'
)

Step 5 — The Two Date-Match Conditions

Still inside the Yes branch, add two Condition actions, side by side (not nested inside each other). Both compare dates as plain yyyy-MM-dd text, which avoids time-of-day/timezone mismatches causing a real match to be missed.

Condition name	Left side	Operator	Right side
Expiring In 30 Days?	`formatDateTime(outputs('Expiry_Date'), 'yyyy-MM-dd')`	is equal to	`formatDateTime(addDays(utcNow(), 30), 'yyyy-MM-dd')`
Expired Today?	`formatDateTime(outputs('Expiry_Date'), 'yyyy-MM-dd')`	is equal to	`formatDateTime(utcNow(), 'yyyy-MM-dd')`

Exact-day match, not "less than 30 days" — this is deliberate. A "less than" comparison would re-send the same warning every single day for a month. An exact-day match means each person gets the 30-day warning once, and the expired notice once, and that's it.

Step 6 — Send The Email, Through The Safety Switch

In each condition's Yes branch, add an Office 365 Outlook – Send an email (V2) action. Both use the same recipient expression — this is the one line that actually enforces TestMode:

if(
    variables('TestMode'),
    variables('TestModeRecipient'),
    items('Apply_to_each_Person')?['Email']
)

Set From (Send as) to your shared mailbox address (the one you already have Send-on-Behalf permission for). Subject and body are plain text/dynamic content, so type them normally — no fx needed, only boolean fields need that. Include the real person's name and course inside the body even while testing, so a TestMode email still tells you exactly who and what it would have been about:

Field	Suggested content
Subject (30-day)	`concat('Training reminder: ', items('Apply_to_each_Course')?['Title'], ' expires soon')`
Subject (expired)	`concat('Training overdue: ', items('Apply_to_each_Course')?['Title'], ' has expired')`

Build the body with plain dynamic content (person's name, course name, expiry date) inserted via the picker — the same "click a field from the picker, never hand-type text_N" rule from the certificate flows applies here too.

Step 7 — Test In TestMode Before Touching The Switch

Save the flow with TestMode still true. Run it manually once (the ▶ Test button, or wait for the scheduled run).
Confirm every email that arrives lands in your own inbox, not anyone else's — check the flow run history's count of emails sent against how many you actually received; they should match exactly.
Read the content of each test email as if you were the named person — does it make sense, is the course name right, is the expiry date right?
Only once you're satisfied, and only once the training data is actually populated enough that this won't blindside 125 people who don't know the app exists: open the flow, edit the first Initialize variable action, change TestMode's value to false, save.

After flipping to live, watch the first real run closely. Check the run history's email count against what you'd expect from the actual number of people genuinely due a reminder that day — a much higher number than expected is a sign something in the filter logic matched more broadly than intended, and is worth pausing the flow to investigate before the next scheduled run.

Build Pass - Snapshot Dashboard

This replaces the parked PDF export with a screenshot-friendly dashboard. It shows a rolling six-month grid. Each month is a stored snapshot, so the history remains stable even if records change later.

Data Source

Create TT ComplianceSnapshots using the schema in Fix 1.
Add it to the app as a SharePoint data source.
Refresh TT ComplianceSnapshots, TT Personnel, TT Courses, and TT TrainingRecords.

Paste The New Screen

Create a new blank screen named scrMandatoryStats.
Open the YAML page below and copy/paste the screen YAML.

Open scrMandatoryStats YAML

Replace Export PDF Button

On scrAdminDash, select the old btnExportPDF button. Rename it to btnMandatoryStats if you want the tree to be tidy, then set these properties.

Property	Value
`Text`	`"Mandatory stats"`
`OnSelect`	`Navigate(scrMandatoryStats, ScreenTransition.Cover)`

Add A Normal-User Entry Point

Because the snapshot screen is for everyone to view, add a button to the scrMyTraining header as well. The screen itself keeps Take snapshot visible only for admins.

Open scrMyTraining.
Insert a modern button named btnMandatoryStatsUser.
Set these properties.

Property	Value
`Text`	`"Mandatory stats"`
`Visible`	`true`
`OnSelect`	`Navigate(scrMandatoryStats, ScreenTransition.Cover)`
`BasePaletteColor`	`RGBA(0,78,66,1)`
`X`	`914`
`Y`	`16`
`Width`	`160`
`Height`	`40`

Monthly Use

Open scrMandatoryStats.
Press Take snapshot once for the reporting month.
Use the visible grid for the snip/screenshot.
Do not edit old snapshot rows unless the monthly report was genuinely wrong and needs correction.

Optional Upgrade — Certificate Privacy (Power Automate)

Superseded — this whole section describes an earlier design. The certificate system actually live today is different (a locked document library, TT - Save Certificate Library / TT - Email Certificate Library, and an in-app viewer, not the TT - Save Certificate / TT - Get Certificate flows and file picker described below). Go here instead: Certificate Library & In-App Viewer — Full Guide. Everything below is collapsed and kept only in case something in the old design is ever worth referencing.

Show the superseded design (click to expand)

This is separate from the v3 build above. Nothing here is required to make Training Tracker work. It exists because certificates are personal data tied to a named employee, and the direct SharePoint-list design means anyone with read access to the raw list can browse everyone's certificate, not just their own or their team's. This section removes the certificate save/replace path from direct SharePoint writes and routes it through one Power Automate flow instead. Skip this whole section if you are not doing this upgrade.

Certificates are ordinary personal data (a name plus a course and a date), not the specially-protected "special category" tier under UK GDPR — but "not special category" is not the same as "fine to leave open." Least-privilege access (the person, their manager, and admins only) is standard practice regardless of legal category, and worth doing on its own merits. Check with whoever owns data protection/information governance at your organisation before treating this as settled.

The One Rule To Remember: `true`/`false` Fields vs Plain Text Fields

Every single time this guide tells you to set a value to true or false (a Yes/No / boolean value), you must click the fx expression icon on that field first, then type the bare word true or false with no quotes. Never use the plain Yes/No toggle, and never just type the word into the plain box without clicking fx first — both of those can silently store the literal text "Yes"/"true" instead of a real boolean, which looks identical on screen but is not the same thing, and can make comparisons and app logic silently fail later with no error shown. After clicking fx and typing it correctly, the value shows up as a small coloured pill/chip in the box — that pill is your confirmation it worked.

This rule does not apply to plain text fields such as message, Title, EventDescription, fileName, or anything else that is ordinary words/sentences. Those are fine to type directly with no fx click needed — plain typed text is exactly what a Text field expects. The rule is only about true/false boolean values.

Use this checklist to go back and audit every boolean field you have already built, in both flows:

Flow	Where	Field	Should be
`TT - Save Certificate`	Step 3, Condition on `HasNewFile`	right-hand comparison value	`true` via fx
`TT - Save Certificate`	Step 5, Respond (authorised/original)	`success`	`true` via fx
`TT - Save Certificate`	Harden, Condition on `IsAdmin`/authorisation	right-hand comparison value	`true` via fx
`TT - Save Certificate`	Harden, Respond (unauthorised, new one you just built)	`success`	`false` via fx
`TT - Get Certificate`	Step 4, Condition on `IsAdmin`/authorisation	right-hand comparison value	`true` via fx
`TT - Get Certificate`	Step 5, Yes branch, `Set variable varSuccess`	`varSuccess`	`true` via fx
`TT - Get Certificate`	Step 6, No branch, `Set variable varSuccess`	`varSuccess`	`false` via fx

Everything else in both flows — message, Title, EventDescription, fileName, fileContentBase64 — is plain text or dynamic content, never a boolean, so none of those need the fx click.

What This Changes

scrCourseDetail no longer uses a SharePoint-bound Form control to save the certificate. It now uses a local file picker and calls one Power Automate flow, TT - Save Certificate, which does the actual write.
The flow runs under its own connection, not the signed-in user's. This is what makes it possible to later lock down direct SharePoint permission on TT TrainingRecords (a separate step, not covered here) without breaking the app — the app was never relying on the user's own list permission for this action in the first place.
No new SharePoint columns are needed. The flow writes the same DateCompleted, UploadedByEmail, LastUpdatedByEmail, LastUpdatedOn, LastStatus and attachment fields the old Form wrote, plus the same TT AuditLog entry the old frmCert.OnSuccess wrote.
The old "remove the × first, then add the new file" warning is gone. Dropping a new file now always replaces whatever was on file — the flow deletes the old attachment and adds the new one in the same run.
A second flow, TT - Get Certificate, and a new Open certificate button let a person re-open or download a certificate they (or an admin, on their behalf) already saved. Without this, saving a certificate would have been a one-way trip — visible as a filename, never viewable again.
TT - Save Certificate is also hardened to genuinely check authorisation server-side (the record's own person, or an admin) before writing anything — not just relying on the app's navigation to keep people on their own records.

Not covered here, on purpose: actually locking the SharePoint list's permissions is a separate step, owned by whoever manages your SharePoint site/security — not a Power Apps change. Both flows in this section are designed to work identically whether that list is still open or already locked down, which is why it is safe to build them now, before that decision is made.

App.OnStart Addition

Add this one line to App → OnStart, anywhere after the existing lines. It creates the local collection the new file picker stages a file into before Save is pressed.

ClearCollect(colNewCertificate, {Name: "", Value: Blank()});
Clear(colNewCertificate)

Why two lines and not just a single-step empty-table ClearCollect: an empty schema-less table makes Power Apps guess the column shape, which is unreliable. Seeding one properly-typed row then clearing it gives the collection a real Name (Text) / Value (File) schema up front.

Paste The Updated Screen

Open the updated scrCourseDetail YAML and paste it over the existing screen (delete the screen's controls first, then paste fresh — do not paste over controls that still exist, per the usual rule for structural changes).

Open scrCourseDetail YAML (v3.7)

Currently fixing TT - Get Certificate? There's a dedicated page with just the action list, no step-renumbering, no scrolling past stuff you've already built: Open: Fix TT - Get Certificate — Exactly What To Change

Live, current path: certificates are stored in a locked document library and served through an authorisation-checked flow, with the certificate rendered directly in the app — no email, no download prompt. Full details, including the in-app viewer: Certificate Library & In-App Viewer — Full Guide.

Where you are in this upgrade:

✅ Done — TT - Save Certificate flow built, and the typed-text field mistakes fixed (collapsed below, click to re-open if you ever need to check a step again).
✅ Done — Harden TT - Save Certificate with authorisation.
▶ Continue here next — Fix TT - Get Certificate (dedicated page, linked above)
Then — Connect both flows in the app (mostly already done by pasting the v3.7 screen)
Then test it — see Progress and error feedback for what you should see on screen

✅ Done: Build the flow TT - Save Certificate (click to expand if you need to re-check a step)

You do not need any Power Automate experience for this — follow these steps in order. This is one flow with a handful of steps, not a big build.

Reminder if you ever rebuild a step from scratch: never type an expression containing text_N, number_N, or boolean_N from this guide or anywhere else — always click a field from the dynamic content picker instead. This is what caused the earlier UploadedByEmail/Title mix-up, which is already fixed now.

Go to make.powerautomate.com, signed in as the same account you use for this SharePoint site.
Create → Instant cloud flow. Name it exactly TT - Save Certificate.
For the trigger, choose Power Apps (V2). Select Create.

Step 1 — Add The Inputs

On the trigger step, press + Add an input once for each row below, in this exact order (the order matters — it must match the order the app sends them). Give each input the exact name shown; Power Automate uses this name as the label in the app's formula bar later.

#	Input type to pick	Name
1	Number	`RecordID`
2	Date	`DateCompleted`
3	Yes/No	`HasNewFile`
4	Text	`FileName`
5	File	`FileContent`
6	Text	`PersonEmail`
7	Number	`CourseID`
8	Text	`CourseName`
9	Text	`ActorEmail`
10	Yes/No	`ViewingForSelf`

RecordID and CourseID must both be type Number, not Text. Every Id field on a SharePoint action (Update item, Get attachments, Delete attachment, Add attachment) expects a number, and TT TrainingRecords.CourseID is itself a Number column, not text. Power Automate's dynamic content picker silently filters its list by matching type — on a Number-typed field, a Text-typed value will not appear in the list at all, showing "No dynamic content available" even though the trigger genuinely has the value. This is documented Microsoft behaviour, not a bug in your flow: Dynamic content picker missing dynamic content from previous steps. If you already created either input as Text, delete it and re-add it as Number before continuing. CourseID going into TT AuditLog.CourseID (a genuine Text column on that list) later in Step 4 is fine as-is — a Number value dropped into a Text-typed SharePoint field is not filtered the same restrictive way, and SharePoint converts it automatically.

Step 2 — Update The Training Record

Add a new step. Search for SharePoint, choose the Update item action.
Site Address: your Training Tracker SharePoint site.
List Name: TT TrainingRecords.
Id: insert the RecordID dynamic content from the trigger.
Fill in these fields using dynamic content from the trigger; leave every other field on this step untouched:

SharePoint field	Value
`PersonEmail`	dynamic content `PersonEmail`
`CourseID`	dynamic content `CourseID`
`CourseName`	dynamic content `CourseName`
`DateCompleted`	dynamic content `DateCompleted`
`UploadedByEmail`	leave this field completely empty. See the callout directly below — do not type an expression into it.
`LastUpdatedByEmail`	dynamic content `ActorEmail`
`LastUpdatedOn`	expression: `utcNow()`
`LastStatus`	type the literal text `Certificate saved`

Why PersonEmail/CourseID/CourseName are in this list even though nothing changes them: those three columns are set as required on TT TrainingRecords. SharePoint's Update item connector demands a value for every required column on every update, even ones you have no intention of changing, or it will refuse to save with a red asterisk and won't let you save the flow at all. Re-supplying the same value the trigger already received is harmless — it does not change the stored data, it just satisfies the connector.

Never hand-type an expression containing text_N, number_N, or boolean_N from this guide, or from anywhere else, including a previous version of this same section. Power Automate assigns those internal names automatically based on the exact order you added your own trigger inputs in, and deleting/re-adding even one input (for example, fixing a Text input to Number) changes the numbering. A raw expression typed by hand with the wrong number silently references nothing, writes blank, and shows no error — it will not warn you. Always build expressions by clicking dynamic content from the picker (which inserts the correct reference automatically), never by typing a field's internal name yourself.

Leaving UploadedByEmail blank for now is deliberate, not a shortcut. It only powers an "uploaded on your behalf by a manager" note elsewhere in the app and is not required for saving to work. If you want it working properly later: add a small Condition step before Update item that checks dynamic content ViewingForSelf is equal to the boolean true (typed via the fx expression editor, not picked from a Yes/No dropdown — see the warning in Step 3 below for why), then put the literal empty text "" in the Yes branch's copy of Update item and dynamic content ActorEmail in the No branch's copy.

Step 3 — Replace The Attachment, Only If A New File Was Sent

Add a new step: Condition. On the left side, pick dynamic content HasNewFile. Set the operator to is equal to. On the right side, do not use the Yes/No dropdown — click the fx (expression) icon on that same value box and type the bare word true (no quotes), then confirm it.
In the If yes branch, add SharePoint → Get attachments. Site Address and List Name as before. Id: RecordID.
Still inside If yes, add Apply to each, output selected from Get attachments. Inside the loop, add SharePoint → Delete attachment: Site Address/List Name as before, Id: RecordID, File Identifier: open the dynamic-content list for the current loop item and look for a field named Id — use that if it is offered. Only fall back to Name/Display Name if no Id field is available.
After the Apply to each finishes (still inside If yes, but below/after the loop — drag the next action so it sits outside the loop but inside the Yes branch), add SharePoint → Add attachment: Site Address/List Name as before, Id: RecordID, File Name: dynamic content FileName (the plain text input, not any FileContent sub-field), File Content: dynamic content FileContent contentBytes — the FileContent File-type input expands into two options in the picker, contentBytes and name; you want contentBytes here, never name.
Leave the If no branch completely empty.

Why true via the expression editor, and not the Yes/No dropdown: when the Condition builder's dropdown is used to pick "Yes" against a genuine boolean field, it can store the literal text "Yes" rather than the real boolean value true. Comparing a real boolean to the text string "Yes" does not reliably evaluate as a match, which means this whole branch — the actual "replace the certificate" logic — can silently never run, with the flow still reporting success because everything before and after it genuinely did succeed. If you already built this Condition using the Yes/No dropdown, open it and redo the right-hand side using the expression editor as described above.

Step 4 — Write The Audit Log Entry

Add a new step, after the Condition (not inside either branch). SharePoint → Create item.
Site Address: your site. List Name: TT AuditLog.

SharePoint field	Value
`Title`	click dynamic content `CourseName`, then type the literal text `certificate saved` straight after it in the same field (do not type a raw expression here — see the warning in Step 2 above)
`EntityType`	literal text `TrainingRecord`
`EntityID`	dynamic content `RecordID`
`PersonEmail`	dynamic content `PersonEmail`
`CourseID`	dynamic content `CourseID`
`EventType`	literal text `Certificate saved`
`EventDescription`	dynamic content `CourseName`, followed by the literal text `certificate saved.`
`ActorEmail`	dynamic content `ActorEmail`
`EventDateTime`	expression: `utcNow()`
`Notes`	leave blank for now — this only needs the "Manager upload" note from the same `ViewingForSelf` condition mentioned in Step 2, which you can add later

Step 5 — Respond To The App

Add a final step: Respond to a PowerApp or flow.
Add two outputs: a Yes/No output named success, and a Text output named message.
For success, do not use the Yes/No toggle — click the fx expression icon on that value and type the bare word true (no quotes), same reasoning as the Condition warning in Step 3: typed "Yes" can end up stored as literal text rather than a real boolean, and the app reads this field expecting a genuine boolean.
For message, type the literal text Saved for record then click dynamic content RecordID straight after it in the same field (mixed text + dynamic content, same as other fields earlier in this flow).
Save the flow.

If Power Automate warns "does not reference any data from the flow run" after saving: this is only a warning, not an error — it means every output field was pure hardcoded text with nothing dynamic in it, so its optimizer can't tell the response is worth waiting for. We do want the app to wait for it (that is the entire point of this action), so do not follow its suggestion to remove the response step. Having RecordID in message as shown above is enough to silence it — any genuine dynamic reference in at least one output works.

This covers the happy path only. A beginner-friendly flow like this does not include retry/error branches. If any step fails, Power Automate shows a red cross on that step in the run history, and the app's IfError(...) around the flow call will show a generic "Save failed" message. That is enough to know something went wrong; diagnosing which step failed means opening that run in Power Automate's history and reading the error on the failed step.

Harden `TT - Save Certificate`: Require Authorisation

The steps above give TT - Save Certificate no authorisation check at all — it writes whatever record the app tells it to, trusting that only the app's own navigation ever points it at the right record. Today that is true in practice, but it is not enforced by the flow itself. This adds the same real check the read flow already has: verified against the actual stored data, not just trusted from the app.

1. Add One More Input

Open the trigger. + Add an input, type Yes/No, name it IsAdmin. Because you are adding this to a flow that already has 10 inputs, it will land at the very end of the schema, after CourseID — that is expected, and is exactly why the app formula below adds it as the last argument, not wherever "feels" logical.

2. Add A Lookup Right After The Trigger

Add SharePoint → Get item as the very first action, before your existing Update item step. Site Address/List Name as usual, List Name: TT TrainingRecords, Id: dynamic content RecordID. This fetches the record's real, currently-stored PersonEmail — the trigger's own PersonEmail input is supplied by the app and is not trustworthy for a security check on its own, the same reasoning as the read flow.

3. Add The Authorisation Condition

Add a Condition directly after the new Get item step (still before Update item).
First row: dynamic content ActorEmail (trigger) is equal to dynamic content PersonEmail (from this new Get item, not the trigger).
+ Add row, switch the group to Or.
Second row: dynamic content IsAdmin (trigger) is equal to — via the fx icon, bare word true, not the Yes/No dropdown.

4. Move Or Rebuild The Existing Steps Into The Yes Branch

Everything you already built — Update item, the HasNewFile Condition with its attachment steps, Create item, and the existing Respond to a PowerApp or flow — needs to end up inside this new Condition's If yes branch, so none of it runs for an unauthorised caller.

Two ways to get there, try the first, fall back to the second if it fights you:

Drag: each action has a small handle on its left edge. Try dragging Update item into the If yes branch first; if the later steps do not follow automatically, drag each one in afterward, in the same order they were in before (Update item, then the HasNewFile Condition, then Create item, then Respond).
Rebuild: if dragging misbehaves, it is equally correct and lower-risk to delete those four existing actions and recreate them fresh inside the If yes branch, following Steps 2 through 5 earlier in this section exactly as written — nothing about their field mappings changes, only where they physically sit on the canvas.

5. Respond For The Unauthorised Case

In the new Condition's If no branch, add Respond to a PowerApp or flow with the same two outputs as the existing one: success set via fx to false, and message set to the literal text You are not authorized to save this record.

Save the flow.

6. Update The App Formula

Already done in scrCourseDetail v3.5 — btnSaveCert.OnSelect now passes varIsAdmin as an 11th, final argument. As always: verify the real order against your flow's own autocomplete hints before trusting it, not this guide's table, especially since you have just changed this flow's structure again.

This flow now has two separate Respond to a PowerApp or flow actions (one per branch) — technically the same unsupported pattern documented in the "two Respond actions" warning below, in the TT - Get Certificate section. It happens to work here only because both branches share the exact same two fields (success, message), so there is nothing for Power Apps to disagree about. If this flow is ever extended to return more fields later, apply the same fix used in TT - Get Certificate: consolidate to variables set in each branch, with a single Respond action after the Condition ends. Not worth rebuilding right now since it is working, but do not copy this two-Respond pattern into a new flow.

Build The Second Flow: `TT - Get Certificate`

This is the read side — it lets someone re-open or download a certificate that is already on file. It is simpler than the save flow: no writes, just one lookup, one authorisation check, and the file handed back.

How authorisation works here, and the trade-off being made: the flow checks, on its own, whether the person asking is the same person the record belongs to — that check happens fully inside the flow, using the record's real stored email, and cannot be faked by the app. For the "or an admin can also view it" case, the flow trusts the IsAdmin flag the app already computed rather than independently re-checking it against TT Personnel a second time. That is a deliberate simplification, not an oversight: it protects against the actual risk this whole upgrade exists for (casual/accidental browsing of the raw SharePoint list, or one person's app session reading another person's certificate), without a second nested lookup. It would not stop someone who already has valid access to your tenant and is deliberately trying to forge a request outside the app entirely — if that is a real concern for your organisation, say so and this can be hardened to re-check admin status server-side too, the same way the self-check already is.

In make.powerautomate.com, Create → Instant cloud flow. Name it exactly TT - Get Certificate.
Trigger: Power Apps (V2).

Step 1 — Add The Inputs

#	Input type to pick	Name
1	Number	`RecordID`
2	Text	`ActorEmail`
3	Yes/No	`IsAdmin`

Two rewrites are baked into the steps below — if you built an earlier version, read this first. (1) A Respond to a PowerApp or flow action cannot sit inside an Apply to each loop — Power Automate blocks this outright at save time ("could not be nested under an action of type 'foreach'"). (2) Just as importantly: a flow cannot have two separate Respond to a PowerApp or flow actions at all — not even one in each Condition branch with identically-named fields. Power Apps cannot reliably combine two Respond actions into one schema, and in practice ends up only recognising whichever fields happen to be common/guessable across both, which is exactly the "only success/message show up, `fileName`/`fileContentBase64` don't" symptom. Confirmed against Microsoft's own community forum: a moderator-verified answer states plainly, "You can't have two Respond to Power Apps actions in the same flow." If you already built two separate Respond actions (one per branch), delete both of them now — the steps below use variables all the way through instead, with exactly one Respond action at the very end, after both branches rejoin.

Step 2 — Initialize Four Variables

These sit at the very top of the flow, directly after the trigger and before anything else — not inside any Condition or loop. Power Automate only allows Initialize variable actions at the top level of a flow, so all four must be added here, even though most are not filled in until later.

Add Control → Initialize variable. Name: varSuccess. Type: Boolean. Value: leave empty.
Add a second Initialize variable. Name: varMessage. Type: String. Value: leave empty.
Add a third. Name: varFileName. Type: String. Value: leave empty.
Add a fourth. Name: varFileContentBase64. Type: String. Value: leave empty.

Step 3 — Look Up The Record

Add SharePoint → Get item. Site Address/List Name as in the other flow, List Name: TT TrainingRecords. Id: dynamic content RecordID.

Step 4 — Check Authorisation

Add a Condition.
First row: left side dynamic content ActorEmail (from the trigger), operator is equal to, right side dynamic content PersonEmail (from the Get item step you just added — not the trigger).
Click + Add row under that same group, and switch the group's operator from And to Or (there is a small And/Or toggle above the rows).
Second row: left side dynamic content IsAdmin (from the trigger), operator is equal to, right side — same rule as every boolean comparison in this guide: click the fx icon and type the bare word true, do not use a Yes/No dropdown.

Step 5 — If Authorised, Fetch The File And Fill In The Variables

Inside the If yes branch (do not add a Respond action in here — that comes later, once, after both branches rejoin):

Add SharePoint → Get attachments. Id: dynamic content RecordID.
Add Apply to each, output selected from Get attachments.
Inside the loop, add SharePoint → Get attachment content: Id: dynamic content RecordID. For File Identifier, open the dynamic content picker and look for the group headed "Get attachments" (not "Get item") — inside that group, pick the Id entry (it will say "File identifier" underneath it). The picker will also show an ID under a separate "Get item" group — that one is the training record's own row ID (same value as RecordID), not the attachment, and is the wrong one here even though it is also called "Id".
Still inside the loop, immediately after, add Control → Set variable: Name: varFileName, Value: the current loop item's Name dynamic content (from the "Get attachments" group).
Still inside the loop, add a second Set variable: Name: varFileContentBase64, Value: click the fx icon and build base64(, then switch to the Dynamic content tab and click Get attachment content's body/file content, then close the bracket ).
Now drag two more actions so they sit after the whole Apply to each loop ends, but still inside the If yes branch — not inside the loop. First, Set variable: Name: varSuccess, Value: click fx, type the bare word true. Second, Set variable: Name: varMessage, Value: literal text OK.

Why variables instead of just reading the loop's output directly: an action's output from inside an Apply to each is technically an array (one result per loop iteration), so it cannot be read directly as a single value once you are outside the loop. Since certificates are limited to one attachment (MaxAttachments: 1 in the app), the loop only ever runs zero or one times in practice — storing the result in a variable inside the loop, then reading that variable later, sidesteps the array problem entirely and is the standard fix for this exact situation.

Step 6 — If Not Authorised, Set The Same Variables

In the If no branch (again, no Respond action here): add Set variable: Name: varSuccess, Value: click fx, type false. Add a second Set variable: Name: varMessage, Value: literal text You are not authorized to view this certificate. Leave varFileName/varFileContentBase64 untouched here — they keep whatever they were initialized to in Step 2.

Step 7 — One Single Respond Action, After Both Branches Rejoin

Drag a new action so that it sits below and outside the whole Condition box — not inside either branch. This is the only Respond to a PowerApp or flow action in this entire flow. Add four outputs: a Yes/No named success set to dynamic content varSuccess; a Text named message set to dynamic content varMessage; a Text named fileName set to dynamic content varFileName; a Text named fileContentBase64 set to dynamic content varFileContentBase64. Save the flow.

Known limitation, accepted on purpose: this flow does not separately handle "the record exists but has no certificate attached." In practice that should not be reachable — the app's Open certificate button only appears when it already knows locally that an attachment exists. If Get attachments genuinely returns nothing, the loop simply does zero iterations, varFileName/varFileContentBase64 stay empty, and the Respond action still fires with varSuccess: true but empty file data — the app would show "success" with nothing to open. Acceptable for now; tell me if you want this handled explicitly.

Connect Both Flows In The App

In Power Apps Studio, open the Training Tracker app.
Data pane → Add data → search Power Automate or your organisation's flows → add both TT - Save Certificate and TT - Get Certificate.
Open scrCourseDetail and click into btnSaveCert.OnSelect. Start typing 'TT - Save Certificate'.Run( and let autocomplete finish the flow name for you — do not hand-type it. Power Apps generates its own identifier from the flow's display name (spaces are usually stripped), and it is easy to get that wrong by guessing.
Do the same for btnOpenCertificate.OnSelect, starting 'TT - Get Certificate'.Run(.
If either pasted formula's flow identifier does not match what autocomplete gives you, replace just that one identifier (leave the rest of the formula as pasted).

Critical, for both flows — the argument order in the pasted formula must match each flow's actual trigger order, not this guide's tables. .Run(...) passes arguments purely by position, not by name. If you deleted and re-added any trigger input while fixing a type, that input moves to the end of the trigger's real parameter order, even though it is still first in this guide's table. Before trusting either pasted formula, click into its .Run( in the formula bar and read the grey parameter-name hints Power Apps shows for each argument slot — reorder the pasted arguments to match those hints exactly, in whatever order your trigger really ends up in. Do not assume either table's order still applies once you have edited any input.

Confirmed live, 2026-07-31 — the FileContent argument must be the whole attachment record, not just its .Value. The original formula passed First(colNewCertificate).Value, which Power Apps rejected with "Invalid argument type (Text). Expecting a Record value instead" — a flow's File-type trigger input compiles to a Record type in Power Fx (matching name + content together), not the raw file content on its own. Fix: pass First(colNewCertificate) (the whole record, no .Value) instead. The Blank() fallback for "no new file picked" needed no change and works correctly alongside this. screens/scrCourseDetail.html v3.7 already has this fix applied — if you pasted an earlier version, open btnSaveCert.OnSelect and change that one argument the same way.

Also verify live, not assume: the Open certificate button uses Launch("data:application/octet-stream;base64," & varCertDownload.fileContentBase64) to trigger a browser download/open of the returned file. This is a well-documented technique for handing a file back from a flow to a canvas app, but it has not been tested against this specific flow and file types (PDF/JPG/PNG/DOCX may each behave slightly differently in how the browser opens vs downloads them). If it does not open the file correctly, tell me exactly what happened (blank tab, download of a corrupted/empty file, nothing at all) rather than guessing at a fix yourself.

If Power Apps says Name isn't valid. 'fileContentBase64' isn't recognized. on the btnOpenCertificate formula: this is not a typo in the formula — it means Power Apps added TT - Get Certificate as a data source before you finished (or changed) that flow's Respond to a PowerApp or flow outputs, and it is still using the older, cached shape of the flow's response. No screen edit needed, just a data source refresh:

In Power Apps Studio, open the Data pane on the left.
Find TT - Get Certificate in the list, click the … (three dots) next to it.
Choose Remove (this only removes the reference to the flow from the app's data sources — it does not touch or delete the actual flow in Power Automate).
+ Add data, search for TT - Get Certificate again, and add it back. This re-reads the flow's current, real output schema.
Go back to btnOpenCertificate.OnSelect — the red squiggly under fileContentBase64 should be gone. If the whole formula turned red/broken from the removal, retype just the .Run(...) line following the guide above; the surrounding Set(...)/If(...) lines around it do not need to change.

Progress And Error Feedback

scrCourseDetail v3.7 (already included in the paste above — nothing extra to build in Power Automate for this part) adds a busy state and a persistent error banner around both Save record and Open certificate, using three screen-local variables seeded in scrCourseDetail.OnVisible: varCertBusy (Boolean), varCertStatusText (Text), varCertError (Text).

The instant either button is pressed, it and the other certificate controls (Cancel, the file picker) grey out via DisplayMode.Disabled, and a small spinner plus a status line (Saving certificate... / Fetching certificate...) appear beneath the buttons.
Once the flow responds, controls re-enable and the spinner/status clear. On success, the usual toast notification still fires (and for Save, the screen navigates back as before).
On failure, instead of only a toast that disappears in a few seconds, the flow's actual error message is written into varCertError and shown in a persistent amber banner with a close (×) icon, so the exact wording can be read, copied, or reported back — it does not auto-dismiss. It clears automatically the next time either button is pressed, or manually via the ×.

Honest limit on "every step of the way": a single .Run(...) call to a flow is one request and one response — the app genuinely has no way to know which action inside the flow is currently executing while it waits, so it cannot show true step-by-step progress ("checking permissions... now fetching the file..."). What this adds is one clear "working" state for the whole operation, and a real, readable error afterward if it fails — not a multi-stage progress bar, because that would require inventing information the app does not actually have.

colNewCertificate seeded in App.OnStart.
scrCourseDetail v3.7 pasted (includes busy/error UI, no separate step needed).
Flow TT - Save Certificate created with all 11 inputs, in order, including IsAdmin added last.
Get item (authoritative PersonEmail lookup) sits before the authorisation Condition, which sits before Update item.
Update item step (inside the Yes branch) writes DateCompleted, LastUpdatedByEmail, LastUpdatedOn, LastStatus, and re-supplies the required PersonEmail/CourseID/CourseName columns from the trigger.
Condition on HasNewFile (also inside the Yes branch), with Get attachments → Apply to each → Delete attachment, then Add attachment, all inside its own Yes branch, comparing to boolean true via the expression editor, not the Yes/No dropdown.
Create item on TT AuditLog (inside the Yes branch) with all nine fields mapped.
Respond to a PowerApp or flow for the authorised case (inside the Yes branch) with success (real boolean true, not typed "Yes") and message outputs; a second Respond in the No branch with success: false and an "not authorized" message.
Flow TT - Get Certificate created with all 3 inputs, in order, plus varSuccess (Boolean), varMessage, varFileName, varFileContentBase64 (all String except varSuccess) initialized at the top level, before Get item.
Condition checking ActorEmail = PersonEmail OR IsAdmin = true (real boolean, via expression editor). Inside the Yes branch: Get attachments → Apply to each (setting varFileName/varFileContentBase64 inside the loop) → after the loop, Set variable for varSuccess/varMessage. Inside the No branch: Set variable for varSuccess/varMessage only. Exactly one Respond to a PowerApp or flow action, placed after the whole Condition (not inside either branch), reading all four values from the variables — never two separate Respond actions in the same flow.
Both flows added as data sources in the app.
Both .OnSelect flow identifiers confirmed via autocomplete, not hand-typed, and argument order confirmed against each flow's real trigger order — especially TT - Save Certificate, since its structure changed again in this hardening pass.
Tested live: save with no new file (date-only change), save with a new file on a record with no existing certificate, save with a new file replacing an existing one, save as an admin on someone else's record, confirm a non-admin cannot save to someone else's record, open/download an existing certificate as the record's own person, open/download the same certificate as an admin, confirm a different non-admin person cannot open someone else's certificate.
Spinner/status text appears on both buttons while their flow is running, and controls visibly grey out; confirm a genuine failure (e.g. temporarily rename a field in a flow to force an error) shows the amber error banner with real wording, and that it clears on the × icon and on the next attempt.

Paste-Ready YAML Pages

Use these after the schema fix is complete. For big replacements, delete the controls on the target screen first, then paste the YAML fresh.

scrAdminDashAdmin dashboard, KPI cards, attention filters, risk bars, timeline, admin certificate viewer and reminder-history pills.

Open

scrAdminManagePeople/course management with audit hooks.

Open

scrCourseDetailCleaner certificate save screen with audit fields.

Open

scrMyTrainingPersonal training dashboard.

Open

scrTeamMemberLine manager chain view.

Open

scrMandatoryStatsSix-month mandatory course snapshot dashboard.

Open

scrNoRecordNo Personnel record screen.

Open

scrStartRouter/loading screen.

Open

Checklist Before We Troubleshoot Screen Errors

TT AuditLog list exists.
All ten TT AuditLog columns exist with exact internal names.
TT Personnel has the four audit columns.
TT Courses has the four audit columns.
TT Courses has CourseHost, CourseURL and optional CourseDescription.
TT Personnel has RemoteWorker.
TT Courses has ExcludeRemoteWorkers.
TT TrainingRecords has the four audit columns.
TT ComplianceSnapshots list exists with all ten snapshot columns.
TT AuditLog is added as a Power Apps data source.
TT ComplianceSnapshots is added as a Power Apps data source.
TT ReminderLog is added as a Power Apps data source before applying the admin certificate/reminder pass.
Office365Users is still connected.
All required SharePoint data sources have been refreshed in Power Apps Studio.
App.OnStart has been updated and run once.
The App formulas dropdown has no required v3 named formula changes.

Once those are done, send over the first remaining error exactly as Power Apps shows it and we will fix the YAML/formula directly.

Training Tracker v3 Fix Guide

What This Page Is

What Is TT AuditLog For?

Fix 1 - SharePoint Schema

Create New List: TT AuditLog

Add Columns To Existing Lists

Confirm Course Delivery Columns

Remote Worker Exclusion Columns

Create New List: TT ComplianceSnapshots

Connect And Refresh In Power Apps

App Setup

Data Sources Required

App Formulas Dropdown

App.OnStart

Fix 2 - Date Of Completion Pre-fill

Fix 3 - Add Course Validation

Fix 4 - Blank Admin Dashboard

Fix 5 - Provider Dropdown Default

Fix 6 - Admin OnVisible Navigation

Fix 7 - Compliance KPI Meaning

Fix 8 - Stale Course Names

Build Pass - Add Mode Buttons

Add btnNewPersonMode

Add btnNewCourseMode

Build Pass - Remote Worker Exclusions

Schema

Add Person Checkboxes

Add Course Checkboxes

Patch Person Writes

Patch Person Writes (already live — shown for reference)

Compliance Formula Changes (not yet live — still needs doing)

My Training Course Filter (not yet live)

Team Member Course Filter (not yet live — four fixes, not one)

Build Pass - Course Expiry Reminder Emails

Step 1 — Build The Flow With The Safety Switch First

Step 2 — Pull The Data

Step 3 — Loop Every Person, Then Every Applicable Course

Step 4 — Find This Person's Record For This Course, Compute The Expiry Date

Step 5 — The Two Date-Match Conditions

Step 6 — Send The Email, Through The Safety Switch

Step 7 — Test In TestMode Before Touching The Switch

Build Pass - Snapshot Dashboard

Data Source

Paste The New Screen

Replace Export PDF Button

Add A Normal-User Entry Point

Monthly Use

Optional Upgrade — Certificate Privacy (Power Automate)

The One Rule To Remember: true/false Fields vs Plain Text Fields

What This Changes

App.OnStart Addition

Paste The Updated Screen

Step 1 — Add The Inputs

Step 2 — Update The Training Record

Step 3 — Replace The Attachment, Only If A New File Was Sent

Step 4 — Write The Audit Log Entry

Step 5 — Respond To The App

Harden TT - Save Certificate: Require Authorisation

1. Add One More Input

2. Add A Lookup Right After The Trigger

3. Add The Authorisation Condition

4. Move Or Rebuild The Existing Steps Into The Yes Branch

5. Respond For The Unauthorised Case

6. Update The App Formula

Build The Second Flow: TT - Get Certificate

Step 1 — Add The Inputs

Step 2 — Initialize Four Variables

Step 3 — Look Up The Record

Step 4 — Check Authorisation

Step 5 — If Authorised, Fetch The File And Fill In The Variables

Step 6 — If Not Authorised, Set The Same Variables

Step 7 — One Single Respond Action, After Both Branches Rejoin

Connect Both Flows In The App

Progress And Error Feedback

Paste-Ready YAML Pages

Checklist Before We Troubleshoot Screen Errors

What Is `TT AuditLog` For?

Create New List: `TT AuditLog`

Create New List: `TT ComplianceSnapshots`

Add `btnNewPersonMode`

Add `btnNewCourseMode`

The One Rule To Remember: `true`/`false` Fields vs Plain Text Fields

Harden `TT - Save Certificate`: Require Authorisation

Build The Second Flow: `TT - Get Certificate`