Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Is it possible to have a pivot table structure for data?

Solved
Jump to Solution
2102 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Sean_Patterson
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a table of feedback for two cafes in a base. Each bit of feedback has an assigned category. 

Screenshot 2022-12-21 at 16.22.25.png

I have another base that is linked to the category so that I can view aggregated data such as average feedback score by category. This data is then visualized outside of Airtable. 

Screenshot 2022-12-21 at 16.40.04.png

The problem is that in Airtable as category is the primary, it lumps the cafes together in each row, so from this table I can't distinguish one from another. I need an output where I can see organisation as a breakdown of the  category. Something like this (almost like a pivot table structure):

Screenshot 2022-12-21 at 16.34.38.png

How would I go about designing my base in order to achieve that?

IMPORTANT - the data needs to be in one view ideally rather than filtered views because my visualisation tool can read at the table level, but not view level (I do my filtering there).

1 Solution

Accepted Solutions
Mohamed_Swella1
13 - Mars
13 - Mars

Hi @Sean_Patterson 

One way comes to mind is having the Categories Cafe specific. (this doesn't have to be on the form side, it can be done using a formula and automation on the backend). 

So for example, in your 2nd table, instead of Service, it becomes Robs Cafe - Service

See Solution in Thread

5 Replies 5
Mohamed_Swella1
13 - Mars
13 - Mars

Hi @Sean_Patterson 

One way comes to mind is having the Categories Cafe specific. (this doesn't have to be on the form side, it can be done using a formula and automation on the backend). 

So for example, in your 2nd table, instead of Service, it becomes Robs Cafe - Service

thanks for the answer @Mohamed_Swella1. The issue with that is that this data is the source of an app. The app filters data based on the organisation and linked emails. It then shows filtered data based on the organisation from the table (works fine when it's not aggregated) and visualise it. If I had categories for each cafe then I would have to create individual app instances for each cafe which isn't practical. 

You can still have the category there, just not in the primary field. This should still work in your app

Hi Sean, here're instructions for what Mohamed_Swella1's describing

1. Create a new table called "Cafe specific feedback" or something, and link it to the table "POC Data"
2. Create an automation that triggers when records are created in the "POC Data" table
3. Give it an "Update Record" action that updates the original triggering record's linked field to the "Cafe specific feedback" table with the following values:

[VALUE OF THE {Organisation} FIELD] - [VALUE OF THE {Feedback Category Label} FIELD]

The new "Cafe specific feedback" table should now display the data in the manner you want

Automatic Pivotal table, CountIf, CountIfs
•Table A field a => Change Field type to link to another record of Table B, Allow linking to multiple records => Choose Blank Table B => Create Roll up field in Table B => Source : Table A, CountA(values) or CountAll(values) ,Only include link record , + add condition, add group)