Solved

Tracking recurring future appointments

Posted on 2010-11-19
7
698 Views
Last Modified: 2012-05-10
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!
0
Comment
Question by:michaelsenn
7 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 63 total points
ID: 34178684
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

ie  
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.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 34178696
>> 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 ;-)
0
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 62 total points
ID: 34179347
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.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 6

Assisted Solution

by:ThomDroz
ThomDroz earned 62 total points
ID: 34179496
michaelsenn

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.
0
 
LVL 9

Assisted Solution

by:jvaldes
jvaldes earned 63 total points
ID: 34179945
Another perspective, I would get a free copy of seedcode's calendar app from http://www.seedcode.com/ccp51/cgi-bin/cp-app.cgi?usr=51F2606694&rnd=1591508&rrc=N&affl=&cip=68.4.227.200&act=&aff=&pg=prod&ref=calendarfree10

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.

0
 
LVL 52

Expert Comment

by:_agx_
ID: 34180816
All great ideas guys!  
0
 

Author Closing Comment

by:michaelsenn
ID: 34181239
Fantastic feedback...will probably incorporate all ideas into a new app design.  Thanks!!!
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
change database name 2 40
Layout parts in FilemakerPro 10 17 39
MySQL-Design Help 12 38
How do I Enable submit button only if listbox has items 4 27
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question