Formula to roll-up data from linked records

Hi everybody,

I use Airtable as a CRM to track all my contacts and interactions. It is pretty neat that I am able to open up a contact in expanded view and view a list of interactions I had with this contact. However, I had to manually click into each individual interaction to view the information I needed. The entity-relationship is as follows:

  • Each Contact may have multiple Interaction

  • Each Interaction has 4 key fields namely Date, Method, Notes, Next Steps

My objective was to put all my interactions for each contact into a single column for quick viewing. To set this up, I did the following steps:

  1. In the Interactions table, create a new column named “Interaction Summary” with ‘Formula’ as the column type.
  2. Create your formula as follows and customise according to your needs:

Formula:
“\n” & CONCATENATE("[",DATETIME_FORMAT({Date},“DDMMYY”),"-",{Method},"]") & “\n” & “=== Notes ===”& “\n” & {Notes} & “\n=== Next Steps ===” & {Next Steps} & “\n”

The '\n’act as line breaks to ‘prettify’ the concatenation.

Expected Output:
[010420-Online Chat]
=== Notes ===

  • Some text
  • Some text

=== Next Steps ===
Follow-up on proposal

[020420-Online Chat]
=== Notes ===

  • Some text
  • Some text

=== Next Steps ===
Negotiate on proposal pricing

  1. In your Contacts table, create a new column named ‘Interaction Summary’ with ‘Rollup’ as the column type.
  2. For the roll-up set-up, select ‘Interaction’ for the linked-table to summarise data from, select ‘Interaction Summary’ as the data you’d like to summarise and type in ‘CONCATENATE(values)’ as the aggregation formula.

This is a use case for a CRM base but I believe it can be used for many other situations as well!

Hope you guys find this interesting!

3 Likes