Collecting and calculating weekly sums in another table + Zaps

Hey @Kristen_Simental

You have the right idea. You need to create a ‘Reports’ table, then link every record you want included from your other table to a single ‘Report’ record. So you could call the record the ‘Daniel’ record and then link all his info. Then use a Single-select to for your Week 1, Week 2, Week 3, etc. or a Date field so you can do it for each student.

Then, you create your Rollup field. So your Rollup field would link to your other table (Students, I’m guessing), then link to the field you want to count. In the Aggregation formula, I sometimes use SUM(values) to calculate totals or you could use COUNT. Other times, I use ARRAYUNIQUE to view unique records.

It would probably help to see a screenshot of what it is you’re capturing and better what exactly you’re doing. You say it adds a 1 for a note, but I’m not sure what that means. Is it a new record in a table, so you have a table full of “1” records?

Thanks Hannah. Here’s a screenshot:

Ok gotcha, @Kristen_Simental.

Here’s my first answer:

I would create a ‘Reports’ table. Then create a linked record to the ‘Reader Counts’ table. Create a Rollup field and label it Count, choose the ‘Readers Counts’ table and then choose the ‘Read Count’ field. For your Aggregation formula, use SUM(values).

(I just happened to have a Rescue Operations example base, so in my example, Animals are your Students. lol sorry)

For the Reports table Primary field, you could use a Text field as the Primary and label it Jan 17- 21, 2021 or Week 2. You could also use a Date field.

Now, in the Report table, each record will be per 1 Student. So, you label it Week 2, and then in the Linked record field, link all of a single student’s record for that week. Your total Read Counts will be Summed in the Count field.

Here’s my 2nd thought:

You might consider making the Students as one table and the Reader Counts another table. When doing Reports, having multiple tables and creating linked records helps.

In the Reader Counts table, you could use a Formula field that combines the Student and a Week as the Primary field, a linked record to the Student, and then your Read Count field.

In your Reports table, you would then have a linked record to the Reader Counts table with the Rollup field.

This is what your Students table would look like

Why this is another option I suggested:

What’ll you find is that it might be difficult to match up the fields in your reports because there are no unique values and you’re creating duplicates (ie Student’s names). When you use multiple tables with linked records, and Formulas with the CONCATENATE option, you can bring multiple field values together and find data easier.

An example of how else this works would be an Inventory base where you have multiple items and individual sales orders where you bring everything together in an order.