I'm adding a scheduling portion to a database. I'm in the rough beginning stages, but the attachment Basic Layout will give you an idea of the setup. Sales guys' names up top, time ranges below, and conditional formatting to give a visual indicator of when they're occupied. The user create events in a separate window, and a colored time-block appears on their schedule.
It works. The problem is when I scale it to a few sales employees, the conditional formatting takes a long, long time to generate in the portals. If I want to view the salespersons' schedules for a different date, it can take up to ten seconds to load daily schedules for five individuals. The problem is my build. I feel like my process is too convoluted. Below is a picture of the graph, and an explanation of what I'm doing.
I have a Menu table on which the layout is based, with a global field where you can pick a date. This is the blue TO on the left. Attached are time table occurrences....one table occurrence for each salesperson. The times records have a date (the match field for the global in Menus), and a repeating field with values that constitute a hour's time block (8:00 AM, 8:15 AM....9:00 AM). The Times tables also have global fields that contain the primary keys of each salespersons records. This helps match them to appropriate event records.
It goes up to year 2030 or thereabouts. I parse this repeating field to 4 separate fields so I could use them as match fields.
Now, the the join to my Events table is a multi-predicate one: Salesperson global field to employee foreign key in events; two joins to establish the date range on the Event, and one to establish the year of the event. Each "repetition" (even though I'm not trying to join with a repeating field) has it's own T.O. in events. This is so I can apply conditional formatting to the displayed repeating field in my portals. Below is an example.
I feel like I've done too much work here, and that there's an easier way to go about this.