Course 3: Platform Training

Leave Requests — Full Structure:

| Display Name     | Internal Name   | Type                  | Required | Notes                              |
|------------------|-----------------|----------------------|----------|--------------------------------------|
| Title            | Title           | Single line of text  | No       | Built-in, can hide or repurpose      |
| Requester Name   | RequesterName   | Person               | Yes      | Add manually after import            |
| Leave Type       | LeaveType       | Choice               | Yes      | Annual, Convalescent, Emergency, Special Liberty |
| Start Date       | StartDate       | Date                 | Yes      | Date only                            |
| End Date         | EndDate         | Date                 | Yes      | Date only                            |
| Days Requested   | DaysRequested   | Calculated (Number)  | —        | Add after import: =[End Date]-[Start Date] |
| Justification    | Justification   | Multiple lines text  | Yes      |                                      |
| Status           | Status          | Choice               | Yes      | Pending, Approved, Denied, Cancelled |
| Approver         | Approver        | Person               | No       | Add manually after import            |
| Date Submitted   | DateSubmitted   | Date and Time        | No       | Default: =TODAY()                    |
| Date Actioned    | DateActioned    | Date and Time        | No       |                                      |

CSV Template (save as LeaveRequests.csv):
LeaveType,StartDate,EndDate,Justification,Status,DateSubmitted,DateActioned
Annual,2026-04-01,2026-04-05,Block leave request,Pending,2026-03-16,

Note: Person columns (RequesterName, Approver) and the Calculated column
(DaysRequested) cannot be created via CSV. Add them after import.

Post-import steps:
1. Save CSV in Notepad (UTF-8) → SharePoint → Site Contents → New → List → From CSV
2. Convert LeaveType to Choice: click column → Edit → type "Choice" → add values
3. Convert Status to Choice: same steps → add Pending, Approved, Denied, Cancelled → default "Pending"
4. Add Person column "RequesterName": Create column → Person or Group → Required: Yes
5. Add Person column "Approver": Create column → Person or Group → Required: No
6. Add Calculated column "DaysRequested": Create column → Calculated →
   Formula: =[End Date]-[Start Date] → Return type: Number
7. Set DateSubmitted default to =TODAY() in column settings
8. Click Integrate → Power Apps → Create an app
                                

The auto-generated app already has an EditForm connected to your Leave Requests list
with all fields wired up. You just need to customize these specific parts:

1. AUTO-POPULATE REQUESTER NAME
In the EditForm, find the RequesterName data card. Set the Default of its text input:
// DataCardValue_RequesterName.Default
User().FullName

Then lock it so users can't change it:
// DataCardValue_RequesterName.DisplayMode
DisplayMode.View

2. VALIDATION — ADD TO THE SAVE BUTTON
Find the save button (usually the checkmark icon). Replace its OnSelect with:
// IconAccept1.OnSelect
If(
    DataCardValue_EndDate.SelectedDate <= DataCardValue_StartDate.SelectedDate,
    Notify("End Date must be after Start Date", NotificationType.Error),
    If(
        Len(DataCardValue_Justification.Text) < 20,
        Notify("Justification must be at least 20 characters", NotificationType.Error),
        SubmitForm(EditForm1);
        Notify("Leave request submitted successfully", NotificationType.Success);
        Navigate(BrowseScreen1, ScreenTransition.None)
    )
)

Note: SubmitForm handles all the data writing — it reads every data card
and patches the SharePoint list for you. You do NOT need a manual Patch()
call when using an EditForm. The auto-generated form already handles
Person fields, Choice fields, and date fields correctly.

3. SET STATUS DEFAULT TO "PENDING"
In the Status data card, set the Default:
// DataCardValue_Status.DefaultSelectedItems
{Value: "Pending"}

4. SET DATE SUBMITTED DEFAULT TO TODAY
// DataCardValue_DateSubmitted.DefaultDate
Today()
                                

Your trigger is already wired. Add these actions after it:

Step 1: Initialize variable "ApproverEmail"
- Name: ApproverEmail
- Type: String
- Value: (leave blank)

Step 2: Condition — Check days requested
- If: [Dynamic Content: Days Requested] is less than or equal to 3
  - Yes: Set variable ApproverEmail to "sectionlead@1bn99marines.mil"
  - No: (add nested condition below)

Step 3: (Inside the No branch) Condition — Check if 4-10 days
- If: [Dynamic Content: Days Requested] is less than or equal to 10
  - Yes: Set variable ApproverEmail to "co@1bn99marines.mil"
  - No: Set variable ApproverEmail to "bn-co@1bn99marines.mil"

Step 4: Update item (SharePoint)
- Site Address: [your site URL]
- List Name: Leave Requests
- Id: [Dynamic Content: ID]
- Approver Claims: [Expression: concat('i:0#.f|membership|', variables('ApproverEmail'))]

