cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Is it possible to query a SharePoint List using approximate string matching and display matched records in a data table?

Hello,

 

i would like to query a SharePoint List using fuzzy string matching and return the matched records into a data table (since most likely there will be many). Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @John_Martel ,

Q1:

You need to create custom connector here:
2288.PNG

 When you create custom connector, you could create custom functions based on your demands.

After you've created your custom connector successfully, you could connect this connector with your app.

 

Q2:use standard function to filter data like your descriprtion.

I've made a similar test and I think you could try this to filter:

Filter(tabelname,
       First(MatchAll(TextInput1.Text,"[A-Z]"&"[a-z]+")).FullMatch in Material_Description,
       Last(MatchAll(TextInput1.Text,"[A-Z]"&"[a-z]+")).FullMatch in Material_Description
       )
//MatchAll(TextInput1.Text,"[A-Z]"&"[a-z]+") will split "BlueBear" based on capital letter, the FullMatch field will have two value: "Blue" and "Bear"

//I found that you split entered data to two parts based on capital letter, so maybe you could filter based on two situations, like "Bear" and "Blue".

2289.PNG

 

I'm not sure whether this formula could meet your demands, just a advice.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

Hi @John_Martel ,

Do you want to filter items based on approximate string?

Yes, this is possible.

Could you tell me the field data type that you want to filter based on?

Please note that only text type could be compared with approximate string.

I suggest you use Filter and IsMatch function.

Try this kind of formula:

Filter(listname,IsMatch(filedname,"approximate string"[, Options ] ))

For example, I use a list named fruit11111, I want to get items that contains these string: "f....f".

Then I use this formula to filter:

Filter(fruit11111,IsMatch(Title,"f.+f",MatchOptions.Contains))

2255.PNG

 The common Predefined patterns that you could use as approximate string:

2256.PNG

 The match options are:

2257.PNG

 

Here's a doc about how to write approximate string in IsMatch function for your reference:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-ismatch

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Thanks @v-yutliu-msft  for your response. To answer your question, yes i am only applying this to type. I don't think Regular Expressions would work in my case. So for instance if the user enters "BluBear" and the record on the SharePoint List is "BlueBear", RegEx will not work. I will need an approximate string match function like Jaro-Winkler or Levenshtein. i think i might have to write custom functions, but also not sure if that is possible in PowerApps. Thanks again

Highlighted

Hi @John_Martel ,

Could you describe more clearly about the conditions that you want to filter based on ?

You could list some examples, just one is not enough.

I've searched about Jaro-Winkler and Levenshtein.

I'm afraid there's no function that could be used as Jaro-Winkler and Levenshtein currently.

As to customize functions, that's not a easy solution...

You need to customize connector to design function based on your demands.

It's complex. Here's doc about this for your reference:

https://docs.microsoft.com/en-us/connectors/custom-connectors/define-blank

Since your demands is only to filter data, I more suggest you use the standard functions in PowerApps.

 

 

Best regards,

 

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Hello @v-yutliu-msft , thanks again for your support. I will give you a little more information about what i am trying to do. I have a screen with a simple input box and a button, where the user will enter the material description, after clicking the "search button", he would get the records with a fuzzy/approximate match.

 

So using the example i provided earlier "BlueBear" and the sample list in the table below. The records : {123, 124, 127} should be returned (or filtered on as you call it) because the descriptions approximately match. I was able to create an Azure function (see link below) based JaroWinker Distance that returns the fraction of how close are the two strings (1 is an exact match). However I am stuck at the creating the custom connector, i don't see the option in my powerapps to create a custom connector as shown in the link you shared. Please let me know your thoughts! thanks

https://azurefunctionsjm20200226041913.azurewebsites.net/api/JaroWinklerDistance?string1=BluBear&str...

 

 

Material_IDMaterial_Description
123BlueBear
124Blue Bear1
125Yellow Bear
126Red Bear
127Blue1Bear
128Orange Bear
129Red 1Bear
Highlighted

Hi @John_Martel ,

Q1:

You need to create custom connector here:
2288.PNG

 When you create custom connector, you could create custom functions based on your demands.

After you've created your custom connector successfully, you could connect this connector with your app.

 

Q2:use standard function to filter data like your descriprtion.

I've made a similar test and I think you could try this to filter:

Filter(tabelname,
       First(MatchAll(TextInput1.Text,"[A-Z]"&"[a-z]+")).FullMatch in Material_Description,
       Last(MatchAll(TextInput1.Text,"[A-Z]"&"[a-z]+")).FullMatch in Material_Description
       )
//MatchAll(TextInput1.Text,"[A-Z]"&"[a-z]+") will split "BlueBear" based on capital letter, the FullMatch field will have two value: "Blue" and "Bear"

//I found that you split entered data to two parts based on capital letter, so maybe you could filter based on two situations, like "Bear" and "Blue".

2289.PNG

 

I'm not sure whether this formula could meet your demands, just a advice.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (7,422)