So I’m kinda new to Airtable. At least the complicated stuff and I’ve already come across a problem.
I just want to create a simple weekly look into my chatter’s earnings. But I have some problems with rollups. Yes, I know you can edit the view to make groups but that’s not my mission, I want to sum all of each chatter’s earnings of the week and show it its own record because I want to make it visible to my chatters and not get confused. It’s gonna be like a scoreboard for them so they are a bit competitive.
This is what I have done so far: (I’ve used chat gpt but its outdated last update on Airtable it has is in 2021)
So the first thing I did is made a form so my chatters can submit their earnings. The table looks like this: They add their earnings, date and they choose them by who submitted it (that’s linked to another table)
So the second table was supposed to be each record for one chatter: they fill it out in the form already so it puts all of they earning in the linked table row.
And the third table was supposed be to rollup or summ all of their earnings of the current week:
To be honest I am really frustrated beacuise I tryed a lot of things. I just dont get it how can I sum or rollup every chatters earnings from this week so I dont have to do it manually each week and to show each chatter in its own record bbut not multiple record for the same chatter. I had some problems with seting that it sums only this week beacuise they changed the sistem so much since 2021 now its WekNum? or something like that.
If you want weekly earnings, I recommend having a [Chatter-Weeks] table, where there is a record for each combination of chatter and week. Each {Amount Earned} record should be linked to the corresponding [Chatter-Week] (This can be done with an automation.) Then the [Chatter-Weeks] table has a rollup of the total sum.
Aha ok got it and I researched some more and maybe found another solution
So I’ve put a formula in the chatters daily earnings section: IF(WEEKNUM(NOW()) = WEEKNUM(date), 1, 0)
And that calculates if it’s this week it puts 1 if it’s not then 0.
What happens next year? You will have two weeks with the same week number but in different years.
The function NOW() is a resource hog. It may not seem like it when your base is small. But as your base grows, you may wish you had done things differently.
Checking for the current week will only give you stats for the current week. Later you may wish to have stats for previous weeks. Easier to build that in the system from the start than to go back and add that feature in.
Aha ok got it so there’s no way to do it that way?
With the tactic you suggested will I have to manually set it every week? Or can I use a formula? I am interested how the table might look like do you maybe have any examples.
Your formula to checking if a record is in the current week will work for identifying records in the current week. You can then rollup data for records only in the current week. However, there are other ramifications of your system. That is why I listed it as “food for thought” versus “don’t do that”.
I don’t have a system with a [Chatter-Week] table that I can share. You would not need to manually set it every week if you have automations that can create and link new records. You would a formula field for each data record that identifies the week (taking into account the year). Then the automation can search for an existing record in the [Chatter-Week] table. If there is one, link the data record to it. If one doesn’t exist yet, then create a new one and link it.
