We help IT Professionals succeed at work.

How can I add an up/down button to rearrange the list in a form select

312 Views
Last Modified: 2013-12-24
I have a form that has two selects.  I have added a javascript that will allow me to move up or down the field I select.  The problem I'm having is that I don't know how to save the list in its new order.  When I hit the save button, it saves it back in the old order.  The select I need to be able to reorder is the rptFlds.  I have attached the code of my form.  Any help would be great since I have a deadline coming up to get this done.  Thank you!!!
<script>
function listbox_move(listID, direction) {   
  
    var listbox = document.getElementById(listID);   
    var selIndex = listbox.selectedIndex;   
  
    if(-1 == selIndex) {   
        alert("Please select an option to move.");   
        return;   
    }   
  
    var increment = -1;   
    if(direction == 'up')   
        increment = -1;   
    else  
        increment = 1;   
  
    if((selIndex + increment) < 0 ||   
        (selIndex + increment) > (listbox.options.length-1)) {   
        return;   
    }   
  
    var selValue = listbox.options[selIndex].value;   
    var selText = listbox.options[selIndex].text;   
    listbox.options[selIndex].value = listbox.options[selIndex + increment].value   
    listbox.options[selIndex].text = listbox.options[selIndex + increment].text   
  
    listbox.options[selIndex + increment].value = selValue;   
    listbox.options[selIndex + increment].text = selText;   
  
    listbox.selectedIndex = selIndex + increment;   
}  
</script>
 
<cfset #pageTitle# = "Administration">
<cfset #curPage# = 77>
<cfset #custContent# = "AP1">
 
<cfset #LbxName# = RemoveChars(#session.lbxformattable#,1,8)>
<cfif #Session.lbxtype# is "Wholesale">
<cfset #RptTableName# = "lbx_rpt_#LbxName#">
<cfelse>
<cfset #RptTableName# = "rlb_rpt_#LbxName#">
</cfif>
 
<cfquery name = "checkforrpttable" datasource="#Application.dsname#">
  SELECT * from sysobjects where name = <cfqueryparam value="#RptTableName#">
</cfquery>
 
<cfif #checkforrpttable.Recordcount# is 0>
  <cfquery name = "createrpttable" datasource = "#Application.dsname#">
     SELECT * INTO #RptTableName# FROM #SESSION.LBXFORMATTABLE#
  </cfquery>
