How to count duplicate records

1537 4
Showing results for 
Search instead for 
Did you mean: 
5 - Automation Enthusiast
5 - Automation Enthusiast

I am working for an Independent cinema and I am writing down each film ever shown. I have looked on other treads and tried stuff out myself, but I can’t seem to find a solution to my problem.
As I write the Film Title in my main field, I would LOVE to have a chechbox field(or something like that) that automaticly checks if the film has been shown before. And I would also love to have a field that counts how many times it has been shown.

Is this possible?

All the best

4 Replies 4

You need a table for Films where each film is listed once, and another table for Showings. Each record in the Showings table would likely have the date the film was shown and a link to the Film from the Films table. Then your Films table would have a count-type field to count how many records it has been linked to in the Showings table.

Thank you for your answer! Although this may help providing an overview over all the films shown in the end of the process, it would be hard to start all over again with writing the dates of the showings in another table, and separate all the films to another. We recorded over 40 years of presentations and over 1700 records, and what I really need is a formula or a check box that can automatically tell me if the films I am writing down is shown before or not, and how many times. I am afraid i can’t make another table with the showings and a table with the films listed once.

I have found a (kind of) solution which is duplicating the main field with all films shown, linking that to a new table, then count the films linked to the main records. This process is troublesome, as I have to do it manually and regularly, but if it somehow is possible to do so automatically, it would save a lot of time

If you’ve already made the table and copied the name of the film from the old column to the new column, then you don’t need the old column anymore. Just enter the name of the film in to the Link column from now on, and turn the primary column into a formula field to show the name of movie and probably the date too.

If you need an automated solution to copy field values from one field to another, just use an Automation to do it. An “update record” step can easily insert the value of one field into another one.

You may use a ‘lifehack’ to get a ‘films listed once’ table (before i discovered it, I used ‘copy to Google Sheets, remove duplicates’ method).
Start to change field type (but not press ‘Save’), choose multiselect, you will receive a set of unique values, which you can copy by Ctrl+C, press ‘Cancel’ and paste where you want.

Note: It should’t work if your film list is a primary field, so you need to duplicate the field.
Also, primary field is recommended to be filled with unique values, i would use autonumber in your case.

I’m not sure if I correctly understand your task, but you can achieve it quite simple, using method described by Kamille_Parks.
Assume you have 1700 records, with (less than 1700) unique film names, that’s exactly your “Showings” table.

  • ensure your film list in “Showings” is not primary field (if yes, insert left autonumber field as primary. or any other unique serial#, “IMDB id” etc…)
  • using ‘lifehack’, copy ‘unique film list’ to table#2, as primary field (that will be your “Films” table)
  • in table1, convert your film list field to linked field, pointing at table2
  • at table 2, you will receive linked field filled with links to “Showings” near each film.
    add ‘count’-type field
  • at table 1, add lookup to linked field, choose this ‘count’

Now, when you add next record and type film name, Airtable interface will show you exactly what you need, with number of shows, and even provide kind of “name autocompletion”, just start typing and then choose from list.
If you add new film, not existed in table2, it will automatically create new record there, put link, and switch ‘counter’ to 1, without any efforts.