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

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!

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)

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!

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?

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

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

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

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

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

Hi @Jamie_Rood ,

Can you please show me a screenshot?

Hi @Mohamed_Swellam ,

See screenshot below.

You’ll see that the name of the opportunity is pulling through (the big text in purple in the email copy), but then the next one isn’t (Which client is this project for? (from Open projects 3)).

I’m unsure why this isn’t pulling through even though this is definitely the name of the field, and the field exists in this table.

Thanks,

Jamie

It might be something very simple as the spelling or an upper/lower case letter. Did you copy paste the field name?

Yep I copied and pasted the field names and double checked. There isn’t a rule that you’re aware of where you can only reference one field that’s not native to a table is there? Only because I referenced Gender from the jobseeker table as a test and that seemed to pull through as well as the (Name and Surname) shown in the screenshot.

Im sorry, do I understand correctly that this field is not in the table you are referencing? It has to be there yes.

It is in the table, but it is a Lookup field from the Open Projects linked field.

Then it should work.

If you want, you can duplicate the base and delete all the info in it leaving just dummy data then sending me a view link so I can check it

Thanks @Mohamed_Swellam ,

Here is the link to the test base: Sign up - Airtable

I have left three records in there for you to use as a test. Let me know if you need anything else.

Thanks so much,

Jamie

Thanks for Sharing @Jamie_Rood ,

Please note as Im not an expert in HTML Im not able to fix it completely but I see the problem

The field name MUST be with curly brackets BUT not as an HTML. Meaning, the field name cannot be “Which client is this project for? (from Open projects 3)” and you add the curly brackets before the , it has to be {Which client is this project for? (from Open projects 3)}

Hi @Mohamed_Swellam thank you very much for looking into this. I think I understand what you’re saying, but I’m not entirely sure. Are you saying the curly brackets (‘{’) are conflicting as part of both the HTML code and the reference of the field?

Hi @Jamie_Rood

It is yes, also the name of the field has to be written all at once without HTML coding