cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Michal
Advocate V
Advocate V

Filter array of users by mobile phone

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?

 

Michal_0-1599770942588.png

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.

 

1234.png

I have used this in the condition:

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

 

But got this error. Again I am mismatching types.

123.png

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

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

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:

Michal_1-1600114755173.png

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:

Michal_2-1600115515295.png

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!

View solution in original post

Jronash
Solution Supplier
Solution Supplier

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?

View solution in original post

8 REPLIES 8
Jronash
Solution Supplier
Solution Supplier

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:

filter2.png

 

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:

filter.png

The expression for the left string() is this:

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

The expression for the right side is this:

string('+45')

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:

Michal_1-1600114755173.png

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:

Michal_2-1600115515295.png

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!

View solution in original post

Jronash
Solution Supplier
Solution Supplier

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.

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
Solution Supplier
Solution Supplier

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?

View solution in original post

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
Solution Supplier
Solution Supplier

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.

It worked OK. Thank you. 

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

Michal_0-1600201801138.png

 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors
Users online (1,048)