Number of Records per Week Number

[Felipe_Almeida]

Hey, Airtable Community!

I’m building a Running gamification system in airtable and right now I’m Stuck in counting the Number of records (Activities) per week Nº.

In my base, I have a table with the participants - which is a Link to another record: another table with a database of people - and one with the recorded activities - which has lots of fields, The important ones for this solution being the “Activity Day” - Which I use a formula field to extract the week number.

How Can I create a formula/rollup that counts the number of activities in a week?

[Mohamed_Swella1]

Hi @Felipe_Almeida,

Since you already have a field with the week no., create a new table where the week no. is in the 2nd field and link it to this table. You can then create a Rollup field and chose the formula to Sum.

Is that helpful?

BR,
Mo

[Felipe_Almeida]

Mo,

Thanks for the reply! But, unfortunately, this dos not solve my problem.

This is what it look like:

I want to count the quantity of same numbers that appears in the field “Semana” (week number), so I can use it in a Conditional Formula in the “Pontos” (Points) Field. The conditional is basically "If the number of records in a week X is less than 3, than return 10. If its 4 or above, return 0.

I dont think I need a field that counts or rollup the No of records in a week, just a formula that recognizes this amount of records.

Hope I’ve been clearer now!

[Mohamed_Swella1]

Hi Felipe,

Ya its clear. However, there is no such formula that would do it in a straightforward way, you will have to do a work around.

The Work around that I can think of would be as follows:

1. Create a new Field, this Field should be a formula combining the Participante and the Semana. Something like Participante&","&Semana . This will create a Field with content such as Felipe De Morais Gardim,7 .

2. In this new Field, customize it to Link To New Record, Create New Table. This will create a new table with Records containing unique values for each participant with the relevant week no. .

3. In this new table, create a Rollup Field and set it to Count. COUNTALL(values) . Will call this Field Count (for the next step). Make sure the Formate is set to Integer.

4. Add a new Field to the same table, with a formula IF(Count<3, 10,0). Now you should have what you need, correct?

5. IF you want to see this same no. in the Registros table, make a Lookup Field there and set it to Lookup this number.

Hope this was helpful.

BR,
Mo

[Felipe_Almeida]

Hey, Mohamed!

The Field “Participante&Semana” Is a good solution to create Cells that has the unique that I need to count.

Still, the bad part is that when I transform a formula field into a Link to new record/Create new table field, it does not automatically pull the data with the previous formula - If I create a New record, Id have to link it manually.

I tried automating the record creation in a new table P&S with Zapier, giving me these fields when I Create a new Record in Registros

Registros with field formula Participante&","&Semana

Table returning new records automated with zapier

Now the problem is that I can’t seem to find a way to link these new records to the Participants itself (Inscritos Table, this one is where I sign participants up to be able to create records with their names in Registros.

I was able to do a lookup field that gets all “Participantes&Semana” together, as you can see in the pic above.

But I really don’t know where to move from here. Do you still have any solutions in mind?

Once again thanks for the thought-provoking help!! It’s making me move foward on this.

Best,
Felipe

[Mohamed_Swella1]

Hi @Felipe_Almeida,

Yes you are correct that once you did the formula you cannot link the records, my bad. It should be the other way around. You should link the records to the P&S table. i.e.: in the 2nd field in this table, instead of having it as text, you can change it to Link To Record and chose the P&S field in the first screenshot.

Now if you Rollup the count in the P&S table, it should solve your problem.