cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BayanMobarak1
Helper I
Helper I

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)

 

 

KPILast period This period Email (The person responsible for the KPI) 
TRS00Bayan.sefri@gmail.com
ATS11whatever@gmail.com
TRI10Test@gmail.com

 

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

 

 

11

the expression written it pink is 

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

22

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)

 

Your help is really appreciated!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
eliotcole
Impactful Individual
Impactful Individual

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)))

 

solution - rows - filter.jpg

 

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:

parse json.jpg

 

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. 🙂

Apply to each.jpg

 

...

 

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'

rows - filter.jpg

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.

View solution in original post

10 REPLIES 10
ekarim2020
Resident Rockstar
Resident Rockstar

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

ekarim2020_0-1629201214846.png

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

Ellis

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 optionsapply to each options

 

 let me what do you think about this

eliotcole
Impactful Individual
Impactful Individual

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:


@BayanMobarak1 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.

 

 

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 errorFilter array error

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

 

 

eliotcole
Impactful Individual
Impactful Individual

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, @BayanMobarak1 ... 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.

Jcook
Super User
Super User

Hi @BayanMobarak1 

 

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.

 


Did I answer your question? Mark my post as a solution!

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





eliotcole
Impactful Individual
Impactful Individual

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)))

 

solution - rows - filter.jpg

 

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:

parse json.jpg

 

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. 🙂

Apply to each.jpg

 

...

 

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'

rows - filter.jpg

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.

View solution in original post

You are GENIUSSSS !!

It worked perfectly 😍

Thank you @eliotcole  so much 

eliotcole
Impactful Individual
Impactful Individual

My pleasure, mate!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,424)