ColdFusion in Context: Sequence Slider

Often you have lists that you need to sequence by something other than the alphabet. Here's a tip that lets you use a select box to specify where you want to place an item in the overall sequence.

Create a Table

Create a table named OrdItem. Give it fields Item (text) and ListOrder (numeric).

Organize

Put this code in work.cfm to organize most of the work. Because setting the sequence is not trivial, let this page organize the posting of both Add and Edit actions to the database. If someone has added or edited an item, include a page to set the sequence. After adding, return to the input screen to let the user add one item after another in rapid succession. After editing, stay here so the user can pick another item to edit. If the user asked to delete an item, make the user confirm that decision. Pass the Item and the decision (yes or no) to the delete code. Finally, list the Items. 

<cfparam name="Mode" default="">
<cfif (Mode is "Add") or (Mode is "Edit")>
  <cfinclude template="sequence.cfm">
  <cfif Mode is "Add">
    <cfinclude template="add.cfm">
    <cfinclude template="input.cfm">
    <cfabort>
  <cfelse>
    <cfinclude template="edit.cfm">
    <!--- (Stay here; show the list) --->
  </cfif>
<cfelseif Mode is "Delete">
  <form action="delete.cfm" method="post">
  <input type="hidden" name="Item"
  value=<cfoutput>"#urlDecode(url.Item)#"</cfoutput>>
  Are you sure you want to delete
  <cfoutput>#urlDecode(url.Item)#</cfoutput>?
  <input type="submit" name="Mode" value="Yes">
  <input type="submit" name="Mode" value="No">
  </form>
  <cfabort>
</cfif>

<cfinclude template="list.cfm">

Delete

Put this code in delete.cfm to delete the Item if the user is sure it should be deleted. 

<cfif Mode is "Yes">
  <cfquery name="deleteItem" datasource="context">
  delete * from OrdItem
  where Item = '#form.Item#'
  </cfquery>
</cfif>
<cfinclude template="work.cfm">

Get Input

Put this code in input.cfm to accept input. The same input screen is used for both add and edit. To tell it which one it should do, this code uses the variable Mode. Because the item list will be needed in order to set the sequence, include a list query for later use.

You need an item query to fill the form. If you're editing, you want to bring back the data for the row you want to edit. If you're adding records, you want an item query OF THE SAME NAME to bring back nothing; so, set an impossible condition in the where clause and add an empty row. 

<!--- Set defaults --->
<cfparam Name="Mode" default="">
<cfinclude template="ItemListQuery.cfm">

<!--- Query to fill the form --->
<cfif Mode is "Edit">
  <cfquery name="ItemGet" datasource="context">
  select * from OrdItem
  where Item = '#Item#'
  </cfquery>
<cfelse>
  <!--- Mode is assumed to be "Add" --->
  <cfquery name="ItemGet" datasource="context">
  select * from OrdItem
  where 1 = 0
  </cfquery>
  <cfset dummy=queryAddRow(ItemGet)>
</cfif>

For the form itself, consider that you'll need a text field for the item name and a select control for the sequence. The select box should let you specify which item this item must follow. To make this item the first item, it should follow none. However, "none" looks very odd in a select box; "top" is less correct but more acceptable. Select "top" to move this item to the top.

For each item in the list, see if the value the item had when you came here matches the item for which you're building an option in the list. You won't get a match on Add - "top" will be the default for Add - but for Edit, one of these items should match and should therefore have the attribute "selected" in the option tag. 

<form name="Change" action="work.cfm" method="post">
Item: <input name="Item" type="text"
value=<cfoutput>"#ItemGet.Item#"</cfoutput>><br>
Immediately follow...
<select name="MySpot">
<option value="0">top
<cfoutput query=ItemList>
<cfif ItemList.Item is not ItemGet.Item>
  <cfset chosen="">
<cfelse>
  <cfset chosen="selected">
</cfif>
<option value="#ItemList.CurrentRow#" #chosen#>#Item#
</cfoutput>
</select>

