Filtering array and sending emails to specific people based on condition

[Anonymous]

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)

Your help is really appreciated!

[ekarim2020]

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]

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

 let me what do you think about this

[eliotcole]

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]

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

[eliotcole]

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.

[Jcook]

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.

[Anonymous]

Sure, please find the link below

https://docs.google.com/spreadsheets/d/1g1pUfmMIkVxzFTCyLd5Jurlrq3tHdOin/edit?usp=sharing&ouid=10987...

[eliotcole · Accepted solution]

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]

You are GENIUSSSS !!

It worked perfectly 😍

Thank you @eliotcole  so much 

[eliotcole]

My pleasure, mate!