data modeling - Inactive relation - need it to be active.

[arunbyc]

The model, among other tables, primarily consists of a Date table, Employee table, Time_sum, and Time_detail.
The structure is as shown in the diagram.

Time_Sum table has the Total time spent on certain dates  by certain employees (depending on certain criteria)

Time_Detail contains the time for all dates for all employees on each of the projects they worked on, 

Employee tale and date table are what they are. 

The requirement is: When an employee's record for a given date is selected in Time_Sum table, the detail table (or matrix) should be filtered to show the records of the employee - not for that date but for the entire period selected in the date slicer.

Had the requirement been to show all time spent by the selected employee on all projects, for the day selected, I could have created a "Date &Employee_id" calculated field in both the summary and detail tables and linked them through a one-to-many relation. 

But in order to get all records of the employee, I set up a bi-directional relation between Time_sum table and the Employee table, and then a 1 to many (single) relation between the employee table and Time_detail. This works. The only hitch is it gives me all the records of the employee for all days in the time_detail table. Not just for the days selected in the date slicer.  

The date table is already connected to the Time_sum. So It is not letting me connect the date table to Time_detail as an active connection.

How can I get the detail table to show only the records per the date slicer, for the employee selected in Time_Sum table. Is the way I set up the model correct?

Thanks

AR

[lbendlin · Accepted solution]

What's your reasoning for the bidirectional connection?

[arunbyc]

I got it to work. I just did not know that I can simply make the inactive relationship active.when the field is connecting to two different tables and not to two fields in the same table. Just had to make it active and it worked.

BRW the reason for bi-directional connection is because the common connection betwwen the two time tables is the composite Employee_no and date. While that gives a proper 1to many connection, the requirement is to get all records of employee to br filtered. But i cant connect by just employee because it is many to many. So I thought I has to use employee table as the via media to propagate the relationship from time-sum to time_detail.