Calendar/Schedule/Date Picker Project Design Structure (1 Viewer)

[dkinley]

I think I have downloaded and researched every reference on the WWW to calendaring as it pertains to MS Access; I know the beginning and I know the end - I just can't make the leap to the middle. I've attached a graphic explaining each, now for the operational narrative followed by my question(s).

Operational Scenario
Setup
The admin will access the user form and schedule an event (A, B, C and Unavailable). Each event A, B, C will have a maximum number of assignments that can be processed that day.

The admin will then select a recurrence pattern (looks suspicously like the layout of another application we all know). Finally, they will enter a range of recurrence: start and end/interval/perpetual (see User Form).

Execution
The admin on other forms (A/B/C) will double-click a date control and it will call up a calendar for date picking. The result of the the setup will be displayed for the admin (see Desired Output - the graphic only has one week for demo purposes). In this manner, the user can visually interpret what date to click on based on their current form.

Problem
User Presentation of Desired Output
My basic take on this is at the end of all the logic I only need 3 pieces of data for the correct formatting of the output: (1) Date, (2) EventType and (3) MaxAssign.

Note: If the admin selects Unavailable, it will have a 'higher priority' and override whatever event was scheduled that day.

Presentation logic will determine precedence for correct formatting of output (e.g., if Unavailable then this formatting over all else).

Through my research, I think I have scrounged enough code and can hack my way through but what I don't know is the best implementation of structure (hence logic follows) to get from the User Form to the Desired Output in terms of table design. I could do this by storing each event in a table but I am hung up and greatly desire to create a perpetual application which would make said table infinitely large and undoable.

My thinking is that the calendar popup would take in the event data, sort it/group it/calculate/interpolate/extrapolate it for presentation purposes (only stored data is the event record). So if event A occurred every 2nd Monday of the Month, it would change the text accordingly on that date.

I have pondered the idea of modifying or somehow overloading the API (for Access '07 DatePicker), but read that this is unadviseable. I have also thought of bridging Outlook for this, but a requirement is that this should be self-contained.

I can code the input and code the formatted output, but my overall question is; how should the event table be set up (additional items to form) and what would the psuedo-code look like to boil the table down to the 3 pieces of required data?

Thanks to any who reply. If there is something out there I missed, I will gladly take it, and if not, any thoughts that will point me in a general direction with be greatly, humoungsly appreciated.

Once completed, I will post this sub-project up here for reviews and any 'hey, it could be better optimized by.... '

-dK

 

[Alisa]

I built something like that last year. It was a huge gigantic PITA. I wish my client would have purchased something that is premade for appointment scheduling - there are plenty of products already on the market for that. Anyway, in the end, my solution was to preload a table with all dates for the next thirty years. The application will be obsolete before then anyway, and if by some chance it is not, someone can go in and add another thirty years to that table - it is really not as much data as you think. Once you have the dates, it is easy to figure out the corresponding days of the week and weeks of the year, and then you can use that information to populate your events table as things are scheduled.

 

[dkinley]

Thanks so much for the response!

Once you have the dates, it is easy to figure out the corresponding days of the week and weeks of the year, and then you can use that information to populate your events table as things are scheduled.

Correct. I do not anticipate any problems in this aspect from a static table of preloaded dates.

- it is really not as much data as you think.

It is good to hear from experience the data size would not be much --THAT was my fear! Which is why I was thinking of some sort of dynamic functionality that would format on-the-fly.

To sound out what is in my head (along with your best practices =]) ... instead of making it one huge step which was my original intent and in light of new information; I will be better off breaking it into two steps and automating the date population on the submission of the User Form and then querying based on the month presented in the date picker?

-dK

 

[Banana]

Just one question, though... Please forgive me if I'm being obtuse!

Why not just hook into Outlook? I mean, it does everything with regards to calendar, and we can just extract the data needed to process the event/whatever and let Outlook take the care of calendaring/date picking?

 

[Alisa]

Thanks so much for the response!



Correct. I do not anticipate any problems in this aspect from a static table of preloaded dates.



It is good to hear from experience the data size would not be much --THAT was my fear! Which is why I was thinking of some sort of dynamic functionality that would format on-the-fly.

To sound out what is in my head (along with your best practices =]) ... instead of making it one huge step which was my original intent and in light of new information; I will be better off breaking it into two steps and automating the date population on the submission of the User Form and then querying based on the month presented in the date picker?

-dK

I am not entirely clear on the details of your project, but I can tell you the way I made it work for me: In my program, each day had 8 available appointment slots. So what I would do is populate my event table (date, appt slot) with empty events a certain amount into the future. So for instance, they wanted to be able to schedule appts 6 months out. In the autoexec, I would find the last appt that had been created in the appt table, and if it was less than 6 months from now, I would populate blank appts out until I got to that 6 month mark.

Then my user interface was based on that table - which already had all the blank spots ready and waiting to be filled in (I think that is what you are getting at?).

From a relational database theory perspective, this design is incorrect - you are only supposed to store data that exists, not space for data that doesn't exist yet and may never exist. But from a programming perspective, it is a heck of a lot easier to populate a graphical representation of a schedule when the blank spots actually exist.

Also, since they only wanted to be able to look back 1 year, I built an administrative option where they could go and archive everything that was over one year old into a backup data base, and then delete all that old data. I didn't really think that the amount of data would end up being a problem - 365 * 8 is less than 3000 records per year. But you never know.

 

[Alisa]

Just one question, though... Please forgive me if I'm being obtuse!

Why not just hook into Outlook? I mean, it does everything with regards to calendar, and we can just extract the data needed to process the event/whatever and let Outlook take the care of calendaring/date picking?

