Bi-directional relationship not function as expected.

[maracles]

I have two tables:

• Order Facts
• Work Records

This relationship is One-to-Many. In this particular scenario the Order Facts is the fact table and its index is based on the OrderID which is unique. The work records table contains multiple rows per OrderID as each order can have multiple work records.

• Both tables have an OrderID column.
• Work Records has an additional Work Record Date column

I know this isn't the usual database scheme but for various complicated reasons there is not a way I have been able to get around it.  Anyway, my goal is to add a related column to my Order Facts table that contains the earliest Work Record Date for a row in that table with a matching OrderID.

I had assumed that the bi-rectional filtering would work for this, however I can only use related columns in the Work Records table - is there a way of reversing this?

Thanks.

[Nhallquist]

I think I understand what you are trying to do, and I think there is a solution.

Have you tried creating a small, 2 column table, where you have the OrderID and calculate the Earliest Work Record Date based on the OrderID from the Work Records?  Then, you could Left Join that small table to the Order Facts table, and thus get your Earliest Work Record date into your Orders Fact Table.