Admin Dashboard Certificate And Reminder Visibility

1. What This Adds

• A View cert button on each completed course row in the admin drill-down panel.
• An admin-only certificate lightbox on scrAdminDash using the existing TT - Email Certificate Library flow.
• A Last reminder pill on each person row, read from TT ReminderLog.
• No email sending and no reminder-flow changes. This page only reads the reminder log and opens certificates.

2. Data Sources And Permissions

1. In Power Apps Studio, open the Data pane.
2. Add the SharePoint list TT ReminderLog.
3. Add or refresh the flow data source TT - Email Certificate Library.
4. Refresh TT Personnel, TT Courses, TT TrainingRecords, TT AuditLog, and TT ReminderLog.

3. Update scrAdminDash.OnVisible

Open scrAdminDash and edit its OnVisible. These are targeted edits, not a full formula replacement.

3.1 Add Viewer Variable Resets

Near the top, just after the existing Set(varAdminStatusFilter, "All"); line, add this block.

Set(varAdminCertViewerOpen, false);
Set(varAdminCertDataUri, "");
Set(varAdminCertError, "");
Set(varAdminCertStatusText, "");
Set(varAdminCertBusy, false);

3.2 Refresh And Cache TT ReminderLog

After the existing Refresh('TT AuditLog'); line, add this.

Refresh('TT ReminderLog');

After the existing ClearCollect(colAuditLog, 'TT AuditLog'); line, add this.

ClearCollect(
    colReminderLog,
    Filter(
        'TT ReminderLog',
        Coalesce(TestMode, false) = false &&
        Coalesce(LiveEmailsEnabled, false) = true
    )
);

3.3 Add LastReminderText To colCompliance

Inside the record built by ClearCollect(colCompliance, ForAll(...)), add this new field just before AttentionState:.

LastReminderText: With(
    {
        lastRem: First(
            SortByColumns(
                Filter(colReminderLog, Lower(PersonEmail) = Lower(P.Email)),
                "SentOn",
                SortOrder.Descending
            )
        )
    },
    If(
        IsBlank(lastRem.SentOn),
        "",
        Switch(
            Text(lastRem.ReminderType.Value),
            "DUE_30", "30-day email",
            "DUE_7", "7-day email",
            "EXPIRED_TODAY", "Expired email",
            "OVERDUE_30", "30-day overdue",
            "OVERDUE_WEEKLY", "Overdue email",
            "OVERDUE_ESCALATED", "Escalated overdue",
            "Reminder email"
        ) &
        " " & Char(183) & " " &
        If(
            DateDiff(DateValue(lastRem.SentOn), Today(), "Days") = 0,
            "today",
            Text(lastRem.SentOn, "dd mmm yyyy")
        )
    )
),

LastReminderText: With(
    {
        lastRem: First(
            SortByColumns(
                Filter(colReminderLog, Lower(PersonEmail) = Lower(P.Email)),
                "SentOn",
                SortOrder.Descending
            )
        )
    },
    If(
        IsBlank(lastRem.SentOn),
        "",
        Switch(
            Text(lastRem.ReminderType),
            "DUE_30", "30-day email",
            "DUE_7", "7-day email",
            "EXPIRED_TODAY", "Expired email",
            "OVERDUE_30", "30-day overdue",
            "OVERDUE_WEEKLY", "Overdue email",
            "OVERDUE_ESCALATED", "Escalated overdue",
            "Reminder email"
        ) &
        " " & Char(183) & " " &
        If(
            DateDiff(DateValue(lastRem.SentOn), Today(), "Days") = 0,
            "today",
            Text(lastRem.SentOn, "dd mmm yyyy")
        )
    )
),

This deliberately stays blank if no live reminder row exists for that person.

4. Add The Last Reminder Pill To galCompliance

Add a small header label outside the gallery, then a pill button inside the gallery template.

4.1 Header Label

Insert a Label directly on scrAdminDash, not inside the gallery. Rename it lblAdminTableHeadReminder.

4.2 Pill In The Person Row

Select galCompliance, add a classic Button inside the gallery template, and rename it btnLastReminderPill.

5. Add Certificate Viewing To The Detail Gallery

The existing right-side detail list already has lblDetailDate. This pass keeps that label and adds one button per completed training record.

5.1 Make The Detail Rows Taller

