Reorder Records Using Drag and Drop

Contents

Overview

This article introduces how to create a Custom View on a Kintone App, where records can be resorted with drag and drop. The resorting feature is accomplished with SortableJs (External link), a JavaScript library for reorderable drag-and-drop lists

Sample Image

When the user navigates to the Custom View, a list of records is displayed in a custom made table. Users can click and drag rows of the table to change the sort order of the records.

Note the order of the first two records in the screenshot below:

Screenshot: A list of records with record number 1 being listed above record number 2.

The rows can be placed into a different location by using drag-and-drop.

Animated GIF: User drags record number 2 above record number 1.

The result is then saved with the new record order.

Screenshot:The result of the reordered records is saved.

Prepare the App

Create the Form

Create an App (External link) with the following field and settings. Save the form when finished.

Field Type Field Name Field Code Remarks
Record number Record number recordId
Number Sort order orderNum Initial value: 0
Text Participant Name part_name
Text State state

Create the View

From the View settings, create a new View and select Custom View. Check the Enable Pagination option. Type the following code into the HTML Code option.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

<table class="sortable_recordlist">
  <thead id="sortable_thead">
    <tr><th>Record Number</th><th>Participant Name</th><th>State</th></tr>
  </thead>
  <tbody id="sortable_tbody"></tbody>
</table>

<style type="text/css">
  .sortable_recordlist {
    width: 100%;
    font-size: 14px;
    border-radius: 3px;
  }
  #sortable_thead th {
    background: #ffffff;
    text-align: left;
  }
  #sortable_tbody th {
    background: #f5f5f5;
  }

  #sortable_tbody tr:nth-child(even) {
    background: #ffffff;
  }
  #sortable_tbody tr:nth-child(odd) {
    background: #f5f5f5;
  }
  .sortable_recordlist tr
  {
    padding : 10px;
    border: 1px solid #e3e7e8;
  }
  .sortable_recordlist th,
  .sortable_recordlist td
  {
    padding: 10px;
    border-right: 1px solid #e3e7e8;
  }

  .ghost {
    opacity: 0.5;
    background: #c8ebfb;
  }
</style>

The completed Custom View settings page should look as follows.

Screenshot: The completed Custom View settings page.

Save the View settings and apply the changes to the App. Navigate to the new Custom View through the blue drop-down list. The view should be displayed with no data inside.

Screenshot: The newly created view, which is empty because there is no data.

Set the Libraries

This sample uses jQuery (External link) v3.6.0 and SortableJS (External link). Set the following URLs into the App's JavaScript and CSS Customization settings (External link).

  • https://code.jquery.com/jquery-3.6.0.min.js
  • https://cdn.jsdelivr.net/npm/sortablejs@latest/Sortable.min.js

Sample Code

Type the following code into a text editor and save it as a JavaScript file. Upload it to the App's JavaScript and CSS Customization settings (External link).

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165

