Hello,
Hoping someone is able to help!
I want to get any rows that have a date from the past 14 days, so I'm using the 'List rows present in a table', then using the filter query to retrieve the rows I want. However I'm not able to get the flow to work, I'm guessing there is something obvious I'm doing wrong but still a beginner!
I've used this exact filter query for 'get items' in a sharepoint list and it has worked, but I'm guessing I can't just write 'Date of Incident' and assume that will know which column to lookup to in my excel sheet. or should it? Is there a way I should get the dynamic content to pull through a step earlier so that I can use that for 'Date of Incident'... i'm not sure!
Anyway, if anyone would be able to help or know the correct way of doing this, it would be greatly appreciated!
This is the full expression from my filter query, the date is formatted the same as in Excel:
Date of Incident ge '@{formatDateTime(addDays(utcNow(), -15), 'dd-MM-yyyy')}'
Thanks! 🙂
The flow still returns all items in the list.
In the List rows present in a table section, I do have the DateTime Format selected as ISO 8601.
Hi @pakalolo13
Pls try the below expression:
@greaterOrEquals(formatDatetime(item()?['DateOfOperation'],'yyyy-MM-dd'), addDays(utcNow(), -1, 'yyyy-MM-dd'))
pls replace the column name with actual name in the excel sheet in above expression.
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
It is date time value. So if there are no records which match the filter, I should be returning no results, correct? My flow is always returning all the items in the table every time it runs. Don't understand what I am missing. I have seen others with the same formula work as intended. All help is appreciated.
Hi @pakalolo13
Pls verify if 'list rows present in a table' action is returning datetime or integer value for 'Date' column by looking at the output from previous run flow instance. If its date, then pls try the below expression:
@greaterOrEquals(item()?['Date'], addDays(utcNow(), -1, 'yyyy-MM-dd'))
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
I know this is an old post but I have used this exact filter query and mine is still producing too many results. I have changed from -15 to -1 as shown below.
@greaterOrEquals(item()?['Date'], formatDateTime(addDays(utcNow(), -1), 'dd-MM-yyyy'))
I should only see one record as of today 2/20/2024. Any help is greatly appreciated!
I'm not sure why your Filter query isn't working. I'd confirm your column name is Site and the text 'Location A' in your Excel table doesn't have any spaces at the start/end that might cause it to not be equal. On a side note - you mentioned this: Site eq 'Location A', it seems to totally ignore the filter array and pulls through every single row that has Location A. The query you put will apply a filter to return all rows that has Location A - is that not what you want?
For the Filter array, I believe your column name in your Excel Table is 'Date of Incident' not "DateOfIncident'. You just need to make sure it's exactly the same as what comes through in your List rows present in a table action. To check you can click on the download link after you run the flow.
@grantjenkins - thank you, that worked! Really appreciate your help.
I'm just wondering if you may know how to solve this issue as well... when I use a filter query on 'list rows present in a table', for example 'Site' like this: Site eq 'Location A', it seems to totally ignore the filter array and pulls through every single row that has Location A.
Do you know why it could be doing this? For info, this is the filter array another user suggested and did work until I put in the filter query on the 'List rows present in a table Data' action.
@greaterOrEquals(item()?['DateOfIncident'], formatDateTime(addDays(utcNow(), -15), 'dd-MM-yyyy'))
UPDATE: @grantjenkins - I noticed that actually the filter array is ignored regardless of the the filter query, it seems to always show show all rows.
Thanks,
Josh
I'd remove both Apply to each actions then add your Create HTML table using the following.
From: Body from your Filter array
Fields:
//Date of incident
item()?['Date of Incident']
//Days since incident
item()?['Days since Incident']
//Other fields...
item()?['NAME OF YOUR FIELD']
Hi @ManishSolanki This solved the problem, thank you so much again.
Sorry to ask for further assistance but now I've spotted an issue this causes to my flow. Originally, I was putting the value from 'list rows present in table' into 'create html table', so that I could add these outputs into an email, however this is pulling through all rows still.
I realise this is because I'm selecting the value from 'list rows present in table', and not the filter array that actually has the rows I need.
Is there a way to present the rows retrieved from the filter array into an email? preferably similar to what I have already done. When I have tried doing it, it forces an apply to each twice, and I'm guessing this is not going to work.
Thank you again,
Josh
Hi @jap11
Pls set the "DateTime Format" flag to "ISO 8601" in List rows present in table action:
If this helps & solves your problem, please remember to give a 👍 and accept my solution as it will help others in the future.
Thanks
WarrenBelz
146,743
Most Valuable Professional
RandyHayes
76,287
Super User 2024 Season 1
Pstork1
66,089
Most Valuable Professional