Skip to main content
Question

Help with Pipeline, loops and emails

  • February 14, 2024
  • 0 replies
  • 403 views
Forum|alt.badge.img+2

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 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  (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
------------------------------
    This topic has been closed for replies.

    Forum|alt.badge.img+9

    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: 

    1. 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
    ------------------------------
      Terms & ConditionsAccessibility statement