Change Column status to Expiring

[dbrown2275]

Hi

I'm creating a training tracker as a SharePoint list. Within the list I have a date column for when a course is going to require a refresher, which is a calculated column based of Date of Training + Refresher period.

If I set up a choice column with the following options - In Date, Expiring and Expired, how can I achieve the following:

When a refresher date is 30 days away the status changes to 'Expiring'

AI gave me the solution below, however it keeps failing. Any help greatly appreciated.

Step 1: Create a New Scheduled Flow

1. Log into Power Automate at powerautomate.microsoft.com.
2. Click on Create > Scheduled flow.
3. Name the flow (e.g., "Expiring Training Flow") and set the recurrence to Daily:
   • Trigger: Recurrence.
   • Frequency: 1 day.

---

Step 2: Get Items from SharePoint

1. Add a New Step > Get Items from SharePoint.

   • Site Address: Select your SharePoint site.
   • List Name: Choose your list that contains the RefresherDate and Status columns.

2. Do not apply any filter here — you will handle this in the flow.

---

Step 3: Initialize Variables for Date Ranges

We will initialize variables to store the dates in dd-MM-yyyy format for comparison with the RefresherDate.

Variable 1: Today’s Date

1. Add a New Step > Initialize Variable.
   • Name: TodayDate
   • Type: String
   • Value:

     formatDateTime(utcNow(), 'dd-MM-yyyy')

     plaintext

     Copy code

     formatDateTime(utcNow(), 'dd-MM-yyyy')
   • This sets today’s date in dd-MM-yyyy format.

Variable 2: 30 Days from Today

1. Add another Initialize Variable.
   • Name: ExpiringStartDate
   • Type: String
   • Value:

     formatDateTime(addDays(utcNow(), 30), 'dd-MM-yyyy')

     plaintext

     Copy code

     formatDateTime(addDays(utcNow(), 30), 'dd-MM-yyyy')
   • This sets the date 30 days from today in dd-MM-yyyy format.

---

Step 4: Apply to Each (Loop through SharePoint Items)

1. Add a New Step > Apply to Each.
   • In the Select an output from previous steps, choose value from the dynamic content of the Get Items step (this will loop through each item in the list).

---

Step 5: Add Condition to Ensure RefresherDate Is Not Null

Before comparing the dates, ensure that the RefresherDate is not null.

1. Inside the Apply to Each, add a Condition:
   • First value: items('Apply_to_each')?['RefresherDate']
   • Condition: is not equal to
   • Second value: Leave empty (this checks if RefresherDate is not null).

---

Step 6: Add Condition to Check If RefresherDate is Between Today and 30 Days

Now we will add a condition to check if the RefresherDate is between today and 30 days from today.

1. Inside the If Yes branch (after checking for null), add a New Condition to compare the RefresherDate to today's date and 30 days from today.

   • Condition 1 (Check if RefresherDate is less than or equal to 30 days from today):

     • First value:

       formatDateTime(items('Apply_to_each')?['RefresherDate'], 'dd-MM-yyyy')

       plaintext

       Copy code

       formatDateTime(items('Apply_to_each')?['RefresherDate'], 'dd-MM-yyyy')
     • Condition: is less than or equal to
     • Second value: variables('ExpiringStartDate')

   • AND

   • Condition 2 (Check if RefresherDate is greater than or equal to today):

     • First value:

       formatDateTime(items('Apply_to_each')?['RefresherDate'], 'dd-MM-yyyy')

       plaintext

       Copy code

       formatDateTime(items('Apply_to_each')?['RefresherDate'], 'dd-MM-yyyy')
     • Condition: is greater than or equal to
     • Second value: variables('TodayDate')

This ensures that the RefresherDate is between today and 30 days away, in dd-MM-yyyy format.

---

Step 7: Update the Status to "Expiring"

1. In the If Yes branch (if the RefresherDate is within the range), add an Update Item action to update the status.

   • Site Address: Choose your SharePoint site.
   • List Name: Select your SharePoint list.
   • ID: Use the ID from the dynamic content in the Apply to Each loop.
   • Status: Set the Status column to "Expiring".

[#2]

@dbrown2275 you've said the flow is failing but not said what the error message is so it doesn't help us.  Also, when doing date comparisons it is usually better to use yyyy-MM-ddd format (whatever your normal date format is).

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)