<cfelse>
 
      <cfquery name = "getfmtflds" datasource = "#Application.dsname#">
         SELECT * FROM #SESSION.LBXFORMATTABLE# where layout = 'AP'
      </cfquery>       
        
      <cfloop query="getfmtflds">
          <cfquery name = "updaterpttable" datasource= "#Application.dsname#">
              UPDATE #RptTableName#  SET fld_head1 = '#getfmtflds.fld_head1#', fld_head2 = '#getfmtflds.fld_head2#', fld_prompt = '#getfmtflds.fld_prompt#'
                   WHERE (layout = <cfqueryparam value="AP">) AND
                         (fld_order = #getfmtflds.fld_order#)
          </cfquery>
      </cfloop>
        
  <cfquery name = "comparefmt" datasource = "#Application.dsname#">
      SELECT * FROM #Session.LbxFormatTable# T1 
	        WHERE (T1.layout = <cfqueryparam value="AP">) AND 
			NOT EXISTS(SELECT * FROM #RptTableName# T2 where (T1.fld_order = T2.fld_order) and layout= <cfqueryparam value="AP">)
  </cfquery>
  <cfif #comparefmt.RecordCount# gt 0>
     <cfquery name = "insertintoReportTable" datasource = "#Application.dsname#">
	     INSERT INTO #RptTableName#(layout,fld_order,fld_name,fld_otype,fld_len,fld_dec,fld_prompt,fld_head1,fld_head2,can_modify,can_search,at_info,at_caption,at_trans,at_chkbody,at_invbody,at_cpnbody,at_support,at_report,rollup,total)
    	   (SELECT * FROM 
			    #Session.LbxFormatTable# T1 
				WHERE (T1.layout = <cfqueryparam value="AP">) AND 
				NOT EXISTS(SELECT * FROM #RptTableName# T2 where (T1.fld_order = T2.fld_order) and layout= <cfqueryparam value="AP">))
	 </cfquery>
  <cfelse>
	  <cfquery name = "comparerpt" datasource = "#Application.dsname#">
    	  SELECT * FROM #RptTableName# T1 
	    	    WHERE (T1.layout = <cfqueryparam value="AP">) AND 
				NOT EXISTS(SELECT * FROM #Session.LbxFormatTable# T2 where (T1.fld_order = T2.fld_order)and layout= <cfqueryparam value="AP">)
	  </cfquery>
		<cfset #Session.report# = 1>
       
  	<cfif #comparerpt.RecordCount# gt 0>
	
    	<cfloop query = "comparerpt">
		<cfquery name="Del" datasource="#Application.dsname#">
	   		DELETE  FROM #RPTTableName# where (fld_order = #comparerpt.fld_order#) and (layout = <cfqueryparam value="AP">)
		</cfquery>
		</cfloop>
	</cfif>
  </cfif>
  
  <cfquery name ="checkforcan_search" datasource="#Application.dsname#">
     SELECT T2.fld_name,T2.layout,T2.fld_order,T2.can_search FROM #RptTableName# T1,#Session.lbxFormatTable# T2 WHERE 
	    (T1.layout = T2.layout) And (T1.layout = <cfqueryparam value="AP">) AND
		(T2.can_search <> T1.can_search) AND (T1.fld_name = T2.fld_name) 
		ORDER BY T1.fld_order
  </cfquery>
  
  <cfif #checkforcan_search.RecordCount# gt 0>
    <cfloop query = "checkforcan_search">
	
   <cfquery name = "updaterpttable" datasource= "#Application.dsname#">
            UPDATE #RptTableName#  SET can_search = #checkforcan_search.can_search#
                 WHERE (layout = <cfqueryparam value="AP">) AND
     		           (fld_name = '#checkforcan_search.fld_name#')
	</cfquery>
	</cfloop>
  </cfif>
</cfif>
 
 
<cfif #ParameterExists(form.add)# AND #Len(Trim(form.add))# gt 0>
<cfif isdefined("form.Flds")>
  <cfset #form.reportFields#="#form.reportFields#,#form.flds#">
</cfif>
 </cfif>
<cfif #ParameterExists(form.addAll)# AND #Len(Trim(form.addAll))# gt 0>
  <cfquery name="getAll" datasource="#Application.dsname#">
	SELECT fld_name, layout, can_search FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> AND can_search= <cfqueryparam value="1">
  </cfquery>
  <cfset #lcv# = 1>
  <cfloop query="getAll">
    <cfquery name="updateAll" datasource="#Application.dsname#">
	  UPDATE #RptTableName# SET at_report=#lcv# WHERE layout= <cfqueryparam value="AP"> AND fld_name='#getAll.fld_name#'
    </cfquery>
    <cfset #lcv# = #lcv# + 1>
  </cfloop>
</cfif>
 
<cfif #ParameterExists(form.remove)# AND #Len(Trim(form.remove))# gt 0>
<cfif isdefined("form.rptFlds")>
  <cfset #tmpList# = #form.reportFields#>
  <cfset #tmpFindVal# = #ListFindNoCase(tmpList, form.rptFlds, ",")#>
  <cfif #tmpFindVal# gt 0>
    <cfset #form.reportFields# = #ListDeleteAt(tmpList, tmpFindVal, ",")#>
    <cfquery name="remove" datasource="#Application.dsname#">
	  UPDATE #RptTableName# SET at_report=0 WHERE layout= <cfqueryparam value="AP">
    </cfquery>
	 </cfif> 		
  </cfif>
</cfif>
 
<cfif #ParameterExists(form.removeAll)# AND #Len(Trim(form.removeAll))# gt 0>
  <cfset #form.reportFields# = "">
  <cfquery name="removeAll" datasource="#Application.dsname#">
	UPDATE #RptTableName# SET at_report=0 WHERE layout= <cfqueryparam value="AP">
  </cfquery>
</cfif>
 
<cfif (#ParameterExists(form.finished)# AND #Len(Trim(form.finished))# gt 0) OR
      (#ParameterExists(form.add)# AND #Len(Trim(form.add))# gt 0) OR
      (#ParameterExists(form.remove)# AND #Len(Trim(form.remove))# gt 0) OR
      (#ParameterExists(form.removeAll)# AND #Len(Trim(form.removeAll))# gt 0)>
 
  <cfset #lcv# = 0>
  <cfloop index="fldName" list="#form.reportFields#">
    <cfoutput>
      <cfset #lcv# = #lcv# + 1>
      <cfquery name="addRptFields" datasource="#application.dsname#">
			UPDATE #RptTableName# SET at_report=#lcv# WHERE fld_name= <cfqueryparam value="#fldName#"> AND layout= <cfqueryparam value="AP">
      </cfquery>
    </cfoutput>
  </cfloop>
</cfif>
 
<cfif #ParameterExists(form.finished)# AND #Len(Trim(form.finished))# gt 0>
  <cfif #ParameterExists(form.reportFields)# AND #Len(Trim(form.reportFields))# gt 0>
    <cfset #tmpUrl# = "custRpt.cfm?errMsg=Custom%20report%20updated">
  <cfelse>
    <cfset #tmpUrl# = "custRpt.cfm?errMsg=Unable%20to%20update%20custom%20report">
  </cfif>
  <cflocation url="#tmpUrl#" addtoken="no">
<cfelse>
  <cfinclude template="header-main.cfm">
 
  <cfquery name="getMaxFields" datasource="#Application.dsname#">
	SELECT fld_name, layout, can_search FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> AND can_search= <cfqueryparam value="1">
  </cfquery>
 
  <cfquery name="getFields" datasource="#application.dsname#">
	SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and can_search= <cfqueryparam value="1"> AND at_report= <cfqueryparam value="0"> ORDER BY fld_name
  </cfquery>
 
  <cfquery name="getRptFields" datasource="#application.dsname#">
	SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY at_report
  </cfquery>
 
  <p><br></p>
  <cfif #ParameterExists(URL.errMsg)# AND #Len(Trim(URL.errMsg))# gt 0>
    <p><div align="center"><font color="#Application.emphtext#"><b><cfoutput>#URL.errMsg#</cfoutput></b></font></div></p>
  </cfif>
  <p><br></p>
 
  <CFSET tmpReportFields = #ValueList(getRptFields.fld_name)#>
  <table>
  	<tr>
    	<td>
        <div> 
        <b class="round"> 
        <b class="round1"><b></b></b>
        <b class="round2"><b></b></b>
        <b class="round3"></b>
        <b class="round4"></b>
        <b class="round5"></b> 
        </b> <div class="round_content">	  
  <table>
    <form method="post" action="custRpt.cfm">
      <input type="hidden" name="reportFields" value="<cfoutput>#tmpReportFields#</cfoutput>">
      <tr>
        <td><span style="color: #FFFFFF"><b>Available Fields:</b></span></td>
        <td>&nbsp;</td>
        <td><span style="color: #FFFFFF"><b>Selected Fields:</b></span></td>
      </tr>
      <tr>
        <td valign="top">
          <select name="flds" size="<cfoutput>#getMaxFields.RecordCount#</cfoutput>">
            <cfif #getFields.RecordCount# is 0>
              <option value="">-- All Selected --</option>
            <cfelse>
              <cfoutput query="getFields">
                <option value="#getFields.fld_name#">#getFields.fld_head1#&nbsp;#getFields.fld_head2#</option>
              </cfoutput>
            </cfif>
          </select>        </td>
        <td valign="top">
          <p><input type="submit" name="add" value="     >     "></p>
          <p><input type="submit" name="addAll" value="    >>    "></p>
          <p><input type="submit" name="remove" value="     <     "></p>
          <p><input type="submit" name="removeall" value="    <<    "></p>        
          <p><input type="button" name="up" onclick="listbox_move('rptFlds', 'up'); " value="    Up    " /></p>
          <p><input type="button" name="down" onclick="listbox_move('rptFlds', 'down'); " value="  Down  " /></p>
          <p><input type="submit" name="finished" value="  Save   "></p>        
        </td>
        <td valign="top">
          <select id="rptFlds" name="rptFlds" size="<cfoutput>#getMaxFields.RecordCount#</cfoutput>">
            <cfif #getRptFields.RecordCount# is 0>
              <option value="">-- None Selected --</option>
            <cfelse>
              <cfoutput query="getRptFields">
                <option value="#getRptFields.fld_name#">#getRptFields.fld_head1#&nbsp;#getRptFields.fld_head2#</option>
              </cfoutput>
            </cfif>
          </select>
        </td>
      </tr>
    </form>
  </table>
            </div>
            <b class="round"> 
            <b class="round5"></b>
            <b class="round4"></b>
            <b class="round3"></b>
            <b class="round2"><b></b></b>
            <b class="round1"><b></b></b> 
            </b> 
            </div>         
  		</td>
	</tr>
</table>
 
  <cfinclude template="footer.cfm">
</cfif>

Open in new window

Comment
Watch Question

erikTsomikSystem Architect, CF programmer

Commented:
in your query you need also Update the ordering column and in the main query then order by that column, that will keep your drop down ordered

Author

Commented:
erikTsomik, thanks for the quick response.  Is there any way you can give me an example.  I'm new to coldfusion and I'm a little lost at this point.   thanks.
erikTsomikSystem Architect, CF programmer

Commented:
OK.
it all of your queries i do not see order by clause

SELECT * from sysobjects where name = <cfqueryparam value="#RptTableName#">

Order by , you need to order by column so you will see the effect of you movement.

I have done the similar thing just by using coldFusion. Where I was selecting items from i drop down and moved them ito the other . Without using javascript
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hey pimpsuit, I get this error when I tried to change the query.

Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
The error occurred on line 195.

Commented:
Try getting rid of the cfqueryparam tag just to see if you can get it working:

SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY "#form.rptFlds#"


erikTsomikSystem Architect, CF programmer

Commented:
change to this
SELECT * FROM #RptTableName# WHERE layout= <cfqueryparam value="AP"> and at_report> <cfqueryparam value="0"> ORDER BY #form.rptFlds#

Author

Commented:
None of these have worked.  Very frustrating when you're a newbie(me) and can't figure it out even with help from you guys! right now my 2 selects work with moving fields from the left to the right.  The javascript I added to the top of the code works with the up/down buttong i added where it will move the fields up and down, it's the saving that's killing me.  

Commented:
Are you getting an error with the dynamic ORDER BY clause, and, if so, what is it?

Author

Commented:
well, no.  it's just doing what it was doing before.  the javascript moves the field up or down to where i want it, but when I click save, it puts the field back in the original position.  shouldn't I need a new query to handle the save?

Commented:
I think that you should first determine if the right value is in fact being passed. Somewhere after the <cfparam name="form.rptFlds" default="at_report"> I suggested earlier you should temporarily display the value of form.rptFlds via <cfoutput>#form.rptFlds#</cfoutput>. When you click your Save button, the value in your select box will be displayed. Try that and see what happens.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.