How Can I Efficiently Pull Information from Related Records when Sending Emails Through an Automation?

[jthurston]

Hello,

My app has a Students table, and a Classes table. The Students Table has a ref column where users of the app can select a student’s class.

We often need to send emails to students that require information from the Classes table. I have a solution that has been working, but it seems terribly inefficient, and I feel like there must be a better way. In the Students column, I have a series of Virtual Columns that pull from the Classes table. And then when I use automations to send emails to students, I pull from that virtual column.

So for example, let’s say I need to send a message to all students telling them when their class starts.

In the Students table, I have a VC titled Start Date with this formula: Select(Classes[Class Start Date], ([Class ID] = [_THISROW].[Class]))

Then I create an automation with an email task that pulls from the Students table, and says: Your class will start on <<[Start Date]>>

Is it possible to skip the VC step and somehow add a formula to the email task that will pull the related information? We have recently begun allowing students to be assigned to 2 classes at once. So we have a 2nd class column. And to continue with my current awful system, I would need to create a second version of each VC (we already have 10!)

Thank you!

[Marc_Dillon]

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

And as a side note I just wanted to point out that you can insert complete expressions into templates as well, not just single column values. Like with your example above, you don’t need the VC in the app, just use the same expression in your template with angle brackets.

<<Select(Classes[Class Start Date], ([Class ID] = [_THISROW].[Class]))>>

[jthurston · Accepted solution]

Thank you so much! That seems way easier than the monster I’ve created. I see the format for a dereference is [ref-column].[value-column]

Would the expression in the template be as simple as <<[Class].[Start Date]>> ?

When you say you can use it in the template, does it have to be a Google Doc added as a template file? Or can I just type it on this screen?

[Marc_Dillon]

Justin_Thurston:

Would the expression in the template be as simple as <<[Class].[Start Date]>> ?

Yes

Justin_Thurston:

When you say you can use it in the template, does it have to be a Google Doc added as a template file? Or can I just type it on this screen?

They are both “templates”

[jthurston]

Cool beans. Thanks again!