Group by a linked multiple field

Ask the community Base design
#1

Hi there! I can’t figure out how to make it right. Any help is welcome!

I have set up forms for surveys.
The surveys are:

  • What are your 5 best movies of all time?
  • What are your 5 worst movies?
  • What are your 5 best horror movies?
  • And so on

Anybody can answer to these surveys by providing up to 5 movies each time.
I want to have:

Survey1: Movie3 (15 votes) Movie7 (13 votes) Movie4 (10 votes) and so on
Survey2: Movie52 (82 votes) Movie2 (54 votes) Movie17 (16 votes) and so on
Survey3: Movie26 (53 votes) Movie82 (26 votes) Movie1 (13 votes) and so on

I can’t find how to count every given movie grouped by survey (then sorted if possible)…
What do you think? Thx for your insight!

#2

I would do it like this:

For the questions in your survey, use a different linked field, linking to the relevant view in the “Movies” table, e.g. for horror movies, use “limit select to a view” - Horror movies.
Then in the movies table you just make a count field for each corresponding question, and use a SUM() formula in the {Total votes} field that adds these up.
Link for the form: Survey

#3

Wow! Thanks for your response. That is very helpful.
But I would also like to keep track of every answer, meaning:

Georges: Best movies : Movie5 Movie2 Movie64 Movie12 Movie31
Arthur: Horror Movies : Movie4 Movie1 Movie4
And so on

I did this: Airtable - Vos recommandations
(sorry for the French)
It’s working finely, except I can’t have the count on every movie per survey.
Since one cannot insert a rollup record, I offer another form to add any given movie.

#4

Can anybody help me on this? For now, maybe I’ll use Excel to find those counts…

#5

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.