← Back to home

Transfer Preference System

Twice a year, a two-week window for department-transfer preferences buried a support inbox in emails and hand-entry. This is the rebuild: self-service intake, automated notifications, an automated seniority/eligibility lookup, and a provenance archive that holds up if the union asks questions.

2×/yrpreference windows automated
2 formscareer & non-career tracks
365-daymove-hold freeze auto-flagged
100%submissions archived for audit

How a submission actually moves

Click the intake node to see what happens when an employee submits a preference selection.
1. Preference Form Career & non-career tracks; identity + timestamp logged, resubmission allowed
→
2. Power Automate Posts alert to Support Teams chat, emails employee a receipt
→
3. Window Locks Both forms close automatically at the end of the 2-week cycle
→
4. Socket Lookup Matches submitter to current seniority + 365-day move-hold flag
→
5. Latest Wins Filtered to one row per employee — locks in final preference
→
6. Fetch Sheet Pick an ORG/DEPT, see who wants in — career + non-career
→
7. Provenance Archive SharePoint, filed by JAN YY / JUN YY, for union disputes
 preference_submit.flow — simulated output
Simulated: this page doesn't post to a real Teams channel or send real email. The mechanics (identity capture, latest-submission logic, the seniority/move-hold lookup) are real, described below.

How the eligibility lookup works

Reconstructed from the described logic — there's no source file for this one to pull real formulas from, unlike the training attendance rebuild.
' Attach current seniority by matching the M365 identity captured on submission
=XLOOKUP([@Identity], SeniorityRoster[M365 ID], SeniorityRoster[Seniority Rank], "Not found")

' Flag anyone still inside the 365-day move-hold freeze
=IF([@Identity] = "", "",
    IF(DATEDIF(XLOOKUP([@Identity], MoveLog[M365 ID], MoveLog[Last Move Date], TODAY()-366), TODAY(), "d") < 365,
       "FROZEN until " & TEXT(XLOOKUP([@Identity], MoveLog[M365 ID], MoveLog[Last Move Date], TODAY()) + 365, "mm/dd/yyyy"),
       "Eligible"))

' Keep only the latest submission per employee once the window locks
=MAXIFS(Submissions[Timestamp], Submissions[Identity], [@Identity])
' … then filter Submissions[Timestamp] = the MAXIFS result for that identity
Roster and move-hold source data are furnished fresh each cycle by the Hiring Support Specialist, so eligibility is checked against current records rather than whatever was true six months ago.

Case study

Situation → Role → Action → Result
Situation
Twice a year, career employees had a two-week window to submit department-transfer preferences by emailing support staff directly — non-career employees went through the same informal channel. The window reliably flooded the inbox with change requests, including people resubmitting to update their choice. Support had to manually work through everything to find each person's most recent submission and hand-enter it into Excel, with no proof of submission if a selection was later contested.
Role
Sole builder. Designed the intake, the automated notifications, the eligibility lookup, the downstream reporting, and the SharePoint provenance archive — and wrote the SOP documenting the process end to end.
Action
  • Built separate career and non-career Microsoft Forms capturing preference selections, M365 identity, and a timestamp on every submission — nothing overwritten, so full history is preserved.
  • Power Automate flow on submit: posts an alert to the Support Teams chat for live monitoring, and emails the employee a receipt as their proof of submission.
  • Both forms lock automatically at the close of each two-week cycle.
  • Designed a "socket" workbook the closed-form export gets pasted into, which looks up each submitter's current seniority and flags anyone inside the 365-day move-hold freeze, against a roster furnished fresh each cycle by the Hiring Support Specialist.
  • Filtered to one row per employee — latest submission wins — which locks in the final preference selection.
  • Built a dropdown fetch sheet so anyone can pick an ORG or DEPT and see career and non-career transfer interest for that unit.
  • Designed a SharePoint provenance archive (filed by JAN YY / JUN YY) backing up each cycle's original export, specifically so a union question about seniority allocation or receipt of submission can be answered with the original record.
  • Scoped and declined an additional stats layer (most-transferred-from departments) to keep the tool focused on what stakeholders actually needed.
Result
Eliminated manual email-triage during the highest-volume HR event of the year on both sides: employees get provable proof of submission, and support gets seniority and eligibility attached automatically instead of checked by hand against records that might already be stale. The system is auditable by design — if a transfer decision is ever contested, the original submission and the archived record are both retrievable, not reconstructed from memory.
Microsoft Forms Power Automate Microsoft Teams Outlook Excel (lookup formulas) SharePoint SOP Documentation