# Counting format occurence in a linked table

Topic Labels: Base design Data Formulas
Solved
531 1
cancel
Showing results for
Did you mean:
4 - Data Explorer

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
18 - Pluto

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.