So here is the problem:-
I have a database on the cloud which is connected to retool which in itself contains around 1000 rows which are sales leads with primary key as a unique email. I want to upload a csv to the retool app and filter out pre existing leads with the aid of email. If a email in csv itself exists in the db it should not be displayed on the table or if possible deleted from csv as well. I was trying to use filebutton.parsedValues[0].Email, but it is not working at all.
In summary is there any way to dynamically modify the uploaded csv and or change the table.
Hi Anand, If your database already has the email set as the primary key, SQL should inherently handle the task of only adding a new record if the email is unique. However, without knowing the specifics of your database system, I can't provide precise guidance.
Actively deleting the duplicate records from the CSV itself would require additional scripting or processing.
For example, you could add a new CSV resource, then you could add a script inside a JS query that goes through each record in that resource in order to insert the record if it does not exist in your main table and delete the record from the new resource if it does.
In this case, you will need to add two queries in GUI mode: one for inserting a record and one for deleting a record. Here is documentation about inserting and deleting records using a GUI mode query.
Your JS query should include Retool's built in .trigger() method. Here's a snippet of what the code could like like for inserting data:
for (let i = 0; i < tempData.email.length; i++) {
insertTrigger.trigger({
additionalScope: {
userData: {
email: tempData.email[i],
first_name: tempData.first_name[i],
last_name: tempData.last_name[i],
id: nextId++,
},
},
onSuccess: function () {
console.log("Successfully ran!");
},
onFailure: function (error) {
console.log(error);
},
});
}
}
Here's an example of what the object inside the insert trigger could look like:
{{ insertScript.userData}}
Note that, in this case, userData is the object inside the additionalScope object from the example .trigger() method above. Also keep in mind that you would need to add a trigger for deleting a record in order to meet your requirements. You could do so inside an if clause that checks to see if the email is already in the other table.
Here is documentation for how to download the updated table data after your query has ran.
I've attached an example application with this setup if you would like to take a closer look.
trigger-query-example.json