How to pull data from another table if it matches a condition?

[J_E1]

On one table I have item name/type/start date, and I’d like airtable to auto-fill some cells next to it in that row depending on the type/start date selected (type is a dropdown single select).

So for instance on this table:

When the type (green arrow) is monthly email (or whatever the type is), then fill in the cells next to it (purple box) which is called from this table:

I’d also like it to fill in dates according to the start date of that item, so for instance if it’s type “monthly email” starting on June 17th, then according to the second table calculate the date of 2 weeks before the start date.

Going to sync this with our CRM for activities, so these cells next to it are the item activities name/date.

Here’s a loom video if it’s easier, thanks a lot

Loom

Charged Production [Copy for Development - June 10, 2022]: Pipedrive...

[Kamille_Parks]

J_E1:

type is a dropdown single select

Aitable Rule of Thumb: if you have a Single Select field with options that also are all records in another table and want to pull in data from that table based on the selection, you shouldn’t be using a Single Select field. that’s what Link to Record fields are for.

In general, it appears you’d have a much easier time if you convert many of your fields to the proper field types. For instance, the {PD Activity 1 - Date} field in the Pipedrive Activities Info table should be a Number, not a Single Line Text. Same with the other {… - Date} fields in that table, probably. The reason for this is because you’re going to use these numbers to perform math on, and you can’t reliably perform math with text. If you’re worried about losing “before” or “after” context, use positive numbers for “after” and negative numbers for “before”.

In your Pipedrive Activities Sync table:

1. Convert your {Type} field into a Link to Record field pointing at the Pipedrive Activities Info table.
2. Then add a lookup field that pulls in the linked record’s {PD Activity 1 - Name} field.
3. Convert the {Activity 1 Name} into a Formula. The formula for the {Activity 1 Name} field in the Sync would then be something like:

SUBSTITUTE({Name of Lookup Field}, "[p]", DATETIME_FORMAT({Start Date}, "M/DD/YYYY")

^^ Since the {PD Activity 1 - Name} field in the Info table has a placeholder for where the date is supposed to be entered, make sure your placeholder is something that would never appear elsewhere in the string. In the example above I assumed the placeholder was [p].

4. Repeat for your other name fields.

For your dates you want to be calculated, you need to use formulas. Again, in the Sync table:

1. Add a Lookup field for the {PD Activity 1 - Date} field
2. Convert {Activity 1 Date} into a formula using something like:
   DATEADD({Start Date}, {PD Activity 1 - Date Lookup Field}, 'days')
3. Repeat for your other dates.

---

You didn’t ask, but the {Client} field in your Pipedrive Activities Sync table should probably be a Link to Record field pointing at your Clients table.

[matthew_b]

Hi! Not sure if this sort of things is still an issue you're trying to solve, but wanted to share that Whalesync just launched a 2-way sync Pipedrive integration that works with Airtable (and includes Activities as a supported table)

https://www.whalesync.com/sync/pipedrive-airtable