How to create a table that only shows the changes

[halil]

hi,
i need to follow the changes in my table one. but i want to see only old value, new value.
is it possible to create it with Retool?

[ScottR]

@halil Welcome to the forum!
Can you share some more details? I am not quite sure I understand what you're trying to achieve.

[halil]

thanks @ScottR.
for example, i have one table in my db, i want to see every changes in our table without opennin my table. because my table a lot of columns, but the change is made only one column. so i do not want to wait too much while the whole table is loaded.

[mbruijnpff]

This would only be possible if you're actually keep track of the changes in the same table (eg, store the old value in another column 'old_value') or if you're keeping track of changes in a separate table with events.

[halil]

thanks for your answer @mbruijnpff .
so should i create another table for this purpose? because existing table huge enough to add new columns (in future we may need new columns).
i just wanted to learn, whether retool can help me to do that, or not.
anyway thanks again
and have a great weekend.

[mbruijnpff]

It is difficult to tell what to do with the limited information. It depends on how many changes you want to store and for how many fields. If it is only 2 fields and you only need to know what the previous value was, you could just add columns for the previous value in the same table. Quick and simple.

But if you need more info, eg when the data was changed, by which user and what the history was, then you'd be better off creating a separate table for 'events'. That would for contain for example the columns ID, foreignkey (record id of the row that has been changed in the other table), date/time, old value, new value, user.

Then each time the value changes, you also write the changes to the event table.

To show only the records in your table with changed values, you'll need to join both tables in your query and show them in the table.

Maybe a bit short explanation, but hopefully it helps a bit.

[halil]

thanks for your response. actually i need to see every changes in my tables. at the beginning it is enough to see old and new values. but i want to record that in another table, which i can improve it later, according to my needs. i mean i need a logic to create a table, which can hold changes, which is made in other tables in retool. i just wanted to learn, if it is possible to do that without creating a new table in my original db.

[halil]

@mbruijnpff for example, now we are using audit log in retool, retool provides us some log info, but there is no info about old value. i can see who did, i can not see what he or she did. Maybe i can add table name too to see which table is manipulated etc.

[mbruijnpff]

I think that a separate audit table is best for you in that case. There are various ways to set this up, but basically, whenever you update your tables, in the same transaction, you write the changed value to the new table as well.
You could create a table with the fields:

• id (PK,int)
• timestamp (timestamp)
• table (varchar)
• user (varchar)
• event (varchar, array or json)

This would be very flexible and you could use it to store various events like updates, deletions, errors, etc. You can add more fields to store all kinds of event data.

Querying the results can be on table or maybe application(id).

[halil]

@mbruijnpff is there instruction, which shows the steps? i am stuck to do that, since i am kind of new retool user.

[mbruijnpff]

What have you tried so far? What is working and what isn't? How does you app look like, what does it do? What data sources, etc.

There are no specific instructions available, you can tailor it to your needs.

[vinnie]

Something like this?

[halil]

@vinnie hi,
absolutely like this. one more column, which holds old value.
and user is allowed to see only upper table, not below one.
bottom table is for only admin.
how can i create this kind of table?

[vinnie]

My main table is called ProductsTable.

Accessing Changes: To see all the changes made, use ProductsTable.changesetArray.

Creating a New Table: This changeset array is the starting point for your new table.

• Delete Unwanted Columns: Remove any columns that come from the new table.
• Add New Columns: Click the + button to add new columns.
• Automatic Recognition: As you make changes, Retool will detect them and set things up for you. See the video for more detail.

Important Notes:

• Primary Key: Whatever you choose as the Primary Key in your original table will appear in the changesetArray. In this example, it's "ID."
• Column Source vs. Label: The original table might have columns labeled one thing, but its actual source might be something different. This will be in the changesetArray, not the original table column label. In this example, the source (from my API query) for the Description column in ProductsTable is actually short_description.

This video should help demonstrate - Creating a Record Update Table in Retool - Album on Imgur

[AnsonHwang]

Hello, here is demo for your reference. I use new feature of new table - Caption in the add-on to show the old value which is indexed out base on the value of new value, using js to do this.

return table1.changesetArray.map(item=>{
  let keys = _.filter(_.keys(item), it=>it!=="id");
  let copyItem = _.clone(item);
  keys.forEach(key=>{
    copyItem = {...copyItem, [key+"_oldValue"]:_.filter(table1.data, {"id":item.id})[0][key]};
  })
  return copyItem;
})

auditlog.json (25.9 KB)

My demo is not a complete solution. You should combine this with @mbruijnpff 's suggestion to save the data.

hope it can help.

[vinnie]

@AnsonHwang Love the caption to display the old value, thanks for turning me on to that feature!

[halil]

@AnsonHwang and @vinnie, thank you.

1. when i save the changes, all changes are gone in log table. how can i save them permanently?
2. is it possible to bring the user, who does changes, to log table?
3. is it possible to hide log table from user, after sharing the app`?

[vinnie]

• when i save the changes, all changes are gone in log table. how can i save them permanently?

I would think this data would need to be sent & stored somewhere, whether in your own database, Google Sheets, or better yet, the built-in Retool database.

• is it possible to bring the user, who does changes, to log table?

Not entirely sure what you mean here. Do you mean bring it into view? If so, when would you want that to happen? With every change may feel chaotic for users, and on save may be too late. Either way, that can be done with event handlers.

• is it possible to hide log table from user, after sharing the app?

I believe you would need to have user groups and what not set up so that those with edit capabilities can see the changelog, while those who should not see it do not.

Another route may be something like this:

// List of user IDs for which the component should be disabled
const disabledUserIDs = [123, 456, 789];

// Get the current user's ID from Retool
const currentUserID = {{current_user.id}};

// Check if the current user's ID is in the list
const isDisabled = disabledUserIDs.includes(currentUserID);

// Set the disabled property of the component based on the condition
yourComponentName.setDisabled(isDisabled);

[Sai_Sukheshwar_Boganadula]

Hi,
I'm using the form to update the table and I need the same function . I'm lost and stuck in this could someone please help me out of this?
I'm kinda new to the retool and I'm not particularly strong in mongo db and struggling to get it done.

[halil]

@vinnie many thanks,

• when i save the changes, all changes are gone in log table. how can i save them permanently? of course to store them, i need a table or another place, i wanted to ask that, i can create a table in retool, which it can hold the changes like above?

• is it possible to bring the user, who does changes, to log table?
  i want to add the user info, who make changes (which one worker1, worker2).

• is it possible to hide log table from user, after sharing the app?
  where should i place these code? or do i need to create js?