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).
@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?
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.
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.
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);
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.
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?
You'd need to pass {{current_user.firstName}}{{current_user.lastName}} when saving changes.
I've got a working example with just my first name. I set up a change cell event handler on my main table {{productsTable}} that runs query10, which contains code (below) you will use to add a user's name. In my table that displays all active changes for review before saving, the data source is query 10. If you wanted to store this to a database, just send query 10 data.
// Get the current user's name (you can add in last name or change to ID if necessary)
const userName = current_user.firstName;
// Get the current changeset array from the table
const changesetArray = ProductsTable.changesetArray;
// Modify the changeset array to include the user's name
const modifiedChangesetArray = changesetArray.map(item => ({
...item,
userName: userName // Add the user's name to each item
}));
// Return the modified data
return (modifiedChangesetArray);
Yeah it'd be a JS query that loads on page load. Below may be a better example.
// List of user IDs for which the component should be hidden
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 and return the result
return disabledUserIDs.includes(currentUserID);
When the query is run, it will return true if the current user's ID is in the disabledUserIDs array and false otherwise. You can then reference this query's value in the Hidden property of the component you want to hide.
i can bring the changes into my table with user info. But i have some issues;
a. before saving my table (which holds the changes) updates itself,
b. i can not bring the changed values, only initial values,
c. now it can show only names, but i need other columns too. in order to achieve that, is there approach to determine the changes field and bring old and new values (one code for name, date, role etc changes)?
