Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Formula to roll-up data from linked records

1467 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Brenkoh
7 - App Architect
7 - App Architect

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!

0 Replies 0