ADF Filter STill not working.....help

Mike Kiser 1,531

This filter is filtering out all the good data . I need all the Companies BCO, BED,BSC,BNB to not be filtered out, we need if an employee is EMP to NOT be filtered out and we need if an Employee's status is Active to NOT be filtered out but the below is filtering these. Any suggestions?

@ANDO (and(contains(createArray('BCO','BED','BSC','BNB'),item().company), equals(item().classcode,'EMP')), not(contains(item().status,'Inactive')))

I just failed the first QA Test on the rework and am trying and trying to figure out why this is filtering these employees out.

Any help would be greatly appreciated!
Thanks,
Mike Kiser

Nasreen Akter 10,736 Reputation points

2021-04-15T19:23:56.563+00:00

@Mike Kiser , do you mean when any of the expression is true e.g., expression1 or expression2 or expression3?
Mike Kiser 1,531 Reputation points

2021-04-15T19:36:00.67+00:00
@Nasreen Akter

Thanks for your fast response.....I am really under the gun lol

The Filter rules:

Do NOT filter if Companies are BCO, BED,BSC,BNB (Filter out all other companies)

Do NOT filter is an Employee is ACTIVE (filter out Inactive)

DO NOT filter if an employee has a classcode of EMP (filter all others)

Thanks SO much!
Mike
Nasreen Akter 10,736 Reputation points

2021-04-15T19:53:22.907+00:00

@Mike Kiser : If all are AND, then the above expression should work. Would you please give some examples of the good data you want to keep, also please share the expression you have in the ForEach--> Settings --> Items. Thanks!
Mike Kiser 1,531 Reputation points

2021-04-15T20:08:00.86+00:00

Hi @Nasreen Akter

The ForEach is
@activity('Filter Out Inactive').output.value

In the filter rules above any of the 3 rules should filter like an OR

1) for example if the "company":"PNL" (not BCO, BED,BSC, OR BNB it should filter and not send this employee through the ForEach

2) for example if the "classcode":"POI" (not "EMP") it should filter the data from going to the ForEach

3) for example if the "status":"Inactive" (NOT Active) it should filter the data from going to the ForEach

so if any one of the 3 filters are true it should not send the employee data through the ForEach

Man, this logic is complex....please ask any other questions if I am not clear and thank you!!!
Mike Kiser

2 answers

Nasreen Akter 10,736 Reputation points

2021-04-15T20:34:04.037+00:00
Hi @Mike Kiser ,

try OR instead of an AND. e.g.,

@or(or(contains(createArray('BCO','BED','BSC','BNB'),item().company), equals(item().classcode,'EMP')), not(contains(item().status,'Inactive')))

Thanks!
Please sign in to rate this answer.
Mike Kiser 1,531 Reputation points

2021-04-15T23:38:13.82+00:00

Hi @Nasreen Akter

Hi @Nasreen Akter

This is so crazy....the @or let the following happen:

@or(or(contains(createArray('BCO','BED','BSC','BNB'),item().company), equals(item().classcode,'EMP')), not(contains(item().status,'Inactive')))

requirement : if the "classcode":"POI" (not "EMP") it should filter the data from going to the ForEach

in the test file just run in QA a few minutes ago 3 employees with classcode=POI were allowed to get through the ForEach.....I don't know what to try now....any suggestions?

Thank you!
Mike Kiser

Nasreen Akter 10,736 Reputation points

2021-04-15T23:44:49.953+00:00

@Mike Kiser : in the output schema, do you always have company, classcode and status populated?

Nasreen Akter 10,736 Reputation points

2021-04-15T23:57:15.15+00:00

@Mike Kiser , I still feel like the expression with AND is correct. @MartinJaffer-MSFT , please advise.

Mike Kiser 1,531 Reputation points

2021-04-16T01:38:08.787+00:00

Hi @Nasreen Akter , they are supposed to be...I checked in the test file that QA ran and they are....good question......
Thanks
Mike

MartinJaffer-MSFT 26,011 Reputation points

2021-04-16T16:53:03.423+00:00

I'm getting confused. This is going all over the place. @Nasreen Akter

I suspected one of the activities was using the wrong activity as input. Like,

Pipeline: Source -> Filter1 -> Filter2, -> Filter3 -> ForEach Where: the input to ForEach should be Filter3, the input to Filter3 should be Filter2 the input to Filter2 should be Filter1 the input to Filter1 should be Source

Filter3 could appear to not be working, if the input to ForEach was Filter2. That would skip Filter3
Filter2 could appear to not be working, in the input to Filter3 was Filter1. That would skip Filter2
Filter1 could appear to be not working, if the input to Filter2 was Source. That would skip Filter1

Can you double check for such cases? @Mike Kiser

Once we are sure everything is aligned properly, then I can focus on the syntax. To make things a little easier for yourself add some indent formatting. It makes the code easier to read.

Mike Kiser 1,531 Reputation points

2021-04-16T18:31:59.68+00:00

Hi @MartinJaffer-MSFT @Nasreen Akter

I could never get the filters to work; I removed 2 Filters and combined the logic per above into one Filter and used that Filter Activity in the ForEach. This was the filter

I finally gave up on the Filter and filtered in the Query of the Lookup but I would still prefer to use the Filters.
Thanks
Mike
Sign in to comment
Mike Kiser 1,531 Reputation points

2021-04-16T01:52:39.677+00:00

@Nasreen Akter , the strangest thing is that it looks like it filtered out the employee with POI...see attached.....and I am using the following as Items into the ForEach

@activity('Filter Out Inactive').output.value
Please sign in to rate this answer.
Mike Kiser 1,531 Reputation points

2021-04-16T03:45:30.043+00:00

Hi @Nasreen Akter ,

I have done one filter at a time and found that something about the following isn't correct:

@contains(createArray('BCO','BED','BSC','BNB'),item().company)

If I have company = 'POI' then this filter still lets this data through the ForEach...I'm trying to figure out why now...

what is really confusing is that it looks like it is filtering, by the following but the ForEach still processes it.

"ItemsCount": 1,
"FilteredItemsCount": 1,
"Value": [
{
"employeeId": "106375",
"firstName": "Rick",

AND this is what I am using in the ForEach for Items: @activity('Filter Out Inactive').output.value. It's like it is filtering it but still sending this employee's data through the ForEach...

Thanks
Mike

Mike Kiser 1,531 Reputation points

2021-04-16T15:51:30.183+00:00

Hi @Nasreen Akter @MartinJaffer-MSFT

I worked until 11pm last night; the Filter just isn't working......I guess I have to try to use a query in the Lookup which is going to take me a while.......I even deleted the Filter and readded it and got the same result....

Mike Kiser
Sign in to comment