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.
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.
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
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?
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.
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?
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
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:
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.
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
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?
the appointment database has a single record for each appointment containing the fields holding the information
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?TSGalFileMaker, Inc.