Time Sheet formatting in Graphic Form

[mattlight]

Hi guys, long time since I've been in here - no it's not that I know everything now - just been away and now back to the grime stone!

Here's the problem - mmmm

You know in Outlook you have a SCHEDULER/CALENDER/PLANNER section? Yes? Well, you'll notice there is a nice graphical way of showing when you have appointments and when you are free.

I am writing a database for a medical centre which will also involve booking appointments to see doctors. Although a list will surfice, I would like to have a graphical representation of their time free and allocated time.

So, I have an appointment date and time field. I have a duration of appointment field and the Doctor's name field.it WILL display, in a portalon the left of the screen, the day's appointments as a list. On the right of the screen I want it to show a graphical representation of the doctor's week's activities.

I've created a GLOBAL Container field with a yellow box graphic. I have created a calculation container field within the diary database.

The calculation says something like:

if(duration = "60 mins", GLOBAL C.F yellow + GLOBAL C.F yellow + GLOBAL C.F yellow + GLOBAL C.F yellow, if(duration = "45 mins", GLOBAL C.F yellow + GLOBAL C.F yellow + GLOBAL C.F yellow, etc......

This does not work AT ALL!

If I do the calculation container as = GLOBAL C.F it works

If I do the calculation container as = GLOBAL C.F + GLOBAL C.F it displays nothing

help - any ideas?

I've printed the two graph POSTS but haven't found the answer in there. I just want to replecate a picture field like a can a text one.

Please help - and thanks for reading!!

Matt

[danjacoby]

Yeah, combining graphics in a field won't work.

You could create four calc fields, each a different width and returning the yellow graphic if the length of time is the correct one for that field, then place them on top of each other in the layout, making sure that they're transparent. Just make sure to go to Format:Graphic and choose the "enlarge" popup and uncheck the "Maintain original proportions" box. The nifty thing is that the global field can be one pixel square, thus taking up less space.

[keshalyi]

Well, I'm not a genius at this, but it seems to me like what you'll have to do is break your time into predictable increments. So, for instance, you'll have to assume that their appointments need not be any more precisely scheduled than, say,15 minute increments. Then, why not just make a table or (shudder) a repeating field of 15 minute increments, that spawns a set for each day? Then, just the box for that 15 minute increment fill in, once something is scheduled in it.

To draw it out into an example:

You have a table of records, representing each 15 minute block of time from midnight to midnight for next monday. Your layout has them laid in a row like this:

* ** ** ** *.....

12 |12:15|12:30|...

Then, when they enter a new meeting from, 3:00 - 3:30, just fill the 3:00 - 3:15 container, and the 3:15-3:30 container, and maybe a text field juxtaposed over the first container that describes the meeting. Voila.

[mattlight]

Hi again guys,

I have a little bit further with this - this is what I've now done.

I created a repeating global container and inserted 4 different colours which would represent different status' of an appointment.

Yellow - Booking

Red - Waiting at Reception

Blue - With Doctor

Grey - Doctor Time Off

I have then created a APPOINTMENT STATUS field that is a value list

Booking

Waiting

With Doctor

Time Off

Then another field STATUS COLOUR that is a calculation which looks like:

if (appointment status = "Booking", 1, if (appointment status = "Waiting", 2, if (appointment status = "With Doctor", 3, if (appointment status = "Time off",4, ""))))

This then becomes the value of the repetion field GLOBAL COLOURS for any of the options.

DATA CODE =

If(Duration of Appointment = "two hour", 15, If(Duration of Appointment = "one and half hour", 7, If(Duration of Appointment = "one hour", 3, If(Duration of Appointment = "half hour", 1, ""))))

If(DATA code = 1 , GetRepetition(Global Colours,Status Colour), If(DATA code = 3 , GetRepetition(Global Colours,Status Colour), If(DATA code = 7 , GetRepetition(Global Colours,Status Colour),If(DATA code = 15 , GetRepetition(Global Colours,Status Colour), ""))))

Then I've got a field that works out the amount of time of appointment

The below field is a calculation allowing four fields to calculate whether or not to put a colour in the time container fields:

GRAPHICAL LENGTH

If(Duration of Appointment = "two hour", "half hour-one hour-one and half hour-two hour", If(Duration of Appointment = "one and half hour", "half hour-one hour-one and half hour", If(Duration of Appointment = "one hour", "half hour-one hour", "half hour")))

then I have 4 fields - one for HALF hour, one for ONE hour, one for ONE AND HALF hour and the other for TWO hour.

HALF HOUR

If(Left(graphical length, 9) = "half hour", SCHEDULER, "")

ONE HOUR

If(Left(graphical length, 18) = "half hour-one hour", SCHEDULER, "")

ONE AND HALF HOUR

If(Left(graphical length, 36) = "half hour-one hour-one and half hour", SCHEDULER, "")

TWO HOUR

If(graphical length = "half hour-one hour-one and half hour-two hour", SCHEDULER, "")

The above fields in total, make four different fields fill with a colour (according to status) which, if all are full, represents two hours, if threee are full - 1.5 hours, 2 = one hour and 1 = half hour.

In this database there is also DOCTOR'S NAME, CLIENT NAME, DATE and TIME OF APPOINTMENT.

I then have a calculation field that collates

APPOINTMENT DATE & "-" & APPOINTMENT TIME & "-" & DOCTOR NAME

which means from another database I can find - in a portal, all the appointments for that doctor on that day.

But,

Displaying them in a vertical or horizontal format will involove (I think - this is where it gets sketchy!) creating a DOCTOR NAME field and APPOINTMENT DATE field and then getting them to collate into 24 fields with 24 different possible times to have relationships going to find the times and plot them on the scheduler.

It all works except I have say - 1st Portal which looks like

HALF HOUR Patient Name

ONE HOUR

ONE AND HALF HOUR

TWO HOUR

this Relationship name is called 08am

but the 830am Relationship, which looks identical, will have to overlap so that the HALF HOUR covers the ONE HOUR field of the last relationship - 8am, 8.30an, etc.

I have done 8am, 830am, 9am - but I have an overlap problem!

In the portal it displays 8am fine - 9am fine - I can display the half hour, one hour, two hour completely fine - but the 8:30 portal will not display anything whilst overlapping between the other two.

I have made sure there is no background colour to either the portal or the fields but still no good. I believe a portal window only comes up if there is a relationship and so should work.

What's happening? Sorry to get you to read all this - but if you have, maybe you're either really confused by now or think you might have the answer!? All the above stuff has just been lateral thinking and there is probably an easier way.

I need this module in a project I'm doing - and I'm sure if the answer is in here somewhere, that it might be to everyone's benefit! There's nothing worse than having your whole business working with Filemaker and then using OUTLOOK to do your SCHEDULING is there!?

Please help and thank you so far - it must have drained you just looking at all this!!

[mattlight]

I'm sorry!! I've nailed it all on my own! Sorry to put you guys through that - I only hope you went to see if there was a reply before reading it - tried to edit the post but it said the time has expired!

If you guys want to know how I've now done my SCHEDULER post a reply and I'll reveal all - it was very simple after all!!

Thanks

[agraham999]

I'd live to hear your solution...I'm about to build a scheduler for a project...and I've been giving this same idea some thought...

[SteveB]

So would I...do you have an example that you can email?

[Kundinger]

I'm INTERESTED too...

I created a calendar system that has:

- Yearly Calendar -

-- displays 12 months

-- move forward/backward one/ten years at a time

-- click on a month to view Monthly Calendar

- Monthly Calendar -

-- displays ALL weeks of a month

-- move forward/backward one month at a time

-- click on a day to view Daily Calendar

- Daily Calendar -

-- displays all events for the day

-- move forward/backward one day at a time

-- click on an event to view Event Details

- Event Details -

-- display specific event details (date/time/status/notes/etc.)

-- move forward/backward one event at a time

In the Monthly and Daily calendars... I created 'hidden' container fields that will change color depending on the 'existence' of scheduled 'events'. The Monthly calendar changes color for the 'whole' day. The Daily calendar changes color in the appropriate 15 minute 'slot'. Some 'events' may extend beyond 15 minutes... so it just changes color for the appropriate number of 15 minute slots.

Over the years, I have implemented this system into some of my clients

[mattlight]

Okay - but I did get the idea from the second reply dude!

The first database is called SCHEDULER - which, through a portal, you enter in each appointment - and through another portal you get a very nice full colour SCHEDULER. (Microsoft Word docoment with a screen shot as to how mine looks!)

The fields.

Appointment Date Date

Week Number Calculation (Number) = WeekofYear(Appointment Date)

Appointment No Serial Number with Current Value: "1" Increment: "1"

Appointment Time Time

Doctor Name Text

Duration of Appoin.. Text

Appointment Status Text

Global Colours Global (Container) Repeating field with 4 repetitions

Status Colour Calculation (Number) = If(Appointment Status = "Booking", 1, If(Appointment Status = "Waiting", 2, If(Appointment Status = "With Doctor", 3, "")))

DATA code Calculation (Number) = If(Duration of Appointment = "two hour", 15, If(Duration of Appointment = "one and half hour", 7, If(Duration of Appointment = "one hour", 3, If(Duration of Appointment = "half hour", 1, ""))))

Schedule Finder Calculation (Text) = DateToText(Appointment Date) & "-" & Doctor Name

SCHEDULER Calculation (Container) = If(DATA code = 1, GetRepetition(Global Colours, Status Colour), If(DATA code = 3, GetRepetion(Global Colours, Status Colour), If(DATA code = 7, GetRepetition(Global Colours, Status Colour), If(DATA code = 15, GetRepetition(Global Colours, Status Colour), ""))))

Graphical Length Calculation(Text) = If(Duration of Appointment = "two hours", "half hour-one hour-one and half hour-two hour", If(Duration of Appointment = "one and half hour", "half hour-one hour-one and half hour", If(Duration of Appointment = "one hour", "half hour-one hour", "half hour")))

Client Name Text

One hour from app Calculation (Time) = If(Left(graphical length,18) = "half hour-one hour", Appointment Time + 1800, "")

One half hour app Calculation (Time) = If(Left(graphical length,36) = "half hour-one hour-one and half hour", Appointment Time + 3600, "")

Two hour from app Calculation (Time) = If(Graphical length = "half hour-one hour-one and half hour-two hour", Appointment Time + 5400, "")

Increment Time Global(Time) Repeating field with 24 repetitions

Increment Colours Calculation (Container) = If(increment time - Extend(Appointment Time) = 0 or increment time - Extend(one hour from app) = 0 or increment time - Extend(one half hour from app) = 0 or increment - Extend(two hour from app) = 0, Extend(SCHEDULER), "")

Then Insert Object (Bitmap) four seperate colours (one in each of the repetitions) and as someone said - you only need ONE pixel's worth of colour and then make sure that the field has MAINTAIN ORIGINAL PROPORTIONS unchecked.

Yellow = Booking

Red = Waiting - at reception

Blue = With Doctor

Grey = Time off

Then go to the TIME INCREMENT field in BROWSE mode and enter in -

08:00 next 08:30 next 09:00 etc etc > 19:30

then go into layout mode and Disallow entry into field.

Create a value list of Appointment times 08:00, 08:30 etc

Create a value list for Duration of Appointment

Half Hour

One Hour

One and Half Hour

Two Hour

same as stated in GRAPHICAL LENGTH and DATA code fields

Then line up the repeating increment TIME field with increment colours field (one under the other)

Then from another database you can have a booking screen including these fields -

Doctor Name - with same source value list

Appointment Date Date format

Times Global(Time) Repeating with 24 values - same as other one

Combined Search Calculation (Text) = DateToText(Appointment Date) & "-" & Doctor Name

Create a relationship between both databases based on

Current Database - Combined Search

SCHEDULER - Schedule Finder

Create a Portal the length of the screen at the bottom of screen with 10 thin rows and vertical scroll bar

Place the Times field above the portal in 9pt Horizontally repeating so it fits on screen AND so you can read the times

then, in the portal grab the INCREMENT COLOURS field and make it as small as the row and as long as the times (so they match up)

With the space at the end of the portal place in there two more fields from the other database - Client Name and Appointment Status

Disallow entry into field on all but APPOINTMENT STATUS and make that field a PULL DOWN MENU

Value list Booking, Waiting, With Doctor

When you select the Doctor name and Appointment date in this database it will list all the appointments for the day for that doctor.

To test it out, flick over to the SCHEDULER database and make a coupe of appointments to the same doctor at different times and different durations. Set them up as "Booking". (I haven't set up an appointment entry screen on the other database yet!)

Then go back into the database with the portal (make sure the portal is sorted by Appointment Time) and you should see the appointments in the SCHEDULER - I do anyway! - Then click on the Status Field on one of them and change it from "Booking" to "Waiting". It turns from Yellow to Red. Because it's a portal, in the doctor's office he should always have a copy of the scheduler visable on all screens of the entire database because even though he might not touch the computer before his eyes the booking turns from yellow to red - notifiying him that the patient is in the building!! Good uh?

Hope this helps you guys - remember me in your will!!

If anyone has some other great database that can be bolted on to a set of databases please let me know!!

Word File.zip

[mattlight]

I'm trying to paste into this forim - no luck

I've done a print scr and CTRL+V but nothing - sorry

how do you do that?

[rivet]

I have designed a complete calander solution and I am considering selling it. Take a look at the features.

http://homepage.mac.com/launchpt/