Unanswered

Filter array based on two dates

(0) Share

Report

Posted on by JoelMills

I am wanting to pull a data set of feedback for an individual user. The power Automate script already goes into the spreadsheet and pulls out only the individual users data, but it is collecting ALL their feedback. So I am using an MS form to specify the start date of the feedback and the end date of the feedback requested.

How can I filter the array so that only the feedback that falls between the two inputted dates is sent to the user and not the whole data set?

Categories:

General topics

I have the same question (0)

All responses (12)

Answers (0)

CU-18081211-6 9,270 Moderator on at

Like (0)

Report
@JoelMills,

I guess that you need a filter operation with filter used in "Edit in advanced mode":

and adapt the following formula to your needs:

@and( less(ticks('FormStartDate'), ticks(item()['DateColumn'])), greater(ticks('FormEndDate'), ticks(item()['DateColumn']) )

The result should be all the items from array with DateColumn between FormStartDate and FormEndDate.

Hope it helps !

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

The problem is my Parse JSON query is picking up the date as a string even though the date format in the spreadsheet is set to date....

"Start Date": {
"type": "string"
},

And it said my expression was wrong in the filter array when I tried to apply it to the array fetched from the table.

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

Any further suggestions please?
I have tried adding this into the advance mode on a Filter Array:
@And(less(ticks('StartDate'), ticks(item()['Simple date'])), greater(ticks('EndDate'), ticks(item()['Simple date']))
but it generates an error in the flow checker...."Invalid expression"... (it keeps changing the "@ and" to @And in my post... sorry...)

"StartDate" and "EndDate" are the names of the columns in the form that the user submits to request their feedback period from and to.
"Simple date" is the column heading in the data source that I am trying to filter out results from based on those two input dates...

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

@abm @ericonline
Can you help with this please????
WHy is this so hard?
Input from the user is two dates.... StartDate & EndDate

Data set in spreadsheet has a date column which is retrieved as 2021-11-23T11:02:19.000Z

All I want to do is filter out all results that fall outside the StartDate and EndDate....

I have gone round and round in circles looking at the JSON schema, expressions in Compose items, Filter Array and I am getting no closer to a solution... Please help as I am ready to give up...!

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

Can anyone help with this please?

I was hoping the community could support me to achieve this...

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report

Show your flow.

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

The above simply returns ALL entires for the user who submits the request form...
The request form (MS Form) has two fields.. start date (when you want to get data from) and end date (When you want the data until)...

Those dates are fed into the flow and trigger it... and then the flow interrogates the data set and would only return rows with dates between those two dates.

Was this reply helpful? Yes No
JoelMills 31 on at

Like (0)

Report

Posted screenshots and what I am needing to do. Hope you can help a bit...

Was this reply helpful? Yes No
VictorIvanidze 13,073 on at

Like (0)

Report

Could you also show "Filter on email" expanded?

Was this reply helpful? Yes No