Self Join using a calculated field

[DFehrenbach]

HAving difficulty getting this to work:

have table of records each with a from and to date.

have global from and to dates. 

have a clac field in each table record (isConcurent?) set to yes/no if the from and to dates of the table record fall anywhere within the global date range.

have a relationship in table based on global field containing "yes" as equal to "isConcurrent?"

 

I want to on a layout for any table record, a portal showing all other table records within the global date range.

 

Portal never shows any records. 

[LaRetta]

The problem is, the calculation references global fields so it must be unstored.  The 'portal' side must be stored.  You might remove the calculation completely and instead change the relationship.  Create a self join (another copy in the graph of the same table):

 

MainTable::gFrom <= MainTable 2::Date

AND

MainTable::gTo >= MainTable 2::Date

[comment]

IIUC, it's a range-to-range relationship, so it needs to be:

YourTable::gFromDate ≤ YourTable 2::ToDate
AND
YourTable::gToDate ≥ YourTable 2::FromDate

[LaRetta]

Ah.  Thank you for the catch. :-)

[DFehrenbach]

<< self-inflicted dope slap>>

Thank you so much for both your repies, I was using the calculation route because I couldn't figure out how to do the range to range relationship.  Much simpler!!! thanks again.