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