cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WDRC
Helper V
Helper V

Filter Query and CSV

All,

I have read the below:

 

https://powerusers.microsoft.com/t5/Building-Flows/Get-items-Filter-Query-Created/m-p/113917

 

My flow looks like the below.

 

Tracking.png

 

 

There are two issues:

 

I want to first filter the data to be sent in the CSV file to only contain data on the last seven days for a column entitled “Current Contact Date” so for example if we have a date in that column of 26/12/2018 that will not get selected as the date for today is 04/01/2019, but a date of 31/12/2018 would.  At this time all data in the SharePoint list gets included which is understandable as I do not have filter in place.  I have tried adding in the filter query either of the following:

 

Current Contact Date with an expression of adddays(utcnow(),-7)

 

Or

 

Current_x0020_Contact_x0020_Date with an expression of adddays(utcnow(),-7)

 

When I try to use ether of the above I get 400 error.

 

The second item is in the SharePoint list is a column entitled “source” which is a drop-down list.  When the CSV file is generated it shows in the column for that field the following:

 

{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":0,"Value":"General Data"}

 

I just want to show the “General Data" and not the rest is that possible do I need to be looking at a compose type action to strip the rest of this out before using the results of the compose in the CSV file creation. 

 

Any guidance would be appreciated.

 

Regards,

 

Andrew

2 REPLIES 2
v-yamao-msft
Community Support
Community Support

Hi @WDRC,

 

Do you want to send an email with items that are within a specific date time range?

What’s the column type of Source? Is it a Lookup column or Choice column?

I am not sure how did you input the function in the Filter query field, could you share a screenshot on it?

According to your description, I have made the following flow, please check it for a reference.

Add the button trigger. In your scenario, you could just change it to Recurrence.

Get items from SharePoint list. Input the following code in the Filter query field to filter out items that DueDate on the last seven days.

DueDate le ‘utcnow(‘yyyy-MM-dd’)’ and DueDate ge ‘formatDateTime(addDays(utcNow(),-7),'yyyy-MM-dd')’

 

Note: Please surround the functions with a single quote.

Then add the action Create SCSV table. Just set Include headers as Yes, and Columns as Custom. Then specify the headers and corresponding value.

Not sure the column type of Resource, so I added a Lookup column and a Choice column in the list. Then I selected dynamic content Lookup value and Choice value for each field.

Then send an email with the CSV table created in previous action. Please make sure enable Yes for Is HTML field.

Screenshots for your reference.

1.PNG2.PNG

Hope the following doc on Filter query be helpful:

https://docs.microsoft.com/en-us/previous-versions/dynamicsnav-2016/hh169248(v=nav.90)

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Mabel @v-yamao-msft,

 

This is very helpful, and is giveing me lot of information on this fliter query, however I am still getting a 400 error, with the following:

The expression \"Current_x0200_Contact_x0200_Date le ‘2019-January-11’ and Current_x0200_Contact_x0200_Date ge '2019-Jan-07'\" is not valid.\r\nclientRequestId: 0b951e22-c581-4ebd-a1e5-3e122d2f4365\r\nserviceRequestId: 2b12b59e-90dd-7000-7224-7585a83389c7"
Okay so you mention "duedate" in your example above.  this did not work so i changed it to
Current_x0200_Contact_x0200_Date, or
Current_Contact_Date, or
Current Contact Date
This is the column I want to filter on for the last seven days of data and everytime I got a similar 400 error.  I modified the -days to see if the error was down to not having any data and still to no avail.
The flow worked perfectly with on Tuesday 08/01/2019 @ 20:00 and the person (external) receving the data got it and was happy to have it.
But at this time the filter is not working, do you have any further suggestions please.
Regards,
 
Andrew
 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,627)