Hi everyone,
I am new to airtable and trying to create a view that will give me a ‘count’ of how many times a client attended a session in the month.
I have linked the ‘client’ table and select all the clients that attended for each session. As can be seen, I have also added an ‘attendance months’ field so I can group by month.
I am having trouble trying to structure one of the tables to provide the count for each client for the given month.
Any help is appreciated, thanks.
Welcome to the community, @David_Dunford! :grinning_face_with_big_eyes: This can be done using rollup fields in your [Clients] table. Using November 2021 as an example, create a rollup field named something like {2021 Nov}, with this configuration:
Here’s the aggregation formula so you can copy it for testing:
IF(values, LEN(SUBSTITUTE(ARRAYJOIN(values, ""), "2021 (11) Nov", "x")))
That formula takes the collection of all month labels from the attendance table for a given client (the contents of the “values” variable), mashes them all together with the ARRAYJOIN() function, replaces each instance of the label with a single character (it doesn’t have to be an “x”, as long as it’s just a single-character string), then returns the length of the result.
Duplicate that rollup field as many times as you want, changing the label string replaced by the SUBSTITUTE() function with the string for another month.
