[SalesForce] Text Formula String Concatenation – Omit Trailing Comma

Scenario : I have 7 checkbox fields. For example say it is Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, and Saturday. In Formula field I want to display all that are true as comma separated text in order from Sunday thru Saturday.

For example:

Sunday – True
Friday – True

Formula Field should be – Sunday, Friday.

I tried below formula for concatenating but comma's are coming extra if any one of the field is false.

IF( Sunday__c = true, 'Sunday', '') + ','+
IF( Monday__c= true , 'Monday', '')+ ','+ 
IF( Tuesday__c= true , 'Tuesday', '')+ ','+ 
IF( Wednesday__c= true , 'Wednesday', '')+ ','+ 
IF( Thursday__c= true , 'Thursday', '')+ ','+
IF( Friday__c= true , 'Friday', '')+ ','+ 
IF( Saturday__c= true , 'Saturday', '')

Note : I don't want to go for trigger

Best Answer

I think this is how far you have to go to implement formula here. I'm doubtful its compile size will be under the limit, but I haven't checked.

IF(Sunday__c, 'Sunday' & IF(
    OR(Monday__c, Tuesday__c, Wednesday__c, Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Monday__c, 'Monday' & IF(
    OR(Tuesday__c, Wednesday__c, Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Tuesday__c, 'Tuesday' & IF(
    OR(Wednesday__c, Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Wednesday__c, 'Wednesday' & IF(
    OR(Thursday__c, Friday__c, Saturday__c), ',', ''
), '') &
IF(Thursday__c, 'Thursday' & IF(
    OR(Friday__c, Saturday__c), ',', ''
), '') &
IF(Friday__c, 'Friday' & IF(Saturday__c, ',', ''), '') &
IF(Saturday__c, 'Saturday', '')

A couple notes:

  • You don't need to check Checkbox__c = true for a checkbox in a formula. Just use Checkbox__c.
  • The proper string concatenation operator is a single ampersand (&).
Related Topic