Sendgrid Email HTML - Pull custom data from another record in a different table

[Jamie_Rood]

Hi!

I run a recruitment business and I currently have a table for ‘Jobseeker sign ups’ and ‘Open Projects’. When I get a new ‘Open Project’, I send an HTML email to notify jobseekers about the project. Apart from referencing the jobseeker’s Name and Surname in the HTML code, the rest of the information about the ‘Open Project’ is hard coded.

I wanted to know whether there is a way to include data about the ‘Open Project’ in the email HTML even though the Sendgrid block is set to send emails to a view in the ‘Jobseeker sign ups’ table.

Ideally when I get an open project, I would like to generate an email template containing the information from the fields that exist in the Open Project table, so I can notify the jobseekers about the details of this Open Project.

Any assistance is greatly appreciated. Thank you!

[Mohamed_Swella1]

Hi @Jamie_Rood ,

Welcome to Airtable Community!

You can definitely do that if you link the Project to the Job Seeker. the way I would do it is:

1. Create a formula field in Project table with the email data you need to send

2. Have a Linked Records field in the Job Seekers table where you can link this new project (simple copy and paste would work if you dont want to automate it)

3. Now that you have this linked, you can bring the formula field using a rollup or a lookup field (you might need to use a filter in this field to only include data for this 1 project)

[Jamie_Rood]

Hi @Mohamed_Swellam ,

Thanks so much for this.

I have formatted my email to pull from the fields I want to from the Open Projects table.

I have also linked the fields from the Open projects table to the Jobseekers table.

However, when I populate a record with ‘Name of opportunity’, it doesn’t populate the other fields from the Open Projects table. See screenshot below:

Please let me know how I can populate these so that the HTML can successfully reference them.

Thank you!

[Mohamed_Swella1]

Hi @Jamie_Rood ,

First of all you dont need to pull all the fields, you can do the formula for the email in the other table directly.

Secondly, are the look fields referencing the correct linked field? i.e.: Open projects 3 in this case?

[Jamie_Rood]

Hi @Mohamed_Swellam ,

Thanks for getting back to me. The issue I’m facing with doing the email in the Open Projects table is how do I reference each open project individually? E.g. if my HTML is referencing (Name of opportunity) how do I pull that information specifically for the opportunity I want to reference?

I believe the linked field is correct, yes.

Thanks,

Jamie

[Mohamed_Swella1]

Hi @Jamie_Rood ,

In this case you will need to do some manual work each time, where you will filter the project in the rollup / look field

[Jamie_Rood]

Hi @Mohamed_Swellam ,

Great, thanks. I’ve now created another view in the Open Projects table, where I will filter for each project that I want to send an email for.

I have linked some fields in the Jobseeker table and the emails are generating for each jobseeker linked to that project.

The only thing now is that I want to reference (Name and Surname) of the jobseeker to address them perfectly. When I reference the linked field, it pulls through all the names of the jobseekers linked to that project. Is there any way that I can get the individual name of each jobseeker to pull through one by one?

See screenshot attached for reference.

Many thanks,

Jamie

[Mohamed_Swella1]

Hi Jamie,

This is why I wanted you to link the project to the Job Seeker and send it from there instead from the Projects table. From there a Lookup field to get the info from the projects table will be better

[Jamie_Rood]

Hi @Mohamed_Swellam ,

I managed to link the Open Projects field to the jobseeker table and reference the fields in the jobseeker table in the email HTML.

Unfortunately it only pulls through the information from one of the fields from the Open Projects table though. I am referencing the lookup fields in the HTML but they don’t display in the Sendgrid preview.

Any ideas?

Thanks so much,

Jamie

[Mohamed_Swella1]

Hi @Jamie_Rood ,

Can you please show me a screenshot?