[SalesForce] SOQL query to find a row with the longest string in Rich Text Field / Long Text Area Field

I am able to find the rows with longest character following method:

Create new formula field of type NUMBER and insert a simple formula to it that calculates a length of the string

LEN(Street__c)

Then query on this field:

Select ... From ...  Where FormulaField___c > 20

Here is the reference that I got the above method:
SOQL query to find a row with the longest string in a column

But how about Rich Text Field / Long Text Area Field? You know that we cannot create formula field on Rich Text Field / Long Text Area Field.
How can I achieve this?

Best Answer

You can't filter or SOQL on Long-text area/rich text fields. You can upvote on the idea here.

You can't use Long-text area/rich text fields in formula either, another idea is here for you to upvote if you so desire.

Workaround-Trigger

You can create a trigger on your object, that has this long-text area field, and use the before insert / before update event. And a custom field to have the length of your long area field to hold, say Length_LongArea__c. A simple on-the-fly example would be

trigger objectTrg on [object] (before insert, before update) {
  for (object obj: trigger.new) {
    if (String.isNotBlank(obj.long_area_Field__c)) {
      obj.Length_LongArea__c = obj.long_area_Field__c.length();
    } else {
      obj.Length_LongArea__c = 0;
    }
  }
}

Workaround-Process Builder

You can also create a process builder to achieve the same result.

  1. Create a Process Builder, select your object, and put a condition ISCHANGED() so that process builder only runs when required.

enter image description here

  1. Add "Update Record" action as shown below enter image description here

From the above code, we are trying to populate the length field with the length of the long-area field, after which you could use that field anywhere you want. e.g. reports, soql filters, etc.

Related Topic