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.

Counting format occurence in a linked table

Topic Labels: Base design Data Formulas
Solved
Jump to Solution
990 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Beerain
4 - Data Explorer
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
kuovonne
18 - Pluto
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.

See Solution in Thread

1 Reply 1
kuovonne
18 - Pluto
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.