< Previous Article

Drag and Drop Between Grids

In this tutorial, we will be demonstrating how to use Rich Display's drag and drop tools to interact with a databse in a stateful application. This tutorial builds off of the completed application of a previous walkthrough, Stateful To Do List Application.

Starting Positions

If you have completed the Stateful To Do List Application walkthrough, fork your application to create a copy workspace to preserve the your original workspace. You can continue in the same workspace if you so choose, but do keep in mind that the changes you make may not be reversed easily. Should you require assistance with forking a workspace, please refer to the site documentation.

fork

If you do not have a Stateful To Do List Application, feel free to fork the completed example workspace we have available in Profound Logic's official workspaces. This purpose of the Stateful To-Do List Application is to allow users to main a list of tasks and persisting the user's list by storing it in a database.

You should see the todo.json and todo.js files in the new fork of the workspace.

Updating the User Interface

In the todo.json file, a grid was added to display an empty or existing to-do list for the original To-Do List application. For the Drag and Drop walkthrough application, two (2) grids are required. To more easily differentiate the grids, the original grid is renamed to todo.

Before continuing, the grid is resized by changing the column widths property so that the original grid takes up about 40% of the panel. This is done to create room for the second grid.

duplicate_grid

To create the second grid, right-click the todo grid and select Duplicate from the context menu to create a copy. The newly created second grid is renamed to done.

Duplicate Grid

todo Grid Properties done Grid Properties

The headers of the first column in each grid were also updated to To-Do and Done.

Both Grids in Final Position

Quick Fix

Since the name of the original grid was changed to todo, the todo.js file will need to be updated. In the todo.js file, there are three instances of "grid" that are replaced with "todo".

Before

Before the Quick Fix

After

After the Quick Fix

Quick Check

To confirm that the changes were properly implemented, save your changes and launch the application to ensure the existing functionality continues to work. You should still be able to add tasks to and remove tasks from the todo grid.

Database

The todo grid is connected to a table in the database that is named tasksToDo. A new table named tasksDone is added to the database to be connected to the done grid to store the users' "done" list. The new table can be created using the Create Table option of the context menu (right-click menu) of the Tables database or by clicking the Query Database button (the scroll of paper at the top-right of the Database tab) and running the following line of SQL.

CREATE TABLE tasksDone LIKE tasksToDo;

The Application Database

Updating removeItems() Function to Handle Both Grids

Now that there are two grids and a corresponding database table for each, the code in todo.js will need to be updated to work with both.

  1. Update the removeItems() function to handle removing elements from both grids.

     function todo() {
       // Additional program content...
       while (true) {
         // Additional program content...
       }
       // Additional program content...
     }
     // Additional program content...
    
     function removeItems(grid) {
       var recordsToRemove = grid.filter(entry => entry.remove);
       var table = (grid.name === 'todo')? "tasksToDo" : "tasksDone";
       recordsToRemove.forEach(record =>
         pjs.query("DELETE FROM " + table + " WHERE id = ? AND user = ?", [
           record.id,
           record.user,
         ])
       );
     }
    
     // Additional program content...
  2. Add a second call to removeItems() to run the function for both grids. It is doable this way because the function will do nothing unless the remove field of a record has been "turned on".

     while (true) {
       // Additional program content...
    
       newTask = '';
    
       display.todoScreen.execute();
    
       if (add) addItem(display.todo, user, newTask);
    
       removeItems(display.todo);
       removeItems(display.done);
    
       // Additional program content...
     }
  3. You can add some test records to the tasksDone table via the Edit Data option of the context menu when you right-click on the table name. Adding some test records to the table will allow you to test the remove functionality of the done grid.

    Optional: Button to INSERT Records into tasksToDo Table

    This is something you can do to quickly enter a few records into tasksToDo for testing purposes.

    1. Add a button to the screen. For this example, it was given the value Reset To-Do Table and the response was bound to and indicator field named resetToDoTable.

      Reset To-Do Table Button Added to Screen

    2. In todo.js, a handler function for when this button is pressed is added after the todo() function.

      function todo() {
         // Additional program content...
         while (true) {
           // Additional program content...
         }
         // Additional program content...
      }
      // Additional program content...
      
      function refillToDoTable(user) {
         var initRecords = [
           {
             task: 'Practice social distancing.',
             user: user
           },
           {
             task: 'Stay at home if and when possible.',
             user: user
           },
           {
             task: 'Wear a mask while in public.',
             user: user
           },
           { task: 'Wash hands for 20 seconds.', user: user }
         ];
      
         pjs.query("DELETE FROM tasksToDo WHERE user = " + user);
         initRecords.forEach(record => { pjs.query('INSERT INTO tasksToDo SET ?', record) });
      }
      
      // Additional program content...
    3. A call to the refillToDoTable() function is added to the while(...) {...} loop.

      function todo() {
         // Additional program content...
         while (true) {
             if (resetToDoTable) refillToDoTable(user);
           // Additional program content...
         }
         // Additional program content...
      }
      // Additional program content...
      Notes on refillToDoTable()
    • The initRecords array holds an array of objects that are each an individual record to add.
    • These records are assigned to the current application user.
    • The pjs.query("DELETE FROM tasksToDo where user = " + user); line is emptying the d

