Update #tablename# SET RowOrder = RowOrder + 1 WHERE RowOrder >= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder < #Evaluate ("FORM.OriginalOrder_" & "#id#")# Update #tablename# SET RowOrder = RowOrder - 1 WHERE RowOrder <= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder > #Evaluate("FORM.OriginalOrder_" & "#id#")# UPDATE #tablename# SET RowOrder = #Evaluate("FORM.SpecifiedOrder_" & "#id#")# WHERE ID=#id# [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Henweigh99 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> UPDATE #tablename# SET RowOrder = #newOrder# WHERE ID = #recordID# UPDATE nr SET nr.RowOrder = ( SELECT count(*) + #(maxSortNumber + 1)# FROM #tablename# WHERE ID NOT IN (#modifiedIdList#) AND RowOrder < nr.RowOrder ) FROM #tablename# AS nr WHERE ID NOT IN (#modifiedIdList#) [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => mxstu [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 12 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> SELECT RowOrder FROM #tableName# WHERE #uniqueID# = #id# Update #tablename# SET RowOrder = RowOrder + 1 WHERE RowOrder >= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder < #checkOrder.RowOrder# AND #UniqueID# IN (#ApplicableRecordList#) Update #tablename# SET RowOrder = RowOrder - 1 WHERE RowOrder <= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder[ref] > #checkOrder.RowOrder#[/ref] AND #UniqueID# IN (#ApplicableRecordList#) UPDATE #tablename# SET RowOrder = #Evaluate("FORM.SpecifiedOrder_" & "#id#")# WHERE #uniqueID#=#id# [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Henweigh99 [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 13 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Manual Sort Order Processing - Coldfusion - Advanced Techniques

Manual Sort Order Processing - Coldfusion - Advanced Techniques

