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

    Active Calender

    andyg4137

      Title

      Active Calender

      Post

      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!! :)

       

      thanks

       

      Andy 

        • 2. Re: Active Calender
          andyg4137
            

          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
            TSGal

            andyg4137:

             

            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.

             

            TSGal

            FileMaker, Inc. 

            • 4. Re: Active Calender
              andyg4137
                

               

               

              okay, the fields are

               

              Date1                 

              Monday

               

              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

               

              andy

              • 5. Re: Active Calender
                TSGal

                andyg4137:

                 

                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?

                 

                TSGal

                FileMaker, Inc. 

                • 6. Re: Active Calender
                  andyg4137
                    

                  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
                    TSGal

                    andyg4137:

                     

                    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?

                     

                    TSGal

                    FileMaker, Inc. 

                    • 8. Re: Active Calender
                      andyg4137
                        

                      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
                        TSGal

                        andyg4137:

                         

                        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.

                         

                        TSGal

                        FileMaker, Inc. 

                        • 10. Re: Active Calender
                          andyg4137
                            

                          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

                           

                          Andy

                          • 11. Re: Active Calender
                            TSGal

                            Andy:

                             

                            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?

                             

                            TSGal

                            FileMaker, Inc.

                            • 12. Re: Active Calender
                              andyg4137
                                 the appointment database has a single record for each appointment containing the fields holding the information
                              • 13. Re: Active Calender
                                TSGal
                                andyg4137:
                                 
                                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?
                                 
                                TSGal
                                FileMaker, Inc.