(function() {
  'use strict';

  /**
   * Display sortable view
   */
  function SortableRecordsManager(records) {
    this.records = records;
    this.recordOrderMap = records.map((recordData) => {
      // Initialize sort order as record number when orderNum is 0
      if (recordData.orderNum.value === '0') {
        recordData.orderNum.value = recordData.recordId.value;
      }
      return {'recordId': recordData.recordId.value, 'orderNum': recordData.orderNum.value};
    });

    // Compare the rearranged id's array with the previous id's array and get the record number and ordering pair
    this.update = function(updatedArray) {
      const tmp = $.extend(true, {}, this.recordOrderMap);
      const updateRecordOrderMap = $.extend(true, {}, this.recordOrderMap);
      const len = updatedArray.length;
      for (let i = 0; i < len; i++) {
        const prev = tmp[i].recordId;
        const cur = updatedArray.shift();
        if (prev !== cur) {
          updateRecordOrderMap[i].recordId = cur;
        }
      }
      this.recordOrderMap = $.extend(true, {}, updateRecordOrderMap);
      return this.recordOrderMap;
    };

    // Add rows for all records
    this.createTableRecords = function() {
      const tb = document.getElementById('sortable_tbody');
      // Number of additional records
      const len = this.records.length;

      // Add rows for all records
      for (let i = 0; i < len; i++) {
        const record = this.records[i];

        // Create row
        const row = tb.insertRow(tb.rows.length);
        row.id = record.recordId.value;
        row.className = 'row';
        const cell1 = row.insertCell(0);
        const cell2 = row.insertCell(1);
        const cell3 = row.insertCell(2);
        const cellText1 = document.createTextNode(record.recordId.value);
        const cellText2 = document.createTextNode(record.part_name.value);
        const cellText3 = document.createTextNode(record.state.value);
        cell1.appendChild(cellText1);
        cell2.appendChild(cellText2);
        cell3.appendChild(cellText3);
      }
    };

    // Clear the table
    this.destroyTableRecords = function() {
      document.getElementById('sortable_tbody').innerHTML = '';
    };
  }

  /**
  * Communicate with Kintone
  */
  function KintoneRecordManager() {
    this.records = [];
    this.appId = kintone.app.getId();
    this.query = '';
    this.limit = 100;
    this.offset = 0;

    // Update all sort order
    this.updateOrderNums = function(recordOrderNumArray) {
      const records = [];
      for (const key in recordOrderNumArray) {
        if (Object.prototype.hasOwnProperty.call(recordOrderNumArray, key)) {
          records.push(
            {
              id: recordOrderNumArray[key].recordId,
              record: {
                orderNum: {
                  value: recordOrderNumArray[key].orderNum
                }
              }
            }
          );
        }
      }
      kintone.api('/k/v1/records', 'PUT', {
        app: this.appId,
        records: records
      });
    };

    // Get all sorted records in "Sort order"
    this.getSortedRecords = function(callback) {
      this.query = kintone.app.getQueryCondition() + 'order by orderNum asc';
      this.getRecords(callback);
    };

    // Get all records
    this.getRecords = function(callback) {
      kintone.api('/k/v1/records', 'GET', {
        app: this.appId,
        query: this.query + (' limit ' + this.limit + ' offset ' + this.offset)
      }, (function(_this) {
        return function(res) {
          Array.prototype.push.apply(_this.records, res.records);
          const len = res.records.length;
          _this.offset += len;
          if (len < _this.limit) {
            _this.ready = true;
            if (callback !== null) {
              callback(_this.records);
            }
          } else {
            _this.getRecords(callback);
          }
        };
      })(this));
    };
  }

  // Record list view
  kintone.events.on('app.record.index.show', (event) => {
    if (event.viewId !== 6160528) {
      return event;
    }
    const kintoneRecordManager = new KintoneRecordManager();
    let sortableRecordsManager = null;

    kintoneRecordManager.getSortedRecords((sortedRecords) => {
      sortableRecordsManager = new SortableRecordsManager(sortedRecords);
      sortableRecordsManager.destroyTableRecords();
      sortableRecordsManager.createTableRecords();
    });

    const sortElement = document.getElementById('sortable_tbody');
    new Sortable(sortElement, {
      animation: 150,
      ghostClass: 'ghost',
      onUpdate:
      function() {
        // Get an array of id of sorted elements
        const trElement = sortElement.querySelectorAll('tr');
        const updated = [];
        for (const key in trElement) {
          if (Object.prototype.hasOwnProperty.call(trElement, key)) {
            updated.push(
              trElement[key].id
            );
          }
        }
        // Compare the rearranged id's array with the previous id's array and get the record number and ordering pair
        const result = sortableRecordsManager.update(updated);
        // Display the result in Kintone
        kintoneRecordManager.updateOrderNums(result);
      }
    });
    return event;
  });
})();

The JavaScript and CSS Customization settings should look like the following:

Screenshot: The JavaScript and CSS Customization settings.

caution
Attention

Caution: The order in which JavaScript and CSS are uploaded to an app matters. In this example, ensure that the jQuery and SortableJS libraries are uploaded before the sample JavaScript file. The order of the uploads can be changed by clicking and dragging on the arrows for each item on the Upload JavaScript / CSS page.

Code Explanation

