10 Replies Latest reply on Jun 28, 2010 8:41 AM by philmodjunk

    Scripting Help for related records and applying date logic

    GregP

      Title

      Scripting Help for related records and applying date logic

      Post

      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)

        Loop

            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.

       

        • 1. Re: Scripting Help for related records and applying date logic
          philmodjunk

          I'm not sure you actually need  a script for this. With the right calculation fields and relationships, it's possible to select a week or month of the year and see total appointments for each provider in a list or table view of the same.

           

          In your appointments table use the following two calculations I've learned from posts made to this forum by Comment:

          Set them both to return "Date" as their return types:

           

          cMonth: ApptDate - Day ( ApptDate ) + 1  /* calculates the date of the first day of the month of ApptDate */

          cWeek: ApptDate - DayOfWeek ( ApptDate ) + 1 /* Calulcates the date of Sunday for the week of ApptDate */

           

          You can use these fields in relationships to link groups of appointments by a specified week or month and you can also use them in a sort order to group appointment records by either week or month.

           

          Define a pair of global date fields in the Providers table: gMonth, gWeek

           

          Give them these auto-enter calculations:

          gMonth: self - Day ( self ) + 1

          gWeek: self - DayOfWeek ( self ) + 1

           

          Make additional table occurrences of Appointments (Button with two green plus signs in Manage | Database | Relationships) and link them to Providers like this:

          Providers:: ProviderID = AppointmentsMonth:: ProviderID AND

          Providers::gMonth = AppointmentsMonth::cMonth

           

          Providers:: ProviderID = AppointmentsWeek:: ProviderID AND

          Providers::gWeek = AppointmentsWeek::cWeek

           

          Define these calculations in Providers:

          cWeeksAppt, Count ( AppointmentsWeek:: ProviderID )

          cMonthsAppt, Count ( AppointmentsMonth:: ProviderID )

           

          Now you can display a list of Provider records with the number of appointments scheduled for a selected week and month simply by placing the gMonth and gWeek fields on a layout where you first select dates for these fields (You can use a drop down calendar on these).

           

          You can also create a summary report of appointment records that groups the records by Provider, Month, and week.

          • 2. Re: Scripting Help for related records and applying date logic
            GregP

            Paul,

             

            Thanks for your reply.  I have a question on the relationships (below):

             

            Make additional table occurrences of Appointments (Button with two green plus signs in Manage | Database | Relationships) and link them to Providers like this:

            Providers:: ProviderID = AppointmentsMonth:: ProviderID AND

            Providers::gMonth = AppointmentsMonth::cMonth

             

            Providers:: ProviderID = AppointmentsWeek:: ProviderID AND

            Providers::gWeek = AppointmentsWeek::cWeek

             

            I'm not sure what the "AppointmentsMonth" or AppointmentsWeek" is?  Is this a field in my database?  Its not obvoius to me how to setup this relationship.

             

            Thanks.

            • 3. Re: Scripting Help for related records and applying date logic
              GregP

              Or is this just the name of the additonal relationships I setup for the Appointment table?  If so, I assume I need to setup 2 of these?

              • 4. Re: Scripting Help for related records and applying date logic
                philmodjunk

                AppointmentsMonth and AppointmentsWeek are additional table occurrences of your appointments table.

                 

                In Manage | Database | Relationships, click Appointments to select it.

                Click the button with two plus signs at bottom of the dialog to make a duplicate table occurrence. This table occurrence points to the same records as Appointments, but now it has a different name, Appointments 2 so that we can set up a different relationship. Double click this new table occurrence box and rename it AppointmentsMonth and you've got the first of two new table occurrences.

                Drag from ProvidersID in Providers to ProvidersID in AppointmentsMonth and from gMonth to cMonth to link this new table occurrence to your exising Providers table occurrence box.

                 

                Keep in mind that I don't know the actual names of any fields already defined in your tables. You may have to subsitute the actual field names you defined in your table for the example names that I used. (Also, I am hoping that you are using a serial number field such as ProviderID instead of provider names to link any of your tables as name based relationships can be a source of significant trouble when you go to put your database into regular use.)

                • 5. Re: Scripting Help for related records and applying date logic
                  GregP

                  Paul,

                   

                  Thanks for the clarification.  I have the relationships setup correctly now.  Also, I am using serial numbers for my ProviderID and AppointmentID in my tables.    Another question,  these calculatuions below in the Provider table should be assigned to new fields in the table - correct?  If so, I assume these are calculation fields in my table?

                   

                  cWeeksAppt, Count(AppointmentsWeek::ProviderID)

                  cMonthsAppt, Count(AppointmentsMonth::ProviderID)

                   

                  Thanks once again for all of your help.

                  • 6. Re: Scripting Help for related records and applying date logic
                    philmodjunk

                    Yes, they should be defined in your provider table. I gave a field name, then the calculation.

                    • 7. Re: Scripting Help for related records and applying date logic
                      GregP

                      Paul,

                       

                      Thanks so much for your guidance.  I have a few more questions.

                       

                      I have setup all of the new fields and relationships.  Now I am trying to display the information as you suggested below:

                       

                      Now you can display a list of Provider records with the number of appointments scheduled for a selected week and month simply by placing the gMonth and gWeek fields on a layout where you first select dates for these fields (You can use a drop down calendar on these).

                       

                      You can also create a summary report of appointment records that groups the records by Provider, Month, and week.

                       

                      So, I created a new layout and added the gMonth and gWeek fields to the layout.  I made these fields drop down calendars.  When I select a date from these fields, I get an error "This action can not be performed because this field is not modifiable".  I assume I am doing something wrong?  Also, can you provide more detail on how I display a list of Provider records on this layout?  Do I use a portal or some other type of control?

                       

                      Also, any additional detail on how to setup the summary report would be helpful.

                       

                      Thanks.

                       

                       

                      • 8. Re: Scripting Help for related records and applying date logic
                        philmodjunk

                        First the layout:

                         

                        SImply create a new layout and specify "Provider" in the show records from drop down.

                        You can set this layout up for a table view or a list view depending on your needs and preferences.

                         

                        You can place the gMonth and/or gWeek fields in the header of this layout. (For a table view, you'll need to enable the "include header" option in layout set up under the table properties section.)

                         

                        I get an error "This action can not be performed because this field is not modifiable".

                         

                        It sound's like you've defined these fields as calculation fields instead of as global date fields with auto-entered calculations.

                         

                        If so...

                        Use Manage | Database | Fields to find these field definitions. On each field in turn, double-click the field definition to select it. Copy the calculation expression from the specify calculation dialog.

                        Click Cancel to dismiss the dialog.

                        Use the type drop down to change the field from Calculation to Date.

                        Click the options button.

                        Click the auto-enter tab.

                        Click the Calculated Value option

                        Paste your copied expression into this specify calculation dialog.

                        Click OK

                        Clear the "Do not replace existing value..." option

                        Click the Storage tab.

                        Select the Use Global Storage option.

                        Repeat these steps for the other field.

                         

                         

                        • 9. Re: Scripting Help for related records and applying date logic
                          GregP

                          Phil,

                           

                          I got it working - thanks.  One other question.  I have created a flag "Available" to determine if the Provider is available based on the gWeek or gMonth dates.   This is working properly.   I would like to filter my list to only include those Providers that are available (Available field = "Y"). 

                           

                          How can I accomplish this is the List view?

                           

                          Thanks.

                          • 10. Re: Scripting Help for related records and applying date logic
                            philmodjunk

                            Use a script trigger on the layout set to perform the following script whenever the layout loads and whenever the window mode changes from Find to Browse:

                             

                            Enter Find mode[]

                            Set field [yourtable::AvailableField ; "Y"]

                            Constrain Found Set[]

                             

                            Constrain Found set in this script will take whatever records are in the current found set and omit those that don't have "Y" in the specified field.

                             

                            Cautionary Note: With these triggers, any other scripts that select this layout or perform finds on this layout will also trigger this script.