4

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

4 Answers 4

4

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 (&).
6

@AdrianLarson I used your suggestion for a similar solution and it worked for a year or so, until I was asked to include more Checkboxes (12 in all) in the formula, then I got:

Error: Compiled formula is too big to execute (5,713 characters). Maximum size is 5,000 characters.

I found a simpler solution. Include the ", " in each result, then add a period to the end of your IF statements. Wrap the code with SUBSTITUTE to replace ", ." with null in order to remove the trailing comma at the end of your text

SUBSTITUTE(
IF( Sunday__c, 'Sunday, ','') &
IF( Monday__c, 'Monday, ','') &
IF( Tuesday__c, 'Tuesday, ','') &
IF( Wednesday__c, 'Wednesday, ','') &
IF( Thursday__c, 'Thursday, ','') &
IF( Friday__c, 'Friday, ','') &
IF( Saturday__c, 'Saturday, ','') &
'.'),
', .', NULL)
1
  • Clever. Nice optimization.
    – Adrian Larson
    Jun 12, 2019 at 0:42
1

You can try the following logic -

IF( Sunday__c , 'Sunday ', '') + 
IF( Sunday__c & Monday__c , ', Monday', IF( Monday__c , 'Monday', '') ) +
IF (Tuesday__c & (Sunday__c OR Monday__c ), ', Tuesday' , IF(Tuesday__c , 'Tuesday', '') )+
So on...

This will add the commas only when it has any former value.

Hope this helps.

1
  • 1
    They are checkbox and it will be in the order they are written. That should not be a problem as in first condition you are checking if any previous checkbox is true then add comma, else simply add a Text without comma.
    – Sneha
    Aug 22, 2016 at 14:10
0

Another solution would be to use the SUBSTITUTE function to break repeated commas down to single commas, and then use the MID function to get rid of the leading and trailing commas:

MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(','&IF(Sunday__c,'Sunday','')&','&IF(Monday__c,'Monday','')&','&IF(Tuesday__c,'Tuesday','')&','&IF(Wednesday__c,'Wednesday','')&','&IF(Thursday__c,'Thursday','')&','&IF(Friday__c,'Friday','')&','&IF(Saturday__c,'Saturday','')&',',",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(','&IF(Sunday__c,'Sunday','')&','&IF(Monday__c,'Monday','')&','&IF(Tuesday__c,'Tuesday','')&','&IF(Wednesday__c,'Wednesday','')&','&IF(Thursday__c,'Thursday','')&','&IF(Friday__c,'Friday','')&','&IF(Saturday__c,'Saturday','')&',',",,",","),",,",","),",,",","))-2)
2
  • Don't you still need to reference the checkbox fields somewhere?
    – Adrian Larson
    Aug 22, 2016 at 14:31
  • @AdrianLarson I hadn't gotten all of the details worked out when I did the first post. This should work though.
    – martin
    Aug 22, 2016 at 14:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .