+ Start a Discussion
kkanthakkantha 

Can we return all ticked values?

Hope someone can help.  Am writing a formula field in one object (B) where I want only the values associated with checkboxes on another object (A) to appear.  There may be one, two or even three checkboxes ticked in object A, so I would want some way of all the values which are ticked appearing in object B, but not the other values which are not ticked.

 

It seems that if I do an IF formula, I can only get the first ticked value in the field to appear, but not the other ticked values.  I tried using a "," separator so that a list of values would appear separated by a comma, but I couldn't get the syntax to work.  I would prefer to have each field labelled (Objective 1, Objective 2 etc), and was trying to think of a way for the field label itself not showing if the value was null, but I can't see a way to do that. This is my formula:

 

IF(Homework__r.Objective_1__c  = TRUE, Homework__r.Objectives_1__c,  IF(Homework__r.Objective_2__c  = TRUE, Homework__r.Objectives_2__c, IF(Homework__r.Objective_3__c  = TRUE, Homework__r.Objectives_3__c, IF(Homework__r.Objective_4__c  = TRUE, Homework__r.Objectives_4__c,  IF(Homework__r.Objective_5__c  = TRUE, Homework__r.Objectives_5__c, IF(Homework__r.Objective_6__c  = TRUE, Homework__r.Objectives_6__c, null))))))

 

Any help on this would really be appreciated.  Thanks!

 

Best Answer chosen by Admin (Salesforce Developers) 
CaptainObviousCaptainObvious

See if this monster formula helps :)

 

IF (Homework__r.Objective_1__c,'Objective 1','') 

& IF (!Homework__r.Objective_1__c,'', IF(OR(Homework__r.Objective_2__c,Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_2__c,'Objective 2','') 

& IF (!Homework__r.Objective_2__c,'', IF(OR(Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_3__c,'Objective 3','') 

& IF (!Homework__r.Objective_3__c,'', IF(OR(Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_4__c,'Objective 4','') 

& IF (!Homework__r.Objective_4__c,'', IF(OR(Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_5__c,'Objective 5','') 

& IF (!Homework__r.Objective_5__c,'', IF(OR(Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_6__c,'Objective 6','') 

& IF (!Homework__r.Objective_6__c,'',IF(Homework__r.Objective_7__c,', ','')) 

& IF (Homework__r.Objective_7__c,'Objective 7','')

 

All Answers

CaptainObviousCaptainObvious

See if this monster formula helps :)

 

IF (Homework__r.Objective_1__c,'Objective 1','') 

& IF (!Homework__r.Objective_1__c,'', IF(OR(Homework__r.Objective_2__c,Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_2__c,'Objective 2','') 

& IF (!Homework__r.Objective_2__c,'', IF(OR(Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_3__c,'Objective 3','') 

& IF (!Homework__r.Objective_3__c,'', IF(OR(Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_4__c,'Objective 4','') 

& IF (!Homework__r.Objective_4__c,'', IF(OR(Homework__r.Objective_5__c,Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_5__c,'Objective 5','') 

& IF (!Homework__r.Objective_5__c,'', IF(OR(Homework__r.Objective_6__c,Homework__r.Objective_7__c),', ','')) 

& IF (Homework__r.Objective_6__c,'Objective 6','') 

& IF (!Homework__r.Objective_6__c,'',IF(Homework__r.Objective_7__c,', ','')) 

& IF (Homework__r.Objective_7__c,'Objective 7','')

 

This was selected as the best answer
kkanthakkantha

Thanks so much!  This is awesome, works great.  Have one small problem though...I know I said I wanted a comma separation, but now it's there I can see with several wordy text fields it looks confusing.  I have tried to put in a line break instead of the comma - & BR() & - but can't find the right place.  Could you tell me if that's possible?  And if so, where to put the break?

 

Thanks so much for spending the time to do this.

Cheers

KK

 

kkanthakkantha

Actually forget that, I worked it out...have managed to put in a line break instead of a comma like this (in case anyone else wanted the same thing):

 

IF (Homework__r.Objective_1__c,Homework__r.Objectives_1__c, ''  ) 
& IF (!Homework__r.Objective_1__c,'', IF(OR(Homework__r.Objective_2__c,Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c),  BR() ,'')) 
& IF (Homework__r.Objective_2__c,Homework__r.Objectives_2__c,'') 
& IF (!Homework__r.Objective_2__c,'', IF(OR(Homework__r.Objective_3__c,Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c),  BR() ,'')) 
& IF (Homework__r.Objective_3__c,Homework__r.Objectives_3__c,'') 
& IF (!Homework__r.Objective_3__c,'', IF(OR(Homework__r.Objective_4__c,Homework__r.Objective_5__c,Homework__r.Objective_6__c),  BR() ,'')) 
& IF (Homework__r.Objective_4__c,Homework__r.Objectives_4__c,'') 
& IF (!Homework__r.Objective_4__c,'', IF(OR(Homework__r.Objective_5__c,Homework__r.Objective_6__c),  BR() ,'')) 
& IF (Homework__r.Objective_5__c,Homework__r.Objectives_5__c,'') 
& IF (!Homework__r.Objective_5__c,'', IF(OR(Homework__r.Objective_6__c),', ','')) 
& IF (Homework__r.Objective_6__c,Homework__r.Objectives_6__c,'')

 

I guess putting a double line break is not possible right?  ...though it would be nice...