Table bulk edit and update

This tutorial will explain how to use Superblocks to create a credit card disputes dashboard for a customer support team to manage disputes, update dispute status, and freeze cards with fraudulent transactions. The customer support team needs to view and edit dispute information as a table, without giving the team full database access.

Superblocks features that are used in this tutorial:

• Table Inline Editing (Frontend)
• Table Bulk Edit (Backend)

PSQL to create table needed for this tutorial.

CREATE TABLE "disputes" (
  "Dispute ID" bigint,
  "Cardholder Name" text,
  "Transaction Date" text,
  "Amount" bigint,
  "Merchant Name" text,
  "Reason for Dispute" text,
  "Dispute Status" text,
  "Card Frozen" boolean
);

INSERT INTO "disputes" VALUES
(1,'John Smith','2022-01-01',150,'XYZ Store','Fraudulent charge','Under review',TRUE),
(2,'Jane Doe','2021-12-20',200,'ABC Hotel','Cancelled service','Resolved',FALSE),
(3,'Tom Lee','2022-02-05',100,'DEF Restaurant','Incorrect amount','Under review',FALSE),
(4,'Sarah Brown','2022-01-15',50,'GHI Supermarket','Duplicate charge','Resolved',TRUE),
(5,'Alice Lee','2022-02-10',75,'JKL Store','Product not received','Under review',TRUE),
(6,'Bob Johnson','2022-02-03',125,'MNO Hotel','Service not as described','Under review',FALSE),
(7,'Cindy Chen','2022-02-12',100,'PQR Restaurant','Duplicate charge','Resolved',FALSE),
(8,'David Kim','2022-01-20',150,'STU Supermarket','Cancelled transaction','Resolved',TRUE),
(9,'Eva Perez','2022-01-05',75,'VWX Store','Fraudulent charge','Under review',TRUE),
(10,'Frank Rodriguez','2022-02-01',200,'YZA Hotel','Service not as described','Resolved',FALSE),
(11,'Grace Lin','2022-01-10',100,'BCD Restaurant','Incorrect amount','Under review',FALSE),
(12,'Henry Chen','2022-02-08',50,'EFG Supermarket','Duplicate charge','Resolved',TRUE),
(13,'Isabel Rodriguez','2022-01-18',125,'HIJ Store','Product not as described','Resolved',TRUE),
(14,'Jack Kim','2022-02-14',100,'KLM Hotel','Cancelled transaction','Under review',TRUE),
(15,'Kelly Wong','2022-01-28',150,'NOP Restaurant','Duplicate charge','Resolved',FALSE),
(16,'Leo Li','2022-02-07',50,'QRS Supermarket','Fraudulent charge','Under review',TRUE),
(17,'Maggie Chang','2022-02-11',75,'TUV Store','Product not as described','Under review',FALSE),
(18,'Nick Davis','2022-01-14',200,'WXY Hotel','Service not received','Resolved',FALSE),
(19,'Olivia Zhang','2022-01-22',100,'ZAB Restaurant','Incorrect amount','Under review',TRUE),
(20,'Peter Smith','2022-02-04',125,'CDE Supermarket','Cancelled transaction','Resolved',TRUE);

Add a Table and load Table data

1. Open the Components Panel and drag and drop a Table component to the frontend Canvas
2. Create an API called "get_disputes" that queries the disputes table in Postgres DB
3. Add a Postgres step to your Backend API and and paste the following code into the input field for Run SQL:

   select * from disputes
   order by dispute_id
4. Run the API and paste {{get_disputes.response}} into the Table Data input field in order to load the response from the backend API in your frontend Table component.

Make columns editable

Once the data is loaded in the table, make the table columns editable by checking the "Editable" checkbox in the properties pane, under the Columns property. In this case, make the ‘Reason,’ ‘Dispute status,’ and ‘Card Frozen’ columns editable.

Besides setting the Column Type, you can also configure the column’s Editing Type.

To visually highlight the dispute_status column, set Column Type to Tags, which will automatically change the Editing Type to a dropdown. The Options field automatically generates an array of all the distinct values in the disputes table, which is customizable.

For the dispute data there are always four options, so set Options to an array of predefined choices for Customer Support to pick from:

