Skip to main content

Hi, I wonder if anyone can help with a query. Using Zapier and email parser I’ve set up specific emails from my client to add as a new row to a table.

My client uses a reference number for each project, whereas I use the project name as it is easier to manage.

The fields that are pulled into Airtable are the date of the email and the reference number.

I’d like Airtable to automatically match that reference number to the project, and then add that linked item to one of the fields. ie if PROJECT NUMBER field is xxx-xxx-xxx show PROJECT field as {linked item}

I think I can do this with a very long winded IF THIS command, listing every one of the 20+ projects but can anyone think of an alternative technique?

Thank you in advance.

Is the email Zap adding a new project to your Projects table?

Is the email Zap adding a new project to your Projects table?


Thanks for the question, I should have clarified that. No, when an email comes in it adds a new row to a table called Purchase Orders, and each purchase order is linked to an existing entry in the table Projects.

I could just manually match the entries, but I get a lot of purchase orders so I’m trying to automate as much of the process as I can.

Thanks for the question, I should have clarified that. No, when an email comes in it adds a new row to a table called Purchase Orders, and each purchase order is linked to an existing entry in the table Projects.

I could just manually match the entries, but I get a lot of purchase orders so I’m trying to automate as much of the process as I can.


So you already have the reference number and name of every project in the Projects table? If this is the case then I think you should be able to deal with this in a multi-stage Zap. After the email triggers the Zap, you should be able to search for the project in the Projects table that matches the reference number. Then, in the final stage where you create a new row in the Purchase Orders table, you can add the Record ID of the project (that you searched for in the intermediate stage) in the PROJECT field.

Hi @C_Thirlwall - if I’ve understood your problem correctly, this might help, using a technique I’ve seen elsewhere on the community.

So, somewhere you have a list of the project IDs and the corresponding project names, yes? Add these to a reference table:

Make the ID the concatenation of the ID and the name (with a “|” as the separator)

Set up a control table which has just a single row in it with the ID of “.”:

Then link each row in the projects ref table to this control table record:

Now I’m assuming you’ve got a “project data” table which is the table that Zapier posts to:

If you can post the project ID and use Zaiper to post a “.” into the Link to Control field, then the other fields in the Project Data table will populate the project name field based on the ID.


I won’t detail all of the fields here (copy the base and have a look through), but essentially the control table is passing back the list of all project IDs and Names and the formula fields after that are extracting the project name based on the project ID.

This is going to be more manageable than a long IF statement - just add new project IDs and names in the ref table as they come along.

JB

So you already have the reference number and name of every project in the Projects table? If this is the case then I think you should be able to deal with this in a multi-stage Zap. After the email triggers the Zap, you should be able to search for the project in the Projects table that matches the reference number. Then, in the final stage where you create a new row in the Purchase Orders table, you can add the Record ID of the project (that you searched for in the intermediate stage) in the PROJECT field.


thank you - I’d not thought of dealing with it in Zapier rather than Airtable. I’ll look at that option as well as Jonathan’s suggestion.

Thank you for such a swift reply too

Hi @C_Thirlwall - if I’ve understood your problem correctly, this might help, using a technique I’ve seen elsewhere on the community.

So, somewhere you have a list of the project IDs and the corresponding project names, yes? Add these to a reference table:

Make the ID the concatenation of the ID and the name (with a “|” as the separator)

Set up a control table which has just a single row in it with the ID of “.”:

Then link each row in the projects ref table to this control table record:

Now I’m assuming you’ve got a “project data” table which is the table that Zapier posts to:

If you can post the project ID and use Zaiper to post a “.” into the Link to Control field, then the other fields in the Project Data table will populate the project name field based on the ID.


I won’t detail all of the fields here (copy the base and have a look through), but essentially the control table is passing back the list of all project IDs and Names and the formula fields after that are extracting the project name based on the project ID.

This is going to be more manageable than a long IF statement - just add new project IDs and names in the ref table as they come along.

JB


that looks very much like what I’m looking to achieve, thank you. I’m very grateful for the step by step screen shots too, as for such as visual product that is a huge help

Terms & ConditionsAccessibility statement