Filter data source based on 2 fields

(0) Share

Report

Posted on by BK-04121952-0

This has been frustrating me for 2 days and various searches have not yielded help - a couple have seemed close, but still not quite getting this working.

The data source is working and I can output the expected array as needed. Just the bit trying to reduce the list down to "expired" rows has me baffled.

Basically,

A Form populates an Excel sheet. The Flow will run once a day to...

For all rows with an integer in the 'Status' column

Take the date in the "Start' column

Perform an addDays on Start using Status (or [negative] -addDays on the current date)

Where the result is less than the current date, put the record into an array & send it to me

The problem is that the filter either gives me all the records with an integer in Status, regardless of the result relative to the current date, or it says addDays can't add Null to a date.

I think the following is what I started with in a Filter Array. Honestly, this middle part has been through so many iterations using Compose, Filter Array, Select, etc. that I'm not sure if this is possible.

@less(formatDateTime(item()?['Start'], 'yyyy-MM-ddT00:00:00.000Z'), formatDateTime(addDays(utcNow(), -int(item()?['Status'])), 'yyyy-MM-ddT00:00:00.000Z')))

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

Ellis Karim 11,937 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @BK-04121952-0,

We can check if the Status property is NOT null - i.e. we check if the 'Status' property is missing from the data. A NULL value will be returned from the expression item()?['Status'] if the 'Status' property is missing.

Then perform the check on the Start date.

The filter array query will look something like:

and( not(equals(item()?['Status'], null)), less( formatDateTime(item()?['Start'], 'yyyy-MM-ddT00:00Z'), formatDateTime(addDays(utcNow(), mul(int(item()?['Status']), -1)), 'yyyy-MM-ddT00:00Z') ) )

Note that the ‘Z’ at the end of the time string explicitly defines this as a UTC time.

For an explanation on how the expression item()?['Status'] works see:
How to Query JSON Data Using JSON Notation with Power Automate❔❔❓ – Ellis Karim's Blog

Ellis
https://elliskarim.com/

Was this reply helpful? Yes No
BK-04121952-0 8 on at

Like (0)

Report
Copy link

Link copied!

Thank you for the reply. The tutorial link was very informative but I had already determined how to eliminate records where Status = null and to include only those rows where Status is a number.

Where I'm stuck is using the numeric value in Status to alter the date value in another field using addDays. The goal is a list of records where the Start date is a variable number of days older than the current date. The original Filter array used a fixed number for addDays...

@less(formatDateTime(item()?['Effective'], 'yyyy-MM-ddT00:00:00.000Z'), formatDateTime(addDays(utcNow(), -30), 'yyyy-MM-ddT00:00:00.000Z'))

Now, that number needs to be based on the value of Status, an Excel column configured as Numeric w/ no decimal.

But, substituting item()?['Status'] for the fixed number fails to even save. Searches for ways to do this included calculating a date via various combinations of using Variable, Compose, & Select for use in a filter, but have also failed.

Was this reply helpful? Yes No
Ellis Karim 11,937 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @BK-04121952-0

I tried the following:
@and( not(equals(item()?['Status'], null)), less( formatDateTime(item()?['Effective'], 'yyyy-MM-ddZ'), formatDateTime(addDays(utcNow(), mul(int(item()?['Status']), -1)), 'yyyy-MM-ddZ') ) )

I used the following sample data:

[ { "Effective": "2024-02-12T15:00", "Status": "1", "Name": "Joe Uil" }, { "Effective": "2024-02-12T15:00", "Name": "Eu Bod" }, { "Effective": "2024-07-15T10:30", "Status": "2", "Name": "Emily Carter" }, { "Effective": "2024-04-20T09:00", "Status": "1", "Name": "John Smith" }, { "Effective": "2024-08-25T14:00", "Status": "3", "Name": "Anna White" }, { "Effective": "2024-12-18T08:45", "Status": "2", "Name": "Michael Brown" }, { "Effective": "2024-07-10T12:15", "Status": "1", "Name": "Sophia Green" }, { "Effective": "2024-08-05T16:30", "Status": "3", "Name": "William Black" }, { "Effective": "2024-09-22T11:00", "Status": "2", "Name": "Olivia Grey" }, { "Effective": "2024-10-01T13:45", "Status": "1", "Name": "James Blue" }, { "Effective": "2024-12-15T07:30", "Status": "3", "Name": "Isabella Rose" }, { "Effective": "2024-12-30T17:00", "Status": "2", "Name": "Ethan Violet" } ]

And this Filter returned the following

{ "body": [ { "Effective": "2024-02-12T15:00", "Status": "1", "Name": "Joe Uil" }, { "Effective": "2024-07-15T10:30", "Status": "2", "Name": "Emily Carter" }, { "Effective": "2024-04-20T09:00", "Status": "1", "Name": "John Smith" }, { "Effective": "2024-08-25T14:00", "Status": "3", "Name": "Anna White" }, { "Effective": "2024-07-10T12:15", "Status": "1", "Name": "Sophia Green" }, { "Effective": "2024-08-05T16:30", "Status": "3", "Name": "William Black" }, { "Effective": "2024-09-22T11:00", "Status": "2", "Name": "Olivia Grey" }, { "Effective": "2024-10-01T13:45", "Status": "1", "Name": "James Blue" } ] }

Ellis

Ellis Karim's Blog – IT doesn't have to be complicated!

Was this reply helpful? Yes No
Suggested answer

BK-04121952-0 8 on at

Like (0)

Report
Copy link

Link copied!

Well, with exception of using mul() to make Status a negative number, your example looked like what I had. So, in usual fashion, I didn't try it in my Flow before really thinking it through what it did and responding.

More or less, then, the problem was just "-int(...)" not being the same as "-30"?

Had I just tried adding days to the first half of the equation instead of subtracting them from the latter, I would not have had to bother you at all. To quote grumpy HTML programmers everywhere, "Damn semicolon!"

Thank, greatly, you for your time!

Was this reply helpful? Yes No