cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
freddiejoseph1
Helper IV
Helper IV

CDS/Dataverse Filter by the last 5 days

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:

 

freddiejoseph1_0-1614617208816.png

 

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.

 

freddiejoseph1_1-1614617474746.png

 

I get this error:

 

freddiejoseph1_2-1614617710887.png

 

Any help would me much appreciated.

 

Thanks,

 

Freddie

8 REPLIES 8
Expiscornovus
Super User
Super User

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
Super User
Super User

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
Super User
Super User

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.

@Expiscornovus @Paulie78 

 

Hi guys,

 

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

 

freddiejoseph1_0-1614623335846.png

 

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

 

freddiejoseph1_1-1614623435438.png

 

DamoBird365
Super User
Super User

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

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
Solution Sage
Solution Sage

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.

 

Capture.JPG

 

Thanks

DamoBird365
Super User
Super User

Hi @freddiejoseph1 

 

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

 

DamoBird365_0-1614626271139.png

 

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
 
 compose.PNG
 
I'm not sure what's up at your end but hopefully this gives you something to compare with?
 
Damien
 
 
 
 
 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,748)