Adding Drag and Drop

  1. In the todo.json file, click the background of the screen (outside of any elements) or select todoScreen from the Screens panel to access the Screen Properties. The Drag and Drop Reponse properties should now be available in the Properties Panel. These properties were bound to the following fields.

    Property Name Field Name Data Type
    dd element id fromGrid String
    dd record number fromRecordNumber Decimal
    target element id toGrid String
    target record number toRecordNumber Decimal
     

    todoScreen Screen Properties

    Notes on the Fields for Drag and Drop Events
    • The fromGrid field will have the id of the grid from which the drag started.
    • The fromRecordNumber field will have the record number the record being dragged from the source grid.
    • The toGrid field will have the id of the target grid.
    • The toRecordNumber field will have the record number of where the record was dropped in the target grid.
  2. Set the Drag and Drop properties of each of the Grids.

    Property Name Value
    allow drag true
    drop targets todo,done
     
    Notes on the Drag and Drop Grid Properties
    • The default value of allow drag is false. Setting this property to true will enable the user to drag records from it, otherwise, the records are not draggable.
    • If the drop targets property is left empty, the Drop Event will not trigger when the dragged element is dropped. This is because the Drop Event only triggers when the dragged element is dropped on a valid target element.
  3. Add the handler function for the Drop Event to todo.js. Ater the while(...) {...} loop, but still inside of todo(), add a new function named moveItem().

     function todo() {
       // Additional program content...
       while (true) {
         // Additional program content...
       }
    
       function moveTask() {
         var toTable = (toGrid === 'todo') ? "tasksToDo" : "tasksDone";
         var fromTable = (fromGrid === 'todo') ? "tasksToDo" : "tasksDone";
         var record = display[fromGrid].getRecord(fromRecordNumber);
         var toTableRecords = pjs.query('SELECT * FROM ' + toTable + whereUser);
    
         if (toTable === fromTable) {
           toTableRecords.splice(fromRecordNumber - 1, 1);
           if (fromRecordNumber > toRecordNumber) toTableRecords.splice(toRecordNumber, 0, record);
           if (fromRecordNumber < toRecordNumber) toTableRecords.splice(toRecordNumber - 1, 0, record);
         } else {
           pjs.query("DELETE FROM " + fromTable + " WHERE id = ? AND user = ?", [record.id, user]);
           if (toTableRecords.length < 1) toTableRecords.push(record);
           else {
             toTableRecords.splice(toRecordNumber, 0, record);
           }
         }
    
         pjs.query("DELETE FROM " + toTable + whereUser);
    
         toTableRecords.forEach((rec, i) => {
           delete rec.remove;
           rec.id = pjs.query("SELECT * FROM " + toTable).length + 1;
           pjs.query("INSERT INTO " + toTable + " SET ?", rec);
         });
       }
     }
    
     // Additional program content...
    Notes on How moveTask() Works
    • The syntax (...) ? ... : ...; is another way to write if (...) {...} statements. This syntax can be used inline of an assignment opperation (i.e. x = y;) to decide between possible values. The variables toTable and fromTable use this inline if (...) {...} statement to get the table name by referencing the grid name stored in toGrid and fromGrid. Regular if (...) {...} statements can, of course, be used instead, and may, in some cases be a more appropriate approach.
    • The if (toTable === fromTable) {...} statement is checking which situation is being handled. Tasks simply being reordered in the same grid need to be handled differently than tasks being moved from one grid to the other.
    • pjs.query("DELETE FROM " + toTable); is emptying the target table to prevent duplications.
    • toTableRecords.forEach(...) is cycling through the complete list of records to set the record ids in order and insert the record into the toTable.
  4. Add code to reset the fields before each screen display. This ensures that the same action is duplicated when other actions are submitted. To make sure a new source and target grid are returned each time and only when current action is a task being moved, insert this line in the main program while(...) {...} loop before display.todoScreen.execute();:

     while (true) {
       // Additional program content...
    
       fromGrid = '';
       toGrid = '';
    
       newTask = '';
    
       display.todoScreen.execute();
    
       if (add) addItem(display.todo, user, newTask);
    
       removeItems(display.todo);
       removeItems(display.done);
    
       // Additional program content...
     }
  5. Test the application.

    final

    Notes on API Used
    • For information on pjs.query() function and use, please visit its documentation page.
    • For information on other APIs used in this step and throughout this walkthrough, please see our complete Profound.js API documnetation on our documentation site.

Completed Application

A completed version of this walkthrough can be seen here: page, run, ide.

For quick reference, the final todo.js code is provided below.

