13 Replies Latest reply on Jan 29, 2009 11:43 AM by TSGal

    Active Calender



      Active Calender


      i'm trying to setup a calender which records the results of the appointment.


      i have created a new database which has a new record for each week and a field which contains the week commencing date and other fields which calculates the date of each of the 7 days in the week.


      within each record there are 20 consultants each having 3 slots available every day for appointments.


      i have managed to set it up with an extended if command that matches the name of consultant, the date and that the appointment time is between the time range for that appointment slot. This uses a boolean value ( 1 & 0) which the other fields are set up to pull through the record if this value is 1


      this works for 1 record but as soon as a conusltant has 2 appointments in 1 day it fails to pick up the 2nd appointment and the boolean value returns "0" even after creating a 2nd consultant name field.



      i tried setting up as a portal but then it pulled through every appointment for that consultant regardless of date.


      i'm close to cracking it but just missing that vital piece of code, is this possible what i am trying to do?


      any help appreciated before i crack up!! :)





        • 2. Re: Active Calender

          Thanks for this,


          unfortunately its not what i'm looking for. i need it to show effectively the appointments for 20-30 consultants for a whole week on one screen with the appointment details made up of fields in another database.

          • 3. Re: Active Calender



            Thank you for your post.


            Since your calculation is breaking down at the "day" level, it appears your current "key" is based on the consultant and day and not including the time slot.  Your boolean value should only give you a false result if you book the consultant for the same date AND time; not just the date.  Does that make sense?


            If you need additional help, please give us an example with some dummy data.  This may help others come up with some ideas of their own.



            FileMaker, Inc. 

            • 4. Re: Active Calender



              okay, the fields are





              consultant                          consultant                       consultant 


              slot1                                  slot2                              slot3

              appt time                            appt time                        appt time

              customer                            customer                         customer

              result                                 result                              result

              booker                               booker                             booker



              above is the current fields for one consultant for one day (monday)


              Slot1, 2 & 3 contain the following calculation:


              If ( Rep 2 = Appointments::Name of Consultant  and Date = Appointments::Date of Appointment  and Appointments::Time of Appointment ≥ 1231 and Appointments::Time of Appointment ≤ 1730 ; "1" ;"0" )


              slot 1 and 3 have different time values.


              this calc works so long as there is only one appt for that day and it will put it in the right slot but it breaks down as soon as there is more than 1 appt and then both boolean results come back as 0


              before i post the rest, is this correct so far?


              many thanks as always



              • 5. Re: Active Calender



                The information looks good.  However, I assume the calculation changes for the other slots because of the time.  Obviously, there is only one time slot between 12:30 PM and 5:30 PM.  What are the ranges of the other time slots?



                FileMaker, Inc. 

                • 6. Re: Active Calender

                  Hi, the ranges are 0900 to 1230, 1231 - 1730 and 1731 to 2100


                  from what i can gather, its not searching all records from the other database and stoping at the first record that meets the criteria and that stops all the other fields from collating the correct info.

                  • 7. Re: Active Calender



                    Thank you for the additional information.


                    I guess the key to this is when you said, "not searching all records from the other database and stopping at the first record".


                    What are the "key" fields that allow you to get more than one record?  Do you have a second table occurrence?  If so, what are the "key" fields for the second occurrence?



                    FileMaker, Inc. 

                    • 8. Re: Active Calender

                      hi that is where it is going wrong i think as they are all based on the same fields. would i need a table for every slot available (25 consultants, 3 a day for 6 days = 450 tables!)


                      the other option i was thinking of was to set up a script to do it on a daily basis rather than have it setup dynamically, but i don't know if filemaker (or myself!) is clever enough to programme it so that it can determine which of the 3 fields available to copy the information to and then the rest can  be pulled through via a relationship

                      • 9. Re: Active Calender



                        No, you would not need a table for every slot available.  Yikes!  You may need a record for each slot available, but that is a concept we'll explore.


                        I'm assuming you have a table for your consultants and Patients.  I'm also assuming you have a table for your appointments.  What I'm not sure is how you are accessing the dates.  I'm assuming it is another table, but it sounds like it doesn't include the time slots, because you seem to be doing that via a calculation, and I'm assuming the time slots are at different times for each consultant.


                        In your appointment table, it sounds like you have the following fields:


                        Consultant ID

                        Date ID


                        PatientID 1st slot

                        PatientID 2nd slot

                        PatientID 3rd slot


                        Time slot 1

                        Time slot 2

                        Time slot 3


                        The problem with this is that you cannot run a report when the Patient visited last, because you don't know if the PatientID last visited in slot 1, slot 2 or slot 3.  That is why you may want to put the time slots in the date table.


                        Let me know if any of my assumptions are incorrect.



                        FileMaker, Inc. 

                        • 10. Re: Active Calender

                          i'll start from the start that might explain it a bit better.


                          we are a renewable energy company that have the following databases


                          1: Enquiries (list potential customers details such as contact details and how they contacted us)

                          2: Appointments (pulls through information from enquiries once we have arranged a cosultation with them and lists name of consultant, time of appt and date of appt)

                          3: Calender/results database which will show the companies appointments for that week ina  spreadsheet layout with days and the 3 slots available across the top and teh names of teh consultants down the.


                          each appointment slots is made up of 7 fields containing teh appt info and result - whether it sold or not etc.


                          there will be 1 record for each week in the calender database 


                          i hope that is a bit clearer


                          appreciate your help as always



                          • 11. Re: Active Calender



                            Thanks for the additional information.


                            One record for each week in the calendar table.  That's fine.


                            The appointments table has a link to the calendar table, consultant table, and company table.


                            Does your appointment table have three separate records for the time slots, or three time slot fields?  If so, which one is giving you the problem?



                            FileMaker, Inc.

                            • 12. Re: Active Calender
                                 the appointment database has a single record for each appointment containing the fields holding the information
                              • 13. Re: Active Calender
                                Thanks you for the clarification.
                                Since each appointment is for each time slot, you should be set up properly.
                                The key fields connecting the appointments to the calendar are weekID, DayID, Consultant and SlotID.  How are the relationships set up currently?
                                FileMaker, Inc.