Reorder Drag and drop example

  • 15 February 2021
  • 0 replies
  • 44 views

Userlevel 6
Badge +18

Goal

Some lists have an order number that functions as a standard sorting, or as a sequence of actions, or for other reasons. The Drag and drop functionality the GUI offers is a great tool to use for this but it needs some coding.

Solution

The following code is a good start for sequential lists (1,2,3,4,etc.) and work with multi-selection.

Requirements: 

  • Task with PK parameters, a from_order_no and a to_order_no parameter.
  • Connect to table with only to_order_no = table.order_no
  • Create drag-drop at desired subject and connect PK parameters and from_order_no = table.order_no

Use following code in Functionality > Template.

--Continue if different
if @from_order_no <> @to_order_no
begin
--Copy all records into #temp
select *
into #temp
from [table] t

--Make space for reorder
update t
set t.order_no = order_no * 10
from #temp t

--Alter order no of dragged row (+1/-1 depending higher/lower dragging)
update t
set t.order_no = @to_order_no * 10 + iif(@to_order_no < @from_order_no, -1, 1)
from #temp t
where t.id = @id --PK columns

--Reorder all items
;with reorder_order_no as (
select t.id --PK columns
,row_number() over (order by t.order_no asc) as new_order_no
from #temp t
)
update t
set t.order_no = new_o.new_order_no
from [table] t
join reorder_order_no new_o
on new_o.id = t.id --PK columns
end

This code does use parameter [table]. In the Assigning screen, enter the following:

 


0 replies

Be the first to reply!

Reply