Adding and updating a category on inline table dropdown

App Building
1

Firstly, can I just say that I really like Retool! Having mashed together end-user-computing for many years using those MSoft products, its refreshing to find something that is more adaptable and professional! I am 2 days old as far as Retool is concerned :slight_smile:

Secondly, can I apologies for what may be a simple question but its has beaten me after a number of hours!

Here goes. I have 2 table - one holding a list of charities and another with a list of causes. They are linked together using a FK of cause_id. I am trying to show a list of charities in a table with the current cause from when the record was created. I am then trying to get allow the user to update that cause choosing from a list of records from the causes table (inline edit). I then need to update that new cause_id once saved.

I have a list active charities query saved in the Query Library:

select
c.id,
c.created_at,
c.charity_name,
c.company_id,
c.website,
c.address_line_1,
c.address_line_2,
c.address_line_3,
c.address_city,
c.address_postcode,
c.updated_at,
cause.cause_name,
cause.id
from
charities c
left join charity_causes cause on c.cause_id = cause.id
where
c.deleted_at is null

and pulling the causes directly from the postgres db for the tag-dropdown:

select * from charity_causes
where deleted_at isnull

I have a third query called update_charity which i have set to run on-change' of the cause_name. I am trying to 'bulk update from primary key' using {{ tbl_charities.changeObjecrt }} but I am getting "Data must be an array" error.

So in summary 2 problems:

1 - how can i get the table to show the current cause_name and then produce a drop down list from get_causes for edits?

2 - Once edited, how do I update the get_causes.cause_id to save in my charities table.

Thanks in advance

2

Workaround so far..... I have set the source back to list_charities.cause_name which has solved the current cause

I have just added a new custom column, set the type to Tags and set the source to the get_causes query

Screenshot 2024-06-01 at 13.57.17
Screenshot 2024-06-01 at 13.57.17320×552 19.5 KB

If there is a better way then please shout. Just need to solve the update part now

3

For the update part, can you use tbl_charites.changesetArray instead? It seems like you need the array and not the object. If you DO require the object but need to format it as an array there is formatDataAsArray(tbl_charties.changeObject) which you can try as well.

4

Thanks for the reply - if I update the query to {{tbl_charities.changesetArray}} and then update the row, I get an error:

update_charity failed (1.547s):update "charities" set "cause_id" = $1 where "id" = $2 - invalid input syntax for type bigint: "{"1"}"

It looks like the query is trying to pass the array not the single value.

5

OK, some progress made. Had a few stupid errors on the above sql regarding IDs (that definitely wasnt helping).

Last thing to solve please. I can get the change cell event handler to run my update query correctly by adding a numberinput to my container and setting its value to {{tbl_charities.changesetArray[0].cause_id}}. I can then set my update query key value pairs to: cause_id & {{numberInput1.value}}.

If I try to remove the number input and add ``{{tbl_charities.changesetArray[0].cause_id}}``` as the value, I am getting a error: Update to charities failed: you must satisfy the constraint 'Charities cause id fkey'. So close.....

6

Got it! Array was passing a string and not a number. Updated the value to
{{Number(tbl_charities.changesetArray[0].cause_id[0])}}

1 Like