Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Counting format occurence in a linked table

Topic Labels: Base design Data Formulas
Solved
Jump to Solution
92 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello All.

In table A I have all of my outbound communications listed. In this table there is a field called "Month Sent", this field is used to link table A & table B. Table B contains the aggregations of all the communications sent per month (Month sent, Total sends, Total opens, percentage opens, etc.). 

I want to show the metrics for the communications for each format type per month (i.e. email, SMS, etc.). The ideal way of doing this would be to somehow have a single select field for the format in table B which whenever changed automatically causes that record to calculate the number of communications for that format only and update the record accordingly. 

I know there is an option in the roll-up to add a condition, but that would mean creating a different view and roll-up condition for each format. In Google sheets, I simply had a table, combo box and if statement that read something along the lines of IF the combo box is equal to the format then add to the count. 

Is there a clever way to do this on Airtable too?

I hope I have explained this adequately. 

1 Solution

Accepted Solutions

It sounds like both TableA and TableB have a single select field for {Format Type}, and both single select fields have the same choices. You also have a rollup field in TableB that rolls up only records in TableA that match TableB's {Format Type}. I am also assuming that each record in TableA links to only one record in TableB, but each record in TableB links to multiple records in TableA.

This requires a system of back-and-forth rollups. 

In TableA, have a rollup {Does Format Type Match TableB?} that gets the value of {Format Type} from the linked TableB record and compares TableB's {Format Type} with its own {Format Type}. Use the following formula in the rollup in TableA. (This could be done with two formula fields, but one is less clutter.)
IF( {Format Type} = ARRAYJOIN(values), "Matching Format Type")

Then in TableB, use a conditional rollup with the condition that the {Does Format Type Match TableB?} field is not empty.

Depending on the size of your base and the number of linked records, it might take several seconds for the rollup to re-calculate when you change the {Format Type} in TableB.

See Solution in Thread

1 Reply 1

It sounds like both TableA and TableB have a single select field for {Format Type}, and both single select fields have the same choices. You also have a rollup field in TableB that rolls up only records in TableA that match TableB's {Format Type}. I am also assuming that each record in TableA links to only one record in TableB, but each record in TableB links to multiple records in TableA.

This requires a system of back-and-forth rollups. 

In TableA, have a rollup {Does Format Type Match TableB?} that gets the value of {Format Type} from the linked TableB record and compares TableB's {Format Type} with its own {Format Type}. Use the following formula in the rollup in TableA. (This could be done with two formula fields, but one is less clutter.)
IF( {Format Type} = ARRAYJOIN(values), "Matching Format Type")

Then in TableB, use a conditional rollup with the condition that the {Does Format Type Match TableB?} field is not empty.

Depending on the size of your base and the number of linked records, it might take several seconds for the rollup to re-calculate when you change the {Format Type} in TableB.