Thanks for the explanation and the code, Bobby! Much appreciated!
Rick -----Original Message----- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 13, 2007 8:35 PM To: CF-Talk Subject: RE: How to re-order items? at the bottom of my forms, I list all the records with [edit] and [delete] links for each. If there is a sortorder on the records then I just put an up and down arrow at the beginning of each record with irl variables for the recordid, the direction (up/down) and somehting else just to look for to know that the user wants to sort something. So a link might look like... http://thissite.com/?sort=true&recordid=5&direction=up which means find record number 5 and move it UP Here is the code behind it... <!--- i already have the total number of records in the table by this point because I query it for all the records to display in the list so from now on, a query of queries can be used ---> <!--- we'll call the initial query "AllRecords" ---> <cfquery name="AllRecords"...> select * from tablename order by sortid </cfquery> <!--- first get the current sortorder number for the record. This serves 2 purposes... it gets the number we will work with and verifies that the record exists ---> <cfquery name="original" .... > select sortid from tablename where recordid = #val(url.recordid)# </cfquery> <!--- if url.sort exists, then the user wants to change the order of the records ---> <cfif isdefined('url.sort')> <cfparam name="url.recordid" default="0" /> <cfparam name="url.direction" default="down" /> <!--- Find out where the record is in the order of things ---> <!--- if it is the last record, it cannot be moved down, if it is the first record, it cannot be moved up ---> <cfquery dbtype="query" name="original"> select recordid, sortid from AllRecords where recordid = #val(url.recordid)# </cfquery> <!--- if no record was found, go back to the page ---> <cfif not val(original.recordcount)> <cflocation url="index.cfm" addtoken="no" /> <cfelse> <!--- the record is valid so find out if it is being moved up or down and whether or not that is possible ---> <cfif url.direction is 'up'> <!--- user wants to move the record UP ---> <cfif original.sortid is 1> <!--- the record is already as high as it can go if its' sortid is 1 so just return to the page ---> <cflocation url="index.cfm" addtoken="no" /> <cfelse> <!--- it is ok to move this record UP 1 level so run two queries to swap the positions ---> <!--- first, move the record that is currently in the desired position DOWN by giving it a higher number ---> <cfquery datasource="#application.maindsn#"> update tablename set sortid = sortid+1 where sortid = #val(original.sortid -1)# </cfquery> <!--- next, lower the number on the selected record to move it UP ---> <cfquery datasource="#application.maindsn#"> update tablename set sortid = sortid-1 where recordid = #val(url.recordid)# </cfquery> <cflocation url="index.cfm" addtoken="no" /> </cfif> <cfelseif url.direction is 'down'> <!--- user wants to move the record down ---> <cfif original.sortid is AllRecords.recordcount> <!--- this is already the last record and cant be moved down anymore ---> <cflocation url="index.cfm" addtoken="no" /> <cfelse> <!---- it is ok to move the record down level ---> <!--- first, move the record that is currently in the desired position UP by giving it a lower number ---> <cfquery datasource="#application.maindsn#"> update tablename set sortid = sortid-1 where sortid = #val(original.sortid +1)# </cfquery> <!--- next, increase the number on the selected record to move it DOWN ---> <cfquery datasource="#application.maindsn#"> update tablename set sortid = sortid+1 where recordid = #val(url.recordid)# </cfquery> <cflocation url="index.cfm" addtoken="no" /> </cfif> </cfif> </cfif> </cfif> That is basically it. Just remember to reorder when deleting a record so everything stays in sequence. Thats easy enough though. First delete the desired record... then get all the remaing records ordered by the sortorder then loop over the results updating each records sortorder with #currentrow# Someone mentioned the trouble of moving record number 50 to the first position too but realistically... how often would that happen? I've found that people would much rather deal with the up and down arrows than a number. If this is all jumbled ill send you a template offlist. Cheers ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 & MX7 integration & create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269752 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
