Help with Pipeline, loops and emails

Question

Hi there,&nbsp;
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 to accomplish this and can't seem to figure it out. Here are the basics of the setup:

TABLE 1 - CONTRACTS

Contract Name
Status

TABLE 2 - Time Cards

Related Contract
Time Card Date
# of Hours

STEP 1: Quickbase Search Records on Contract table (Query - a)
Filtering by Status = Active

STEP 2: Loop through records

STEP 3: Quickbase Search Records on Time Cards table (Query - b)
Filtering by Time Card Date between {{(time.now).replace(day=1)}} and {{ time.now }}

STEP 4: Condition If Quickbase Search Records on Time Cards table is not empty

STEP 5: IF condition is met, Outlook Send an Email &nbsp;(Action - c)
Here is where I don't know how to get the records found from Step 3 into that email body. I would like to put the data into a nice html table format, but I just don't know how to loop through that data.

Attaching screenshot in case that is of use.

Any help is much appreciated! Thank you!------------------------------Carrie K------------------------------

chayceduncan · Answer

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:&nbsp;

Create a 'Table Row' field in your timecards. Make it a formula-text field. In that field - build a row object something like:

"&lt;tr&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;td&gt;" &amp; [Name] &amp; "&lt;/td&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;td&gt;" &amp; [Date] &amp; "&lt;/td&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;td&gt;" &amp; [Hours] &amp; "&lt;/td&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;td&gt;" &amp; [Activity] &amp; "&lt;/td&gt;" &amp;&nbsp;
"&lt;/tr&gt;"
Fill out whatever fields you want here.&nbsp;
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:&nbsp;
"&lt;table&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;thead&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "&lt;tr&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"&lt;th&gt;Name&lt;/th&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"&lt;th&gt;Date&lt;/th&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"&lt;th&gt;Hours&lt;/th&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;"&lt;th&gt;Activity&lt;/th&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "&lt;/tr&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;/thead&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;tbody&gt;" &amp;&nbsp;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SearchAndReplace(ToText([Combined Summary Row Field]), "; ", "") &amp;&nbsp;
&nbsp; &nbsp; &nbsp;"&lt;/tbody&gt;" &amp;&nbsp;
The idea is that your buildind the row in your child table and then putting those rows into the table body dynamically.&nbsp;

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.&nbsp;------------------------------Chayce Duncan------------------------------

carriekrakowski · Answer

Wow, this was much easier than what I was trying to do. What a great, simple solution! Thank you so much!------------------------------Carrie K------------------------------

chayceduncan · Answer

The only cautionary tale will be the 'style' if you want to change things like font-size, color, bolding etc.&nbsp;

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:&nbsp;
&lt;td style='font-weight:bold; color: blue;'&gt;Name&lt;/td&gt;&nbsp;
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:&nbsp;
&lt;td style='font-weight:bold| color: blue|'&gt;Name&lt;/td&gt;&nbsp;
And then in your contract formula-rich text field you put them back with:&nbsp;
SearchAndReplace(SearchAndReplace(ToText([Combined Summary Row Field]), "; ", ""), "|",";")------------------------------Chayce Duncan------------------------------

carriekrakowski · Answer

Yep, I noticed that and your suggestion is a great workaround. Thank you thank you thank you!------------------------------Carrie K------------------------------

Forum Discussion

Help with Pipeline, loops and emails