Updating Sharepoint list item depending on todays date

[iamrufus]

Hi

I have created a powerapp that use a SharePoint list as the source. In the list i have a date column and i have also added a calculated column that looks at this date and depending if its greater or less than today it will be given either Valid or Expired. This is to do with Insurance documents.

The calculated list formula is

=IF([Insurance expiry date]>TODAY(),"Valid","Expired")

This works fine, the problem is its only fine at the point of the item creation and this field will not get checked again.

In my powerapp itself i created a gallery and used this list as the source. What is the best way to get every item added in this list to have the date checked against today to see if its valid or expired? Could a flow scheduled to run everyday at 00:01 do the trick (not used flow before)

The SharePoint list itself wont need to be looked at as everything will be done within the power app itself. The other thought i had was to remove the calculated field from the list altogether and just add a text field in the gallery that does the check.

Something like this

If(
ThisItem.'Insurance expiry date' > Today(),
"Valid",
"Expired"
)

Again this works fine but i cant filter against this as the text field doesnt exist in the SharePoint list which filters are being applied against.

Hope this makes sense, appreciate any ideas

Thank you

[Scott_Parker]

You seem to be overlooking something: Power Apps Filter() can filter your data source on the date/time field. You don't need to do an extra status determination step. It will be delegable to SharePoint. Then add on the status field in the app like you were thinking.

[iamrufus]

Thank you for this, appreciate it. Being new to powerapps could you give me an example of how the date filter would work?

Thank you

[NurSabrina24]

How about you try this, 

Add another label (in your gallery) and set the Text properties:

DateDiff(Text(Today(), "dd/mm/yyyy"), <datacardvalue name for date to calculate>.Text)

And, for the status output (visible in your gallery), set the Text properties:

If(Value(<label that makes the calculation>.Text) >= 0, "Valid", "Expired")

Hope this solves your problem.

[iamrufus]

Thanks very much for this, i will give it a try. How would i then filter on the Status field from the gallery items? I already have a filter applied that looks at one of the properties in the Sharepoint List itself but these new label fields wont exist in the sharepoint list so im not sure i could filter the result?

[NurSabrina24]

Please Accept as Solution if this solves your problem so that others can refer to this.

And yes, you can include in the gallery a label that isn't related to SP list. If you didn't want it to visible to other users, you can set the Visible properties to Off. 

Please make a new post on this if there's no post that have the similar solutions you want. And please include in the post on what do you mean by filter on the Status field from the gallery items? Do you mean you want to make a drop-down of a status choice outside of the gallery where users can choose from the dropdown for example Valid and only items with Valid status will listed in the gallery?