question

ewinkiser avatar image
0 Votes"
ewinkiser asked MartinJaffer-MSFT commented

ADF Filter Activity NOT filtering

Hello! @nasreen-akter @MartinJaffer-MSFT

I have a Filter activity after a Lookup Activity (which is successfully bringing back data from a SQL Server DB Table); I want to send the filtered data to the ForEach for if an Employee is NOT "Inactive"....as shown below. My Filter Condition is: @startswith(item().status,'Inactive')

I have in my first row of data the Status = 'Inactive'; however the employee data in the first row of the Inactive filter is still being passed to the ForEach Activity; therefore something is incorrect. Should I have all my Filters inside the ForEach instead of before it?

Any suggestions will be greatly appreciated! Thanks! Mike Kiser

87528-image.png


87591-screenshot-2021-04-13-222747.jpg


87527-screenshot-2021-04-13-221657.jpg



azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I am a little confused. Has the issue been resolved @ewinkiser ?

0 Votes 0 ·
VaibhavChaudhari avatar image
0 Votes"
VaibhavChaudhari answered MartinJaffer-MSFT commented

If you just want to process records with Status active, then try something like

 @contains(item().status,'Active')


Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your reply...I just tried running it with your expression and see the following in the debugger; for some reason it is not filtering an Inactive employee with one row of data in the DB Table. This is the data in the field for this in the Table

"status": "Inactive",

{
"ItemsCount": 1,
"FilteredItemsCount": 0,
"Value": []
}

Can someone please assist? I have 3 of these filters; if I can get this Inactive filter to work I can do the other 2 myself...


Thanks
Mike

@nasreen-akter @MartinJaffer-MSFT

0 Votes 0 ·

That is good solution, as long as there are only 2 status, "Inactive" and "Active". If there are more status like "Retired" or "Deleted" then it gets harder.

0 Votes 0 ·
MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered ewinkiser published

@ewinkiser

There is a "not" function. You can use this to invert the results of the contains.

  @not(contains(item().status,'Inactive'))
· 9
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @MartinJaffer-MSFT , @nasreen-akter @VaibhavChaudhari

I tried that and the debugging info said it had filtered out one item....but the data still flowed to the ForEach. Here is what I am guessing is happening, but not sure how to set the ForEach to fix it.....Here is the ForEach items:

@activity('Look Up Employee Status in Success Factors').output.value

So even with the flow going through the filters correctly my subsequent ForEach is still using the Lookup before the filters. How do I flow the filtered data through the ForEach without calling the Lookup (before the filters)? I have never run into this before and just wrongly assumed that the data would flow through the filters into the ForEach.

Thanks so much! They caught this in QA Testing and I just wrongly assumed it was working .
Mike Kiser

I just thought this....should I filter AFTER the ForEach?

87882-image.png



0 Votes 0 ·
image.png (83.0 KiB)

Thanks @MartinJaffer-MSFT , @nasreen-akter @VaibhavChaudhari

I tried that and the debugging info said it had filtered out one item....but the data still flowed to the ForEach. Here is what I am guessing is happening, but not sure how to set the ForEach to fix it.....Here is the ForEach items:

@activity('Look Up Employee Status in Success Factors').output.value

So even with the flow going through the filters correctly my subsequent ForEach is still using the Lookup before the filters. How do I flow the filtered data through the ForEach without calling the Lookup (before the filters)? I have never run into this before and just wrongly assumed that the data would flow through the filters into the ForEach.

Thanks so much! They caught this in QA Testing and I just wrongly assumed it was working .
Mike Kiser

I just thought this....should I filter AFTER the ForEach?

87882-image.png



0 Votes 0 ·

@ewinkiser
The ForEach items should be taking from the nearest activity output (filter), not the earlier Lookup activity. The filter does not retroactively affect the lookup output. Filter has its own output.

1 Vote 1 ·

@ewinkiser, what is the expression you have in the ForEach-->Settings-->Items ?

0 Votes 0 ·

Hi @ewinkiser, According to the diagram, I think in the ForEach-->Settings-->Items, it should be @activity('Filter for only Company Employees').outout.Value, it could also be @activity('Look Up Ethnicity'), but not @activity('Look Up Employee Status in Success Factors').output.value

1 Vote 1 ·
Show more comments