Writing Formulas with Rich Text Areas

edited June 25, 2020 in Questions
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)

Comments

  • Pat VachonPat Vachon 💎💎💎
    edited June 25, 2020
    Are you saying it's never/always shown as filled out?
  • edited June 27, 2019
    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
  • Pat VachonPat Vachon 💎💎💎
    edited June 27, 2019
    Certainly can work around this using javascript to set boolean UI only fields.
  • edited June 27, 2019
    Can you help me with this please? As you can tell, I'm not too experienced with writing formulas, let alone javascript.
  • Pat VachonPat Vachon 💎💎💎
    edited June 27, 2019
    Sure. I'll whip something together this evening.
  • edited June 27, 2019
    Thanks sir
  • edited June 25, 2020
    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.

  • edited June 27, 2019
    I'm writing this in Salesforce by the way.
  • edited June 27, 2019
    Is there any way to accomplish the same thing using different code? I am building this formula in Salesforce.
  • Pat VachonPat Vachon 💎💎💎
    edited June 27, 2019
    Yes, but you'd have to save the record first
  • edited June 27, 2019
    I can delete the lines not working and save it. Then what?
  • edited June 27, 2019
    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).
  • edited June 27, 2019
    Any idea what it would look like?
  • edited June 27, 2019
    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.)

  • Pat VachonPat Vachon 💎💎💎
    edited June 25, 2020
    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?
  • edited June 28, 2019
    It needs to be saved in the record. 
  • edited June 28, 2019
    Could Skuid help me with this?
  • edited September 3, 2019
    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.

  • Pat VachonPat Vachon 💎💎💎
    edited June 25, 2020
    So the value can be calculated using Skuid and saved to the record in a percentage field.
  • edited June 25, 2020
    Is this easy to do? I don’t use Skuid but I use Salesforce a lot and I would appreciate anything that made using it easier. What are the pros of Skuid? Would you recommend it?
  • Pat VachonPat Vachon 💎💎💎
    edited July 3, 2019
    A little confused. Do you not have Skuid?

    I don't always recommend Skuid, but those times are very few and far between. ;)

    The pros are endless, but it depends on your needs and potential use cases. If you have standard CRM needs, few users and few customization requests, then Skuid is likely to be recommended.

    So ... what are your needs besides this thread? This requirement can be done easily with Skuid.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!