I have a requirement to perform fuzzy search on a specific long text area field on a custom object and return the results, where long text area field can have some keywords, hashtags or quantity of products.

Expectation :

**When creating new record (from UI) for TestAccount, user inputs some detailed text in field1, so based on the keywords in those text, I want to perform fuzzy search on field2 and return the fuzzy matching records, may be we will limit the records to 200. Could you please suggest on below points:

  1. What is the best approach/ solution to achieve above requirement?
  2. Please suggest if there is any better way / solution present for this
  3. How can we perform scoring of records based on matching of keywords**

For example : Suppose there is a custom object with name "TestAccount" which has two long text area fields i.e., field1, field2.

  1. field1 will have summary of account filled by user.
  2. field2 will have the keywords, hashtags extracted from field1.

Please find below 3 TestAccount records:

Name -> account1

field1 -> ABC has been an industry leader in Computer, and Super Computer manufacturing and assembling services. For one of their their upcoming bulk order they want to purchase some of computer components, So they want know the estimation cost of 10000 microprocessor chip and 50000 keyboards, 100000 mouses.

field2 -> #keyboards, #microprocessor, #chip , #mouse, keyboardsquantity:50000, microprocessorquantity:10000, mousesquantity:100000

Name -> account2

field1 -> XYZ industries want to purchase some of the electronic equipments, So they want know the estimation cost of 5000 microprocessor chip and 9999 keyboards

field2 -> #keyboards, #microprocessor, #chip, keyboardsquantity:9999, microprocessorquantity:5000

Name -> account3

field1 -> CDEF INC, For one of their bulk order they want to buy some of computer components, they want know the estimation cost of 90000 processor chip and also 500 keyboards.

field2 -> #keyboards, #microprocessor, #chip , keyboardsquantity:500, microprocessorquantity:90000

As per observations: Different options:

  1. Matching rules : As noticed text area field is not displaying for selection when creating matching rules. and in case of Text fields, used in Matching Rules it only looks at the first 100 characters of the field.

Ref : https://help.salesforce.com/s/articleView?id=sf.duplicate_rules_overview.htm&type=5 and https://help.salesforce.com/s/articleView?id=sf.matching_rules_matching_methods.htm&type=5

I see there is a limitation : Duplicate/Match Rules - Exact Match on Custom Text Field Showing Inaccurate Error Results

  1. SOSL :

Is there a way to search a long text area for contains string in SOSL SOSL Multiple Search Words SOSL query having issues with quotes (in the record(s) that should be returned, not the query string) SOSL query field limitations

  • 1
    The SOSL issue you referenced is specific to JSON in a long text area. While SOSL does not specifically allow you to search in just one field, just in "types of field", this seems likely the only option you have. SOQL does not support filtering on long text area fields at all.
    – Phil W
    Jan 3 at 7:21


You must log in to answer this question.