Time to set controls and hidden values. To remember what the item was before you messed with it, store it in OldItem. If you're adding, the first submit button will say "Add". Otherwise, it will say "Edit". The second submit button says "List" and corresponds to a "cancel" operation. Items contains the entire list of items, separated by ^. Numbers contains the entire list of sequence numbers (ListOrder numbers in the table), separated by ^. Close the form. 

<input name="OldItem" type="hidden" 
value=<cfoutput>"#ItemGet.Item#"</cfoutput>>
<input name="Mode" type="submit"
value="<cfoutput>#Mode#"</cfoutput>>
<input name="Mode" type="submit" value="List">
<input name="Items" type="hidden" value=
<cfoutput>"#valueList(ItemList.Item, '^')#"</cfoutput>>
<input name="Numbers" type="hidden" value=
<cfoutput>"#valueList(ItemList.ListOrder, '^')#"</cfoutput>>
</form>

List Query

Put this code in ItemListQuery.cfm. There's not much to it, but it gets used more than once. 

<cfquery name="ItemList" datasource="context">
select * from OrdItem
order by ListOrder
</cfquery>

Sequence

Put this code in sequence.cfm. Here's the main event; the reason for the rest of this work. It begins by setting a one-time loop of convenience. Whenever you wish to skip to the bottom of the loop, just cfbreak. MySpot is the value from the select box of input.cfm; Items is the list of items, and Numbers is the list of ListOrder values. If the select box is NOT on the topmost item (0, "top"), and the user didn't move the select box to reposition the item, then issue it its original ListOrder number and break.

Otherwise, determine Item and ListOrder for the items surrounding the position where you want this item to wind up. Usually, you can infer the previous item from the select box's current position. If you're at the top, use 0 and "_top" as the previous item. Usually, you can infer the next item by adding one to the select box's current position. If you're at the bottom, use 1 and "_bottom" as the next item. (ListOrder numbers are decimals between zero and one so that you can almost always squeeze in between two of them.) You won't actually use the names that begin with underscores; they just help you remember what's going on. 

<cfloop from="1" to="1" index="dummy">
<cfparam name="MySpot" default="">
<cfif (MySpot gt 0)
  and (listGetAt(Items,MySpot,"^") is form.Item)>
  <cfset NewListOrd=listGetAt(Numbers,MySpot,"^")>
  <cfbreak>
</cfif>
<cfif MySpot ge 1>
  <cfset PrevItem=listGetAt(Items,MySpot,"^")>
  <cfset PrevNr=listGetAt(Numbers,MySpot,"^")>
<cfelse>
  <cfset PrevItem="_top">
  <cfset PrevNr=0>
</cfif>
<cfif MySpot+1 le listLen(Items,"^")>
  <cfset NextItem=listGetAt(Items,MySpot+1,"^")>
  <cfset NextNr=listGetAt(Numbers,MySpot+1,"^")>
<cfelse>
  <cfset NextItem="_bottom">
  <cfset NextNr=1>
</cfif>

Once you've determined which neighbors should bracket the item, take the difference of their ListOrder values to see how far apart they are. If you have to squeeze between items that are very close together, you might eventually exceed the accuracy of the database. To avoid this, if the item are very close together, include ordfix.cfm to do a massive query to spread all the items out before continuing. Ordfix.cfm will also find out the neighbors' new ListOrder values and their difference so the rest of the code won't have to change. If ordfix.cfm is called even once in six months, I'll be surprised; but it's nice to know you can handle the situation.

Split the difference, add it to the ListOrder value of the previous item, and assign it as the new ListOrder value for the item you just added or edited. Close the loop. 

<cfset Diff=NextNr-PrevNr>
<cfif Diff lt 0.0001>
  <cfinclude template="ordfix.cfm">
</cfif>
<cfset NewListOrd=PrevNr+(Diff/2)>
</cfloop>

Add

Put this code in add.cfm to post a new item to the database. If the item already exists, stop. Otherwise, insert it. Normally, we'd use the value (val) function to protect the database from number entries, but the user didn't get to enter the number directly in this case. 

<cfquery name="ItemGet" datasource="context">
select * from OrdItem
where Item = '#form.Item#'
</cfquery>
<cfif ItemGet.recordCount>
  The item already exists in the database;
  no action was taken.
