How to auto delete past records patched in sharepoint list?

[MAYURIV]

Hello,

We are patching responses in sharepoint list.

In items property of gallery, we have mentioned below code in powerapps.

Filter(
Sort('AOA_FT_Response abnormal value_previous',ID,Ascending),
Start_Time >= DateAdd(
Today(),
-1,
Days
)
)

Our requirement is to filter sharepoint list based on previous date. This we could achieve by above code and it is working fine in the powerapp screen. However when we check the same sharepoint list in .iqy file, we are able to see all date logs.

By This code, everyday we are able to see previous day logged data but with other days history also.

If I am seeing the sharepoint list today, I could see yesterday's history which is the ultimate goal. If I see the sharepoint list tomorrow, I am able to see the history of today and yesterday which I dont want. Basically I want only last 24 hours data. I dont want data of day before yesterday.

Please help.

[Mike2500]

If you're concerned about people viewing the sharepoint list itself, you could change the default view to have a filter so users would only see the data for the last day. If you don't need the data at all and want to delete it, you could create a flow that runs on a schedule that queries the list for any items older than a day and deletes them. 

Here is a post that shows how to delete items older than 21 days. That post shows an example with a manual trigger, you'd just need a scheduled trigger.

[MAYURIV]

Hi,

Our organization O365 account only supports powerapps and sharepoint. The solution which you are suggesting will need Power automate. Please give us solution in powerapps only.

[MAYURIV]

Please note that Start_Time column in sharepoint list consist of date & time as well.

[MAYURIV]

Hello,

We tried your solution in Power automate by writing below function.

Start_Time le fx addDays(utcNow(),-1,'dd/mm/yyyy hh:mm')

Start_Time is the column field in sharepoint list which holds date time format.

The main objective is to delete records less than 1 day.

We tried this code in flow but we are getting error

{"odata.error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"Start_Time le03/25/2022 05:25 \" is not valid."}}}

Please help us resolve this issue.

Regards,

Mayuri

[Mike2500]

Please note the single quotes in the linked post. It should be:

Start_Time le fx 'addDays(utcNow(),-1,'dd/mm/yyyy hh:mm')'

(The bolded section is done in the expression editor, so the syntax will actually work with the double set of single quotes). 

But, to get the time, we need this format:

2022-04-04T23:59:00.000Z

To achieve this, please try the following as the expression:

formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:mm:ssZ')

Also, if you only want items from the last 24 hours, it would need to be gt, not lt. so:

Start_Time gt 'formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:mm:ssZ')'

[MAYURIV]

I am getting error as below when I tried with expression 

formatDateTime(addDays(utcNow(),-1),'yyyy-MM-ddTHH:mm:ssZ')

{"odata.error":{"code":"-1, Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":"en-US","value":"The expression \"Start_Time gt 2022-04-04T04:01:41Z \" is not valid."}}}

[Mike2500]

You're missing the quotes.

Your error message has Start_Time gt 2022-04-04T04:01:41Z \" is not valid

But, the resulting expression should be:

Start_Time gt '2022-04-04T04:01:41Z'

[MAYURIV]

Thank you so much for the quick reply. It helped me execute my code.

Regards,

Mayuri Vichare

[SUPRIYAK]

Hello Sir, 

I & Mayuri are working on the same project.

As per your reply, we had successfully created a flow for deleting data older than 30 days.

The flow was running successfully for the last 5 days but today it has failed. After checking the log, I found this below error statement:

String was not recognized as a valid DateTime. Creating query failed.
clientRequestId: 53bb8ec2-2e1e-44f1-a9f0-de92e395c17c
serviceRequestId: 53bb8ec2-2e1e-44f1-a9f0-de92e395c17c

Whereas we have not done any changes to the Sharepoint list.

Kindly help.