Adding virtual column info to workflow email?

AppSheet AppSheet Q&A
,
1

Hi all -

I have an app that generates an email that sends out just fine.
I’d like to pull in some data from an outside source (wunderground.com) to incorporate the hourly forecast table into the workflow email.

I have 2 tables. Table 1 collects user input (truck checkoff).
I have a second table (weather) that has an ImportHTML formula: (=importhtml(“https://www.wunderground.com/hourly/us/tx/dallas/75240?cm_ven=localwx_hour","table”,1)

I was hoping I could pull data from the Weather table into the first, which would then push that into the workflow email that gets sent, but I must not have the right button checked.

Or is this even the best way to accomplish this?
Any suggestions or help would be awesome!!

Cheers,
Tim

2

Do you want to include the whole Table2 or just the data for one row from Table2?

3

Hi Tim,

You can definitely include data from another table, in your email template.

One way to do this is to write a Start expression that selects one or more rows from the second table.
That Start expression would typically select the row or rows in the second table based on values in the current record from the first table. Your template can them display the appropriate field values from the second table. This article shows examples of templates having Start expressions that display rows from a second table. https://help.appsheet.com/en/articles/2697047-sample-email-templates

Start expressions are described here https://help.appsheet.com/en/articles/961746-template-start-expressions

Alternatively, you might include one or more Virtual columns in your first table that have AppFormulas that obtain data from the second table. You could then display those Virtual column values in your template.

The first approach is normally better when you wish to display more that one row from the second table.

4

thanks for your reply @Bellave_Jayaram - I was looking for some of the rows, but if I have to include all, that’s fine too.

it looks like @Phil has replied also - so I’ll see how it goes.

thank you, gentlemen!

5

so…i was finally able to get my data from the weather table into my email notification (yay!), but not exactly in a very friendly format…it looks like this:

2X_6_67749d5f06921f3d39ed988ca861e6103075e39a.png
2X_6_67749d5f06921f3d39ed988ca861e6103075e39a.png854×336 124 KB

What I’d like is like this:

2X_b_b169f40c4ae90d2507fe72918a9ea140902567f2.png
2X_b_b169f40c4ae90d2507fe72918a9ea140902567f2.png921×595 84.4 KB

And, no @Bellave_Jayaram , I don’t need all the rows, (or even all of the columns), but I have a feeling it’d be easier coding to include them all.

6

As that web service is returning HTML, I wonder if you could get that nice table formatting if you use a HTML template rather than a Google Doc template (which is what I presume you are using.)

7

no…actually i’m not using a template at all…
I have the data coming in via virtual columns as suggested by @Phil:

2X_2_29ebe43b24158cab6b108012170b9ce7a91fa46d.png
2X_2_29ebe43b24158cab6b108012170b9ce7a91fa46d.png774×232 37 KB

I’ve not been able to figure out the template thing for some reason. When I tell AppSheet to create the template, nothing happens (a template isn’t made/placed where it tells me it should be).
The email that goes out is the default body template:

2X_e_e3c4bf9a0bc00319c173b9b4f42f4d4a8111dacf.png
2X_e_e3c4bf9a0bc00319c173b9b4f42f4d4a8111dacf.png667×100 14.4 KB

If I could get the html to get pulled in via a virtual column (or some other way) that’d be great!

8

Here’s a template you could try:

<html>
  <head>
  </head>
  <Body>
<table>
<tbody>
<tr>
<td>
<h2><strong>Time</strong></h2>
</td>
<td>
<h2><strong>Conditions</strong></h2>
</td>
<td>
<h2><strong>Temp.</strong></h2>
</td>
<td>
<h2><strong>Feels Like</strong></h2>
</td>
<td>
<h2><strong>Precip</strong></h2>
</td>
<td>
<h2><strong>Amount</strong></h2>
</td>
<td>
<h2><strong>Cloud Cover</strong></h2>
</td>
<td>
<h2><strong>Dew Point</strong></h2>
</td>
</tr>
<tr>
<td>
<h2><span style="font-weight: 400;"><<Start: SELECT(Weather[ZIP], [ZIP]=[_THISROW].[ZIP])>></span><span style="font-weight: 400;"><<[</span><span style="font-weight: 400;">MBOID</span><span style="font-weight: 400;">]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Time]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Conditions]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Temp.]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Feels Like]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Precip]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Amount]>></span></h2>
</td>
<td>
<h2><span style="font-weight: 400;"><<[Cloud Cover]>></span></h2>
</td>
<td>
<p><span style="font-weight: 400;"><<[Dew Point]>><<End>></span></p>
</td>
</tr>
</tbody>
</table>
</Body>
</html>
2 Likes
9

I found the template!!!
So…now I just have to get the html into it.

10

So I’ve finally been able to import my table data into the email that gets kicked out…so, small victory there.

Any suggestions as to how to get each row from the table into individual rows in the email?
Here’s what the email looks like that gets sent:

2X_f_fbbd63b17a6192f4d28c8f99e89253ac5057cbe4.png
2X_f_fbbd63b17a6192f4d28c8f99e89253ac5057cbe4.png790×247 44.2 KB

And here is what’s in my email template (for reference):

2X_b_b1711897c86150914af81321b93ec15ef8144652.png
2X_b_b1711897c86150914af81321b93ec15ef8144652.png782×107 54.3 KB

I did try to use a START: & <>, but honestly that broke the email that got sent.
I checked for carriage returns, as that seems to be a common failure point, but that didn’t seem to be the issue.

Thanks in advance everyone!