I have a table generated from RestAPI and updates periodically. I want to add a note field (column) so that from front-end we can keep a persistent note about a row.
I'm confused how I can do this. Can anyone help?
Hi @btcjon, great question!
If we would like the changes to persist, handling it just on the frontend won't be enough. We need to create a database and a table for those comments to persist for each row. The Retool DB is a great option. Here is a topic on how to achieve this functionality.
The challenge for your use case is, as you mentioned, the data from the API updates periodically. If we only add the rows we don't have, but there were updates in rows we already did, those updates won't show in our db unless we are constantly looking for changes. This may slow things down significantly.
With this in mind, what I would recommend doing is:
First, create a "comments" table with two columns besides the auto incrementing primary key, a foreign key to reference the id of the entity from the API the comment is for (i.e. "user_id", "customer_id", "entity_id"), and a "body" column with a "Text" or "String" "Field Type" depending on your needs.
Second, create a "Resource query" to get all comments from your db.
Third, create a new "Resource query" using Query JSON with SQL to run a join SQL query, in order to generate a result table that combines the response of the RestAPI call, with the results of the query from the previous step.
The "Query JSON with SQL Resource query" will be similar to this one from the docs:
Just make sure that the output of both queries are in an array format. You can always use the formatDataAsArray function if needed. Here is also a SQL cheatsheet in case you need it.
Feel free to ask any questions! 