Model Query operator for Contains doesn't sub spaces with %

  • 1
  • Question
  • Updated 7 months ago
  • Answered
  • (Edited)
Hello!

I'm loving Skuid and I'm on version 11.1.7 but noticed something that's given me a challenge with the "contains" function in queries. When you set a model condition using "contains" as a condition this uses the LIKE function in SOQL.

Use case: I want the user input to generate a search on another model and return results based on similar names.

Here's an example using the Account model, this is the actual Skuid soql:

SELECT Name,Type,Id FROM Account WHERE (Name LIKE '%Test Corp%') LIMIT 11

This query doesn't find things like "Test 2 Corp".

This would be significantly more effective if the spaces were substituted for a % sign so the query ready like this:

SELECT Name,Type,Id FROM Account WHERE (Name LIKE '%Test%Corp%') LIMIT 11

This would find things like "Test 2 Corp"

Thank you,

Dan
Photo of Dan Boudreau

Dan Boudreau

  • 820 Points 500 badge 2x thumb

Posted 8 months ago

  • 1
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Hi Dan,

I'm not sure how your search is set up, but if you're able to use the setting to Tokenize search terms, this should do what you're after. Is that option visible in the search settings? If so, does it get you any closer to the behavior you want?
Photo of Dan Boudreau

Dan Boudreau

  • 820 Points 500 badge 2x thumb
Hey Mark,

Great question but unfortunately I wasn't clear :(

I'm using a model query so the option to tokenize search isn't there. The reason I'm using a model query is my use case:

  • Create new Accounts
  • Show potential duplicates
  • There is no action to populate a search component (without code) to show potential duplicates (which would be ideal)
If you have alternative suggestions; that would be great. In fact, I went so far as for my Person Account scenario to build a formula that simply inserts % between first and last name--works great but that doesn't solve for non-Person Accounts.

Thank you,

Dan
Photo of Dan Boudreau

Dan Boudreau

  • 820 Points 500 badge 2x thumb
Hey Mark,

I solved for the Consumer scenario but not the Business one where the Account name is truly one value.

My specific use case aside, I'm surprised the Skuid team isn't replace a space with a % in contains query; that would be considered standard if I wrote the code/queries manually because it would improve search results.

Thanks

Dan
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
There are places where the search terms are tokenized, which does what you're describing. Components like tables, queues, decks, or any that have a search field built in, can tokenize. Here's what the query generally looks like from that kind of search using the search string test ABC:

SELECT RecordTypeId,Name,Id FROM Account WHERE (((Name LIKE '%test%'))AND((Name LIKE '%abc%'))) LIMIT 101

Do you suppose you could repurpose one of these components for the purpose of searching?

I'm also testing the Search Component and seeing that it seems to tokenize the search terms. You can define actions that the page should take when a user selects a search result, so perhaps the first step in your account creation process would be to search for duplicates, and if one is found, fire off an action sequence that allows the user to select that one instead of creating a new one. If you need one set of actions for a Person Account and another for non-Person accounts, you could use branch actions to check for this on the clicked result in Return Action setup. That's a very simple summary, but it might be worth checking into further. 
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Hi Dan,

A colleague suggested that one more way to do this might be to create two conditions on your model. The first one says the name of the person you look for has to start with your string (Match for first name).
The second one would have to end with your string (Match for last name)

Then change your conditional logic to say either 1 or 2 has to be True.

Is this something that might work?
Photo of Dan Boudreau

Dan Boudreau

  • 820 Points 500 badge 2x thumb
I solved for the Consumer (first and last name) scenario but not the Business one where the Account name is a single name (e.g. Acme Corporation).
Photo of Mark DeSimone

Mark DeSimone, Official Rep

  • 11,050 Points 10k badge 2x thumb
Understood. You're right that you can't declaratively pre-populate the search term in a search component, but you do have a great deal of control over how the search behaves, as well as what actions happen when a user clicks on a search result. So, I've often seen the search component used as a first step in looking for potential duplicate records.  A common approach would be to start by asking a user to first search for potential duplicates before creating a new record. If they see an existing record they'd like, then they can click on it, and that click would fire off whatever action sequences you need to take the user to the next step in their workflow (create a new reference to the selected record, create a new event associated with the selected record, and so on). The search component should also search more quickly thanks to SOSL, and it will tokenize its search terms.