Relationship Question based on dates

Discussion created by sccardais on Oct 21, 2016
Latest reply on Oct 21, 2016 by sccardais

I am trying to get a count of records in a related table based on date fields but having trouble defining a relationship that will do what I want.


PERIODS: is used to create Rolling 12 Month reports. Each record is a 12 month period defined by two fields:


  • PERIODS::DateStart
  • PERIODS::DateEnd.


ORGS: each records holds customer information. Two fields in ORGS should give me what I want;


  • ORGS::Date Ordered
  • ORGS::DateCanceled


I want two new fields in PERIODS to show the client count at the beginning and end of each period.

  • PERIODS::ClientsAtStart
  • PERIODS::ClientsAtEnd

The calculation for PERIODS::ClientsAtStart seems simple. ORGS::DateOrdered < PERIODS::DateStart AND ORGS::DateCanceled is NOT < PERIODS::DateStart.


It's the "NOT"  part of the relationship that I can't work out.


Can this be done with a Relationship?