Answered

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

(0) Share

Report

Posted on by John_Martel

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!

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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))

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

The match options are:

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,

Was this reply helpful? Yes No
John_Martel 38 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
v-yutliu-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

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,

Was this reply helpful? Yes No
John_Martel 38 on at

Like
a
(0)

Report
Copy link

Link copied!

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&string2=BlueBear1

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

Was this reply helpful? Yes No
Verified answer

v-yutliu-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @John_Martel ,

Q1:

You need to create custom connector here:

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".

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

Best regards,

Was this reply helpful? Yes No