That is certainly the way to go for simple scheduling - in my case there were two reasons we couldn't use outlook. One was that there were always exactly 8 appts per day, but the appts were relative - they weren't at a certain time, and the order of the appts could change depending on the attributes of the appt. The second reason is that the graphical representation of the data that they wanted was not doable in outlook. They wanted a sort of double crosstab thing that you could right click on and edit.

 

[dkinley]

Why not just hook into Outlook?

I tried! =]

It is a very non-technical org. They have federal requirements of maintaining a warehouse of filing cabinets with official looking stamped stuff on reams of documents.

Currently, only the head honcho and chief admin have computers (circa x486). They are purchasing rebuilt ones and want only the OS and this application on it (quasi-terminal services) there is 'no need' for anything else (to save $$ and ensure employees won't be wasting time printing off birthday cards from powerpoint). The network for these will be on a switch without any outside access. All will be using '07 runtime on it and because of all the above, thus the application must be self-contained.

I opined, recommended, argued, and bludgeoned but now qualified to say that you are not being obtuse .. they are.

-dK

 

[Banana]

I'm so sorry that this is the case.

Would be nice to also include Outlook's APIs with the runtime engine so you still had the UI and functionality, though I don't know if this is even doable.

 

[dkinley]

From a relational database theory perspective, this design is incorrect - you are only supposed to store data that exists, not space for data that doesn't exist yet and may never exist. But from a programming perspective, it is a heck of a lot easier to populate a graphical representation of a schedule when the blank spots actually exist.

I might have not been very clear in what I was saying and I think it is in my head --- so I've included another graphic! The tblSchedule is the table for the User Form presented earlier. User fills in some jumbo and hits "OK". Some coding does its magic and populates tblCalendar with the required information (EventType, Date, MaxAssign) along with a foreign key (for let's say 30 years). So there are no "spaces" - each record will have data.

On an assignment form depending on where the task is in its process, they double-click on the date control (standard Date Picker operation) with the exception the calendar control (form) is formatted as a result of querying tblCalendar. Per my requirements - it is up to the user to decide the appropriate date (hence the formatting) and not the logic of the application to say (if on process x then the date must be y - although this functionality may be written in if they change their minds).

EDIT Note: The 'delete fkID if removed' is a cascade delete. Also, I am only representing PKs and FKs in the diagram - not all of the data elements.

-dK

 

[Alisa]

Yes, the issue I was getting at with storing the unpopulated appointments had to do specifically with the type of calendar display they wanted. I don't know what yours is going to look like, so you may be able to do it more "correctly" and only store the appointments that have actually been populated. It sounds like your users may have to click on task to see or assign it's date, or click on a date to see it's task, rather than having everything viewable. If that is the case, your life will be much easier!

 

[dkinley]

Good to hear ... correct, there will not be a viewer for "month view" that breaks the task out seperately. It is not intended as an individual scheduling piece, more of a group concerted effort piece.

The Boss lays out "on these days we do x events and these others we do y" through the schedule form. As the documents flows through their processes, the users only need to see some sort of representation (the formatting goal of the data picker) to correctly select and insert the date of the next process step for the date assignment.

When I spoke of priority ... for holidays and stuff, they input the 'unavailable' event. This will format the date picker "black" for that particular date and the users will know not to schedule any assignments for that date; thus I can conditionally format "if event = unavailable then make black and ignore the rest of the if event = whatever format".

Thanks again for the support. Again, once I have it framed up I will post the outcome for review/criticism (in hopes of improvement/optimization).

-dK

 

[dkinley]

First pass is done. I apologize about the delay, another project took my attention for a bit. I also wanted to develop a complex enough demo for complete understanding and to spark ideas on usage and expansion.

I am posting '07 and '02/'03 versions. Unsure if the '02/'03 version works because this was done in '07 with a 'Save As'.

Most importantly before I jump into it, I would like to thank everyone that knowingly or unknowingly contributed to this effort and now ask for your indulgence in ripping it apart.

*Change From Original Design Notes:
- Changed the original requirement of the date number/background changing colors based on a number of maximum tasks being met (visual challenges from user perspective). Background colors are for the day event with a control tip listing the quantity of tasks for that day.
- On the event scheduler, the perpetual event (No end date) is set at 10 years (thanks for the idea, Alisa).

*Application Notes on Non-Working Items:
- Disclaimer: A message box will pop up stating the function is not available at this time. I figured I could dink around with those in the interim (I am not the best at doing all the date stuff). Form fSchedule (and tSchedule) is not optimized by any means, just enough effort to get a working demo. Focus was placed on fDatePicker.
- On the event scheduler, the 2nd item (End after: __ ) in the Range of Recurrence section does not work properly.
- I did some token validation but nothing foolproof on the data-entry and form control side of things. Just enough to have the demo run.
- The system handles a 'soft' conflict (where 2 event dates collide/double-booking) by using the priority setting. A 'hard' conflict (a task for a particular event is on a particular day and a higher priority event is scheduled over that day) is ignored at this time.

*Major Questions
- Form fSchedule has a sub called "AddRecord". For each write, I pass the appropriate date to this sub. At this time, the tables are not linked or go across a network. My concern is that this opens and closes the connection for each record write and I am worried about the application being slow if demo is snapped into a networked database. Is there a way to hold this connection open until all adds have been completed? Is it a good idea to hold the connection until all adds have been written?

Again, I am not opposed to a redesign of the structure or what-have-you if there is a better way. This was a proof of concept focused on the date picking aspect.

Mostly I would like to personally thank everyone and an advanced thanks for any critique you supply.

-dK