Filter array of users by mobile phone

[Michal]

Hi, 

I am trying to filter out users with a specific phone number from Office Search for users (V2)

First wanted to filter the array using nested ['BusinessPhones'] array but it didn't work. 

I have used this but apparently I am mismatching string with the array. Does anyone have any idea how this could work?

In the filter I used: 

@contains(first(item()?['BusinessPhones']), '+45')

When the above has failed I tried another approach and tried to apply the filter within "Apply to each" of the found users.

I have used this in the condition:

@contains(first(item()), +45)

But got this error. Again I am mismatching types.

Can you give me a hint how to get it to work?

[Jronash]

You're getting confusing results because contains() behaves differently if it's applied to an array or a string.

If you run contains() on a string, it looks for a substring.

If you run it on an array, it looks for an array element that matches the full value. (not a substring)

So you have two options for how to solve this.  If you have the full number that you're looking for, the easiest way would be like this:

If you don't have the full number, and want to filter out any number that contains '+45', it's a little trickier.  You will need to convert both sides of your filter criteria into strings:

The expression for the left string() is this:

string(item()?['BusinessPhones'])

The expression for the right side is this:

string('+45')

[Michal · Accepted solution]

Unfortunately, it didn't work as you suggested, but it pointed me in a good direction. 

If searching for a specific phone number, the working condition looks like this:

Where Business phones is equal to array('+45#########') 

In advance mode it looks like this:

@equals(items('Apply_to_each')?['BusinessPhones'], array('+45#######'))

If searching for a fragment of the phone (i.e. by country code), this works almost as you suggested. There are some details different:

On the left side: string(item())

On the right side: string('+45')

In advance mode it looks like this:

@contains(string(item()), string('+45'))

Thank you for help!

[Jronash]

Glad you found something that's working for you! However, the solutions you are using might have a few unintended side effects.

Your first solution:

@equals(items('Apply_to_each')?['BusinessPhones'], array('+45#######'))

This will work fine as long as the individual only has one Business phone number.  If they have two or more business phone numbers on record, it will not return them in your results, even if one of the numbers matches your filter.

Your second solution:

@contains(string(item()), string('+45'))

This searches the entire object for '+45', not just the phone numbers.  So if by some strange coincidence someone had '+45' as part of their email address or job title or department or anything like that, they would be included in the results of your filter.  This is pretty unlikely, though.

If these side effects won't be an issue with your dataset, great!  If you want to keep troubleshooting, I can help you take a closer look at what's going on.

[Michal]

You are right. So how to address those two scenarios?

When trying to build the condition on arrays and I use "contains" a specific array with the specific phone, it doesn't catch the phone. 

@contains(items('Apply_to_each')?['BusinessPhones'], array('+45########'))

What do I do wrong?

[Jronash · Accepted solution]

I'd like to go back to my original solution, as it's working in my tests and I believe it could work for you as well.  I see from your screenshots that you doing an Apply to Each over the Value collection.  I don't think it's necessary to do this loop.  We should be able to set that value collection as the "From" in your filter, and still have it search only the Business phones array.

What I would suggest is to create a new Filter Array, and don't put it inside a loop this time.  Set the From field to "value" from your "Search for Users" action, and then go to advanced mode and set the criteria to this:

@contains(string(item()?['BusinessPhones']), string('+45'))

If it doesn't work, let me know what happens.  Do you get error messages, or does it just not return the results that you expect it to?

[Michal]

It works OK.

But I found another obstacle. The  Search for users (V2) returns only 1000 records. I got more than that and this is why I don't get a result as the phone I am looking for is out of those first 1k records. 

Any idea how to overcome this?

[Jronash]

Maybe this method would work for you?

https://powerusers.microsoft.com/t5/Building-Flows/Get-all-users-through-Office-365-Users-connector-...

I don't have that many users in my environment so I can't test it.

[Michal]

It worked OK. Thank you. 

The maximum amount of records which can be retrieved is 5k by changing the pagination: