web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Best way to update Sha...
Power Automate
Answered

Best way to update SharePoint List every day and few other questios

(0) ShareShare
ReportReport
Posted on by Microsoft Employee

I have a SharePoint List (Around 4.6k records and growing) and two of the columns are Expiration Date (Date type) and Expired (Single Line of Text)

 

Now I want to schedule a daily Flow to go through all the List and check if Expiration Date < Today and change the Expired to Yes from No.

 

Is the Flow best to achieve this?

 

I think I saw somewhere that there is a limit of only 500 rows that Flow can retrieve at a time is that correct?

That might not be a big issue, as I could just filter my list to check only rows that are currently Expire = No (currently around only 300 rows have Expired = No). However, what happens if at some point in future there are more than 500 rows with Expired = No?

 

 

 

I was playing around in Flow and here is what I have:

 6.png

7.png

 8.png

 

Issues:

1. What function should I write to compare date to todays date? What I have right now is

@less(items('Apply_to_each')?['ValidUntilDate'], @today)

But I don’t think @today is valid in Flow

 

2. When I do the Update Item action, do I need to fill out each field or it will take current value that the field has if I leave it blank?

Categories:
  • ScottShearer Profile Picture
    25,331 Most Valuable Professional on at

    @Anonymous:

     

    Here are some answers to your questions:

     

    1) The SharePoint connector returns only 100 items by default.  You can configure it to return up to 5000 records.

    Here is a link to a blog post that discusses this issue.

     

    2) The expression for today's date/time is utcNow()

    3) You only need to supply values for required fields when updating a SharePoint list item.  You can reference existing values via Dynamic properties for the required fields if you do not want to change them.

    4) You are best off using a Recurrence trigger with an OData filter that will return only those items where the expiration date is less than today (Odata less than operator is lt).  Here is a link to a community post that demonstrates using a date filter when retrieving items from a SharePoint list.

     

     

    Please mark your response as Solved if your questions are answered.

     

    Scott

     

  • Verified answer
    v-yuazh-msft Profile Picture
    Microsoft Employee on at

    Hi @Anonymous,

     

    You could the current time with the expression utcNow().

     

    For your first issue:

    Please take a try with expression below in your condition:

     

    @less(formatDateTime(items('Apply_to_each')?['ValidUntilDate'], 'yyyy-MM-dd'), formatDateTime(utcNow(), 'yyyy-MM-dd'))

    For your second issue:

    Yes, you only need to fill out the must input field in the Update Item action.you don't need to fill out each field, if you leave the field blank,it will take the current value.

     

     

     

    Best regards,

    Alice

  • MarkStokes Profile Picture
    561 on at
    Out of interest,why do you need Flow?

    This can be achieved directly in SharePoint with a Calculated Column.

    Set the calculation to be =IF(ExpirationDate>=[Today],1,0] and have the field return as. Yes/No field.

    The syntax may not be 100% right but it should give you the right idea.

    Otherwise, in Flow you use and AND in your OData filter on your Get Items abs return just the items where ‘ExpirationDate eq Today AND Expired eq “No”’

    To be fair if you only run it once then you just need where ExpirationDate eq Today and set the Expired field anyway.

  • Community Power Platform Member Profile Picture
    Microsoft Employee on at

    Thanks for all the great answers!

     

    @MarkStokes, for the calculated value isn’t it evaluated only when record is created or modified? I would need to reevaluate that field each day.

     

    I am now stuck with writing my ODATA query on Get Items. Here is what I have:

    @and(
     equals(
     Expired, 
     'No'
     ),
     less(
     formatDateTime(
     ValidUntilDate,
     'yyyy-MM-dd'
     ),
     formatDateTime(
     utcNow(),
     'yyyy-MM-dd'
     )
     )
    )

     1.png

     

    However, this is failing when I try to run it.

     

    Any help appreciated. Also, could someone link me to the documentation on how to use the syntax in those fields? I can never get them right.

  • Verified answer
    MarkStokes Profile Picture
    561 on at

    @Anonymous 

     

    No, a calculated field will be evaluated at the point it is "viewed" which includes any time a query is done against the list or the item is shown on the page. You are thinking of a lookup column.

     

    Now, on to your other point.  The Filter Query uses a different syntax. It uses ODATA. This is a great starting point:

    https://flow.microsoft.com/en-us/blog/advanced-flow-of-the-week-filtering-with-odata

     

    So, in your case it might be:

    Expired eq 'No' and ValidUntilDate  lt '2018-08-21'

     

    To compare dates in SharePoint I highly recommend using a UTC formatted date.

     

    Hope this helps. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Automate

#1
Mohsin Ali Profile Picture

Mohsin Ali 330

#2
11manish Profile Picture

11manish 282 Super User 2026 Season 2

#3
Henil Profile Picture

Henil 123

Last 30 days Overall leaderboard