I am trying to build a flow that automatically generates a Project Request ID for a Sharepoint list I have titled "2025List"
I want the flow to populate the list with this Prefix: PR2025-001. I want it to generate the next number sequentially after an item is created, so the next one in my example would be PR2025-002, then PR2025-003 and so on. I need the sequential number to reset at the start of the year. So in 2026, the first project request ID would be PR2026-001, the PR2026-002, and so on.
I've tried the following process, but I keep getting invalid errors for some of the expressions.
Step-by-Step Guide to Create the Power Automate Flow**
Step 1: Create the Flow
1. Go to **Power Automate** → Click **Create** → Choose **Automated cloud flow**.
2. **Flow Name:** `Generate Project ID for 2025List` 3. Select **"When an item is created" (SharePoint trigger)** → Click **Create**.
---
Step 2: Configure the Trigger** 1. **Site Address:** Select your SharePoint site.
2. **List Name:** Select `2025List`.
---
Step 3: Retrieve Last Used Number** 1. **Add "Get items" action** (SharePoint → "Get items").
- **Site Address:** Select your SharePoint site.
- **List Name:** Select `ProjectIDTracker`.
- **Filter Query:**
```
Title eq 'YYYY'
```
- Replace `'YYYY'` with the **dynamic content** `utcNow('yyyy')`.
- This ensures we only retrieve the last used number for the **current year**.
---
Step 4: Add a Condition to Check if the Year Exists** 1. **Add "Condition" action**:
- **Expression in the left field:**
```
length(body('Get_items')?['value'])
```
- **Operator:** `is greater than`
- **Right field:** `0`
This checks if there’s already an entry for the current year in `ProjectIDTracker`.
---
Step 5: YES Branch (Year Exists – Continue Numbering)**
**If YES (an entry for the year exists):** 1. **Add "Set Variable" action**
- **Name:** `NextNumber`
- **Type:** Integer
- **Value:**
```plaintext
int(body('Get_items')?['value'][0]['LastUsedNumber']) + 1
```
- This retrieves the last used number and increments it.
2. **Add "Update item" action** (Update `ProjectIDTracker`)
- **Site Address:** Your SharePoint site.
- **List Name:** `ProjectIDTracker`.
- **ID:**
```plaintext
body('Get_items')?['value'][0]['ID']
```
- **Title:** `utcNow('yyyy')`
- **LastUsedNumber:** `NextNumber`
---
Step 6: NO Branch (New Year – Reset to 1)**
**If NO (it's a new year, reset to 1):** 1. **Add "Set Variable" action**
- **Name:** `NextNumber`
- **Type:** Integer
- **Value:** `1`
2. **Add "Create item" action** (Create new entry in `ProjectIDTracker`)
- **Site Address:** Your SharePoint site.
- **List Name:** `ProjectIDTracker`.
- **Title:** `utcNow('yyyy')`
- **LastUsedNumber:** `1`
---
Step 7: Format the Project ID** 1. **Add "Compose" action**
- **Name:** Format Project ID
- **Expression:**
```plaintext
concat('PR', utcNow('yyyy'), '-', formatNumber(variables('NextNumber'), '000'))
```
- **Example Output:** `PR2025-001`
---
Step 8: Update the Newly Created Item in 2025List** 1. **Add "Update item" action**
- **Site Address:** Your SharePoint site.
- **List Name:** `2025List`.
- **ID:** Use **ID** from the **trigger ("When an item is created")**.
- **Project Request ID:** Use the output from **"Compose - Format Project ID"**.
