2 Replies Latest reply on Sep 19, 2013 3:51 PM by raybaudi

    Checkbox Calculation

    Dominic

      Title

      Checkbox Calculation

      Post

           Hey guys, struggling to figure out how to do this...

           I have two check-box fields; one is a list of names, and the other is the email for the names. What I want to be able to do is when I check-mark multiple names there corresponding emails get checked. I'm using this for sending emails out.

           Here is what Ive got so far on the email calculation field:

           Case (
           Project Mgr Designer = "Carrie" ; "carrie@pazdesigngroup.com" ;
           Project Mgr Designer = "Dominic"  ;  "dominic@pazdesigngroup.com" ;
           Project Mgr Designer = "Justin" ;  "justin@pazdesigngroup.com" ;
           Project Mgr Designer = "Paz"  ; "paz@pazdesigngroup.com" ;
           )
            
           It works perfectly when I only have the one designer checked but it does not when you check multiple people. I've tried everything thing i can think off. I thought it would work something like this but no such luck:
            
           Project Mgr Designer = "Paz" & "Dominic"  ; "paz@pazdesigngroup.com" & "dominic@pazdesigngroup.com";
            
           I've tried other variations as well but cant seam to figure it out. Any help would be much appreciated.
            
           Thanks!
            

        • 1. Re: Checkbox Calculation
          philmodjunk

               You might find it enlightening to make a copy of your Project Mgr Designer field and reformat the copy as an edit box, and watch the data change in this field when you click different checkboxes and in different orders. The edit box copy will show you exactly what data is stored in the field as you click different checkboxes.

               Personnally I would use a related table of this info with a portal for selecting as many designers as needed. I could then select a designer from a drop down and the email address could be automatically be looked up from yet another related table. This not only would let you select multiple individuals, but it's a fairly simple data entry task to update things when you gain or lose personnel or their email address needs changing. The approach you are trying to set up is more complex and you have redefine the calculation each time that there is a change.

               But if you really want to do it as a calculation field:

               Substitute (
               List (
               If ( Not IsEmpty ( FilterValues ( Project Mgr Designer ; "Carrie" ) ) ; "carrie@pazdesigngroup.com" ) ;
               If ( Not IsEmpty ( FilterValues ( Project Mgr Designer ; "Dominic" ) ) ; "dominic@pazdesigngroup.com" ) ;
               If ( Not IsEmpty ( FilterValues ( Project Mgr Designer ; "Justin" ) ) ; "justin@pazdesigngroup.com" ) ;
               If ( Not IsEmpty ( FilterValues ( Project Mgr Designer ; "Paz" ) ) ; "paz@pazdesigngroup.com" )
               )
               ; ¶ ; ";" )

               This produces a list of email addresses separated by semi colons.

               But a portal to a related table is much simpler to work with.

          • 2. Re: Checkbox Calculation
            raybaudi

                 Substitute (
                                       List (
                                                 If ( ValueCount ( FilterValues ( Project Mgr Designer = "Carrie" ) ) ; "carrie@pazdesigngroup.com" ) ;
                                                 If ( ValueCount ( FilterValues ( Project Mgr Designer = "Dominic" ) ) ; "dominic@pazdesigngroup.com" ) ;
                                                 If ( ValueCount ( FilterValues ( Project Mgr Designer = "Justin" ) ) ; "justin@pazdesigngroup.com" ) ;
                                                 If ( ValueCount ( FilterValues ( Project Mgr Designer = "Paz" ) ) ; "paz@pazdesigngroup.com" ) 
                                               )
                                      ; ¶ ; " & "
                                     )