AnsweredAssumed Answered

Scripting Help for related records and applying date logic

Question asked by GregP on Jun 21, 2010
Latest reply on Jun 28, 2010 by philmodjunk


Scripting Help for related records and applying date logic


I am working on an application for a volunteer organization to link Doctors to Patients for free health and dental care.  I am using FileMaker Pro 11 on Windows.  I have setup the following tables and forms for maintaining the information:


1.  Patients - includes basic Patient information (name, address, etc...)

2.  Providers (Doctor/Dentist, etc.) - basic information (name, address, type of provider, etc.) including how many patients they will see per week or per month

3.  Appointments - basic information (appt date, time, reason for visit, etc.) to keep track of the number of Appointments for each Patient and which Provider they visited


I have setup the appropriate relationships between each table.  Now comes my question.  Each Provider has specified how many Patients they will see in a given week or month.  I need to write a script that will tell me which providers are available for appointments at a given point in time.  I was thinking the output from this script would be a report but maybe there is a more clever way to do this?


Can someone give me some direction on how to do this?  I know I need to write a script but what is the best way to accomplish this in FileMaker.  Here is some psuedo code for my algorithm:


  Date = user input date to begin the search

  Week = calendar days of the week specified

  Month = number of the month specified


  Read a Provider Record (first record in table)


      Available = false

      If (TreatmentPerWeek > 0) {

            Count = number of appointments this week

            If (Count < TreatmentPerWeek)

                Available = true


  else If (TreatmentPerMonth > 0) {

            Count = number of appointments this month

            If (Count < TreatmentPerMonth)

                Available = true



    If Available = true

         Print ProviderName


    Read Next Provider Record (until last record in table )

  End Loop



Thanks for your help.