Hello All - I am trying to display the output from an SQL query directly within the body of an automatically generated email via Retool Email.
I have been able to create the SQL query I want, as well as create the emailing functionality which is working as expected. I just need to link the two functionalities. Has anyone done this before so that the table from the query shows up in the email body?
1 Like
Hello @Sam_Dreyfuss ,
What you are trying to do can be acomplished with a little bit of data manipulation, to transform the query into an html table (if that's your goal) and usage of the "additionalScope" property of retool. Here is how I did it:
Add this script to your query, as transform results (if you want it separate, just put it in a js query and define data as the result of your query):
let htmlTable = '<table border="1">';
htmlTable += '<tr>';
// Add table headers
for (const header in data[0]) {
htmlTable += `<th>${header}</th>`;
}
htmlTable += '</tr>';
// Add table rows
formatDataAsArray(data).forEach(row => {
htmlTable += '<tr>';
for (const cell in row) {
htmlTable += `<td>${row[cell]}</td>`;
}
htmlTable += '</tr>';
});
htmlTable += '</table>';
return htmlTable;
Next, you want to set an event handler for your original query (if you're transforming results) or for your js query (if you split it from the original one):
![image](https://community.retool.com/uploads/default/original/3X/8/5/85741e92a37c8da4c287b680ae50dfc20e30d84d.png)
After that, just set up your retool email query to use additional scope. It will show as yellow, as it sees the property as undefined, but it will work:
Finally, here is the result in my inbox:
Sorry for the long reply, hope it works for you! Let me know if there is anything else I can help you with, or if you are having trouble with any of the steps
1 Like
Hi @GuilhermeSilva -
Thank you so much for the reply!!! I really appreciate the help.
To confirm, when you say "original query" are you referring to the SQL query? I only have an SQL query and the Retool Email currently. I believe "original query" referees to the SQL query.
Additionally, it sounds like its possible I put all the logic Javascript code into the SQL query (is that correct?). If that's possible, I think that would be the "cleanest" route.
Thanks again for all the help!!! I look forward to your response.
Sam
For additional clarity, this is what my tabs look like:
![Screenshot 2024-06-20 125537](https://community.retool.com/uploads/default/original/3X/6/c/6c25b1611e0504562a6d8ca0867ffe0a311c25b6.png)
Yes, that's what I'm refering to.
You should have the js code in the "Transform results" part of yyour query.
As an event handler you can run the "DISEmail" query and put {{queryStaleDataInterityValues.data}} in the email body. Using additionalScope might be complicating it.
Thanks for the quick response, so would the following be correct?:
SQL Query (called: queryStaleDataIntegrityValues):
Transform Results:
let htmlTable = '
';
htmlTable += '';
// Add table headers
for (const header in data[0]) {
htmlTable += <th>${header}</th>
;
}
htmlTable += '
';
// Add table rows
formatDataAsArray(data).forEach(row => {
htmlTable += '
';
for (const cell in row) {
htmlTable +=
<td>${row[cell]}</td>
;
}
htmlTable += '';
});
htmlTable += '
';
return htmlTable;
Event handlers:
await
DISEmail.trigger({additionalScope:{query_result: queryStaleDataIntegrityValues.data}})
Retool Email (called: DISEmail):
Body:
{{query_results}}
Does the above look correct @GuilhermeSilva
I think you have an extra "s" in query_results, but that seems right.
A simpler alternative, in this case, is to set the event handler on queryStaleDataIntegrityValues to trigger DISEmail directly.
Then go to DISEmail and set the body of DISEmail to queryStaleDataIntegrityValues.data
@GuilhermeSilva We're getting closer! The output in the sent email now shows the actual HTML code:
|
1 | etc... but doesn't show the table itself.
Any additional thoughts? I feel like we are very close |
I copied the HTML code into the response prompt and I realize it changed the structure of my response above. I should have shown as text like: "
@GuilhermeSilva - would the fact that im reviewing the emails in gmail make a difference?
Hi @GuilhermeSilva - I'm realizing the issue. This block JS code outputs a result with quotes around it. Once the quotes are removes, the table shows up as expected. I just need to figure out how to remove the quotes from the outputted htmlTable.
let htmlTable = '
';
htmlTable += '';
// Add table headers
for (const header in data[0]) {
htmlTable += <th>${header}</th>
;
}
htmlTable += '
';
// Add table rows
formatDataAsArray(data).forEach(row => {
htmlTable += '
';
for (const cell in row) {
htmlTable +=
<td>${row[cell]}</td>
;
}
htmlTable += '';
});
htmlTable += '
';
return htmlTable;
Alright, I got it all working. Thank you @GuilhermeSilva !!! I really really appreciate the help!
1 Like
Good job figuring out the details! Sorry for not replying, was unavailable!
Glad to have been helpful!
No worries at all, that would have taken me like a week to figure out without your help. One more questions, would it be the HTML you sent, which I would need to adjust to add on the column headers to the table?
This part should add the headers, is it not working well for you?
I think you might need to change this part to something like:
// Add table headers
const headers = Object.keys(data)
for (const header of headers) {
htmlTable += <th>${header}</th>
;
}
htmlTable += '';
Something like that maybe? It worked for me!
2 Likes