Anyone implemented a drag and drop or up/down sort order grid?

Scriptcase 9 General
#1

Hi. My client does not want to worry about seeing or changing a sort order field I have on a certain grid. I was thinking of making buttons in each line, like up/down. Then have it change the order values in all records - to change the sortorder field values. Anyone done this in SC? I could not find any example of this so far here.
Thanks,
Jamie

#2

OK. I did it. Here is a roadmap for anyone interested:

This will work for any grid, just make sure you have a field (can hidden if desired so they dont even see these values) called SortOrder. The two buttons in a field on the grid , created below, are Up and Down. When you are the top of the grid, I show Up but has no effect. Same when at bottom, where Down has no effect. Note that I am showing all records in my grid. Not sure what happens if you page - may of may not work. Have no time to think that thru.

In the grid’s onRecord add some up/down buttons:
{updown} = “<a href=’…/grid_dinergrade_session/grid_dinergrade_session.php?rid=”.{ID}."&rsortorder=".{SortOrder}."&rdirection=up’ target=’_self’ ><button class=‘upbtn’ >Up</button></a>";
{updown} .= “<a href=’…/grid_dinergrade_session/grid_dinergrade_session.php?rid=”.{ID}."&rsortorder=".{SortOrder}."&rdirection=down’ target=’_self’ ><button class=‘downbtn’ >Down</button></a><BR>";

In onScriptInit:

if (isset($_GET[‘rid’]) AND isset($_GET[‘rsortorder’]) AND isset($_GET[‘rdirection’]) )
{
$the_rid = $_GET[‘rid’];
$the_rsortorder = $_GET[‘rsortorder’];
$the_rdirection = $_GET[‘rdirection’];

// figure number of existing records
$the_count = 0;
$check_sql = "SELECT COUNT(*)"
	. " FROM dinergrade_session"
	. " WHERE SiteID = '" . [glo_site_id] . "'";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]}))     // Row found
{
	$the_count = {rs[0][0]};
}
else     // No row found
{
	$the_count = 0;
}



// now see if there is an up or down to move to
if (($the_rsortorder &gt; 1) AND ($the_rdirection == 'up'))
{
	// process an up swap
	$the_process = "OKup";
	$new_rsortorder = $the_rsortorder - 1;
	// change the target record first
		// SQL statement parameters
		$update_table  = 'dinergrade_session';      // Table name
		$update_where  = "SortOrder = '".$new_rsortorder."' AND SiteID = '".[glo_site_id]."' "; // Where clause
		$update_fields = array(   // Field list, add as many as needed
 			"SortOrder = '".$the_rsortorder."'",
		);
		// Update record
		$update_sql = 'UPDATE ' . $update_table
			. ' SET '   . implode(', ', $update_fields)
			. ' WHERE ' . $update_where;
		sc_exec_sql($update_sql);		
	// change the clicked record second
		// SQL statement parameters
		$update_table  = 'dinergrade_session';      // Table name
		$update_where  = "ID = '".$the_rid."' AND SiteID = '".[glo_site_id]."' "; // Where clause
		$update_fields = array(   // Field list, add as many as needed
 			"SortOrder = '".$new_rsortorder."'",
		);
		// Update record
		$update_sql = 'UPDATE ' . $update_table
			. ' SET '   . implode(', ', $update_fields)
			. ' WHERE ' . $update_where;
		sc_exec_sql($update_sql);		
}
elseif (($the_rsortorder &lt; $the_count) AND ($the_rdirection == 'down'))
{
	// process a down swap
	$the_process = "OKdown";
	$new_rsortorder = $the_rsortorder + 1;
	// change the target record first
		// SQL statement parameters
		$update_table  = 'dinergrade_session';      // Table name
		$update_where  = "SortOrder = '".$new_rsortorder."' AND SiteID = '".[glo_site_id]."' "; // Where clause
		$update_fields = array(   // Field list, add as many as needed
 			"SortOrder = '".$the_rsortorder."'",
		);
		// Update record
		$update_sql = 'UPDATE ' . $update_table
			. ' SET '   . implode(', ', $update_fields)
			. ' WHERE ' . $update_where;
		sc_exec_sql($update_sql);		
	// change the clicked record second
		// SQL statement parameters
		$update_table  = 'dinergrade_session';      // Table name
		$update_where  = "ID = '".$the_rid."' AND SiteID = '".[glo_site_id]."' "; // Where clause
		$update_fields = array(   // Field list, add as many as needed
 			"SortOrder = '".$new_rsortorder."'",
		);
		// Update record
		$update_sql = 'UPDATE ' . $update_table
			. ' SET '   . implode(', ', $update_fields)
			. ' WHERE ' . $update_where;
		sc_exec_sql($update_sql);		
}
else
{
	// can't move - do nothing
	$the_process = "nothing";
}

[testinfo] = "the_count=".$the_count." the_process=".$the_process." the_rid=".$the_rid." the_rsortorder=".$the_rsortorder." the_rdirection=".$the_rdirection;

}
else
{
[testinfo] = “”;
}

That was the easy part. Getting the delete to reorder the remainder of the records was tricker: In the form’s beforeRecordDelete:

// prep to change the sortorder field for all downstream records
$test_store = “”;
for ( $i={SortOrder}+1; $i<=[the_count]; $i++ )
{
// process each downstream record, to shift each sortorder down one
$test_store .= " i=".$i;
$j = $i - 1; // look up to get new sortorder

		// Update record
		$update_sql = "UPDATE dinergrade_session SET SortOrder = '".$j."' WHERE SortOrder = '".$i."' AND SiteID = '".[glo_site_id]."' ";
		sc_exec_sql($update_sql);

}

Remember to deal with adding a new record too. All I did was set it so that a new value was forced, using a global count of records - the new record in my case is always at the end of the grid, so the new value for SortOrder is the count of records + 1

Hope that helps get people started :slight_smile:

#3

Is ok also for multi record ?