Record Summation & Linking

[Dimitris_Alikio]

Hello, I am new to Airtable (AT) and I am evaluating it so as to become (or not) my mainstream application. One of the features I desperately need (and I can’t find it) is the vertical summation of records. It is a pity that the AT provides a summation indicator at the bottom ribbon of each field but this cannot be accessed or used in any way. Am I missing something? Can this be done?

The second question refers to dynamic linking of fields. I want to link an entire field (with how many records it may have, i.e. linked dynamically) because there is no way I can add them manually one by one with the plus sign (they are just too many records and the number of records in the field can change any time). Is there a way around this?

[David_Skinner]

It would probably be easier if you give an indication of what your Airtable base is intended to do. Structuring tables (and the links between them) correctly is the most important part of setting up a base.

[Dimitris_Alikio]

My AT Base is an advanced CRM and Sales monitoring tool. The primary field is the customer name and the next fields (vertical columns) are populated by sales figures, opportunity data etc. One vertical column (field) is booked revenue and I want to sum all revenue figures in the booked revenue column to a single record and then link this record to another base for further calculations. I see the sum of all booked revenue at the bottom ribbon but this in not accessible and have no way of adding the numbers vertically as Airtable does not support that.

[JonathanBowen]

Hi @Dimitris_Alikiotis - how about this:

You have one table that records your opportunities:

Then another table which does the roll-up of these opportunities:

You can hide the Opportunities column in the Summary table as this will likely get a bit crowded over time.

The Opportunites Summary column in the first table is a link to the Opportunities summary table using the status as a key (something you may have on the opportunity record already).

If you don’t want to roll-up on the opportunity status you could simply have a “booked/not booked” status in the summary table.

Here’s the demo I created:

Airtable

Summing Row - Airtable

Explore the "Summing Row" base on Airtable.

Hope this helps.

JB

[David_Skinner]

Something like JonathenBowen’s answer is likely to be the solution to your 1st question i.e. rolling up revenue into a summary table.

A couple of things you might want to bear in mind:

1. It might be best to have separate Transaction and Customer tables and then link each Transaction to a Customer. Definitely if your business model involves multiple transactions with a single customer.

2. Links between Bases are not possible. You can only link between Tables within a Base.

I still don’t quite understand your second question, so perhaps you can explain that within the context of your Base?

[Dimitris_Alikio]

Hello, thanks for the advice and the example. However, if you check your example you will see that all the opportunities you show, total to 155.000. Well, this is the number I need to have in ONE record, to use it to calculate total attainment level versus total target, etc. I am already an EXCEL file I have created long ago which I use daily and I have quite complex views which are difficult to maintain on changes and this is the reason I am evaluating AT which appears to be very user friendly although I understand it is not a spreadsheet…
My apologies for trying to link bases, I meant to say Tables within the same base. So, if I have 250 opportunities in 50 customers I want to AUTOMATICALLY sum all the booked revenue (say 155.00) which is the sum of ONE column and link/pass this number to another Table which does booked vs target calculations. This is what I have difficulty in doing. If I do the manual linking and the rollup, every time a new opportunity appears I will have to manually add it to the linked table which beats the purpose. I simply need an automatic link mechanism to link my summed booked revenue record and AT should pass this record to the linked table. Is this possible? (I hope I expressed my need in an understanding way…)

[Dimitris_Alikio]

Please see my answer to Jonathan.

[David_Skinner]

This how I would set up your Base from the information given so far:

Airtable

Airtable: Organize anything you can imagine

Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.

But yes, you will need to manually link Transactions to Customers, and then Customers to Summary. This does not seem like such an onerous task as I assume you are manually entering the Opportunities already.

[JonathanBowen]

Hi @Dimitris_Alikiotis - you can modify this down to a single row - this is what @David_Skinner has done in his example.

If I understand you correctly though, you only need to link the revenue line in the first table. In my example, if you make the link in the “opportunities” table (in red below):

the link in the “opportunities summary” table is done for you - you don’t have to go and make the connection there too.

JB