Hello All, I have created a Netflix style queue system in one of my applications to help users sort records in a database. The basic form of the database structure is as follows (Structure is same for both Access and MSSQL versions): ID = Unique Identifier Field Name = Text name of this data RowOrder = Numeric identifier which we use to sort data. When displaying the data, I include a text field containing the value RowOrder called "SpecifiedOrder_#ID#". Next to the text field is a hidden field that contains the same value but is called "OriginalOrder_#ID#". When the form ...

  1. #1

    Default Manual Sort Order Processing

    Hello All,

    I have created a Netflix style queue system in one of my applications to help
    users sort records in a database.

    The basic form of the database structure is as follows (Structure is same for
    both Access and MSSQL versions):
    ID = Unique Identifier Field
    Name = Text name of this data
    RowOrder = Numeric identifier which we use to sort data.

    When displaying the data, I include a text field containing the value RowOrder
    called "SpecifiedOrder_#ID#". Next to the text field is a hidden field that
    contains the same value but is called "OriginalOrder_#ID#". When the form gets
    submitted, cold fusion checks to see if the OriginalOrder_#ID# matches the
    SpecifiedOrder_#ID#. If it does not, it means the user wishes to change the
    row order of this record. I then run some code to modify the row orders of all
    of the records around this record and adjust it's row order to the desired
    value. Unfortunately, this causes problems when more than one row order is
    adjusted in the form. Does anyone have any ideas on how I can improve upon
    this to sucessfully alow users to modify the row order?

    Row Order Processing Code:



    <!--- lets loop through all of the rows submitted and see if any of the row
    orders have been changed--->
    <CFLOOP list="#form.RecordID#" index="id">

    <!--- okay lets see if the new sort order is different than the old
    one--->
    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# IS
    #Evaluate("FORM.OriginalOrder_" & "#id#")#>
    <!--- okay, the fields match. lets go on--->
    <CFELSE>
    <!--- oh. they want to change the order on this one--->
    <!--- lets create a space for it--->
    <!--- lets see if we are moving it to a lower position or
    higher position--->
    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# LT
    #Evaluate("FORM.OriginalOrder_" & "#id#")#>
    <!--- if here. then we are moving it to a lower location--->
    <!--- lets move everything from the target location through the old location
    (not including) up one--->
    <CFQUERY datasource="#datasource#">
    Update #tablename#
    SET RowOrder = RowOrder + 1
    WHERE RowOrder >= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder <
    #Evaluate ("FORM.OriginalOrder_" & "#id#")#
    </cfquery>

    <!--- okay, now everything above and equal to the target
    location has been updated---->
    </CFIF>
    <!--- now the opposite. If the target location is higher than the old
    location--->
    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# GT
    #Evaluate("FORM.OriginalOrder_" & "#id#")#>
    <!--- if here. then we are moving it to a higher location--->
    <!--- lets move everything from the target location through the old
    location (not including) down one--->
    <CFQUERY datasource="#datasource#">
    Update #tablename#
    SET RowOrder = RowOrder - 1
    WHERE RowOrder <= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND
    RowOrder > #Evaluate("FORM.OriginalOrder_" & "#id#")#
    </cfquery>
    </cfif>
    <!--- okay, now everything below and equal to the target location has been
    updated---->
    <!--- done with updating all the other ones, lets update the specified one--->
    <cfquery datasource="#datasource#">
    UPDATE #tablename#
    SET RowOrder = #Evaluate("FORM.SpecifiedOrder_" & "#id#")#
    WHERE ID=#id#
    </cfquery>
    <!--- booya, touchdown. The roworder has been updated--->
    </CFIF>

    </CFLOOP>

    Henweigh99 Guest

  2. #2

    Default Re: Manual Sort Order Processing

    Anyone have any thoughts or suggestions?
    Henweigh99 Guest

  3. #3

    Default Re: Manual Sort Order Processing

    I have a few thoughts, but when you say "sort records" it raised a few
    questions. It looks like users can rank / prioritize all of the records in the
    entire table. Will more than one user be performing this sorting process at
    one time? Do the "RowOrder" numbers need to be consecutive?

    On a side note, if you're using MX , one of the improvements was that form
    values can now be accessed using array notation, making code a lot cleaner. So
    this code ...

    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# LT
    #Evaluate("FORM.OriginalOrder_" & "#id#")#>

    .... could become ...

    <!--- not tested --->
    <CFIF FORM["SpecifiedOrder_" & id) LT FORM["OriginalOrder_" & id)>



    mxstu Guest

  4. #4

    Default Re: Manual Sort Order Processing



    On 9/16/2005 7:44 PM mxstu intelligently wrote:
     

    Psssst....They could be in prior versions too ;-)

    --
    Tim Carley
    www.recfusion.com
    com
    Mountain Guest

  5. #5

    Default Re: Manual Sort Order Processing

    >Psssst....They could be in prior versions too ;-)

    LOL. You could be right! In my narrow world, the only versions of CF are the
    ones I've used. You're going to make me go look it up now, aren't you? :P

    mxstu Guest

  6. #6

    Default Re: Manual Sort Order Processing

    >Psssst....They could be in prior versions too ;-)

    I stand corrected. Wow. Apparently you can use this notation all the way back
    to the dark ages of CF 4.5 .. perhaps even further. (But let us not dwell on
    those dark days ;-)

    mxstu Guest

  7. #7

    Default Re: Manual Sort Order Processing

    Originally posted by: mxstu
    I have a few thoughts, but when you say "sort records" it raised a few
    questions. It looks like users can rank / prioritize all of the records in the
    entire table. Will more than one user be performing this sorting process at
    one time? Do the "RowOrder" numbers need to be consecutive?

    Thanks for the reply. You are correct. This function is designed to let users
    sort (prioritize) all of the records in an entire table all at once. I doubt
    that more than one user will be using this function at the same time, however,
    I think I should probably do a CFTRANSACTION anyway just to make sure.

    Also, thanks for the snippit of code... my fingers were getting tired of
    writing evaluate functions all the time. I had no idea you could evaluate form
    variables that way.

    Do you have any thoughts on how I can improve on my processing of the sort
    order? My method tends to create gaps and doesn't work very well if people are
    trying to change the sort order of multiple items.

    TIA.


    Henweigh99 Guest

  8. #8

    Default Re: Manual Sort Order Processing

    It depends on the amount of data, what values are being ranked and the sort
    order numbers. If the amount of data is small, the sort numbers do not have any
    special signifigance (ie. just 1,2,3,4) and the records have small
    descriptions, like "Department A, Department B, etc.." I would usually put the
    records in a list box and provide buttons for the users to move the records up
    and down to indicate rank/priority. Typically, users find it more intuitive
    than providing a separate sort order option for each row.

    When the form is submitted, I just loop through the list of record id's and
    set the row sort number equal to the list position. After the updates, I would
    run query to fix any duplicate sort numbers. Although, there shouldn't be any.
    This method requires updating every record in the table, which is not always
    the most efficient method, but with the correct locking it does ensure
    consistency.

    Would this approach work for your data? If not, do you use some sort of list
    to restrict the sort numbers entered? For example, if the table contained (4)
    records, the sort numbers allowed would be 1 through 4. How much data are you
    talking about and what kind of values?



    mxstu Guest

  9. #9

    Default Re: Manual Sort Order Processing

    mxstu,

    Your last post gave me an idea. I wonder if I can use some kind of Javascript
    function to allow "real-time" sorting of the data? If the user can use up/down
    arrows to sort the data the way they want, and then click a "save changes"
    button...perhaps I can then have cold fusion update each record with their
    correct sort order.

    Now the question is: Is there some kind of JavaScript out there that will let
    you re-order table cells right on the browser? It sounds complicated... but it
    might make for an easier user experience. What do you think?

    The JavaScript function(s) would need to do the following:

    1. Allow TR elements to be "moved" up or down using arrows.
    2. Once an item has been moved, the TR background color should probably change
    3. Once the user has everything arranged the way they want, they can click
    "save changes" which submits the form.
    4. Cold Fusion should be able to look at the form fields processed and somehow
    determine the order and write that order to the "sortOrder" column in the
    database (integer).

    To answer your other questions. The amount of data will vary. Sometimes
    there may be 4 rows. Other times there may be 30 or more. The problem with the
    processing logic lies when moving the position of multiple items. I wish I
    could see the processing code used on the Netflix site. I've never encountered
    a problem using it.

    Henweigh99 Guest

  10. #10

    Default Re: Manual Sort Order Processing

    I was thinking about that too. The basic concept is the same as what I
    described execept that the data is displayed in table rows instead of a list
    box. I like the idea, so long as the end product works with most browsers and
    the code doesn't end up being more complicated than it is worth. I don't know
    what netflix is using, and unfortunately it doesn't look like you can view this
    option without joining.

    As you said, I think this might be accomplished be with with javascript/css.
    As always the concern with javascript is that it may be disabled or the
    functions may not be supported with all browsers. However, off the top of my
    head, it may be as simple as swapping the visible text of two rows using
    "innerHTML" and "innerText" and updating the values of hidden form fields with
    javascript. And the same function could also use javascript to change the row
    color.



    mxstu Guest

  11. #11

    Default Re: Manual Sort Order Processing

    Yeah, I think you are right. JavaScript sounds like the way to go here. Since
    this portion of my application will be in a "logged-in" environment I will be
    able to force users to use a compatible browser. Hopefully I can find/create a
    script that will work with IE and FireFox browsers.

    I'll do some Google searching on this topic and post my findings here. If you
    like, you can subscribe to this thread and I'll post my results and test code
    if you are interested.

    Thanks for your time and help.

    Henweigh99 Guest

  12. #12

    Default Re: Manual Sort Order Processing

    I have subscribed and look forward to seeing what you come up with. I'm
    posting an example of how I might have handled your original sort form in case
    it is of interest to anyone. It is geared towards the sql server version and
    needs error handling, transactions, etc.. but demonstrates the idea.





    <cfparam name="form.totalRecords" default="0">

    <!--- verify that sort numbers are unique --->
    <cfset usedSortNumbers = "">
    <cfloop from="1" to="#form.totalRecords#" index="row">
    <cfset newOrder = val(form["SpecifiedOrder_"& row])>
    <cfif listFind(usedSortNumbers, newOrder) neq 0>
    <cfabort showerror="ERROR! Sort number [#newOrder#] was already selected">
    </cfif>
    <cfset usedSortNumbers = listAppend(usedSortNumbers, newOrder)>
    </cfloop>

    <!--- Assumes "SpecifiedOrder_N" is a unique and consective row number
    1,2,3,4, etc. --->
    <cfset sortNumberList = "">
    <cfset modifiedIdList = "">
    <cfloop from="1" to="#form.totalRecords#" index="row">
    <cfset recordID = val(form["ID_"& row])>
    <cfset newOrder = val(form["SpecifiedOrder_"& row])>

    <cfquery name="updateOrder" datasource="#datasource#">
    UPDATE #tablename#
    SET RowOrder = #newOrder#
    WHERE ID = #recordID#
    </cfquery>
    <cfset sortNumberList = listAppend(sortNumberList, newOrder)>
    <cfset modifiedIdList = listAppend(modifiedIdList, recordID)>
    </cfloop>
    <!--- get the maximum sort order number used --->
    <cfset maxSortNumber = arrayMax(listToArray(usedSortNumbers))>

    <!--- reset sort order number of duplicates --->
    <cfif listLen(modifiedIdList) gt 0>
    <cfquery name="updateOrder" datasource="#datasource#">
    UPDATE nr
    SET nr.RowOrder = ( SELECT count(*) + #(maxSortNumber + 1)#
    FROM #tablename#
    WHERE ID NOT IN (#modifiedIdList#) AND
    RowOrder < nr.RowOrder
    )
    FROM #tablename# AS nr
    WHERE ID NOT IN (#modifiedIdList#)
    </cfquery>
    </cfif>

    mxstu Guest

  13. #13

    Default Re: Manual Sort Order Processing

    I've played around with my original code and I think I've come up with a
    solution that works. This will allow someone to have a "netflix style" sort
    order queue for a list of records.

    The final code for my row order processings is as follows:

    <!--- first define a variable for all of the records we will be making changes
    to--->
    <CFSET ApplicableRecordList = AllRecords>

    <!--- lets loop through all of the rows submitted and see if any of the row
    orders have been changed--->
    <CFLOOP list="#AllRecords#" index="id">

    <!--- okay lets see if the new sort order is different than the old one--->
    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# NEQ
    #Evaluate("FORM.OriginalOrder_" & "#id#")#>

    <!--- lets get the actual sort order for this guy--->
    <cfquery datasource="#datasource#" name="checkOrder">
    SELECT RowOrder FROM #tableName# WHERE #uniqueID# = #id#
    </cfquery>

    <!--- lets create a space for it--->

    <!--- lets first see if we are moving it to a lower position or higher
    position--->
    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# LT #checkOrder.RowOrder#>

    <!--- if here. then we are moving it to a lower location--->

    <!--- lets move everything from the target location through the old
    location (not including) up one--->
    <CFQUERY datasource="#datasource#">
    Update #tablename#
    SET RowOrder = RowOrder + 1
    WHERE RowOrder >= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder
    < #checkOrder.RowOrder#
    AND #UniqueID# IN (#ApplicableRecordList#)
    </cfquery>
    <!--- okay, now everything above and equal to the target location has been
    updated---->

    </CFIF>

    <!--- now the opposite. If the target location is higher than the old
    location--->
    <CFIF #Evaluate("FORM.SpecifiedOrder_" & "#id#")# GT #checkOrder.RowOrder#>

    <!--- if here. then we are moving it to a higher location--->
    <!--- lets move everything from the target location through the old
    location (not including) down one--->
    <CFQUERY datasource="#datasource#">
    Update #tablename#
    SET RowOrder = RowOrder - 1
    WHERE RowOrder <= #Evaluate("FORM.SpecifiedOrder_" & "#id#")# AND RowOrder 
    AND #UniqueID# IN (#ApplicableRecordList#)
    </cfquery>
    </cfif>

    <!--- okay, now everything below and equal to the target location has been
    updated---->
    <!--- done with updating all the other ones, lets update the specified
    one--->
    <cfquery datasource="#datasource#">
    UPDATE #tablename#
    SET RowOrder = #Evaluate("FORM.SpecifiedOrder_" & "#id#")#
    WHERE #uniqueID#=#id#
    </cfquery>
    <!--- booya, touchdown. The roworder has been updated--->

    </CFIF>

    </CFLOOP>

    Henweigh99 Guest

Similar Threads

  1. DataGrid manual sort and refresh datasource
    By TiranaCity in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: August 19th, 04:23 PM
  2. Using Database for Website & Order processing software
    By StylusDesigns in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 14th, 02:05 PM
  3. PHP Sort order definitions
    By Csaba Gabor in forum PHP Development
    Replies: 0
    Last Post: May 3rd, 11:57 AM
  4. Sort order
    By hobbit in forum IBM DB2
    Replies: 4
    Last Post: July 11th, 12:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •