5 Replies Latest reply on Dec 18, 2012 7:27 AM by Annette

    Find Script



      Find Script



           I have a client database which captures appointments by varoius professionals on it. 

           It's kind of in stages though, and by that I mean.....

           There is a client table, which is related to a referral table.  This referrals table is where all referalls received for the client are recorded.  This referrals table is related to a service table which records any services that will be involved relating to that referral.  And then there is staff activity related to the service table which shows all the appointments that person offered for their service. 

           I want to create a layout which looks for all appointments by any professional that are set for a client that havent happened yet.  I would like to create a button that when you click it there is a pop up window showing this info. 

           At this point I have the button, there is a script to it that has open new window and I have the size and positions set and I have go to layout and selected the appropriate layout.  Now I want to enter find mode, I'm assuming, and search for any appointments that were offered to this person that haven't occurred yet, regardless of what referral and service they are associated to.  The idea is that so when a professional goes to issue an appointment time to the client they can look up all upcoming appointments so they don't double book the client.  I'm just not how to put that search criteria in. 


           Thanks in advance!

        • 1. Re: Find Script

               Client >--< Referral >--< Service >--< Staff

               I am a Tech, not a FMP expert.
               What I am learning is that you can have a Global ID in each Table that relates them all together. Meaning you can put Fields from other Tables on a layout that work. Unfortunately, portals do not do this wide-ranging access - sorta limited to two Tables.

               The question is which Table to use for generating a layout to access the fields you need. In some cases, a Join Table is created just for this purpose.

               You might be asked for a screenshot of your relationships and a list of the fields you want included in your Find. You are sorta asking about a Dashboard (main display screen of current activity).  About Dashboards -  http://www.anvildataworks.com/?p=554/

          • 2. Re: Find Script

                 We do need more information here. Exact information on your tables and relationships, how you log an "appointment offered" and how that data changes when it has been "kept". Are all necessary details that we don't yet have.

            • 3. Re: Find Script

                    Apologies for not providing all of the information that is needed. 

                   Here is how the tables relate:

                   Client to Referrals by the ClientID

                   Service Activity is related to Referrals by Referrals ID

                   Staff Activity is related to Service Activity by ServiceActivityID

                   The process for filling in the appointment goes in certain stages.  The user starts on the Client layout which has various fields on it, they click a button which brings them to a Referral Layout.(Showing Table Clients) where they enter what referral they received and from whom in a portal from the referrals table.  After this they have to enter the services which will be received from this referral so they click the line of the referral they want to enter service for.  This is scripted to show the records related to the portal row they clicked.  They click a button which brings them to the Service Activity (showing results from Referrals table) which has a portal from Service Activity Table.  Here they enter the various services that will be involved for that referral.  They then need to enter the staff activity that will occur within a service.  Again, they click the line of the service they want to enter staff activity for and click a button which brings them to the Staff Activity Layout. 

                   The Staff Activity Layout (showing records from Service Activity Table ) has a portal with Staff Activity on it.  It shows the following fields:

                   StaffActivityID, StaffName, Appointment Type, Appointment Date, Appointment Time, and Appointmnet Status. 

                   When a client is entering an appointment in advance of the appointment they just fill in those fields on this layout and leave the Appointment Status field blank.  When the appointment date comes they then fill out the status which is a drop down list with Attended, Cancelled or Did Not Attend in it. 

                   Basically I would like to be able to have a button which will bring me to a layout showing all appointments entered for this client (Regardless of what referral, service or staff it was associated with).  The purpose of this is that since there are multiple services involved with a client the professional needs to be able to see what appointments are already offered to a client so that they dont schedule an appointment at the same time. 


              • 4. Re: Find Script

                     First to check my understanding:

                     Of course, your field names may differ from what I am using, but it appears that you have these relationships:

                     client ---< Referrals-------<ServiceActivity-----<StaffActivity

                     Client::__pkClientID = Referrals::_fkClientID
                     Referrals::__pkReferralID = ServiceActivity::_fkReferralID
                     ServiceActivity::__pkServiceActivityID = StaffActivity::_fkServiceActivityID

                     As I understand it, a record in StaffActivity represents an appointment and that you want to list the records from this table for a specific client where the status field is empty--indicating that this appointment has not yet been kept or cancelled.

                     Now a recomendation:

                     There are two ways that you can bring up this list in your pop up window. You can base the window's layout on StaffActivity and use a list or table view to display the records, or you can base the layout on Client and use a portal to StaffActivity to list the same records in a portal.

                     WIth either option, you can use GoToRelated Records to open a new pop up window listing all appointments for that client. If you use the list/table view option, your script can then enter find mode, specify a lone = in the status field and constrain the found set to drop out past/cancelled appointments. If you use the portal, a portal filter expression can drop out the same records from the portal.

                • 5. Re: Find Script

                       That's great thanks for that.  I didn't think it would be so simple!!  I think my problem is I over-think things WAY too much.  I went with the layout with the portal option.