cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Filtering array and sending emails to specific people based on condition

Hello everyone,

I would really appreciate your help regarding this case.

I`m trying to make a flow that compare the results a KPI in two period in excel, if the values are 0 and 0 for last and current period send notification to the email in same row. for example TRS is sent only to Bayan.sefri@gmail.com .if the result of the two period is  0 and 1 or 1 and 1 no need to do anything.(please check the table)

 KPI Last period This period Email (The person responsible for the KPI) TRS 0 0 Bayan.sefri@gmail.com ATS 1 1 whatever@gmail.com TRI 1 0 Test@gmail.com

I did the following flow, but it is not working well

1

the expression written it pink is

int(item()?['This peroid '])
is equal to
Int(item()?['Last perioud '])

2

my problems are two:

1- It sends an email to every one

2 -I don`t know if filter is the right solution and how to write its condition (if the value of previous period and this period is zero, filter the table and keep only the kpi`s with 0 and 0)

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Solution

The excel table row connector passes the integers as strings, and it seems the easiest way to match them is to convert the zero to string, this worked for me:

``and(equals(item()?['This period'], string(0)), equals(item()?['Last period'], string(0)))``

So this is almost the same equation as I mentioned before, but we're removing the int() functions, and adding string() functions. Whatever works, right?

If you clicked on the 'Edit in advanced mode' it will look like this, and you can paste this in (I think - if not, play with the column names😞

``@equals(and(equals(item()?['This period'], string(0)), equals(item()?['Last period'], string(0))), true)``

Whoop!

Email Troubles

For your troubles getting the data out of the filter.

Test it without the email stage (just put a terminate action in to stop the flow) and once you can run it OK copy the body data from the filter, it should look a bit like this:

``````[
{
"@odata.etag": "",
"ItemInternalId": "a17af041-8bf0-428a-b2a2-58390f4d3b27",
"Email": "Bayan.sefri@gmail.com",
"KPI": "ML Production Volume",
"This Period": "0",
"Last Period": "0"
},
{
"@odata.etag": "",
"ItemInternalId": "64f2f9cf-3876-4256-924a-eb6863b04fa9",
"Email": "Osamaasefri22@gmail.com",
"KPI": "ML Dispatch Volume",
"This Period": "0",
"Last Period": "1"
}
]``````

Now create a Parse JSON action, click on the 'Generate from sample' button, and paste it in there, then click done.

Once you've done that, you should have a Parse JSON action that looks like this:

Now if you run your Apply to each on the Body from the Parse JSON action then you can more easily pick the field that you wish to use in the email action. 🙂

...

Alternate Solution

OK, here, to save some flow processing, I renamed the columns to have no spaces, so that I could do a single ODATA filter on the table (in case that it gets large😞

ThisPeriod eq '0'

That string expression is so that when it looks at the LastPeriod column it compares it to a string version of the zero value ... as this is how it is passed through in the JSON.

But if you want it without the ODATA and pure filtering:

[to be updated in a second]

The reason for taking the spaces out initially is that the excel ODATA filter cannot handle spaces. It also cannot handle 'and' or 'or', so only one query per excel ODATA apparently.

10 REPLIES 10
Super User

Please check that the value in the Apply to each loop is taken from the OUTPUT of the Filter array:

and that you are selecting the email values from the Apply to each loop: items('Apply_to_each')['Email]

Ellis

Anonymous
Not applicable

Hello Ekarim,

Thank you for replaying, I tried what you said but I`m nit sure if I implement it correctly, apply to each only had two options "Body" and "Item", and when I choose any of them, another apply to each is inserted to the flow.

apply to each options

Super User

Your condition formula is just a bit wonky, mate, that's all, plus you're going to get matches for anything where both values are the same number ... whatever that number is.

So, let's take a look at this, mate:

@Anonymous wrote:

int(item()?['This peroid '])

is equal to
Int(item()?['Last perioud '])

So, first out, you need to get the spelling, spacing, and case right on all of that.

I don't say that to insult, if your field names really do have those spaces after them (it seems that they at least are spelled that way) then that's all fine. But I would hazard a guess that someone will eventually correct those table column headings, so it might as well be you, and you can amend them here, too:

int(item()?['This period'])

is equal to
int(item()?['Last period'])

That second 'int' was capitalised.

However, if you click the 'Edit in advanced mode' that will (should) look like this:

``@equals(int(item()?['This period']), int(item()?['Last period'])``

I believe, that in order to get only items where the number is zero and they match, then you need to filter like this:

``@equals(and(equals(int(item()?['This period']), 0), equals(int(item()?['Last period']), 0)), true)``

There you are essentially saying:

Show a true value IF int(item()?['This period'] is equal to 0 AND int(item()?['Last period'] is equal to 0

is equal to
true

The first side of the condition will only show a true value if both those numbers are 0, and the condition will only follow the yes branch if that happens.

Anonymous
Not applicable

Thank so much Eliotcole,

Regarding the spelling and spaces, English is not my mother tongue, so when I`m in hurry i don`t give spelling that much attention but you are totally correct, I `ll fix it.

Thank you again for the code, i tried but an error occur I didn`t get what`s the problem

Filter array error

let me know, if you have any idea about the error, your help is appreciated !!

Super User

Oh, of course, I wasn't for a second suggesting that you were in any way deficient, there! As long as it works, who really cares how things are spelled. 🙂

That said, you have spaces before and after the column headings, and that can be problematic if they aren't there in the original table. It was all just everything together, I thought it best to suggest ensuring that the sample data that's coming in is nailed down tightly, that's all.

So, with that in mind, I think your referencing just needs a little tidying up, that's all, @Anonymous ... keep trying from scratch, and use the GUI to select the values that you need when building the expression. Just remember if you rename any column headings in the table, you might need to amend the expression here.

Super User

Hi @Anonymous

Could you send over a copy of the excel file? You can dummy the data (put fake data) in the table before sending if needed.

If you like my post please hit the Thumbs Up

Proud to be a Flownaut!

Check out my blog for Power Automate tips,
tricks, and guides
FlowAltDelete

Anonymous
Not applicable
Super User

Solution

The excel table row connector passes the integers as strings, and it seems the easiest way to match them is to convert the zero to string, this worked for me:

``and(equals(item()?['This period'], string(0)), equals(item()?['Last period'], string(0)))``

So this is almost the same equation as I mentioned before, but we're removing the int() functions, and adding string() functions. Whatever works, right?

If you clicked on the 'Edit in advanced mode' it will look like this, and you can paste this in (I think - if not, play with the column names😞

``@equals(and(equals(item()?['This period'], string(0)), equals(item()?['Last period'], string(0))), true)``

Whoop!

Email Troubles

For your troubles getting the data out of the filter.

Test it without the email stage (just put a terminate action in to stop the flow) and once you can run it OK copy the body data from the filter, it should look a bit like this:

``````[
{
"@odata.etag": "",
"ItemInternalId": "a17af041-8bf0-428a-b2a2-58390f4d3b27",
"Email": "Bayan.sefri@gmail.com",
"KPI": "ML Production Volume",
"This Period": "0",
"Last Period": "0"
},
{
"@odata.etag": "",
"ItemInternalId": "64f2f9cf-3876-4256-924a-eb6863b04fa9",
"Email": "Osamaasefri22@gmail.com",
"KPI": "ML Dispatch Volume",
"This Period": "0",
"Last Period": "1"
}
]``````

Now create a Parse JSON action, click on the 'Generate from sample' button, and paste it in there, then click done.

Once you've done that, you should have a Parse JSON action that looks like this:

Now if you run your Apply to each on the Body from the Parse JSON action then you can more easily pick the field that you wish to use in the email action. 🙂

...

Alternate Solution

OK, here, to save some flow processing, I renamed the columns to have no spaces, so that I could do a single ODATA filter on the table (in case that it gets large😞

ThisPeriod eq '0'

That string expression is so that when it looks at the LastPeriod column it compares it to a string version of the zero value ... as this is how it is passed through in the JSON.

But if you want it without the ODATA and pure filtering:

[to be updated in a second]

The reason for taking the spaces out initially is that the excel ODATA filter cannot handle spaces. It also cannot handle 'and' or 'or', so only one query per excel ODATA apparently.

Anonymous
Not applicable

You are GENIUSSSS !!

It worked perfectly 😍

Thank you @eliotcole  so much

Super User

My pleasure, mate!

Announcements

#### Register for a Free Workshop

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

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Top Solution Authors
Top Kudoed Authors
Users online (1,445)