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

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

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
Community Champion
Community Champion

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

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
Community Champion
Community Champion

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
Community Champion
Community Champion

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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (54,322)