Delete Rows older than 7 day`s

[frixel]

I try to create a flow to remove lines where the date in the column received mail is older than 7 days.
However, things are not going well.
Flow runs very long and no lines are deleted.

[frixel]

Yes that is correct

[yashag2255]

Okay. Can you confirm on your excel sheet how many records have been deleted and how many remain (of the date and conditon 29-05-2019)

[frixel]

There-are 2 rows deleted and both with the date 29-05-2019

[yashag2255]

So the two records that satisfied the condition have been removed right? Is there anything else that you need help with?

[frixel]

Yes, all the rows older then 7 days must be deleted !

[yashag2255]

Hey! In that case, you will have to put the "greater than or equal to" in the condition

[yashag2255]

@frixel It should be "less than or equal to" for the previous records/. Sorry for the confusion

[frixel]

Hi @yashag2255 

As you can see in my previous posts I use equal to and greater is then I tried but not everything older than 7 days is deleted.
Only with exactly 7 days ago, so 29-05-2019 will be deleted

[frixel]

When i do less than it also deleted all files that are newer than 7 day`s ago.

See here it deletes also rows from 01-06-2019!!

[yashag2255]

@frixel 

I did a little more research and observed that we re doing this very wrong. Comparing dates is to be done in the similar format of dates and apparently the date coming from the excel is not properly formatted to compare with the date in flow. Can you share details on where is your date field coming from in excel?

Thanks, 

[frixel]

It comes there from an other flow with the connector “add a row”

With this rule:

@{addHours(triggerBody()?['DateTimeReceived'],2,'dd-MM-yyyy HH:mm:ss')}

[yashag2255]

HI @frixel So here is the thing, after saving the previous value in Excel using the update/create row action, this date is getting formatted somehow and is not returning properly in the next flow. Can you please make sure that the type is set to text in your column in excel sheet? We will then have a look at the flow with the extracted text values. 

Also as we need to use the UTC date connector, can you make a minor change to the format of the other flow and get the date in the yyyy-MM-dd format? 

Thanks, 

[frixel]

Hi @yashag2255 

So you see i set the receive colomn in Text.

I am not happy with the change in the date format because here in the Netherlands / Europe we use the date format dd-mm-yyyy

Is that realy needed?

why does "equal to" go well and 2 lines are removed, (okay too little because everything must be removed before 29-05-2019) And if I fill in "less then" is almost everything removed?

[yashag2255]

Hey @frixel 

We use the dd-MM-YYYY in India too. It is just for the utc conversion i need it to be that way. Rather, let us keep it that way and use some variable functions in flow to format accordingly. (So do not change that)

Follow the steps:

Use the expression: 

split(items('Apply_to_each')?['Description'],'-')

Replace "Description with the Received Date of your excel column" in the variable. 

For the compose action, use: 

concat(variables('test')[2],'-',variables('test')[1],'-',variables('test')[0],'T00:00:00')

test is the name of a variable i assigned

In the condition, the expression for the left one is : 

ticks(outputs('Compose'))

And for the right one is 

ticks(addDays(utcNow(),-7))

Rest you can follow. I tested this with a couple of dates and it works. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[frixel]

Hi @yashag2255 

What do you mean with the 'discription'?

Like this? 

split(items('Apply_to_each')?['Received Mail'],'-')

[yashag2255]

---

@frixel wrote:

Hi @yashag2255 

What do you mean with the 'discription'?

Like this? 

split(items('Apply_to_each')?['Received Mail'],'-')

---

@frixel yes. Description was my column name. 

[frixel]

Hi @yashag2255 

See this 

[frixel]

There is no difference in the processing and the same error message when I put the "Received Mail" column in both "text" or "date"

[frixel]

Hi @yashag2255 

Oké, i have made a new document with same colums but no inport from an other flow.

In this case I will go a step further but now a error by the function "ticks"

[yashag2255]

Hi @frixel 

The issue here is the data format from the Excel sheet. the tick function works only on the date time string as menstioned in the error. What you can do now is, select the entire column of your excel sheet and convert the type to text. Remove any pre existing data and populate the fields now. After this, check the data type by clicking on the cell, if it shows as text, run the flow with the modifications that we did. (Along with the tick function and comparision by splitting the array). If the flow fails, post the screenshot of the array split and compose outputs. 

Hope this Helps!