+ Start a Discussion
NPMNPM 

Status Derived from several Fields

Need help with a formula, if it possible, to look at four checkbox fields and depending on which ones are checked set a text field with a code that is unique to each of the various combinations of whether these 4 checkbox fields are checked or not.  I am reaching a mind block, can someone help me out?
 
They checkboxes might be named Key, Target, Popular, Redone
 
 Example:
The user wants the Code field to be K, or T, or P, or R,or KT, or KP, or KR, or KTP, or KTR, or KTPR, etc. depending on which checkboxes are checked. 
 
Any ideas?
Kent ManningKent Manning
I would suggest you use Customize/Workflow Rules/Field Update and then choose the field you want updated.  Then write a fomula to update that field.

Write a formula that uses the case statement, something like:

Case(Key__c, True, "K",
          Target__c, True, "T",
          Popular__c, True, "P",
          Redone__c, True, "R",
          AND(Key__c,Target__c), True, "KT",
          AND(Key__c,Target__C, Popular__c), True, "KTP",
                     [continue to add statements
                       to the list to cover the combinations
                        you want.]
         "Unknown")


I have not tested this so don't know if the syntax is right, but this should give you the general idea. (I use a similar idea to update a Serial number field on one of our objects).
Also look under online help for "case function" for additional explination on how to use CASE.

One limit on this is that the formula field can't be longer then 1300 characters.  I have heard rumors that this is changing in Spring 08 release.

Hope this Helps

Kent

LI-COR Biosciences
NPMNPM

Thanks a bunch - you got me on the right track :smileyvery-happy:.  I found that I could not get CASE to work for this but had to go to IFs with  AND and NOT.

IF(AND(Key__c,AND(NOT(Target__c),NOT(Popular__c),NOT(Redone__c))),"K",
IF(AND(Target__c,AND(NOT(Key__c), NOT(Popular__c),NOT(Redone__c))),"T",
IF(AND(Popular__c,AND(NOT(Key__c),NOT( Target__c),NOT(Redone__c))),"P",
IF(AND(Redone__c,AND(NOT(Key__c),NOT (Target__c),NOT(Popular__c))),"R",
IF(AND(Key__c,Target__c,AND(NOT(Popular__c),NOT(Redone__c))),"KT",
IF(AND(Key__c,Target__c,Popular__c,AND(NOT(Redone__c))),"KTP",
IF(AND( Key__c,Target__c, Popular__c, Redone__c),"KTPR",
IF(AND( Key__c,Popular__c,AND(NOT(Target__c),NOT(Redone__c))),"KP",
IF(AND( Key__c,Popular__c, Redone__c,AND(NOT(Target__c))),"KPR",
IF(AND( Key__c, Redone__c,AND(NOT(Target__c),NOT(Popular__c))),"KR",
IF(AND( Target__c, Popular__c,AND(NOT(Key__c),NOT(Redone__c))),"TP",
IF(AND( Target__c, Popular__c, Redone__c,AND (NOT(Key__c))),"TPR",
IF(AND( Popular__c, Redone__c,AND(NOT(Key__c),NOT(Target__c))),"PR",
"Unknown")))))))))))))

dkorba2k5dkorba2k5
you guys made this too complex.  This is way easier to do this way:

IF(Key__c,"K","") + IF(Target__c,"T","") + IF(Popular__c,"P","") + IF(Redone__c,"R","")

Kent ManningKent Manning

It is much easier to do it this way. I'm going to file this away for future use.  Thanks for sharing it.