matthias-range-ais
commented
2 years ago Hi, there is only one event calling the the PL/SQL code when dropping an item, so you have to recalculate the values in FLOW_PRIORITY_INSIDE_CATEGORY for each item in the group. The sorting behavior depends on your usecase, i.e. is it allowed to move items between groups or not.
In your case there are 3 columns (FLOW_PRIORITY) and each with a defined sort order (FLOW_PRIORITY_INSIDE_CATEGORY) starting with the value "0", right? You can do this in 3 steps (i did not tested it):
- decrement FLOW_PRIORITY_INSIDE_CATEGORY for each item in old column behind old position of your item =>
update MY_FLOWS set FLOW_PRIORITY_INSIDE_CATEGORY = FLOW_PRIORITY_INSIDE_CATEGORY - 1 where FLOW_PRIORITY= VR_OLD_COLUMN_ID and FLOW_PRIORITY_INSIDE_CATEGORY > VR_OLD_INDEX - increment FLOW_PRIORITY_INSIDE_CATEGORY for each in new column beginning at new position of your item =>
update MY_FLOWS set FLOW_PRIORITY_INSIDE_CATEGORY = FLOW_PRIORITY_INSIDE_CATEGORY + 1 where FLOW_PRIORITY= VR_NEW_COLUMN_ID and FLOW_PRIORITY_INSIDE_CATEGORY >= VR_NEW_INDEX - set your item at new position =>
update MY_FLOWS set FLOW_PRIORITY = VR_NEW_COLUMN_ID, FLOW_PRIORITY_INSIDE_CATEGORY = VR_NEW_INDEX where flow_id = VR_ITEM_ID
hint: recalculate the sort orders of items is more complex when moving items between groups
Hello! @McRange, firstly thanks a lot for this amazing plug in! Can you please advise how to change the index of the items in a category? I have 3 columns (for 3 priorities based on FLOW_PRIORTY column) and I need to arrange the items inside each priority based on FLOW_PRIORITY_INSIDE_CATEGORY column. If I move one item up/down, the index will change properly for the item that I have moved, but the others ones will not be updated, they will remain with the old index, even though they are not correct now.
I need something like this, but it's not correct/complete yet:
DECLARE VR_ITEM_ID VARCHAR2(200) := :APEX$ITEM_ID; VR_OLD_GROUP_ID VARCHAR2(200) := :APEX$OLD_GROUP_ID; VR_OLD_COLUMN_ID VARCHAR2(200) := :APEX$OLD_COLUMN_ID; VR_OLD_INDEX VARCHAR2(200) := :APEX$OLD_INDEX; VR_NEW_GROUP_ID VARCHAR2(200) := :APEX$NEW_GROUP_ID; VR_NEW_COLUMN_ID VARCHAR2(200) := :APEX$NEW_COLUMN_ID; VR_NEW_INDEX VARCHAR2(200) := :APEX$NEW_INDEX; BEGIN for c in (select flow_id, flow_name, flow_priority, flow_priority_inside_category from MY_FLOWS where flow_priority = VR_NEW_COLUMN_ID) loop update MY_FLOWS set flow_priority = VR_NEW_COLUMN_ID, flow_priority_inside_category = VR_NEW_INDEX where flow_id = VR_ITEM_ID; end loop; END;
For example, if I have this situation inside column1 (flow_priority=1) and I want to move item4 on second row...
FLOW_NAME FLOW_ID FLOW_PRIORITY FLOW_PRIORITY_INSIDE_CATEGORY item1...................15................1................................0 item2..................23.................1................................1 item3..................11.................1................................2 item4..................79.................1................................3
... it should become: FLOW_NAME FLOW_ID FLOW_PRIORITY FLOW_PRIORITY_INSIDE_CATEGORY item1...................15................1................................0 item4..................79.................1................................1 item2..................23.................1................................2 item3..................11.................1................................3
With the current code, the records look like this: item1...................15................1................................0 item4..................79.................1................................1 item2..................23.................1................................1 item3..................11.................1................................2
Also, the indexes should be updated/arranged if one item moves from a column to another (for example from flow_priority=1 to 2).
Thank you!