Step 5: Send an email (V2) (Office 365 Outlook)
- To: [Expression: variables('ApproverEmail')]
- Subject: New Leave Request Pending Your Approval
- Body:
  A new leave request requires your approval:

  Requester: [Dynamic Content: Requester Name DisplayName]
  Leave Type: [Dynamic Content: Leave Type Value]
  Dates: [Dynamic Content: Start Date] to [Dynamic Content: End Date]
  Days: [Dynamic Content: Days Requested]
  Justification: [Dynamic Content: Justification]

  [Dynamic Content: Link to item]
                                

Modify your existing flow. Replace Action 5 (Send an email) with an approval action:

Step 5: Start and wait for an approval (Approvals)
- Approval type: Approve/Reject - First to respond
- Title: Leave Request Approval Needed
- Assigned to: [Expression: variables('ApproverEmail')]
- Details:
  Requester: [Dynamic Content: Requester Name DisplayName]
  Leave Type: [Dynamic Content: Leave Type Value]
  Dates: [Dynamic Content: Start Date] to [Dynamic Content: End Date]
  Days: [Dynamic Content: Days Requested]
  Justification: [Dynamic Content: Justification]
- Item link: [Dynamic Content: Link to item]
- Item link description: View in SharePoint
- IMPORTANT: Open Settings on this action → set Timeout to P2D (2 days)

Step 6: Condition — check approval outcome
- [Dynamic Content: Outcome] is equal to "Approve"

  Yes branch:
  Step 6a: Update item (SharePoint)
  - Id: [Dynamic Content: ID]
  - Status Value: Approved
  - DateActioned: [Expression: utcNow()]

  No branch:
  Step 6b: Update item (SharePoint)
  - Id: [Dynamic Content: ID]
  - Status Value: Denied
  - DateActioned: [Expression: utcNow()]

To include approver comments (optional — add ApproverComments column to list first):
Add to both Update item actions:
- ApproverComments: [Dynamic Content: Responses Comments]
                                

Add a notification email inside each branch of the Condition (Step 6):

In the Yes (Approve) branch, after Update item:
Step 7a: Send an email (V2) (Office 365 Outlook)
- To: [Dynamic Content: Requester Name Email]
- Subject: Your Leave Request Has Been Approved
- Body:
  Your leave request has been APPROVED.

  Request Details:
  Leave Type: [Dynamic Content: Leave Type Value]
  Dates: [Dynamic Content: Start Date] to [Dynamic Content: End Date]
  Days: [Dynamic Content: Days Requested]

  Approved by: [Dynamic Content: Responses Approver Name]
  Comments: [Dynamic Content: Responses Comments]

  Your leave is now authorized. Complete any required checkout procedures.

In the No (Reject) branch, after Update item:
Step 7b: Send an email (V2) (Office 365 Outlook)
- To: [Dynamic Content: Requester Name Email]
- Subject: Your Leave Request Has Been Denied
- Body:
  Your leave request has been DENIED.

  Request Details:
  Leave Type: [Dynamic Content: Leave Type Value]
  Dates: [Dynamic Content: Start Date] to [Dynamic Content: End Date]
  Days: [Dynamic Content: Days Requested]

  Denied by: [Dynamic Content: Responses Approver Name]
  Comments: [Dynamic Content: Responses Comments]

  Contact your approving authority if you have questions.
                                

Three additions to the flow:

1. TRY/CATCH ERROR HANDLING:
   - Select all actions (Steps 1 through 7b) → click "Add a scope" → name it "Try"
   - After the Try scope, add a new Scope → name it "Catch"
   - Click "..." on the Catch scope → Configure run after → check ONLY "has failed" and "has timed out"
   - Inside the Catch scope, add:
     Send an email (V2) (Office 365 Outlook)
     - To: admin@1bn99marines.mil
     - Subject: Leave Request Flow Failure
     - Body:
       The leave request workflow failed.
       Requester: [Dynamic Content: Requester Name DisplayName]
       Item ID: [Dynamic Content: ID]
       Review the flow run history:
       [Expression: concat('https://high.flow.microsoft.us/manage/environments/', workflow()?['tags']?['environmentName'], '/flows/', workflow()?['name'], '/runs/', workflow()?['run']?['name'])]

2. APPROVAL TIMEOUT (do NOT use a Delay action):
   - Go back to Step 5 (Start and wait for an approval)
   - Open its Settings tab
   - Set Timeout to: P2D (2 days = 48 hours)
   - After the timeout expires, the flow continues to Step 6 (Condition)
   - The Outcome will be empty/timed out — add a third branch:
     Step 6c: If Outcome is empty (timed out):
     - Send reminder email to approver: [Expression: variables('ApproverEmail')]
     - Update Status to "Escalated" or re-route to default approver

   Do NOT add a separate Delay action for timeouts. The approval action's
   built-in Timeout property handles this correctly. A separate Delay would
   run AFTER the approval completes, not in parallel with it.

