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

Question

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?

Answer 1

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.

2. Add "Update Record" action as shown below

---

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.