`{{ ["Resolved", "Under Review", "Not Reviewed", "On Hold"] }}`

Since a dispute can only have one status, disable multi-select.

To edit any cell in the dispute_status column, double click. A list of predefined statuses will appear, which your support representatives can choose from.

This app enables support reps to easily freeze or unfreeze cards, given the frequency of disputes related to fraudulent transactions. Since the card_frozen column is composed of boolean values, set the Column Type to Boolean. This will automatically configure the Editing Type to be Checkbox and set the False Value to be an unchecked box.

Edit the table data

As soon as you edit a cell, the "Save changes" and "Cancel" buttons will show up. The "Save changes" button will also indicate how many cells have been edited. These buttons will trigger their respective events ("onSaveChanges" and "onCancelChanges"), which you can use to store or discard the updated values of the table. (See the section below for more information).

note

Understand the edited table data

You can use the edited table data to save to any API, not just Postgres. The table produces two output formats: all edits and diff edits.

Table1.allEdits contains all table rows, representing what you see in the table component. This is most useful when saving to external REST services. Note: Table1.allEdits is not the same as Table1.tableData, which contains the unedited table data. If you require the complete table data with edited values, you must use Table1.allEdits.

Table diff edits are used for saving to your database. To understand the diff format:

1. Drag in a Diff Viewer
2. Set the Original Text field to {{Table1.editedData.originalRows}}
3. Set the New Text field to {{Table1.editedRows.updatedRows}}

Table1.editedData.originalRows is an array of Table row objects containing the initial data. This array only contains rows that have been edited.

Table1.editedData.updatedRows is an array of Table row objects containing the updated data. This array only contains rows that have been edited.

Being able to access both the original and updated rows is crucial when modifying primary key values in the SQL database.

Bulk update the Postgres table

info

We are using Postgres as an example, this form is also available for MySQL, MariaDB, and Microsoft SQL.

Now that the table is editable, it needs to be saved into your Postgres database. To do this, create a Postgres API and name it 'update_table_data', and then set the operation dropdown to 'Update rows using form'.

In the Postgres Table dropdown, select the ‘disputes’ table.

Step 1 - Match rows

This step identifies the database rows that need updating. By default, the step matches the primary keys of the database table – dispute_id in this case. This means that for each table row, it extracts the dispute_id column value and searches for a matching row in the database table. If a match is found, the row in the database table will be updated with the value provided in Step 2.

You don't need to change this behavior as the dispute_id column is unique for each dispute.

info

If your database does not have a primary key or you want to match on more than one database column, check out how to configure Step one here.

Step 2 - Update matched rows

This step updates rows that were matched in the previous step. Connect it to the table using {{Table1.editedRows.updatedRows}}, which contains only the rows that were modified.

Step 3 - Match columns

This step matches the keys extracted from the array of row objects to the corresponding columns in the SQL database. In this tutorial you have not modified the column names, so you can use the default option of automatically mapping all row object keys to database table columns.

Automatic mapping option requires you to ensure that your UI table 1-1 matches your disputes Postgres table. Otherwise, the update query will fail, resulting in no modifications to your SQL database.

info

If your table is created from a query involving SQL joins or your table columns are aliased and don’t map 1-1 with your database table, follow the manual mapping tutorial here.

Finally, here is what your Update rows form should look like.

Save table changes

Now that your update form is set up, configure your Table’s onSaveChanges event to run the update API. This will ensure that every time the Save Changes button is pressed, update_table_data API will run to update the modified changes.

When the update_table_data API successfully runs, you need to re-run the get_disputes API in order to fetch the updated data from your disputes table.

1. Click on the ‘Response’ block in the update_table_data API
2. Click on ‘Select an Action’ under onSuccess
3. Keep the ‘Run APIs’ action type and select the get_disputes API
4. Additionally you'll show a success alert after the table data is reloaded

Now every time the update_table_data API successfully runs, your app will display the latest data from your SQL DB in your table & get a success notification.

Test table updates

Finally, you are ready to test the end-to-end flow of updating your table data! Edit a few cells and press the ‘Save Changes’ button. The Table should successfully display your updated data & you should see a notification telling us the update occurred successfully.