3. INVALID APPROVER FALLBACK:
   - In Steps 2-3 (routing conditions), add a final Else branch
   - Set variable ApproverEmail to "co@1bn99marines.mil" (default)
   - Add: Send an email (V2) to admin@1bn99marines.mil
     Subject: Leave Request Routing Fallback
     Body: Could not determine approver for [Dynamic Content: Title].
           Defaulted to CO. Days requested: [Dynamic Content: Days Requested].
                                

Training Tracker — Structure:

| Display Name    | Internal Name   | Type              | Required | Notes                       |
|-----------------|-----------------|-------------------|----------|-----------------------------|
| Marine Name     | MarineName      | Person            | Yes      | Add manually after import   |
| Training Event  | TrainingEvent   | Choice            | Yes      | See values below            |
| Completion Date | CompletionDate  | Date              | No       | Date only                   |
| Status          | Status          | Choice            | Yes      | Complete, Overdue, Scheduled|
| Due Date        | DueDate         | Date              | Yes      | Date only                   |
| Days Until Due  | DaysUntilDue    | Calculated        | —        | Add after import            |

CSV Template (save as TrainingTracker.csv):
TrainingEvent,CompletionDate,Status,DueDate
Annual Rifle Qual,,Scheduled,2026-06-15

Post-import steps:
1. Convert TrainingEvent to Choice (values: Annual Rifle Qual, CBRN Awareness, Cyber Awareness, PFT/CFT, SAPR Training)
2. Convert Status to Choice (values: Complete, Overdue, Scheduled)
3. Add Person column "MarineName": Create column → Person or Group → Required
4. Add Calculated column "DaysUntilDue": =[Due Date]-TODAY(), return type: Number
   (Note: this only recalculates when the item is edited, not daily)
5. Integrate → Power Apps → Create an app

This structure creates one row per training event per Marine.
                                

Restructure as one row per Marine:

| Display Name         | Internal Name       | Type    | Required | Notes                                  |
|----------------------|---------------------|---------|----------|----------------------------------------|
| Marine Name          | MarineName          | Person  | Yes      | Add manually after import              |
| Rank                 | Rank                | Choice  | Yes      | PFC, LCpl, Cpl, Sgt, SSgt             |
| Annual Rifle Qual    | AnnualRifleQual     | Choice  | No       | Complete, Overdue, Scheduled, Not Started |
| Annual Rifle Qual Date | AnnualRifleQualDate | Date  | No       |                                        |
| CBRN Awareness       | CBRNAwareness       | Choice  | No       | Same values as above                   |
| CBRN Awareness Date  | CBRNAwarenessDate   | Date    | No       |                                        |
| Cyber Awareness      | CyberAwareness      | Choice  | No       | Same values                            |
| Cyber Awareness Date | CyberAwarenessDate  | Date    | No       |                                        |
| PFT/CFT              | PFTCFT              | Choice  | No       | Same values                            |
| PFT/CFT Date         | PFTCFTDate          | Date    | No       |                                        |
| SAPR Training        | SAPRTraining        | Choice  | No       | Same values                            |
| SAPR Training Date   | SAPRTrainingDate    | Date    | No       |                                        |

CSV Template (save as TrainingDashboard.csv):
Rank,AnnualRifleQual,AnnualRifleQualDate,CBRNAwareness,CBRNAwarenessDate,CyberAwareness,CyberAwarenessDate,PFTCFT,PFTCFTDate,SAPRTraining,SAPRTrainingDate
Cpl,Complete,2026-01-15,Overdue,,Scheduled,2026-04-01,Complete,2025-12-01,Not Started,

Post-import steps:
1. Convert all training status columns to Choice (add values: Complete, Overdue, Scheduled, Not Started)
2. Add Rank as Choice (PFC, LCpl, Cpl, Sgt, SSgt)
3. Add Person column "MarineName" manually
4. Delete the old Training Tracker list
5. Integrate → Power Apps → Create an app

