Having trouble deleting multiple items from a table at once

App Building
,
1

  • Goal:

  • I am trying to delete multiple items from my table component, "customerLookup_Table". The data source for this component is just a SQL resource query that gets all items from a table in my retool DB called "lumberaidcustomer".

  • Steps:

  • So far, my approach has been to combine a couple of queries (one JS one SQL), along with the use of a JS variable, "customerIDsForDeletion", to hold temporary values (this variable is initially set to ).

  • I have a JS query, "getIDsForDeletion", designed to dynamically grab the customer_id values of each selected row, and plant them into the value of "customerIDsForDeletion" (SS of code below).

  • I also have a SQL Resource query, "deleteCustomers", that is meant to delete any row in the table where the customer_id is in "customerIDsForDeletion" (SS of code below).

  • Details:

  • Right now when running "getIDsForDeletion", it runs without errors on its own, but when it calls "deleteCustomers.trigger()", I have the following error showing as part of the SQL query's output (from when I tried to delete 3 customers with ID's of 69, 1, and 13):

invalid input syntax for type integer: "{"69","1","13"}"

1 Like
3

Hello @Weston_Bainbridge ,

I found the issue causing the error when deleting multiple customers. The selected IDs weren't being passed to the SQL query correctly.

Fix:

  • We updated the JavaScript code to ensure customer_id values are converted to numbers before adding them to the customerIDsForDeletion array.
  • The SQL query was adjusted to use the array parameter directly without extra formatting.

Here's the updated code:

JavaScript:

const selectedCustomerIDs = customerLookup_Table.selectedRows.map(row => Number(row.customer_id));
customerIDsForDeletion.setValue(selectedCustomerIDs);

SQL

DELETE FROM lumberaidcustomer
WHERE customer_id = ANY({{ customerIDsForDeletion.value }});

This should resolve the error and allow you to delete multiple customers successfully.

Please let me know if you have any further questions or encounter any issues.

4 Likes
4

Worked like a charm... thank you and Merry Christmas!

1 Like
5

Welcome, @Weston_Bainbridge, and Merry Christmas!

4 Likes