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

[Anonymous]

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:

 

 

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?

[ScottShearer]

@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

[v-yuazh-msft · Accepted solution]

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]

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.

[Anonymous]

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'
    )
  )
)

 

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.

[MarkStokes · Accepted solution]

@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.