I've done something like this I've created a new collection for auditLog and inserted the data to audit log table.
Hope this serves your use case. 
2 Likes
- your-instance.retool.com/resources - find the
databaselink at the top for Retool's built-in DB.
- 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.
2 Likes
@Sai_Sukheshwar_Boganadula thanks for your answer,
is it possible to share UpdateFields query and AuditLogTable query?
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)?
have a great weekend everyone 
is there anyone, who can help me?
Hi there! This may be because you are using tableName.changesetArray.name.
The changesetArray is an array of objects, so you'd need to map over the array to get each change, and key into the value tableName.changesetArray[0].name, for example
@Tess thanks for your answer.
my problems are that;
- i can catch the changes and put them into my table with the following code
{
"Id":"{{table1.selectedRow.id}}",
"Old":"{{table1.selectedSourceRow.name}}",
"Updated":"{{table1.changesetArray[0].name}}",
"updatedBy":"{{current_user.firstName}}",
"updateAt":"{{moment()}}"
}
But this part does not bring the old value, since when i change one cell then press enter, that makes the selected cell become next cell. so "Old":"{{table1.selectedSourceRow.name}}", part does not bring old value, it only brings next row related cell.
for instance: 2nd column, 2nd row i change and press enter, now selected row become 2nd column 3rd row.
- when i click any cell without any change, it cause my table to add one more row, because it accepts selecting any cell is a change
i tried this with sql, but...
INSERT INTO mr_audit_log_retool (entity_id, table_name, field_name, previous_value, new_value)
VALUES
('{{table1.changesetArray[0].buchungId}}', '{{mr_bookings.id}}', '{{table1.selectedCell.columnId}}'
, '{{table1.selectedCell.value}}', '{{table1.changesetObject[1].buchungEnde}}');
but there are some handicaps. that does not have a dynamic structure
- there is no loop to get all changes from changesetArray
- column name (field_name), previous value and new value is catched staticly.
and i tried to make it in a loop, that does not seem good.
i totally need help 
there is an another source, you can compare it, if you need.
Inserting Rows Into a Table from Node.js.
table1.changesetArray[0].forEach(each => {
// Extract key and value from the object
// Build and execute the INSERT query for each key-value pair
'INSERT INTO mr_audit_log_retool (entity_id, table_name, field_name, previous_value, new_value) VALUES (table1.changesetArray[0].buchungId, mr_bookings.id, table1.selectedCell.columnId, table1.selectedCell.value, table1.changesetArray[0].each)';
});
Hi @halil
For JS loops, you can use .trigger() and addtionalScope to pass values from the JS query to a SQL query (general tutorial here).
Here's a similar example
I believe we are exploring ways to query old & new values, as discussed here. You should be able to reference the table data for the original values though (something like: table1.data.find(x=>x.id === table1.changesetArray[0].id).property_name)! You could have some JS logic that checks if the old & new values are different
1 Like
thanks @Tess ,
i am little bit confused.
is that right?
- i need to create sql query to insert new datas.
- i need to create a javaScript query to trigger first query.
and i have no idea how to integrate them, can you a little bit explain?
right now i have sql query, which is a little bit dynamic, it is like
INSERT INTO mr_audit_log_retool (entity_id,
table_name,
field_name,
previous_value,
new_value,
retool_user)
VALUES
('{{table1.changesetArray[0].buchungId}}',
'{{mr_bookings.id}}',
'{{Object.keys(table1.changesetObject[1])[0]}}',
'{{table1.data.find(
x=>x.buchungId === table1.changesetArray[0].buchungId)[Object.keys(table1.changesetObject[1])[0]]}}',
'{{table1.changesetObject[1].buchungEnde}}',
{{current_user.id}});
Hi @halil ,
Your query is failing because of the z in the previous_value. Format the previous value using momentjs to make it match the mariadb/mysql datetime format (yyyy-mm-ddThh:mm:ss.sss)
1 Like
i want to share the solution
function runSaveToAuditLog() {
//-table1- is the source table
if (!table1.changesetArray ||
table1.changesetArray.length == 0) {
console.log("The -changesetArray- is emtpy.");
} else {
console.log("The -changesetArray- has entries. Lets process!");
//The -changesetArray- has en entry for every channge, let's loop that table
let bookingsChanged = table1.changesetArray.length;
console.log("size: " + bookingsChanged);
for (var i = 0; i < bookingsChanged; i++) {
let entry = table1.changesetArray[i];
console.log("Working on entry with booking id = " + entry.buchungId);
//Lets so our what changed and how we need to process it
//What we need for putting an entry into the audit log
let table_name = "mr_booking" //For now this is static
let entity_id = entry.buchungId;
let retool_user = current_user.email;
//The changeset saves one entry per booking but can have either buchungStart or buchungEnde or both set
if (entry.hasOwnProperty("buchungStart")) {
let field_name = "buchungStart";
let new_value = entry.buchungStart;
let previous_value = table1.data.find(x => x["buchungId"] === entry.buchungId).buchungStart;
console.log("Ready to save to database.");
//Now all values are set, let's write them into the database
console.log("-------------------");
console.log("table_name = " + table_name);
console.log("field_name = " + field_name);
console.log("entity_id = " + entity_id);
console.log("previous_value = " + previous_value);
console.log("new_value = " + new_value);
console.log("retool_user = " + retool_user);
console.log("-------------------");
save_to_audit.trigger({
additionalScope: {
entity_id: entity_id,
table_name: table_name,
field_name: field_name,
previous_value: previous_value,
new_value: new_value,
retool_user: retool_user
},
onSuccess: function() {
console.log("Successully saved in database!");
}
});
}
if (entry.hasOwnProperty("buchungEnde")) {
let field_name = "buchungEnde";
let new_value = entry.buchungEnde;
let previous_value = table1.data.find(x => x["buchungId"] === entry.buchungId).buchungEnde;
console.log("Ready to save to database.");
//Now all values are set, let's write them into the database
console.log("-------------------");
console.log("table_name = " + table_name);
console.log("field_name = " + field_name);
console.log("entity_id = " + entity_id);
console.log("previous_value = " + previous_value);
console.log("new_value = " + new_value);
console.log("retool_user = " + retool_user);
console.log("-------------------");
save_to_audit.trigger({
additionalScope: {
entity_id: entity_id,
table_name: table_name,
field_name: field_name,
previous_value: previous_value,
new_value: new_value,
retool_user: retool_user
},
onSuccess: function() {
console.log("Successully saved in database!");
}
});
}
}
console.log("And we are all done!");
}
}
runSaveToAuditLog();
1 Like
What was the issue? It doesn’t seem that you formatted the date?
1 Like
the problem is i could not pass the values to next query with additionalScope in jS query.
and my main goal was like; get all changes with changsetObject and loop them and pass the values with trigger() additionalScope to sql query and insert the passed values in my table.
1 Like