SortableRecordsManager

A constructor function called SortableRecordsManager is created that is used to display the Sortable enhanced custom view. This constructor function is then given three methods: update, createTableRecords, and destroyTableRecords.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

function SortableRecordsManager(records) {

  this.records = records;

  this.update = function(updatedArray) {
    // ...
  };

  this.createTableRecords = function() {
    // ...
  };

  this.destroyTableRecords = function() {
    // ...
  };
}

The update method takes an array of sorted element IDs as its argument. It compares it with the previous array of IDs and order numbers to get an array of objects with each row's record number and ordering pair. The image below explains this process.

Image: The sorted element IDs are compared with the previous array of IDs.

The createTableRecords method references the HTML table in the custom view called sortable_tbody and creates a row in the table for each record.

The destroyTableRecords method then replaces the HTML table contents with an empty string to delete all of the rows in the table.

KintoneRecordManager

Another constructor function called KintoneRecordManager is created. This function is used to organize the functions that utilize Kintone APIs to retrieve and update data. This function also has three methods: updateOrderNums, getSortedRecords, and getRecords.

The updateOrderNums method takes the array of objects created by the update method of the SortableRecordsManager constructor function as its argument. It then uses the Update Records REST API to update the values of the Sort order field with the sort order values from the array.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

function KintoneRecordManager() {

  this.records = [];
  this.appId = kintone.app.getId();
  this.query = '';
  this.limit = 100;
  this.offset = 0;

  this.updateOrderNums = function(recordOrderNumArray) {
    //   ...
  };

  this.getSortedRecords = function(callback) {
    // ...
  };

  this.getRecords = function(callback) {
    //  ...
  };

}

The getSortedRecords method takes a callback function as its argument. It uses the kintone.app.getQueryCondition() method to retrieve the filter settings for the current view. It then adds the specification to order the records by ascending orderNum, and sets the value into this.query. The callback function is then passed to the getRecords method.

The getRecords method also takes the callback function that was passed as the getSortedRecords argument, as its own argument. It uses the query that was created in the getSortedRecords method to call the Get Records REST API. If the number of retrieved records is less than the set limit of 100, the callback function runs. If the number of retrieved records is exactly 100, the getRecords method is called again and continues to be called until the number of retrieved records is less than 100.

app.record.index.show Event

The last section of the code uses the previously created SortableRecordsManager and KintoneRecordManager constructor functions when the record list is loaded.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

kintone.events.on('app.record.index.show', function(event) {
  var kintoneRecordManager = new KintoneRecordManager();
  var sortableRecordsManager = null;

  kintoneRecordManager.getSortedRecords(function(sortedRecords) {
    sortableRecordsManager = new SortableRecordsManager(sortedRecords);
    sortableRecordsManager.destroyTableRecords();
    sortableRecordsManager.createTableRecords();
  });

  $('#sortable_tbody').sortable({
    update: function(_event, ui) {
      var updated = $('#sortable_tbody').sortable('toArray');
      var result = sortableRecordsManager.update(updated);
      kintoneRecordManager.updateOrderNums(result);
    }
  });
});

The app.record.index.show event is declared so that the code within the event declaration is automatically run when the record list is loaded. A new instance of KintoneRecordManager is created and passed to the variable kintoneRecordManager. The variable sortableRecordsManager is also prepared, but is passed null for its value.

The getSortedRecords method is run with the callback function creating a new instance of SortableRecordsManager and passes the value to the prepared sortableRecordsManager variable. The destroyTableRecords method is used to reset the table. The createTableRecords method is used to rebuild the table with the necessary number of rows.

Finally, SortableJS's sortable method is called on the element with the ID of sortable_tbody. With the sortable method, a function is given to the update property, which is an event that is triggered when the rearrangement of the sortable element has ended. In this case, sortable's toArray (External link) method is used to get an array of the sorted ID elements. The array is then passed to the update method of the sortableRecordsManager instance to create the updated result. To update the Sort order field values in the actual Kintone records, the updateOrderNums method of the kintoneRecordManager instance is called. With this, the new sort order is maintained even when the page is refreshed.

Reference