16 Replies Latest reply on Dec 13, 2012 9:49 AM by jlig

    Adding a CFCASE to a Coldfusion SELECT dropdown

    jlig Level 1

      I have the following simple dropdown on my form:

      ---------------------------------------------------------

                                <select name="ttTo" id="ttTo" >

                                  <option value="">Choose One</option>

                                  <cfoutput query="rsTicketTo">

                                    <option value="#rsTicketTo.ttEmail#">#rsTicketTo.ttDesc#</option>

                                  </cfoutput>

                                </select>

                              </div>

      --------------------------------------------------------

       

      How would I change my select above to include the sample functionality below but using ColdFusion?

      Note: The #TicketType# variable below will be on the page but not from the same table as the ttTo value.

       

             // Check where to send support request to...

             switch (trim($fTicketTo))

             {

             case "":

      $error = 1;

      $tMessage = "You must select a valid Recipient!";

      break;

       

             case "Service":

      if ($fTicketType == "T1")

      {

      $fTicketTo = 'serv1@xyz.com';

      }

      else

      {

      $fTicketTo = 'serv2@xyz.com';

      }

      break;

       

             case "Outage":

      $fTicketTo = 'serv1@xyz.com';

      break;

       

             case "Data":

      $fTicketTo = 'data@xyz.com';

      break;

       

             case "Voice":

      $fTicketTo = 'voice@xyz.com';

      break;

       

             case "T1":

      $fTicketTo = 't1@xyz.com';

      break;

       

             case "Wire":

      $fTicketTo = 'wire@xyz.com';

      break;

       

             case "Phone":

      $fTicketTo = 'pst@xyz.com';

      break;

       

             case "Prov":

      $fTicketTo = 'prov@xyz.com';

      break;

             case "Cust":

      $fTicketTo = 'cc@xyz.com';

      break;

       

             }

        • 1. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
          Dan Bracuk Level 5

          Using switch/case inside a select control doesn't make any sense.  What are you hoping to accomplish?

          • 2. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
            jlig Level 1

            The drop-down is filled with values from a query.

            - and each value has an associated email address

            - most of the time the value selected will go to its associated email

            - but occasionally, depending on an a value in a 2nd drop-down, I need the associated email to be different (see the code above, where ticketType=T1)

            • 3. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
              BKBK Adobe Community Professional & MVP

              It isn't clear which query column corresponds to the case values 'service', 'outage', 'data', etc. There are also inconsistenties. You have defined the cases as subsequent values of trim($fTicketTo). Yet, within each case block, you again define a new variable of the same name ($fTicketTo). Another inconsistency is that "T1" is at once a value for $fTicketType and for trim($fTicketTo).

               

              If all you wished to do is to convert the given switch-case into a select-box, then you could proceed as follows:

               

              <cfform>

              <cfselect name="ttTo" id="ttTo" required="yes" message="You must select a valid Recipient!">

              <option value="">Choose support request</option>

              <option value="serv1@xyz.com">T1 Service</option>

              <option value="serv2@xyz.com">Service(except T1)</option>

              <option value="serv1@xyz.com">Outage</option>

              <option value="data@xyz.com">Data</option>

              <option value="voice@xyz.com">Voice</option>

              <option value="t1@xyz.com">T1</option>

              <option value="wire@xyz.com">Wire</option>

              <option value="pst@xyz.com">Phone</option>

              <option value="prov@xyz.com">Prov</option>

              <option value="cc@xyz.com">Cust</option>

              </cfselect>

              <cfinput name="sbmt" type="submit" value="Submit">

              </cfform>

              • 4. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                Dan Bracuk Level 5

                In that case, my first choice would be to use the case construct in the actual query to get the appropriate email address.

                • 5. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                  jlig Level 1

                  Thanks Dan..

                  I did think &amp; try adding the case into my query, but not sure how to reference the "value" of ticketType which comes from a second drop-down earlier on the form?

                   

                  On my form the user is first asked for:

                  - ticketType (T1 is an option)

                  Later in another select is asked for:

                  - ttTo (if T1 was chosen above, then the email address for ttTo will be serv1@xyz.com, else it should be serv2@xyz.com)

                   

                  I'm basically trying to convert the php code snippet above to CF.

                  • 6. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                    Dan Bracuk Level 5

                    There are a few of ways to do it. 

                     

                    One is to set up cascading selects bound to cfc's.  If you choose this option, you use your cfc to pass the correct email to the second select.

                     

                    Another is to figure out the correct email with coldfusion code once the form has been submitted.  This would be my preference.

                     

                    Another is to use javascript to look at the values of the two selects and put the email address into a hidden field.

                    • 7. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                      jlig Level 1

                      Dan, I copied your code example below, what I really need to know is:

                      - how to add in that "one" case for Service where "ticketType=T1"

                      ------------------------------

                      case "Service": 

                      if ($fTicketType == "T1")

                      {

                      $fTicketTo = 'serv1@xyz.com';

                      }

                      else

                      {

                      $fTicketTo = 'serv2@xyz.com';

                      }

                      -------------------------------

                       

                       

                      Can you add in this missing If/Then/Else to your code below?

                      -------------------------------------------

                      <cfform>

                      <cfselect name="ttTo" id="ttTo" required="yes" message="You must select a valid Recipient!">

                      <option value="">Choose support request</option>

                      <option value="serv1@xyz.com">T1 Service</option>

                      <option value="serv2@xyz.com">Service(except T1)</option>

                      <option value="serv1@xyz.com">Outage</option>

                      <option value="data@xyz.com">Data</option>

                      <option value="voice@xyz.com">Voice</option>

                      <option value="t1@xyz.com">T1</option>

                      <option value="wire@xyz.com">Wire</option>

                      <option value="pst@xyz.com">Phone</option>

                      <option value="prov@xyz.com">Prov</option>

                      <option value="cc@xyz.com">Cust</option>

                      </cfselect>

                      <cfinput name="sbmt" type="submit" value="Submit">

                      </cfform>

                      ---------------------------------------------------------

                      • 8. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                        BKBK Adobe Community Professional & MVP

                        <cfform>

                        <cfselect name="ttTo" id="ttTo" required="yes" message="You must select a valid Recipient!">

                        <option value="">Choose support request</option>

                        <cfif $fTicketType EQ "T1">

                            <option value="serv1@xyz.com">Service</option>

                        <cfelse>

                            <option value="serv2@xyz.com">Service</option>

                        </cfif>

                        <option value="serv1@xyz.com">Outage</option>

                        <option value="data@xyz.com">Data</option>

                        <option value="voice@xyz.com">Voice</option>

                        <option value="t1@xyz.com">T1</option>

                        <option value="wire@xyz.com">Wire</option>

                        <option value="pst@xyz.com">Phone</option>

                        <option value="prov@xyz.com">Prov</option>

                        <option value="cc@xyz.com">Cust</option>

                        </cfselect>

                        <cfinput name="sbmt" type="submit" value="Submit">

                        </cfform>

                        • 9. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                          jlig Level 1

                          Thanks Dan..

                          I updated my select to the following and it works, but I see a problem:

                          - The  #FORM.ttType# can be changed at any time by the user which causes the Select to "lose track" of the value?

                          - Is there a way to "auto-update" the value in the Select when the #FORM.ttType# value changes on the form?

                          -----------------------------------------------------------

                          <select data-native-menu="false" name="ttTo" id="ttTo" data-mini="true">

                          <option value="">Choose One</option>

                          <cfif #FORM.ttType# EQ "T1"> 

                          <option value="serv1@xyz.com">Service</option>

                          <cfelse>

                          <option value="serv2@xyz.com">Service</option>

                          </cfif>

                          <cfoutput query="rsTicketTo">
                          <option value="#rsTicketTo.ttEmail#">#rsTicketTo.ttDesc#</option>
                          </cfoutput>
                          </select>

                          ------------------------------------------------------------

                           

                          After working through the previous example, it looks like I might need this If/Then/Else logic in my "INSERT QUERY" rather than in the Select because my #FORM.ttType# can be changed at any time by the user which causes the Select to "lose track" of the value.

                           

                          - So, how do I add it (<cfif #FORM.ttType# EQ "T1"> THEN ttTo = serv1@xyc.com, ELSE ttTo = serv2@xyc.com ) to the following INSERT statement?

                          -------------------------------------------------------------

                          <!--- Insert the new Ticket into the tblTickets table --->

                          <cfset CurrentPage=GetFileFromPath(GetBaseTemplatePath())>

                          <cfif IsDefined("FORM.MM_InsertRecord") AND FORM.MM_InsertRecord EQ "form1">

                            <cfquery datasource="care" result="insertQueryResult">   

                              INSERT INTO tbltickets (ttDate,  ttType, ttTo)

                          VALUES (<cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'>

                          , <cfif IsDefined("FORM.ttDateDue") AND #FORM.ttDateDue# NEQ "">

                          <cfqueryparam value="#FORM.ttDateDue#" cfsqltype="cf_sql_timestamp">

                          <cfelse>

                          NULL

                          </cfif>

                          , <cfif IsDefined("FORM.ttType") AND #FORM.ttType# NEQ "">

                          <cfqueryparam value="#FORM.ttType#" cfsqltype="cf_sql_clob" maxlength="30">

                          <cfelse>

                          ''

                          </cfif>

                          , <cfif IsDefined("FORM.ttTo") AND #FORM.ttTo# NEQ "">

                          <cfqueryparam value="#FORM.ttTo#" cfsqltype="cf_sql_clob" maxlength="255">

                          <cfelse>

                          ''

                          </cfif>

                          )

                            </cfquery>

                          ---------------------------------------------------------------

                          • 10. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                            BKBK Adobe Community Professional & MVP

                            In my opinion, all the preconditions you're setting make the issue unnecessarily complex. The values are coming in via a form, so you cannot avoid the effects of user-input. That said, the following is consistent with what you already have

                             

                            <cfquery datasource="care" result="insertQueryResult">  

                                INSERT INTO tbltickets (ttDate,  ttType, ttTo)

                                VALUES (<cfqueryparam cfsqltype='CF_SQL_timestamp' value='#CreateODBCDateTime(now())#'>

                                ,<cfif IsDefined("FORM.ttDateDue") AND FORM.ttDateDue NEQ "">

                                    <cfqueryparam value="#FORM.ttDateDue#" cfsqltype="cf_sql_timestamp">

                                <cfelse>

                                    NULL

                                </cfif>

                                ,<cfif IsDefined("FORM.ttType") AND FORM.ttType EQ "">

                                    <cfqueryparam value="#FORM.ttType#" cfsqltype="cf_sql_varchar" maxlength="30">

                                <cfelse>

                                    ''

                                </cfif>

                                ,<cfif IsDefined("FORM.ttType")>

                                    <cfif FORM.ttType EQ "">

                                        ''

                                    <cfelseif FORM.ttType EQ "T1">

                                        <cfqueryparam value="serv1@xyc.com" cfsqltype="cf_sql_varchar">

                                    <cfelse>

                                        <cfqueryparam value="serv2@xyc.com" cfsqltype="cf_sql_varchar">

                                    </cfif>

                                </cfif>

                                )

                            </cfquery>

                            • 11. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                              Dan Bracuk Level 5

                              If you want to update a value in a select based on the value of another form field, you have to use javascript.  I wouldn't bother.  I'd simply generate the email address I wanted when I process the submitted form.    

                              • 12. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                                BKBK Adobe Community Professional & MVP

                                On second thoughts, the following is more in tune as last term:

                                 

                                <cfif IsDefined("FORM.ttType")>

                                        <cfif FORM.ttType EQ "T1">         

                                                <cfqueryparam value="serv1@xyc.com" cfsqltype="cf_sql_varchar">

                                        <cfelseif FORM.ttType NEQ "">               

                                               <cfqueryparam value="serv2@xyc.com" cfsqltype="cf_sql_varchar">

                                        <cfelse>

                                            ''

                                        </cfif>

                                <cfelse>

                                          ''

                                </cfif>

                                • 13. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                                  jlig Level 1

                                  Dan, I think I need to try a different approach to this:

                                  - Rather than using alias email addresses, I plan to send the email(s) directly to each users address

                                  - But how do I selectively send users an email for only certain ttType's?

                                  - Remember, each ticket in my system gets assigned a ttType, which should determine "who" gets the email

                                   

                                  I'm not sure how to "tie" the tblUsers (where the email address is) to the tblTickets (where the ttType is) and only send those people the email..

                                   

                                  ps: I did go ahead and create a form with checkboxes to manage the Users preferences:

                                  - if an item is checked, then that user should receive that kind of email from the ticketing system

                                  UserEmailActionPreferences.jpg

                                  • 14. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                                    Dan Bracuk Level 5

                                    Regarding:

                                    I'm not sure how to "tie" the tblUsers (where the email address is) to the tblTickets (where the ttType is) and only send those people the email..

                                     

                                    Neither am I because I am not familiar with your database.  This is a rather long thread in which the topic has changed.  My suggestion is to start a new one that focuses on this particular problem.  Provide as much information about your database as you deem relevent.  Look for a table that has both a user id and a ticket id.

                                    • 15. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                                      BKBK Adobe Community Professional & MVP

                                      Dan Bracuk wrote:

                                       

                                      Neither am I because I am not familiar with your database.  This is a rather long thread in which the topic has changed.  My suggestion is to start a new one that focuses on this particular problem.

                                      Hyear, hyear.

                                      • 16. Re: Adding a CFCASE to a Coldfusion SELECT dropdown
                                        jlig Level 1

                                        will do.. thanks.