cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Search with Wildcards

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.

 

3 REPLIES 3
Highlighted
Super User II
Super User II

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Super User
Super User

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

Highlighted
Super User II
Super User II

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 😺


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

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