Trouble Updating PostgreSQL Table with Selected Data from List Collection

💬 Queries and Resources
1

Hello everyone,

I'm working on a project where I'm trying to update a PostgreSQL table with data selected from a List Collection component. Here's a brief overview of what I'm trying to achieve and the challenges I'm facing:

Objective:

  • I have a List Collection (listCollection2) in my Retool app that displays different tower garden types.
  • When a user selects a specific tower garden from this collection, I want to update a row in my my_towers table in PostgreSQL with the details of the selected tower garden.

What I've Tried:

  1. I used the following SQL query to achieve this:

sqlCopy code

UPDATE my_towers
SET tower_type = '{{listCollection2.selectedRow.tower_garden}}',
    ports = {{listCollection2.selectedRow.ports}}
WHERE id = {{localStorage.towerId}};

However, this resulted in the error: "could not determine data type of parameter $1".
2. To debug the data type, I hard-coded the values and ran:

sqlCopy code

UPDATE my_towers
SET tower_type = 'TestType',
    ports = 1234
WHERE id = 17;

This query ran successfully and updated the database as expected, indicating that the issue might be with how I'm accessing the data from listCollection2.

Also ran:

return listCollection2.data[0].tower_garden;

That worked. Of course you cant hard code that...

Questions:

  1. How can I correctly access the selected row's data from the List Collection component to use in my SQL query?
  2. Are there specific properties or methods associated with the List Collection component that I should be using to achieve this?

Any insights or suggestions would be greatly appreciated. Thank you in advance!

2

Welcome to the forum:
Try removing the single quotes in the update query for tower type:

UPDATE my_towers
SET tower_type = {{listCollection2.selectedRow.tower_garden}},
    ports = {{listCollection2.selectedRow.ports}}
WHERE id = {{localStorage.towerId}};
3

Thanks Scott, there is something wrong with my query though, it does not like the way i structured the listCollection2.

image
image1263×381 67 KB

4

Your list collection is a listView? Do you have a screenshot of it... I don't believe that selectedRow is correct unless it is a table....

5

yes sir, here you go:

Capture4
Capture4277×535 22 KB

Capture5
Capture5389×595 23.1 KB

6

List Collection? Can you share what type of component this and not what you have named it....
I am going to assume it is a listView component.... and if so, selectedRow is not possible...
{{listCollection2.data[i].tower_garden}}

1 Like
7

Awesome Scott, thanks, that got it! Great to get through some of this early learning...

2 Likes