Hi, I'm building a timesheet app where emplo...

[Ville_Valve]

Hi,

I’m building a timesheet app where employees log their work time for different projects. Employee can have many timesheets and every timesheet has a reference to a row in Projects-table.

Report template works fine until the point where I would like to list all projects where the employee has worked and present the sum of work hours in every particular project.

I’ve tried several variations of SELECT() expression but none of them seems to work in a way I would like. I have listed some of the variations and their results in the template file: https://docs.google.com/document/d/14KYEv0ANpQ4cyjH_Bob-d1Q4dsKZ8g3FFDwwaO1R658/edit?usp=sharing

I can generate a correct list of references to Project-table with expression SELECT([Related Timesheets by Employee][Project], TRUE, TRUE) but this is not valid together with template start expression.

[List of reverse references][Column] seems to work in some places (for example the sum expression in the last row in report table, but maybe this is something that is not fully supported?

Any help or comments would be appreciated!

[Aleksi_Alkio]

Could you please show an example what you would like to have as a result.

[Ville_Valve]

Basically I would like to have table that shows all the projects that an employee has been working with and the employees total hours for each project.

Project1

16,5h Project2

9h etc…

Since the amount of projects for one employee is not fixed, I reasoned that I should build a start-end expression to go through the projects. So the first step would be to get this working and after that find a way to calculate the sum for each project.

[Harry2]

@Ville_Valve Could you elaborate on the relationships between the employee table and the project table? Is it a one-to-one, one-to-many, or many-to-many relationship? What are the exact columns involved?

[Ville_Valve]

@Harry Employee can have many timesheets and there is a reverse reference to them in [Related timesheets by Employee]. Every Timesheet record references one project with ref column [Project]. So I guess the relationship between Employee and Project tables is many-to-many. What confuses me most is that the IN() expression does not work even though the list used for comparison seems to have the correct values before the start expression. Maybe the problem is somehow related to the context where the expressions are evaluated, but still I can’t see it.