# Complete Implementation Guide: M365 Automated Associate Transfer Workflow

This guide walks you through setting up a **centralized, zero-duplicate, automated associate transfer process** using standard Microsoft 365 tools: **SharePoint Lists, Microsoft Forms, Excel VBA, and Power Automate**.

---

## Architecture Overview

```text
[ Excel Macro ] --(Click Button: Save to Cloud)--> [ SharePoint Folder ]
                                                           │
                                                (Power Automate Flow 1)
                                                           │
                                                           ▼
                                                [ SharePoint List ] 
                                           (Status: PENDING | Unique URL)
                                                           │
                                                (Email Sent to Supervisor)
                                                           │
                                                           ▼
                                                [ Microsoft Form ]
                                               (Supervisor Completes)
                                                           │
                                                (Power Automate Flow 2)
                                                           │
                                                           ▼
                                                [ SharePoint List ]
                                              (Status: COMPLETED)
```

---

## Step 1: Create the SharePoint List (Central Dashboard)

This list acts as your live dashboard. Everyone sees the same data in real time.

1. Go to your team’s **SharePoint Site**.
2. Click **+ New** > **List** > **Blank list**. Name it **`Associate Transfers`**.
3. Create the following columns:

| Column Name | Type | Notes |
| :--- | :--- | :--- |
| **Title** | Single line of text | Rename standard "Title" to **Associate Name** |
| **Old Department** | Single line of text | |
| **New Department** | Single line of text | |
| **Meets HR Criteria** | Single line of text | YES or NO |
| **Supervisor Email** | Person or Group (or Text) | Email of supervisor who needs to take action |
| **Status** | Choice | Choices: `Pending`, `Completed` (Default: `Pending`) |
| **Completed By** | Person or Group (or Text) | Leave blank initially |
| **Completion Date** | Date and Time | Leave blank initially |
| **Form Link** | Hyperlink | Holds the unique link for the supervisor |

---

## Step 2: Create the Microsoft Form

