Help with Pipeline, loops and emails
Hi there,
Please be kind as I am very new at this :)
I'm in need of sending an email to clients with a list of time entries for the month (up to the current date). I am trying to use Pipelines t...
Forum Discussion
If you're open to it - I would suggest actually trying to build out the HTML table in Quickbase first using a rich-text formula that way you can see it and tweak it much easier, and then configure the email by just plopping that field into your pipeline. What that looks like is something close to this:
- Create a 'Table Row' field in your timecards. Make it a formula-text field. In that field - build a row object something like:
"<tr>" &
"<td>" & [Name] & "</td>" &
"<td>" & [Date] & "</td>" &
"<td>" & [Hours] & "</td>" &
"<td>" & [Activity] & "</td>" &
"</tr>"
Fill out whatever fields you want here.
2. In your relationship between contracts and timecards - create a new summary field that is a combined-text and you will summarize the 'table row' field you just made. In this summary field - add your filters for the timecards you want to show
3. In your Contracts table - create a new formula-rich text field called 'Email Table' - and in that formula do:
"<table>" &
"<thead>" &
"<tr>" &
"<th>Name</th>" &
"<th>Date</th>" &
"<th>Hours</th>" &
"<th>Activity</th>" &
"</tr>" &
"</thead>" &
"<tbody>" &
SearchAndReplace(ToText([Combined Summary Row Field]), "; ", "") &
"</tbody>" &
The idea is that your buildind the row in your child table and then putting those rows into the table body dynamically.
Then in your pipeline - you can search just for the contracts that have timecards to send, and simply put the html contents from the field created into the body.
------------------------------
Chayce Duncan
------------------------------
Wow, this was much easier than what I was trying to do. What a great, simple solution! Thank you so much!
------------------------------
Carrie K
------------------------------
The only cautionary tale will be the 'style' if you want to change things like font-size, color, bolding etc.
When Quickbase summarizes combined text - it combines them with a semi-colon which is why in my example I have a search and replace to remove it. If you do something like:
<td style='font-weight:bold; color: blue;'>Name</td>
And then you remove all the semi-colons then the style gets thrown off. Typically how I handle it is in the 'row' in your child table - I swap out the ; with a | and then search and replace it when it gets summarized into the parent. So it instead looks like:
<td style='font-weight:bold| color: blue|'>Name</td>
And then in your contract formula-rich text field you put them back with:
SearchAndReplace(SearchAndReplace(ToText([Combined Summary Row Field]), "; ", ""), "|",";")
------------------------------
Chayce Duncan
------------------------------Yep, I noticed that and your suggestion is a great workaround. Thank you thank you thank you!
------------------------------
Carrie K
------------------------------