Using Functions Across Tables

[Joe_Ambrose]

Hello,

I am trying to identify the most recent meeting with a Contact. The key information is stored in the Activities table except for the Contact & Account which is linked from the Contacts table.

I’ve been trying to use a Max date function and could use some advice:

Table and Field:
Contacts in Contact & Account
Activity Completed
Activity Mode
Activity Contact & Account (linked to Contact Table)

I’m looking for assistance with the function I have written:
max(Activity!Completed, Activity!Mode,“Meeting”, Activity!{Contact & Account},{Contact & Account})

Any suggestions?

[Kamille_Parks]

Can you explain your formula a little more? By using Activity!{Contact & Account} are you trying to reference a field in another table? Because that’s not directly possible.

You have to link your records together (which you say you’ve done) and then in the [Contacts] table use a Rollup field targeting your {Meeting Date} field with the MAX(values) aggregation.

[Joe_Ambrose]

Yes, I am trying to identify the max dates for a meeting for the contact & account fields that are in a different table (Activity) and bring the date back to the Contacts table on the Contact & Account record for that person.

[Joe_Ambrose]

I have already concatenated the Contact & Account columns to form a unique value in the Contact table. I have linked that Contact & Account field in the Activites table.

[Kamille_Parks]

You should be able to use a Rollup field in your [Contact & Account] table targeting the date field from the [Activity] table using the MAX(values) aggregation. If that doesn’t work I’m not sure I can help without looking at the base

[Joe_Ambrose]

Hello Kamille,

I’m trying to write a function across two tables and multiple fields, but the naming convention is tripping me up.

I need to identify the most recent meeting with a contact. The results will be stored in the [Contacts] table, which is linked to the [Activities] table via the {Contact & Account} field.

My use case is to pull the most recent date on the [Contact] table from the [Activity] table when the {Completed} field is populated (w/ a date), the {Mode} = Meeting and the {Contact & Account} name are equal

This string is getting an Invalid Formula error:

MAX([Activity]!{Completed}, [Activity]!{Mode}="Meeting",{Contact & Account}"="[Activity]!{Contact & Account},””)

Any suggestions or could you please confirm the syntax for tables and fields?

[Jeremy_Oglesby]

Hi Joe,

The way you are trying to reference the Activity Table from your formulas will not work in Airtable. A formula field will only be able to access fields within its own table (the same table within which the formula field exists).

To find the MAX() date for Activity records that meet certain criteria, you will likely have to do some ping-ponging of data between the two tables. You can find the MAX(values) for the date field from Activity table in a Rollup field located in the Contact table, but you’ll have to do your whittling down of Activity record properties with formula fields in the Activity table itself. You will probably have to use several fields, passing data back and forth between the tables with Lookups and Rollups.

I would like to offer more specific help here, but I’m on my phone and with my family so I have to call it quits for now.

[Joe_Ambrose]

Thank you for your assistance Jeremy.

As the largest date is needed if the Contact & Account has had Meeting for each of the linked records, I created this function. However, it is an invalid formula. Any thoughts?

[Joe_Ambrose]

MAX(values),[Activity]!{Mode}=“Meeting”,"")

[Kamille_Parks]

You can’t filter results like that from within your Rollup. In your Activity table add a new formula field with the formula: IF({Mode}="Meeting",{Mode}). Call this field {IfMeeting} or something similar. Hide this field from view so its out of your way. *

In your [Contacts] table, your Rollup field will still use your {Interactions} field for linked records, but the field from [Activity] will now be {IfMeeting}. The aggregation function will just be MAX(values)