7 Replies Latest reply on Nov 2, 2009 10:36 AM by BKBK

    Count <cfselect> values

    djkhalif Level 1

      Good evening all,

       

      I have two <cfselect>s in my form, I want to count how many values the user select and save that count to my database. Anyone seen this before? I would like to accomplish this, any help is very welcome.

       

      Thanks,

       

      DJ Khalif

        • 1. Re: Count <cfselect> values
          BKBK Adobe Community Professional & MVP

          Remember the cfselect attribute multiple="yes", which enables a user to select multiple items!

          • 2. Re: Count <cfselect> values
            BKBK Adobe Community Professional & MVP

            Suppose the fields are called select1 and select2. You could, in the action page, do something like listLen(form.select1)+listLen(form.select2). After all, a multiple selection will produce a comma-delimited list. 

            • 3. Re: Count <cfselect> values
              djkhalif Level 1

              BKBK,

               

              Thanks. I do have another question:

               

              How do I INSERT that list into SQL with same ID number for each item in list that is the same as the ID of the main table?

               

              Thanks,

               

              djkhalif

              • 4. Re: Count <cfselect> values
                Dan Bracuk Level 5

                <cfquery>

                insert into yourtable (id, otherfield)

                select 1, ''

                from some_small_table where 1 = 2

                <cfloop list = "#form.something#" index = "something">

                union

                select distinct #id_variable#, '#something#'

                from some_small_table

                 

                closing tags

                • 5. Re: Count <cfselect> values
                  djkhalif Level 1

                  Dan,

                   

                  This is my "INSERT" Code:

                   

                  <cfquery name="finalInspectionInsert" datasource="#REQUEST.datasource#">
                  INSERT INTO tbl_FinalInspAttr (DateInsp, INspby, WorkOrder, Total_Insp, Total_Defective, Total_Defects, Notes, Fitem, Model, Battery_Type, Cells, NoPlates)
                  VALUES('#Trim(FORM.DateInsp)#',
                    '#Trim(FORM.Inspby)#',
                          '#Trim(FORM.WorkOrder)#',
                          '#Trim(FORM.Total_Insp)#',
                          '#Trim(FORM.Total_Defective)#',
                          '#Trim(FORM.Total_Defects)#',
                          '#Trim(FORM.Notes)#',
                          '#Trim(FORM.Fitem)#',
                          '#Trim(FORM.Model)#',
                          '#Trim(FORM.Battery_Type)#',
                          '#Trim(FORM.Cells)#',
                          '#Trim(FORM.NoPlates)#')
                  </cfquery>

                  <!-- Insert Final Inspection into the Final Defects table -->
                  <cfquery name="finalDefects" datasource="#REQUEST.datasource#">
                  INSERT INTO tbl_Final_Defects (ID, Unit, DefectCode)
                  SELECT ID, '#Trim(FORM.Unit)#', '#Trim(FORM.DefectCode)#'
                  FROM tbl_FinalInspAttr
                  WHERE tbl_FinalInspAttr.WorkOrder = '#FORM.WorkOrder#'
                  <cfloop list  = "#FORM.UNIT#" index="j">
                  UNION
                  SELECT DISTINCT #UNIT#, '#j#'
                  FROM tbl_DefectTypes
                  </cfloop>
                  </cfquery>

                  • 6. Re: Count <cfselect> values
                    djkhalif Level 1

                    Dan,

                     

                    This is my Confirmation Page. The use will see this for proceding to action (INSERT) page.

                     

                    <cfquery name="rsItemLU" datasource="#REQUEST.datasource#">
                    SELECT     i.Item, i.Model, i.Model_Adder, i.Type, i.Plates, i.Cells
                    FROM         tlkp_Item AS i INNER JOIN
                                          tbl_Assembly_Production AS p ON p.Item = i.Item
                    WHERE     (p.WorkOrder = '#FORM.WorkOrder#')
                    GROUP BY i.Item, i.Model, i.Model_Adder, i.Type, i.Plates, i.Cells
                    </cfquery>
                    <cfset list1 = "#FORM.total_defective#">
                    <cfset list2 = "#FORM.total_defects#">
                    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
                    <html xmlns="http://www.w3.org/1999/xhtml"><!-- InstanceBegin template="/Templates/atDatTemplate.dwt.cfm" codeOutsideHTMLIsLocked="false" -->
                    <head>
                    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
                    <title><cfoutput>#REQUEST.companyName#</cfoutput></title>
                    <link rel="shortcut icon" href="/images/favicon.ico">
                    <link href="/finalCut.css" rel="stylesheet" type="text/css" />
                    <cfset copyRight = DateFormat(now(), 'yyyy')>
                    </head>

                    <body>

                    <!---<div id="header"></div> --->

                    <div id="contentContainer"><div id="topNav" style="font-family:'Trebuchet MS'; font-size:11px; padding-left:60px;"><cfinclude template="../includepages/topnavigation.cfm"/>  </div>
                    <div id="centerCol"><!-- InstanceBeginEditable name="editCentCol" -->


                    <b>Date Inspected:</b><cfoutput>#DateFormat(FORM.DateInsp, 'mm/dd/yyyy')#</cfoutput>
                    <br />
                    <b>Work Order:</b><cfoutput>#FORM.WorkOrder#</cfoutput>
                    <br />
                    <b>Inspected by:</b><cfoutput>#FORM.inspBy#</cfoutput>
                    <br />
                    <cfoutput>#FORM.inspectedByName#</cfoutput>
                    <br />
                    <b>Total Inspected:</b><cfoutput>#FORM.Total_Insp#</cfoutput>
                    <br />
                    <b>Total Defective:</b><cfoutput>#ListLen(FORM.total_defective)#</cfoutput>
                    <br />
                    <b>Total Defects:</b><cfoutput>#ListLen(FORM.total_defects)#</cfoutput>
                    <br />
                    <b>Comments / Notes:</b><cfoutput>#FORM.notes#</cfoutput>
                    <h3 style="font:Trebuchet MS; font-size:12px;">Battery Description</h3>
                    <cfif isDefined ("form.WorkOrder")>
                    <p style="font:Trebuchet MS; font-size:11px;">
                    <cfoutput query="rsItemLU">
                    <b>Item:</b> #Item#<br />
                    <b>Model:</b> #Model#<br />
                    <b>Adder:</b> #Model_Adder#<br />
                    <b>Type:</b> #Type#<br />
                    <b>Cells:</b> #Cells#<br />
                    <b>Plates:</b> #Plates#
                    </cfoutput></p>
                    </cfif>
                    <cfif listlen(list1) gt 0>
                    <cfloop from="1" to="#listlen(FORM.Total_Defective)#" index="j">
                    <cfoutput>#listGetAt(FORM.Total_Defective, j,",")# </cfoutput>
                    </cfloop>
                    <br />
                    <cfloop from="1" to="#listlen(FORM.Total_Defects)#" index="k">
                    <cfoutput>#listGetAt(FORM.Total_Defects, k,",")#</cfoutput>
                    </cfloop>
                    </cfif>

                    <cfform name="inputFinalInspection" action="../ActionPages/finalinspectionaction.cfm">
                    <cfinput name="Back" type="button" value="Back" onClick="history.go(-1)">
                      <cfinput name="submitValues" value="Submit" type="submit" />
                    <cfinput type="hidden" name="DateInsp" value="#DateFormat(FORM.DateInsp, 'mm/dd/yyyy')#">
                    <cfinput type="hidden" name="InspBy" value="#FORM.inspBy#">
                    <cfinput type="hidden" name="Total_Insp" value="#FORM.Total_Insp#">
                    <cfinput type="hidden" name="Total_Defective" value="#ListLen(FORM.total_defective)#">
                    <cfinput type="hidden" name="Total_Defects" value="#ListLen(FORM.total_defects)#">
                    <cfinput type="hidden" name="Notes" value="#FORM.notes#">
                    <cfinput type="hidden" name="FITEM" value="#rsItemLU.Item#">
                    <cfinput type="hidden" name="Model" value="#rsItemLU.Model#">
                    <cfinput type="hidden" name="ItemDesc" value="#rsItemLU.Model_Adder#">
                    <cfinput type="hidden" name="Battery_Type" value="#rsItemLU.Type#">
                    <cfinput type="hidden" name="Cells" value="#rsITemLU.Cells#">
                    <cfinput type="hidden" name="NoPlates" value="#rsItemLU.Plates#">
                    <cfinput type="hidden" name="Unit" value="#listGetAt(FORM.Total_Defective, j, ",")#">
                    <cfinput type="hidden" name="DefectCode" value="#listGetAt(FORM.Total_Defects, k,",")#">
                    </cfform>

                    • 7. Re: Count <cfselect> values
                      BKBK Adobe Community Professional & MVP

                      Are you storing the list FORM.total_defective into a database column? That is usually to be avoided.