Writing Formulas with Rich Text Areas

I’m writing a formula that looks at certain fields and counts whether or not it is filled out. At the end, I will divide it by the number of fields in order to get a completion percentage. I’m having trouble doing this with rich text fields.

This is the formula it work until the two last lines which are rich text areas.

If(ISBLANK(Salesforce_Enrollments__c ),0,1)+ 
If(ISBLANK(Total_Referrals__c ),0,1)+ 
IF(ISPICKVAL(Enrollment_Targets_Score__c, “–None–”), 0,1)+
IF(ISPICKVAL(Participant_Retention_Score__c, “–None–”), 0,1)+
If(ISBLANK(Funder_Outcomes__c ),0,1)+
If(LEN(Enrollment_Explantion__c = 0),0,1)+
If(LEN(Retention_Explanation__c = 0),0,1)

Are you saying it’s never/always shown as filled out?

This is the error I get. It won’t even let me save the formula.

 Error: You referenced an unsupported field type called “Rich Text Area” using the following field: Enrollment_Explantion__c

Certainly can work around this using javascript to set boolean UI only fields.

Can you help me with this please? As you can tell, I’m not too experienced with writing formulas, let alone javascript.

Sure. I’ll whip something together this evening.

Thanks sir

This is not strictly a skuid problem, if you are building this formula in Salesforce, as it appears. Skuid, however, may help you with a UI-Only field. The formulas there will support not only LEN() but also ISBLANK().

You won’t be able to use an is-blank Condition on the the field in your model, however.

I’m writing this in Salesforce by the way.

Is there any way to accomplish the same thing using different code? I am building this formula in Salesforce.

Yes, but you’d have to save the record first

I can delete the lines not working and save it. Then what?

An apex trigger was a suggestion I found elsewhere. There’s an assumption that apex functions can test for null text fields (on insert and update).

Any idea what it would look like?

The trigger? Maybe something like this:

trigger yourTriggerName on YourObjectAPI_Name (before insert, before update) {

List <YourObjectAPI_Name> triggerSet = Trigger.new;
for ( YourObjectAPI_Name theRow : triggerSet )
{
    Integer n = 0;

    if ( String.isEmpty(theRow.Salesforce_Enrollments__c) ) { n++; }
    if ( String.isEmpty(theRow.Total_Referrals__c) ) { n++; }
    ...
    if ( String.isEmpty(theRow.Retention_Explanation__c) ) { n++; }
    
    //if ( n > 0 )
    //{
        theRow.YourNumericField__c = n/7; //assuming 7 fields to test
    //}
}

}

(The field you add to the object (Your Numeric Field) is a Number datatype, not a Formula.)

Oh. One more thing. Do you need this value to be saved in the record or is it simply for the users knowledge to know how “complete” the record is?

It needs to be saved in the record. 

Could Skuid help me with this?

Sam, Pat and I may be seeing this differently, and Pat’s probably got more SF/Skuid knowledge than I do. But, here’s how I understand the situation:

  1. Your initial approach was to define a formula field in the SF object. Since this needs this to be saved in the record (as you indicated above), that is the best option.
  2. That first approach fails, because the formula field string functions do not work with long/rich text fields.
  3. If you did not need to save the percentage but only display it to the users, Skuid would let you define a similar formula in a UI-Only field. Because skuid receives the text values as strings, its IsBlank() function will work as desired.
  4. Skuid might be able to retrieve a record without a percentage value, compute it using a ui-only field, and update the percentage into the database record. Generally speaking, that's an odd way to go; however, the percentage is a result of user input. (Now, I'm not sure about the ui-only fields tracking the changes to the fields. Javascript might be needed here.) Maybe we need to understand your workflow too.
  5. The apex trigger happens inside the database, independent of the user interface. It would essentially work like a formula field, with the logic embedded in the trigger rather than the meta-data definition of the percentage field. The difference is that the isEmpty() method works on long/rich text in apex where isBlank() fails in a formula. [Note: I tested the apex code with a long-text field, not with rich-text; there may be html code built into the rich text such that it is never truly 'empty'!]
  6. Field formulas are evaluated on record access. Trigger formulas are evaluated on record creation and update. UI-Only formulas are evaluated when included in a skuid model. Javascript formulas are evaluated when run.
Let us know if that helps.

So the value can be calculated using Skuid and saved to the record in a percentage field.