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?
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):
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
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:
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 = '
';// Add table headers
for (const header in data[0]) {
htmlTable += <th>${header}</th>;
}
htmlTable += '
// Add table rows
formatDataAsArray(data).forEach(row => {
htmlTable += '
<td>${row[cell]}</td>;htmlTable += '
return htmlTable;
Event handlers:
await
DISEmail.trigger({additionalScope:{query_result: queryStaleDataIntegrityValues.data}})
Retool Email (called: DISEmail):
Body:
{{query_results}}
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: "
| 1 | " |
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 = '
';// Add table headers
for (const header in data[0]) {
htmlTable += <th>${header}</th>;
}
htmlTable += '
// Add table rows
formatDataAsArray(data).forEach(row => {
htmlTable += '
<td>${row[cell]}</td>;htmlTable += '
return htmlTable;
Alright, I got it all working. Thank you @GuilhermeSilva !!! I really really appreciate the help!
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!
