Spreadsheet Row Soft Delete

Introduction

This article demonstrates how to implement a row soft delete method for a spreadsheet element. A soft delete allows you to mark rows as deleted without removing the associated uploaded files, ensuring data integrity and retrievability.

How does it work?

1. Create a form which will serve as the child form of the spreadsheet. In it, include a hidden field named is_deleted and set the value to "no".
2. Next, head to your spreadsheet and configure the Data Store as follows:

• • Load Data From: Use the below SELECT query based on your requirements and enable the Handling for Uploaded Files option.

SELECT * 
FROM app_fd_up
WHERE (c_is_deleted != 'yes' or c_is_deleted is null) and c_fk = ?

• • Save Data To: Use the below query for the respective column and again enable the Handling for Uploaded Files option.

• • SQL SELECT Query:

-- Select query
SELECT * 
FROM app_fd_up
WHERE id = {id}

• • SQL INSERT Query:

-- Insert query
INSERT app_fd_up
   (id,
	c_name,
	c_file_upload,
	c_fk)
VALUES ({uuid},
	{name},
	{file_upload},
	{foreignKey})

• • SQL UPDATE Query:

-- Update Query
UPDATE app_fd_up
SET id = {id},
    c_name = {name},
	c_file_upload = {file_upload},
	c_fk = {foreignKey}
WHERE  id = {id}

• • SQL DELETE Query:

-- Delete Query
UPDATE app_fd_up
SET id = {id},
    c_is_deleted = 'yes',
	c_fk = {foreignKey}
WHERE  id = {id}

Expected Outcome:

Files will be stored in the following Joget directory: wflow\app_formuploads\<form-id>. The file name corresponds to the record ID, and the table also maintains the file name. Therefore, files deleted through this method can still be restored if needed.

Download sample app