Select galPersonDetail and set this property.

Move btnDetailPill.Y to 8 and move lblDetailDate.Y to 42.

5.2 Confirm The Completed Date Label

Select lblDetailDate. Its Text should be this.

If(
    IsBlank(ThisItem.DateDone),
    "Not completed",
    Text(ThisItem.DateDone, "dd mmm yyyy") &
    If(!IsBlank(ThisItem.UploadedBy), " (via " & ThisItem.UploadedBy & ")", "")
)

5.3 Add The View cert Button

Select galPersonDetail, add a classic Button inside the gallery template, and rename it btnAdminViewDetailCert.

Set btnAdminViewDetailCert.OnSelect to this full formula.

Set(varAdminCertError, "");
Set(varAdminCertStatusText, "Fetching certificate...");
Set(varAdminCertBusy, true);
Set(
    varAdminCertRaw,
    IfError(
        'TT - Email Certificate Library'.Run(ThisItem.RecordID, varMyEmail, varIsAdmin),
        Blank()
    )
);
Set(varAdminCertBusy, false);
If(
    IsBlank(varAdminCertRaw),
    Set(varAdminCertError, "Flow could not be reached. Check your connection and try again.");
    Notify("Could not open certificate - see the error below.", NotificationType.Error),
    With(
        {parsed: ParseJSON(varAdminCertRaw.payload)},
        If(
            Boolean(parsed.success),
            Set(varAdminCertStatusText, "");
            Set(varAdminCertFileNameLower, Lower(Text(parsed.fileName)));
            Set(varAdminCertIsPdf, EndsWith(varAdminCertFileNameLower, ".pdf"));
            Set(
                varAdminCertMimeType,
                If(
                    varAdminCertIsPdf, "application/pdf",
                    EndsWith(varAdminCertFileNameLower, ".png"), "image/png",
                    "image/jpeg"
                )
            );
            Set(varAdminCertDataUri, "data:" & varAdminCertMimeType & ";base64," & Trim(Text(parsed.fileContentBase64)));
            Set(varAdminCertViewerOpen, true),
            Set(varAdminCertError, Text(parsed.message));
            Notify("Could not open certificate - see the error below.", NotificationType.Error)
        )
    )
)

6. Add The Admin Certificate Lightbox

Add these controls directly on scrAdminDash, not inside any gallery or group. Put them at the end of the tree so they sit above the dashboard when visible.

scrAdminDash
├── existing dashboard controls
├── galCompliance
├── galPersonDetail
├── existing loading overlay
├── recAdminCertViewerBG
├── pdfAdminCertViewer
├── imgAdminCertViewer
├── btnCloseAdminCertViewer
└── lblAdminCertError

6.1 Dimmed Background

Insert a classic Button and rename it recAdminCertViewerBG.

6.2 PDF Viewer

Insert PDF viewer and rename it pdfAdminCertViewer.

6.3 Image Viewer

Insert an Image control and rename it imgAdminCertViewer.

6.4 Close Button

Insert a Modern Button and rename it btnCloseAdminCertViewer.

6.5 Error Label

Insert a Label and rename it lblAdminCertError.

7. Test Script

1. Run App.OnStart, then open scrAdminDash in Preview.
2. Click a person who has at least one completed course with a certificate in TT Certificates_LTD.
3. Confirm the right-side detail row shows the completed date.
4. Click View cert. Confirm the screen dims and the certificate opens inline.
5. Close the viewer and open the same certificate again to prove the state resets cleanly.
6. Check a person who has no reminder log rows. Their row should have no reminder pill.
7. Check a person with a real live reminder row. Their row should show a pill like 30-day email · 12 Aug 2026 or Overdue email · today.
8. Create or inspect a test-mode reminder row. Confirm it does not appear on the dashboard row.

8. Checklist

• TT ReminderLog added as a data source.
• Jackie has read permission to TT ReminderLog if the list is locked down.
• TT - Email Certificate Library added or refreshed as a flow data source.
• scrAdminDash.OnVisible refreshes and collects colReminderLog.
• colCompliance includes LastReminderText.
• galCompliance has btnLastReminderPill.
• galPersonDetail has btnAdminViewDetailCert.
• Viewer controls are direct children of scrAdminDash and last in the tree.
• PDF and image certificates both render inline.
• No reminder emails are sent by anything on this page.