Create Insert SQL from table.recordupdates

App Building
#1

My app is based on a Postgresql database view including two tables. Table1 has a series of columns, including URL. Table2 has two columns: URL, pagetype. A user will be asked to go through a list of url's to determine the web page type. Table1 has url's with some page types already assigned and other url's where the page type is yet to be determined. The view is roughly as follows:

SELECT a.url,
b.pagetype
from "Table1" a
left join "Table2" b on a.url = b.url
where b.pagetype is null

As a user works the data by going to the url, identifying the appropriate page type and then editing the pagetype cell, I would like to either to do a bulk insert or single row insert into table 2. There would be fewer keystrokes required if I do a bulk insert. Is there a way to use the recordupdates array to complete the insert SQL? If bulk updates is not used, can I trigger an event to run the insert query each time I change a cell. Keep in mind that it looks like a cell change would require an UPDATE, but it actually requires an INSERT into Table2. When the view is refreshed, the record will no longer appear in the form table since the pagetype will be null. In subsequent apps, I will use the pagetype values once all the url records from Table1 have been processed.

INSERT INTO "Table2" (url, pagetype)
VALUES( )

#2

Hi @adrooney!

You can certainly trigger an insert with recordUpdates:

table1 is displaying query1.data (where i have a bunch of urls from the links table that don't yet exist in the pagetypes table):

Then, I update 2 of the rows by using a bulk insert query triggered on Save changes (using table1.recordUpdates):

After triggering this query, I can refresh query1 & see that I only have 1 record in my view:

This is best supported by using the Save Changes button to submit all changes in bulk. However, if you wanted to update each row one at a time, I'd suggest using a form instead of the editable table

Let me know if this helps at all or if it sounds like I am misunderstanding your question

#3

Thanks Tess. I might've caused some confusion when I referenced "Table1" and "Table2" in the SQL of my post. These are not the real names of the database tables. I thought it would be less confusing to use these names rather than the actual table names in the database. I don't know why I didn't think about the fact that Retool uses thsoe names for the table components. I reworked my case using the actual names to remove this confusion (bold words). I'll do better next time I post a case.

My app is based on a Postgresql database view including two tables. Report has a series of columns, including URL. Serpstack has two columns: URL, pagetype. A user will be asked to go through a list of url's to determine the web page type. Serpstack has url's with some page types already assigned and other url's where the page type is yet to be determined. The view is roughly as follows:

SELECT a.url,
b.pagetype
from "Report" a
left join "Serpstack" b on a.url = b.url
where b.pagetype is null

As a user works the data by going to the url, identifying the appropriate page type and then editing the pagetype cell, I would like to either to do a bulk insert or single row insert into table 2. There would be fewer keystrokes required if I do a bulk insert. Is there a way to use the recordupdates array to complete the insert SQL? If bulk updates is not used, can I trigger an event to run the insert query each time I change a cell. Keep in mind that it looks like a cell change would require an UPDATE, but it actually requires an INSERT into Serpstack. When the view is refreshed, the record will no longer appear in the form table since the pagetype will be null. In subsequent apps, I will use the pagetype values once all the url records from Report have been processed.

INSERT INTO "Serpstack" (url, pagetype)

VALUES( )

The query below references "links". I don't know what that is. It also references what appears to be a table PageType which is not a database table I have. Maybe it's your test data. I thought this could be a query that Retool recognizes and, when I previewed it, I get an error message. I have a query like this in Retool that works with my database. I assume you just want me to use that?

image.png

Also, on your last screen shot, can you explain the transformer you used? I get that it is putting the contents of the recordupdates into the proper SQL syntax, but I don't understand why it references specific url's and product values. I assume that was the data you used. With that said, I don't want to create this line of code (from scratch) from each of the records in recordupdates. Did you execute some process that converted the recordupdates value into this line? Lastly, is bulk updates a safe way to update the database? For example, if the power goes off after entering changes to a large number of records, will the data be lost? If it is vulnerable, should I consider doing individual record inserts?

#4

BTW, I noticed Kabirdas messaged me during the conference call today and asked for information about this case. I sent him some information. Can you let him know you're working it? Thanks!

#5

Hi @adrooney! Thanks I connected with Kabirdas internally.

My example is also referencing two database tables that I created for this example (links & pagetypes) You can replace links with "Report" and pagetypes with "Serpstack" when referencing my screenshots for your own app:

There are no transformers in my example screenshots. Any time a value is edited by a user in the table component, it'll get automatically added to tableName.recordUpdates.

It's a good question about bulk inserts/updates. If the power goes out or the page gets refreshed, the pending changes that haven't yet been saved will be lost. If you want to minimize the risk of that happening, you could have the user submit changes one at a time. More info about recordUpdates here: Present data in tables

1 Like
#6

Thanks. I’ll try that today. Is there a way for me to monitor the number of pending inserts in a bulk update and then trigger the insert? That way I can limit the rework if there’s an interruption in power or inadvertent closing of the retool browser window.

#7

Yes, I think you could implement that! recordUpdates is an array, so you could use some Javascript to monitor the length of .recordUpdates