1. Go to **[forms.office.com](https://forms.office.com)** and create a **New Form** named **`Associate Transfer Evaluation`**.
2. Add your questions (including conditional logic/branching if needed).
3. **CRITICAL STEP (Pre-filling / Tracking):** 
   * Add a Short Text Question at the top: **`SharePoint Record ID`** (Supervisors won't fill this; we will pass it in the URL).
4. **Get the Pre-filled URL**:
   * Click the **`...` (More options)** menu in the top right of the Form editor.
   * Select **Get pre-filled URL**.
   * Type `[RECORD_ID]` in the **SharePoint Record ID** answer field.
   * Click **Get pre-filled link** and copy it. It will look something like this:
     `https://forms.office.com/r/xyz123?r...&f...=[RECORD_ID]`

---

## Step 3: Update Your Excel VBA Macro

Modify your existing macro to:
1. Ensure data is formatted into an **Excel Table** named `TransferData`.
2. Automatically save a copy to your locally synced SharePoint/OneDrive folder.

Add/adapt this code in your Excel VBA Module:

```vba
Sub ExportToSharePointFolder()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim savePath As String
    Dim fileName As String
    
    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("TransferSheet") ' Adjust to your sheet name
    
    ' 1. Ensure data is formatted as a Table (Power Automate requires an Excel Table)
    On Error Resume Next
    ws.ListObjects.Add(xlSrcRange, ws.UsedRange, , xlYes).Name = "TransferData"
    On Error GoTo 0
    
    ' 2. Define SharePoint/OneDrive Synced Local Path
    ' (Path to the local synced SharePoint folder on your machine)
    savePath = "C:\Users\" & Environ("USERNAME") & "\YourCompany\SharePointSite - TransferFiles\"
    fileName = "TransferData_" & Format(Now(), "YYYYMMDD_HHMMSS") & ".xlsx"
    
    ' 3. Save Copy
    wb.SaveCopyAs savePath & fileName
    
    MsgBox "Data successfully exported! Power Automate is processing the transfers.", vbInformation, "Success"
End Sub
```

---

## Step 4: Create Flow 1 (Import Excel & Notify Supervisors)

This flow triggers automatically when the VBA macro saves the file into SharePoint.

1. Go to **[make.powerautomate.com](https://make.powerautomate.com)**.
2. Click **+ Create** > **Automated cloud flow**.
3. **Trigger:** Search for **SharePoint - When a file is created (properties only)**.
   * **Site Address:** Select your SharePoint site.
   * **Library Name:** Select the document library where the macro saves the file.
4. **Add Action:** **Excel Online (Business) - List rows present in a table**.
   * **Location:** Select SharePoint Site.
   * **Document Library:** Select your library.
   * **File:** Select `Identifier` from the Trigger dynamic content.
   * **Table:** Select `TransferData`.
5. **Add Action:** **Apply to each** (Select `value` from the previous Excel step).
   * Inside the loop, add **SharePoint - Create item**:
     * **Site Address:** Your site.
     * **List Name:** `Associate Transfers`.
     * **Title (Associate Name):** `Combine/Select Name column from Excel`.
     * **Old Department:** `Old Dept column from Excel`.
     * **New Department:** `New Dept column from Excel`.
     * **Meets HR Criteria:** `Criteria column from Excel`.
     * **Supervisor Email:** `Supervisor Email column from Excel`.
     * **Status:** `Pending`.
   * Add Action inside loop: **SharePoint - Update item**:
     * Generate the **Form Link** using expression:
       `concat('YOUR_PREFILLED_FORM_URL_HERE', outputs('Create_item')?['body/ID'])`
   * Add Action inside loop: **Office 365 Outlook - Send an email (V2)**:
     * **To:** `Supervisor Email` from Excel.
     * **Subject:** `Action Required: Transfer Review for [Associate Name]`
     * **Body:** 
       > Hello Supervisor,
       > 
       > Please review the position switch for **[Associate Name]**.
       > 
       > Click the link below to complete the response:
       > **[Form Link]**
       > 
       > *Note: Please check the dashboard to ensure this action is still pending before submitting.*

---

## Step 5: Create Flow 2 (Update Status on Form Submission)

This flow runs whenever a supervisor fills out the Form and marks the SharePoint item as **COMPLETED**.

1. Create a new **Automated cloud flow**.
2. **Trigger:** **Microsoft Forms - When a new response is submitted**.
   * Select your **Associate Transfer Evaluation** Form.
3. **Add Action:** **Microsoft Forms - Get response details**.
   * **Form ID:** Select your Form.
   * **Response ID:** Select `Response ID` from Trigger.
4. **Add Action:** **SharePoint - Update item**.
   * **Site Address:** Your SharePoint site.
   * **List Name:** `Associate Transfers`.
   * **ID:** Select the answer from the `SharePoint Record ID` form response field (convert to integer if required: `int(outputs('Get_response_details')?['body/r...'])`).
   * **Status Value:** Select `Completed`.
   * **Completed By:** Select `Responder's Email` from Form dynamic content.
   * **Completion Date:** Select `Submission time` from Form dynamic content.

---

## Step 6: Set Up SharePoint Views (Preventing Duplicates)

To ensure supervisors only see what needs work:

1. Open your **SharePoint List**.
2. Click **View options** (top right of the list) > **Save view as** > Name it **`Pending Transfers`**.
3. Click **Filter current view** > Set filter: **`Status` is equal to `Pending`**.
4. Create a second view named **`Completed Transfers`** with filter **`Status` is equal to `Completed`**.
5. Use **Conditional Formatting** on the `Status` column:
   * `Pending` = **Soft Yellow / Orange**
   * `Completed` = **Green**

---

## How the Solution Solves Your Original Problems

1. **No Duplicates:** Supervisors open a single live link. If another supervisor completed the response 10 seconds ago, the status will show **COMPLETED** (or disappear from the "Pending" view).
2. **Clear Progress Tracking:** HR can view the SharePoint List at any time and see exact percentages: e.g., 15 Pending, 35 Completed.
3. **No Paid Licenses:** Uses only standard built-in triggers and actions included in Microsoft 365.
4. **Familiar 1-Click Workflow:** You still just click a button in your Excel sheet to launch the entire automated pipeline.