<cfelse>
  <cfquery name="ItemAdd" datasource="context">
  insert into OrdItem
  (Item, ListOrder)
  values
  ('#form.Item#', #NewListOrd#)
  </cfquery>
  <cfoutput>#form.Item#</cfoutput>
  has been sent to the database
</cfif>

Edit

Put this code in edit.cfm to post a change to the database. If we've changed the item name, and the new name already exists, stop. Otherwise, update the name and ListOrder. (Yes, you usually only need to change one or the other, but that's OK.) 

<cfif form.OldItem is not form.Item>
  <cfquery name="ItemGet" datasource="context">
  select * from OrdItem
  where Item = '#form.Item#'
  </cfquery>
  <cfif ItemGet.recordCount>
    The item already exists in the database;
    no action was taken.
    Press your back button to continue.
    <cfabort>
  </cfif>
</cfif>
<cfquery name="Change" datasource="context">
update OrdItem set
Item = '#form.Item#',
ListOrder = #NewListOrd#
where Item = '#form.OldItem#'
</cfquery>

Fix

Put this code in ordfix.cfm to handle the pathological case where the table has been changed so often that items are getting too close together. Start with a list query. Then add one to the number of rows and divide the result into 1 to determine the multiple you want to use to spread the rows evenly: Spread. Loop over the list query and keep track of the row you're on. For each item in the list, multiply the row by the Spread and set the result as the new ListOrder. 

<cfinclude template="ItemListQuery">

<cfset Spread=1/(itemList.recordcount+1)>
<cfset Row=0>

<cfloop query="itemList">
<cfset Row=Row+1>
<cfset NewSeq=Spread*Row>
<cfquery name="ordSpread" datasource="context">
update OrdItem
set ListOrder = #NewSeq#
where Item = '#itemList.Item#'
</cfquery>
</cfloop>

Now you have to cope with unfinished business. Spreading out the items changed their ListOrder, and you need those values for the previous and next items in order to give the item to be moved a new ListOrder. Therefore, if the previous item's ListOrder isn't zero - your item won't be at the top, find its new value. If the next item's ListOrder isn't one - your item won't be at the bottom - find its new value. Get their new difference, and let the code in sequence.cfm pick up where it left off. 

<cfif PrevNr is not 0>
  <cfquery name="getOne" datasource="context">
  select ListOrder from OrdItem
  where Item = '#PrevItem#'
  </cfquery>
  <cfset PrevNr = getOne.ListOrder>
</cfif>
<cfif NextNr is not 1>
  <cfquery name="getOne" datasource="context">
  select ListOrder from OrdItem
  where Item = '#NextItem#'
  </cfquery>
  <cfset NextNr = getOne.ListOrder>
</cfif>
<cfset Diff=NextNr-PrevNr>

List and Pick

Put this code in list.cfm to let you add, edit, and delete items. Begin by listing the items (since you've probably updated the table and need a fresh list even if you've run this query before). Head straight to input.cfm with Mode="Add" if you need to add something. Put edit and delete controls in a list of items for display. If you want to edit something, head to input.cfm with Mode="Add" and the Item encoded appropriately so nothing will be lost in the URL. If you want to delete, pass Mode="Delete" and the encoded Item to work.cfm to let it handle the details. 

<!--- Fill and display the list and controls --->
<cfinclude template="ItemListQuery.cfm">
<a href="input.cfm?Mode=Add">Add</a>
<table border="1">	
<tr><td>Edit</td><td>Item</td><td>Delete</td></tr>
<cfoutput query="ItemList">
<tr><td><a href=
"input.cfm?Mode=Edit&Item=#urlEncodedFormat(Item)#">
Edit</a></td>
<td>#Item#</td>
<td><a href=
"work.cfm?Mode=Delete&Item=#urlEncodedFormat(Item)#">
Delete</a></td></tr>
</cfoutput>
</table>

Try It

Browse work.cfm. Add a few items, then rearrange them and give them new names. This technique is good for as large a list as you're ever likely to set up on a non-alphabetical basis. =Marty=