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

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

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (105,828)