I developed an app that lets me track the work time of employees. the employees create entries of the time they have spent on a project. So this table shows the data of: Person working, the project worked at, the time spent, extra costs.
Is there a way I can create two tables that sums up these data for each month (a line for each month) to show:
for each employee: sum of hours worked and sum of extra costs
for each project: sum of hours spent on the project and sum of extra costs
Yes, you can use what we call an Aggregate table. It is a simple table that contains rollups, queries and states. You can also use scheduled trigger workflow to create a line every month. You will be able to use that aggregate table anywhere you want by querying anywhere in any other tables.
In your case, you would probably need an aggregate table for employees too. If you have an employees table, you could use it to make the relations and rollips by employees. Then, use this table as queries in your main aggregate table to summarize everything.
An alternative if you don’t want to use more rows is:
Create a template column that joins the userID/projectID and the month and then a relation to itself (not great performance wise), check multiple matches. You can also use a query to not have to add a template column.
Create a rollup of hours worked/extra cost on top of that relation/query.
Create a single value column to return the first rowID from the relation/query,
Utilize a collection of rows that have rowID = first rowID from relation/query and display the rollup count.