Skip to main content

Notifications

Community site session details

Community site session details

Session Id :
Power Apps - Building Power Apps
Unanswered

Search with Wildcards

Like (0) ShareShare
ReportReport
Posted on 6 Dec 2019 03:21:35 by 2,914

Using a wildcard (%) in a Search with D365 connector is supported but, unless I'm missing something, it appears wildcards are not supported in either SQL or when searching within local collections.

Anoyone know of a way of getting wildcards to work with SQL or local collections? A Search should be translated to 'like' in SQL so I'm guessing the connector is actively escapting the % wildcard character which is a strange thing to do...

Wildcards really should be supported in these cases.

 

  • iAm_ManCat Profile Picture
    18,206 Most Valuable Professional on 09 Dec 2019 at 10:10:39
    Re: Search with Wildcards

    Yeah, I'd only advise the IsMatch for local collections - definitely not delegable! (yet)

     

    What you're doing at the moment seems the best way to go about it in the short term until more functions are made delegable 😺

  • PaulD1 Profile Picture
    2,914 on 09 Dec 2019 at 01:09:46
    Re: Search with Wildcards

    Thanks for the suggestion @iAm_ManCat 

     

    For the moment I'm experimenting with nesting multiple searches inside one another and splitting the search term on the wildcard character (in this case, a space).

    If(
     Len(conSearchText) > 2,
     SortByColumns(
     Search(
     Search(
     Search(
     Search(
     '[cr].[Account]',
     First(
     Split(
     conSearchText,
     " "
     )
     ).Result,
     "AccountName"
     ),
     Last(
     FirstN(
     Split(
     conSearchText,
     " "
     ),
     2
     )
     ).Result,
     "AccountName"
     ),
     Last(
     FirstN(
     Split(
     conSearchText,
     " "
     ),
     3
     )
     ).Result,
     "AccountName"
     ),
     Last(
     FirstN(
     Split(
     conSearchText,
     " "
     ),
     4
     )
     ).Result,
     "AccountName"
     ),
     "AccountName"
     ),
     Blank()
    )

     It has the benefit that the search terms do not have to appear in order, so search for 'A B Smith' would give 'B A Smith', 'Bob Smith-Allan' and so on. Down side is that if searching for 'M Simon' it will be just the same as searching for 'Simon' as it already contains an 'm'. 

    Will see if this suits the user-needs, if not, will looking into IsMatch and regular expressions (though can't recall if IsMatch is delegable, and if not, it won't help as the dataset is large).

  • iAm_ManCat Profile Picture
    18,206 Most Valuable Professional on 06 Dec 2019 at 10:31:40
    Re: Search with Wildcards

    Can't really advise on the SQL side, but for local collections have you tried an IsMatch and then used regex to perform the wildcard?

     

    Cheers,

    Sancho

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Building Power Apps

#1
mmbr1606 Profile Picture

mmbr1606 39 Super User 2025 Season 1

#2
MS.Ragavendar Profile Picture

MS.Ragavendar 32

#3
DBO_DV Profile Picture

DBO_DV 31 Super User 2025 Season 1

Overall leaderboard