Tracking recurring future appointments

New to database design, and looking for an elegant way to track patient follow-ups at a non-profit mental health clinic. Specifically, we need to know which patients are due for a 90-day consultation, and these can extend out indefinitely for as long as the patient is active.  It would obviously be easy if we just limited it to a certain number of periods, but theoretically patients could need 90-day follow-ups for years and years.  

So we have a patient.IntakeDate, and need to know which patient in any given month is coming up on another 90-day interval from their last one.  So, for instance, I would want to search the date range of 12/1/2010 - 12/31/2010, and know which patients' are due in that time frame.  Creating a hundred different fields of the IntakeDate + 90, + another 90, and so on seems crude and ridiculous, so I'm sure the experts out there have a better idea.

Even though the existing database is Filemaker Pro 11, I am open to switching to either SQL Server or MYSql, so anything relevant would be helpful.

Thanks in advance for your expertise!
Who is Participating?
_agx_Connect With a Mentor Commented:
It's easier if you think about it another way.  You're looking for patients whose last visit was 90 days ago. In other words between 9/1/2010 and 10/1/2010.  I don't use Filemaker. But most db's provide date functions that let you subtract 90 days from any given date.  For example in MySQL

StartDate: DATE_ADD('2010-12-01', INTERVAL -90 DAY)   // returns 9/2/2010
EndDate:  DATE_ADD('2010-12-31', INTERVAL -90 DAY)   // returns 10/2/2010

Just plug those dates (or functions) into your query.  The result would be something like

SELECT Columns
FROM    TableName
WHERE DateOfLastVisit >= '09-02-2010'
AND      DateOfLastVisit <= '10-02-2010'

NOTE - The exact values depend on whether you're storing dates only or dates and times. But you get the idea.
>> 9/1/2010 and 10/1/2010
>> '09-02-2010' ... '10-02-2010'

Ignore the discrepancy in dates. I didn't *actually* do the date math ;-)
lesouefConnect With a Mentor Commented:
agx's idea is good, very simple, can be done in fm too; dates are easier to handle in fm than in sql, especially mssql2005 and below.
If I should add my point, I won't do a search, but use a global repetitive field to represent a calendar, then compare the current date range with the original entry date (no need to store the last visit), modulo 3 months.
basically: if mod(today - patient's entry date) / 90 = 0, then this patient should be selected.
this is the theory, this has to be adapted with months instead of days, but you see the idea.
and everyday, when you open the app, the current day shifts, and shows expected patients
why a repetitive field for current day? it allows to show the whole month effortless by only indicating the value of the first repetition, others can be worked out using something like this:
Extend(date_ref) +  Get ( CalculationRepetitionNumber ) -1
just magic to show a few weeks across the screen.
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

ThomDrozConnect With a Mentor Commented:

2 interesting approaches above, let me throw in a 3rd.

If you have a table (ConsultantDate) to store the dates of visit of patients. Then have a field in a related table called NextScheduledVisit.  This field could be an calculation field of the most recent date (use a variable to compute this)  in ConsultantDate +90.

Then you could search this field for a date range and find the patients that are due.  This would allow flexibility if you had a patient that was given a consultation late and still have the next consultation be in 90 days from the last visit.
jvaldesConnect With a Mentor Commented:
Another perspective, I would get a free copy of seedcode's calendar app from

And I would write a routine to auto-generate the appointment entries into the calendar. This would create an elegant interface and provide an easy entry in scheduling of tasks. Providing for you daily, weekly and monthly views.

All great ideas guys!  
michaelsennAuthor Commented:
Fantastic feedback...will probably incorporate all ideas into a new app design.  Thanks!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.