CDS/Dataverse Filter by the last 5 days

[freddiejoseph1]

Hi all,

I have a powerapp in MS Teams and I collect info about work that agents are carrying out. One of the fields being collected is the date.

Every Friday at 5.30pm I want to flow the weeks job tickets from the CDS table to a sharepoint list which has the same column names. My flow currently looks like this:

This works fine if I want to upload all the tickets that have ever been created into sharepoint. How can I get it to only upload ones from the last 5 days though? So on Friday I want to upload all tickets from Monday, Tuesday, Wednesday, Thursday and Friday for that week.

I have tried using a filter rows of: crb24_date1_1 le '@{addDays(utcNow(),-5)}' but that didn't work here.

I get this error:

Any help would me much appreciated.

Thanks,

Freddie

[Expiscornovus]

Hi @freddiejoseph1,

Just to double check. Is your custom column crb24_date1_1 a Date and Time Date type or is it a Text data type?

[DamoBird365]

Hi @freddiejoseph1 ,

You probably need to format the datetime.

formatdatetime(addDays(utcNow(),-5),'yyyy-MM-dd')

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here

[Paulie78]

I'd say you want a greater than also there, not a less than, otherwise you're going to get everything from before the day the flow runs, minus 5 days. Instead of everything in the 5 days leading up to the flow run.

[freddiejoseph1]

@Expiscornovus @Paulie78 

Hi guys,

Thanks for your replies. I have tried your suggestions and I now get this error:

And that error is having tried both GE and LE with the filter rows: crb24_date1_1 ge/le formatdatetime(addDays(utcNow(),-5),'yyyy-MM-dd'). 

My column crb24_date1_1 is a date only column

[DamoBird365]

If it was me, I would introduce a couple of compose actions for 

crb24_date1_1 

and 

formatdatetime(addDays(utcNow(),-5),'yyyy-MM-dd')

after the re-occurrence to see what you are playing with and then check via the history.

Damien

[freddiejoseph1]

I am not too sure I understand. If I introduce 2 composes for those the output is the same as the input once I test the flow

[ManishJain]

Hi @freddiejoseph1 ,

Since you are using List Records action from CDS there is a option to use Fetch XML . Just go to CRM and do advanced find in there . Download the fetch xml and use the same xml in the Fetch XML parameter of List records.

Thanks

[DamoBird365]

Hi @freddiejoseph1 

I've created same at my end and I don't get the same errors as you so I am not sure.

I ended up using

crc93_date le '@{formatdatetime(addDays(utcNow(),5),'MM/dd/yyyy')}'

through trial and error.

First off I tried

crc93_date le '10/03/2021'

then

crc93_date le '03/10/2021' and only then did I get the results I expected

When I run list rows without the filter, you can retrieve the crc93_date using a compose like so:

outputs('List_rows')?['body']?['value']?[0]?['crc93_date']

The format is yyyy-MM-dd for the date I inserted into my test CDS table

 

I'm not sure what's up at your end but hopefully this gives you something to compare with?

Damien