Forum Discussion
Update a choice column based on date
Hey!
So I'm trying to automate my SPO List but haven't manage to get it to work. So I was wondering if there is anyone who knows how to do this.
My case:
I have a staffing list where I track the employees if they are new hires, working or terminated. So my columns are:
Name (text), Location (choice), Start date (date), End date(date) and Status (choice of new hire, working or terminated).
ideally I want the start date to steer the status column. So when the starting date is todays date I want it to change the column from new hire to working.
is that possible to do? i have been experimenting with flow but i don't get it to work.
All the best,
Torbjörn
Torbjorn_Andersson the flow to do this is as follows and in this example we will change Alison Krauss from New Hire to Working as she starts today:
The flow will run once a day at 8am:
We get the items from the list where the Status is 'New Hire'
Format today's date with a Compose and the expression formatDateTime(utcNow(),'yyyy-MM-dd')
As we've used Get items, add an apply to each and inside it add another Compose control and get the start date then format that with another Compose control and the expression formatDateTime(outputs('ComposeStart'),'yyyy-MM-dd')
Next, add a condition to check if the output of the ComposeFormatStart is equal to the ComposeToday:If it is, update the Status value to Working:
And this is the result:
Rob
Los Gallardos
Microsoft Power Automate Community Super User
Torbjorn_Andersson the flow to do this is as follows and in this example we will change Alison Krauss from New Hire to Working as she starts today:
The flow will run once a day at 8am:
We get the items from the list where the Status is 'New Hire'
Format today's date with a Compose and the expression formatDateTime(utcNow(),'yyyy-MM-dd')
As we've used Get items, add an apply to each and inside it add another Compose control and get the start date then format that with another Compose control and the expression formatDateTime(outputs('ComposeStart'),'yyyy-MM-dd')
Next, add a condition to check if the output of the ComposeFormatStart is equal to the ComposeToday:If it is, update the Status value to Working:
And this is the result:
Rob
Los Gallardos
Microsoft Power Automate Community Super UserI don't know what I do wrong, but I was trying your process but is not working for me, obviously I miss something here:
I am new using Flow, I appreciate any help!
lcdelgado from what I can see you've not got an apply to each which you need (see my earlier screenshots).
Good Day Sir,
I am trying to follow your instructions on setting up a flow to have one of my Choice Columns update based on todays date. Below are screen shots of everything I copied from your post, I would be most thankful if you can find out why I keep getting an error stating my "Status" column doesn't exist.
I particularly want my Status Column to update from 'Completed' to 'On Leave' when the LV Start Column hits today's date.
Any help on this matter would be greatly appreciated, thank you in advance!
Dotson_J check what the internal name of the column is from list settings then edit the column. In the address bar at the top the internal name is after field=
Rob
Los Gallardos
Intranet, SharePoint and Power Platform Manager (and classic 1967 Morris Traveller driver)
RobElliott Dear Rob,
I am trying this flow as well. I want status Up to date (when todays date is before end date) and status Expired (when todays date is after end date). See example below. I managed to create the flow expired, but Up to date is giving error.
I used formatDateTime(outputs('ComposeEnd'),'yyyy-MM-dd') in stead of ComposeStart. Why is it not working?
The flow works, because status changes to Up to date. But Microsoft tells me run failed, but see image, only checks.
Thank you in advance for your help.
Kind regards.