// Main Program Function
function todo() {
  // Define the display file.
  pjs.defineDisplay('display', 'todo.json');

  // Get the current user.
  var user = pjs.getUser();
  var whereUser = " WHERE user = '" + user + "'";

  // Set header value of program panel.
  if (user && user !== null) header = user + "'s To-Do List";
  else {
    header = 'A To-Do List';
    user = 'User Not Specified';
  }

  // Start main program loop.
  while (true) {
    // If Refill To-Do Table button was pressed, call refillToDoTable() for quick INSERT
    // of multiple records into tasksToDo table for testing purposes.
    if (resetToDoTable) refillToDoTable(user);

    // Update grids.
    var todoList = pjs.query("SELECT * FROM tasksToDo" + whereUser);
    display.todo.replaceRecords(todoList);
    var doneList = pjs.query("SELECT * FROM tasksDone" + whereUser);
    display.done.replaceRecords(doneList);

    // Reset Drag and Drop fields.
    fromGrid = '';
    toGrid = '';

    // Reset New Task field.
    newTask = '';

    // Display the screen.
    display.todoScreen.execute();

    // If adding a new task, call addItem() to add it to tasksToDo table.
    if (add) addItem(display.todo, user, newTask);

    // Check for any tasks to remove.
    removeItems(display.todo);
    removeItems(display.done);

    // If a fromGrid and a toGrid are returned, call moveTask() to move the task.
    if (fromGrid.length > 0 && toGrid.length > 0) moveTask();

  }

  // Define moveTask() function to be handler of the drop event.
  function moveTask() {
    // Determine to and from tables.
    var toTable = (toGrid === 'todo') ? "tasksToDo" : "tasksDone";
    var fromTable = (fromGrid === 'todo') ? "tasksToDo" : "tasksDone";
    // Retrieve record to move.
    var record = display[fromGrid].getRecord(fromRecordNumber);
    // Get current content of target table.
    var toTableRecords = pjs.query('SELECT * FROM ' + toTable + whereUser);

    // If reording a grid, reorder the records.
    if (toTable === fromTable) {
      toTableRecords.splice(fromRecordNumber - 1, 1);
      if (fromRecordNumber > toRecordNumber) toTableRecords.splice(toRecordNumber, 0, record);
      if (fromRecordNumber < toRecordNumber) toTableRecords.splice(toRecordNumber - 1, 0, record);
    } else {
      // Otherwise, delete the record from the from table.
      pjs.query("DELETE FROM " + fromTable + " WHERE id = ? AND user = ?", [record.id, user]);
      // If the to table is empty, simply push the record to the toTableRecordsArray.
      if (toTableRecords.length < 1) toTableRecords.push(record);
      else {
        // Otherwise, insert record at target index.
        toTableRecords.splice(toRecordNumber, 0, record);
      }
    }

    // Empty toTable before INSERT statement(s) to prevent duplicate entries.
    pjs.query("DELETE FROM " + toTable + whereUser);

    // For each record in toTableRecords array, delete the remove property, set the id to current record order, and INSERT into toTable.
    toTableRecords.forEach((rec, i) => {
      delete rec.remove;
      rec.id = pjs.query("SELECT * FROM " + toTable).length + 1;
      pjs.query("INSERT INTO " + toTable + " SET ?", rec);
    });
  }
}

// Define addItem() function.
// @parameters:
//   object: grid
//   string: user
//   string: newTask
function addItem(grid, user, newTask) {
  // Add new record to the todoGrid.
  grid.push({ user: user, task: newTask });
  // INSERT new record into tasksToDo table.
  pjs.query('INSERT INTO tasksToDo SET ?', { id: grid.getRecordCount + 1, user: user, task: newTask.trim() });
}

// Define removeItems() function.
function removeItems(grid) {
  // Retrieve record(s) to remove.
  var recordsToRemove = grid.filter(entry => entry.remove);
  // Determine target table based on grid name.
  var table = (grid.name === 'todo') ? "tasksToDo" : "tasksDone";
  // Delete from target table each record in recordsToRemove array.
  recordsToRemove.forEach(record =>
    pjs.query("DELETE FROM " + table + " WHERE id = ? AND user = ?", [record.id, record.user]));
}

// Define refillToDoTable() for quick INSERT of multiple records for testing purposes.
function refillToDoTable(user) {
  var initRecords = [
    {
      id: 1,
      task: 'Practice social distancing.',
      user: user
    },
    {
      id: 2,
      task: 'Stay at home if and when possible.',
      user: user
    },
    {
      id: 3,
      task: 'Wear a mask while in public.',
      user: user
    },
    {
      id: 4,
      task: 'Wash hands for 20 seconds.',
      user: user
    }
  ];

  pjs.query("DELETE FROM tasksToDo WHERE user = ?", user);
  initRecords.forEach(record => { pjs.query('INSERT INTO tasksToDo SET ?', record) });
}

exports.default = todo;

Questions?

Have questions about this topic? Ask for help on our NodeRun Discussion Forum.

< Previous Article