Saving Changes on a Custom Column

I am using a custom column (boolean) in a table to allow other users to come in and check mark rows of entries to mark them as complete. I would like these changes to be stored in the app so that they will exist the next time someone uses it. Is there a simple way to do this?
Screen Shot 2023-06-08 at 10.51.18 PM

The easiest way would be to add a new field to whatever table/collection you're using to get the data for that table. You could add something like a complete field that is set to false by default but can be updated by editing that table.

This is also something where using the new table component might help you. Accessing custom columns from the legacy table component is a bit wonky: you need to use table.columnMappers['Custom Column n'] n being replaced to the id of your custom column (if you only have one custom column it will be 1). In the new table component you could just give it a name and then access it's values through either changeSetObject or changeSetArray which is a lot easier.

Past that, It would be easier to give you pointers if you could give more information on what your limitations are and what kind of database you're using.

Thanks bonnyag. The database that I'm using is a read-only database and I don't want to be making any changes to the database itself, but rather just make edits to the one editable column in a table and have those changes saved between sessions. I can have the edits mapped to an id if that's helpful. I'm just not quite sure how to create an event handler that will allow me to save these edits or use changeSetObject or changeSetArray.

@Ben_Evans Okay I think I got it! From what I can understand it seems like you want to have multiple users update the state of those rows and have that show up for other users as well.

I don't know of any way to store that information "in" the app per say because tempStates and localStorage get reset every time you reload the app so you'll have to use some sort of database that runs alongside your read-only database you're getting the info from.

I think the simplest way to implement this would be to use Retool database and create a new table (in Retool db that is) that contains two columns row_id (or whatever the rows represent) and complete.

CleanShot 2023-06-09 at 12.31.33

Then you'll need the use the new table component because it will make your use case a lot easier. Set your row id as the primary_key (which will allow you to use the changeSetArray). Create a new column and set the id to "complete" and make it editable.

CleanShot 2023-06-09 at 12.34.42

Now when a user updates that table, create an update query to your retool database table and pass the table1.changeSetArray as the body of that query and since changeSetArray only has the id of the updated row and the updated value it will automatically update your table.

Then you'll need to query that retool database table on load and use that info to show which rows are checked or not on the table.

Hope this helps, let me know if you need more details!

1 Like

@bonnyag Appreciate the help!

What does the query look like to pass the changeSetArray code through? Would it simply look like the screenshot below where refund_complete is my new Retool db table with uid and complete as the only two columns?

I then added an event handler on the Save button to trigger the query. Is that the correct step or should I be doing something different here?
Screen Shot 2023-06-12 at 9.41.20 AM

@Ben_Evans You got it exactly right! The only thing left is to make sure to add a success event handler to update_query that queries your new retool database table again to make sure the changes were applied properly.

@bonnyag The query seems to have an error where it keeps running and doesn't actually update anything. What should the success event handler look like? Apologies for all the followup questions - this is all quite new to me :sweat_smile:

@Ben_Evans No worries! We've all been there!

Here's what your success handler on update_query should look like (just replace query4 with the GET query from your retool database table):
CleanShot 2023-06-12 at 09.05.17

Some follow-up questions though, do you have a GET query set up for your retool database table and do you have a way to map that out to your table (If not there's a few ways to do it that I can walk you through) ? If you don't that's probably why it seems like it's not updating. Also you mentioned there was an error with your update query, do you actually get an error message or is it just not behaving the way you expect it?

@bonnyag Thanks! I've added in a GET query and a success handler that triggers it:


The issue with my update query is that it continues to run and doesn't ever stop, so it doesn't complete the update. I'm not sure why that would be happening. The refund_complete table that I created in the Retool db is just the two empty columns, uid and complete. Is there possibly an issue with that?

@Ben_Evans I think I know what the issue is. In update_query, it should be tableB.changesetArray (the S shouldn't be capitalized like it is on your screenshot, sorry my bad for giving you the wrong spelling).

Also are you using the new table component or the legacy component? If you're still using the legacy component (like on the screenshot in your very first post) the changesetArray object will just return undefined.

@bonnyag I'm using the new table component. It seems to be working now after that edit to changesetArray, but now I've got two issues:

  1. If someone wants to edit a checkmark and uncheck it, will they be able to do so with these configurations? Or does this only allow you do make an initial edit to checkmark something and ?
  2. How can I add this editable "complete" column to another table component that is linked to another query with all the other data fields related to the uids?
  1. If a user unchecks the line, it should update your retool db and set the complete field for that uid to false instead of true (I would test it out just to be safe)
  2. I'm not sure I understand the question but it seems like you should be able to just duplicate whatever you did and add the column to a new table. You could create a new version of update_query that would get its info from the other table and write the logic into the existing update query too.