I have a table of 200 clients related to a table of each client's visits. The visit table has the date of the visit plus other fields not involved here. These monthly visits numbering 1000 range across all visit records from 2003 through 2013. I put the visits in a portal on the client layout. It all works fine.

What I'd like to do is show only those records that have visited recently. The business rule is: If the client hasn't visited since Jan 2008, he's abandoned us and is not longer interested. Of course I'd not delete those abandoned cases but I want FM 11 to show me only those records with a visit from Jan 2008 to now.

I've tried doing this with the simple FM 11 menus using the "Date_of_Visit" field which is all I have. I can't use "> */*/2008" becuase that includes all records back to 2003. I can't use "< 12/31/2007" because that again includes all records which I don't want. Perhaps I don't know the sequence of joining a ">" find to a "<" find.

How do I filter these dates?

Hello Hoib,

 

One way to accomplish this is to create two calculation fields in your Clients table startDate and endDate that result in a date type. (see calcs below) Once you have these two fields in place create a multiple predicate relationship between Clients and Visits. (see below)

 

Fields

 

startDate = Month( Get(CurrentDate)) & "/" & Day( Get(CurrentDate)) & "/" & Year( Get(CurrentDate)) - 2

endDate = Get(CurrentDate)

 

Relationship

 

       pk_ClientID = fk_ClientID

and startDate < VisitDate

and endDate > VisitDate

 

I hope this helps...

 

Thanks,

Rich