# RowReorder

### Manual ordering of rows in a grid

This option will prepare the app for manual ordering of rows in a grid. Only set this option, if you want users to be able to order grid rows by drag and drop.

{% embed url="https://youtu.be/i5rwxNfoKJM" %}

### Setting this option will make the following changes to your app:

* **Create a new field** for the underlying datatable. The default name will be **brixx\_RowOrder**. Type will be set to **HierarchyId**.
* The **View** for this app will get a new column to show the RowOrder with hex value.

```
CONVERT([varchar](512), CAST([ExampleTableName].[brixx_RowOrder] as varbinary), 2) as brixx_RowOrder]
```

* A **InsertTrigger** will be created for the new field. Every time a new row is inserted without a value for brixx\_RowOrder an order value will be generated to place the new row at the end of already existing data.

```
CREATE  Trigger [dbo].[brixx_ExampleTableNameInsert_RowOrder]
ON  [dbo].[ExampleTableName]
AFTER INSERT

AS

SET NOCOUNT ON;

/* IF all brixx_RowOrder fields are valid, no need for any processing */
IF (SELECT COUNT(*) FROM inserted WHERE brixx_RowOrder IS NULL) = 0
BEGIN
	--PRINT ('No Action needed for Trigger on ExampleTableName');
	RETURN;
END

DECLARE @RowId BIGINT;
DECLARE @InsertAfterHierarchyId as HierarchyId;
DECLARE @ParentHierarchyId as HierarchyId;

DROP TABLE IF EXISTS #tmpTable;
SELECT Id INTO #tmpTable FROM inserted WHERE brixx_RowOrder IS NULL;

WHILE EXISTS (SELECT * FROM #tmpTable)
BEGIN
	SET @RowId = (SELECT TOP(1) ID FROM #tmpTable ORDER BY Id);

	/* If the INSERT does not provide a HierarchyId, generate a value at the end of list */
	SET @InsertAfterHierarchyId = (SELECT TOP 1 brixx_RowOrder FROM [dbo].[ExampleTableName] WHERE brixx_RowOrder IS NOT NULL ORDER BY brixx_RowOrder DESC );
	IF (@InsertAfterHierarchyId IS NULL)
	BEGIN
	    -- Default Parent ID
	    SET @ParentHierarchyId = '/1/';
	END
	ELSE
	BEGIN
	    -- Take Parent ID from Child
	    SET @ParentHierarchyId = @InsertAfterHierarchyId.GetAncestor(1);
	END

	UPDATE [dbo].[ExampleTableName] SET brixx_RowOrder = @ParentHierarchyId.GetDescendant(@InsertAfterHierarchyId, NULL) WHERE Id = @RowId;

	--PRINT ('INSERT Trigger on ExampleTableName. Parent: ' + @ParentHierarchyId.ToString() + ' Insert After: ' + @InsertAfterHierarchyId.ToString() + ' New RowOrder: ' + @NewHierarchyId.ToString() + ' for Id: ' + CONVERT(varchar(10),@RowId));

	DELETE #tmpTable WHERE Id = @RowId;
END

DROP TABLE #tmpTable;
```

* If no **ORDER BY** is provided for the SELECT Statement, ORDER BY will use the brixx\_RowOrder Field
* **All Existing data will be updated!** If rows exist when saving your app brixx\_RowOrder will get a default value for ordering.

```
UPDATE ExampleTableName SET brix_RowOrder =  '/1/' + CONVERT ( nvarchar(30) , Id) + '/' FROM ExampleTableName WHERE brix_RowOrderField IS NULL
```
