Admin Data Maintenance Build Pass

This adds two maintenance features: certificate library metadata so PDFs are identifiable in SharePoint, and an admin-only permanent person removal route that deletes that person's training records and certificate files after a strong confirmation.

0. Why The Reminder Flow Guide Changed

You are currently building the email reminder flow. The guide was changed for two concrete reasons, not just tidying:

If you are halfway through that build, keep following Course Expiry Reminder Emails from the corrected Ready to send section onward. The changes are there because the older branch shape was genuinely invalid.

1. Certificate Library Metadata

The locked certificate library should not be a pile of anonymous PDFs. Add these columns to TT Certificates_LTD / TT Certificates (LTD).

1.1 Update TT - Save Certificate Library

Open the save-certificate flow. Inside the authorised branch, inside the HasNewFile - True / If yes branch, the shape should be:

HasNewFile
└── True / If yes
    ├── Get existing certificate files
    ├── Apply to each existing certificate
    │   └── Delete file
    ├── Create file
    └── Update file properties

If you already have Update file properties, edit it. If not, add it directly under Create file. Do not put it inside the delete loop.

For PersonName, use section 1.2. Do not guess a dynamic token called Title if there are several of them.

1.2 Optional But Recommended: Fill PersonName

To fill the human-readable name, add this before Update file properties, still inside the same HasNewFile - True / If yes branch.

1. Add SharePoint - Get items. Name it Get Person For Metadata.
2. List: TT Personnel.
3. Filter Query: use the expression below.
4. Top Count: 1.

concat("Email eq '", PERSON_EMAIL_FROM_TRIGGER, "'")

Then set PersonName in Update file properties with this expression.

first(body('Get_Person_For_Metadata')?['value'])?['Title']

2. Build TT - Remove Person

This should be an instant cloud flow called from Power Apps. It performs the destructive cleanup under the flow connection, not by giving every admin direct delete formulas in the app.

2.1 Trigger Inputs

2.2 Flow Shape

Power Apps (V2)
├── Initialize varSuccess = false
├── Initialize varMessage = ""
├── Get person
├── Condition - Authorised and confirmed
│   ├── True / If yes
│   │   ├── Get certificate files
│   │   ├── Apply to each certificate file
│   │   │   └── Delete file
│   │   ├── Get training records
│   │   ├── Apply to each training record
│   │   │   └── Delete item
│   │   ├── Get people using this line manager
│   │   ├── Apply to each managed person
│   │   │   └── Update item - clear LineManagerEmail
│   │   ├── Delete item - TT Personnel
│   │   ├── Create item - TT AuditLog
│   │   ├── Set varSuccess = true
│   │   └── Set varMessage = "Person removed."
│   └── False / If no
│       └── Set varMessage = "Not authorised or confirmation did not match."
└── Respond to a PowerApp or flow

2.3 Authorisation Condition

The condition must check both admin status and typed confirmation. Use this as the left side expression, operator is equal to, right side true.

and(
    equals(triggerBody()?['boolean'], true),
    equals(toLower(triggerBody()?['text']), toLower(triggerBody()?['text_1']))
)

2.4 Delete Certificate Files

Inside Authorised and confirmed - True / If yes, add SharePoint - Get files (properties only).

concat("PersonEmail eq '", triggerBody()?['text'], "'")

Add Apply to each over the files returned. Inside it add SharePoint - Delete file. Use Identifier from the current certificate file item.

2.5 Delete Training Records

Still inside the True branch, add SharePoint - Get items against TT TrainingRecords.

concat("PersonEmail eq '", triggerBody()?['text'], "'")

Add Apply to each over the returned records. Inside it add SharePoint - Delete item, list TT TrainingRecords, Id from the current training record.

2.6 Sever Line Manager Links

Still inside the True branch, add SharePoint - Get items against TT Personnel to find anyone whose line manager is the removed person.

concat("LineManagerEmail eq '", triggerBody()?['text'], "'")

Add Apply to each. Inside it, Update item on TT Personnel, keep required fields unchanged, and set LineManagerEmail blank. Also set LastStatus to Line manager removed, LastUpdatedByEmail to ActorEmail, and LastUpdatedOn to utcNow().

2.7 Delete The Person And Respond

1. Add SharePoint - Delete item, list TT Personnel, Id = PersonID from the trigger.
2. Add SharePoint - Create item, list TT AuditLog, event type Person permanently removed.
3. Set varSuccess to true.
4. Set varMessage to Person removed..
5. Put one Respond to a PowerApp or flow after the whole condition, not inside either branch. Return success and message.

3. Add The Power Apps Remove Button

This is a targeted edit to scrAdminManage. Do not replace the whole screen unless you want to discard your manual spacing tweaks.

3.1 Add Confirmation Input

Inside the edit-person area, add a modern text input named txtRemovePersonConfirm.

3.2 Add Remove Button

Add a modern button named btnRemovePersonPermanent.

Set btnRemovePersonPermanent.OnSelect to this. Formula bar paste: no leading equals sign.

If(
    !varShowRemovePersonConfirm,
    Set(varShowRemovePersonConfirm, true);
    Reset(txtRemovePersonConfirm);
    Notify("Type the person's email address to confirm permanent removal.", NotificationType.Warning),
    If(
        Lower(Trim(txtRemovePersonConfirm.Text)) <> Lower(varEditPerson.Email),
        Notify("Email confirmation does not match. Nothing was removed.", NotificationType.Error),
        Set(varCertBusy, true);
        Set(
            varRemovePersonResult,
            IfError(
                'TT - Remove Person'.Run(
                    varEditPerson.ID,
                    Lower(varEditPerson.Email),
                    Lower(Trim(txtRemovePersonConfirm.Text)),
                    varMyEmail,
                    varIsAdmin
                ),
                {success: false, message: "Remove flow could not be reached."}
            )
        );
        Set(varCertBusy, false);
        If(
            varRemovePersonResult.success,
            Refresh('TT Personnel');
            Refresh('TT TrainingRecords');
            Set(varEditPerson, Blank());
            Set(varShowRemovePersonConfirm, false);
            Notify(varRemovePersonResult.message, NotificationType.Success),
            Notify(varRemovePersonResult.message, NotificationType.Error)
        )
    )
)

3.3 Add Cancel Remove Button

Add a modern button named btnCancelRemovePerson.

4. Test Checklist

1. Upload or replace one certificate.
2. Open TT Certificates_LTD directly in SharePoint and confirm metadata columns show person email, course, date, uploader, and current marker.
3. Create a disposable test person with one test training record and one test certificate.
4. Run TT - Remove Person only against that test person first.
5. Confirm the person row is gone, training records are gone, certificate files are gone, and any LineManagerEmail links pointing at that email are blank.
6. Confirm TT AuditLog has a permanent-removal entry.

5. References

• Microsoft Learn: Power Fx Remove and RemoveIf functions. This build deliberately uses a flow instead of direct app-side removal because it also deletes library files and writes audit.
• Microsoft Support: delete files from a SharePoint document library.