One row per Marine, all five training events visible at a glance. Trade-off: less
flexible if you add new training events later (you'd need to add two new columns).
                                

Create a Power BI report connected to your SharePoint list:

1. Open Power BI Desktop
2. Get Data > SharePoint Online List
3. Enter your SharePoint site URL
4. Select "Training Status Dashboard" list
5. Click Load

Create the table visualization:

1. Add a Table visual to the canvas
2. Add these fields to the table:
   - Marine Name
   - Rank
   - Annual Rifle Qual
   - CBRN Awareness
   - Cyber Awareness
   - PFT/CFT
   - SAPR Training

3. Add conditional formatting:
   - Select the "Annual Rifle Qual" column
   - Click Format > Conditional Formatting > Background Color
   - Set rules:
     - If value = "Complete", background = Green
     - If value = "Overdue", background = Red
     - If value = "Scheduled", background = Yellow

4. Repeat conditional formatting for all five training columns

5. Add a Card visual showing total number of Marines

6. Add a Pie Chart showing percentage Complete vs. Overdue vs. Scheduled across all training

Your dashboard is now ready to publish to Power BI Service and share with leadership.
                                

You need to use "Field value" formatting, not "Rules." Here's the correct procedure:

1. In your Table visual, click on the column header for "Annual Rifle Qual"
2. In the Format pane, expand "Conditional formatting"
3. Click "Background color"
4. In the dialog:
   - Format style: Field value
   - Based on field: Annual Rifle Qual (should auto-select)
   - Click "Advanced controls"
   - For each value, set the color:
     - Complete: Green (#00B050)
     - Overdue: Red (#FF0000)
     - Scheduled: Yellow (#FFFF00)
     - Not Started: Gray (#D3D3D3)
5. Click OK

Repeat this process for each of the five training event columns.

Note: You must do this for each column individually. There's no way to apply the same
conditional formatting rule to multiple columns at once in Power BI Desktop.

For the summary metrics, instead of a pie chart, create:
- A Card showing "Total Complete" (count where any status = Complete)
- A Card showing "Total Overdue" (count where any status = Overdue)
- A Stacked Bar Chart showing count of each status across all five training events

This gives leadership a clearer picture of overall training readiness.
                                

You'll need to create calculated columns in Power BI for each training event.

In Power BI Desktop, go to the Data view (left sidebar, table icon).

Create a new column for "Rifle Qual Days Until Due":

Rifle Qual Days Until Due =
VAR CompletionDate = 'Training Status Dashboard'[Annual Rifle Qual Date]
VAR DueDate = DATE(YEAR(CompletionDate) + 1, MONTH(CompletionDate), DAY(CompletionDate))
VAR DaysUntil = DueDate - TODAY()
RETURN
IF(
    ISBLANK(CompletionDate),
    BLANK(),
    IF(
        DaysUntil < 0,
        "Overdue by " & ABS(DaysUntil) & " days",
        "Due in " & DaysUntil & " days"
    )
)

Repeat this formula for each training event, changing the column names:
- CBRN Awareness Days Until Due
- Cyber Awareness Days Until Due
- PFT/CFT Days Until Due
- SAPR Training Days Until Due

Add these new calculated columns to your Table visual to show leadership exactly how
many days remain for each training requirement.
                                

Create a Scheduled Cloud Flow in Power Automate:

Step 1: [Trigger] Recurrence
- Interval: 1 week
- Day: Monday
- Time: 08:00

Step 2: Get items (SharePoint)
- Site: [your site URL]
- List: Training Status Dashboard
- Top Count: 5000 (IMPORTANT — default is 100, will miss Marines)

Step 3: Initialize variable "TotalEvents" (Integer, value: 0)
Step 4: Initialize variable "CompleteEvents" (Integer, value: 0)
Step 5: Initialize variable "OverdueItems" (String, value: "")
Step 6: Initialize variable "ScheduledItems" (String, value: "")

Step 7: Apply to each
- Input: [Dynamic Content: value] from Get items
- Settings → Concurrency: 1 (keep sequential — we're incrementing counters)

Inside the loop, for EACH of the 5 training columns:
  Condition: [Dynamic Content: Annual Rifle Qual Value] equals "Complete"
    Yes: Increment variable CompleteEvents by 1
    No: Condition: equals "Overdue"
      Yes: Append to string variable OverdueItems:
           [Expression: concat(items('Apply_to_each')?['MarineName']?['DisplayName'], ' - Annual Rifle Qual', '\n')]
  (Repeat for CBRN, Cyber, PFT/CFT, SAPR — 5 conditions total)
  Increment variable TotalEvents by 5

Step 8: Compose — calculate readiness percentage
[Expression: mul(div(float(variables('CompleteEvents')), float(variables('TotalEvents'))), 100)]
Note: float() prevents integer division from returning 0.

Step 9: Send an email (V2) (Office 365 Outlook)
- To: s3@1bn99marines.mil
- Subject: [Expression: concat('Weekly Training Readiness - ', formatDateTime(utcNow(), 'dd MMM yyyy'))]
- Body:
  Battalion Training Readiness: [Dynamic Content: Outputs from Step 8]%
  across 5 tracked events.

  CRITICAL OVERDUE (>7 days):
  [Expression: if(empty(variables('OverdueItems')), 'None', variables('OverdueItems'))]

  UPCOMING SCHEDULED:
  [Expression: if(empty(variables('ScheduledItems')), 'None', variables('ScheduledItems'))]