Use multiple day event in Calendar region

jfosteroracle · Sep 8, 2009 3:24PM

I am using a Calendar region in my application. One issue i have run into is, some of my events are multiple-day events, such as a conference. The calendar application does not seem to be able to deal with multiple-day events. It takes one date as the date. There doesn't seem to be any way of dealing with multiple day events. In my case, I have 2 date fields in my table associated with my calendar - a starting date, and an ending date.

Has anyone found a way to deal with this situation? I don't want to have a separate entry for each date of the event. I'd like to be able to use my start date, and ending date fields.

Thanks,
John

Scott · Sep 9, 2009 10:32AM

John,

Let's start with the trigger syntax. :NEW references the new value that's passed to the table, and :OLD references the current value for that specific row. More about Triggers can be found here:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

Thus, your code should look something like this:

CREATE OR REPLACE TRIGGER ai_meetings
AFTER UPDATE OR INSERT ON meetings_tbl
FOR EACH ROW
DECLARE
l_date_diff NUMBER;
BEGIN
IF UPDATING THEN
DELETE FROM meetings_tbl_cal WHERE m_id = :NEW.m_id;
END IF;
l_date_diff := :NEW.meeting_end - :NEW.meeting_start;
FOR x IN 0..l_date_diff
LOOP
INSERT INTO meetings_tbl_cal
(m_id, meeting_date)
VALUES
(:NEW.m_id, TO_DATE(:NEW.meeting_start + x));
END LOOP;
END;
/

You also need to make sure that you have a column called M_ID in the table MEETINGS_TBL.

Thanks,

- Scott -

http://spendolini.blogspot.com/
http://sumnertechnologies.com/

20020 · Sep 8, 2009 4:06PM

Hello John,

This may be a perfect situation for using a pipelined function. Check into them if you're not already familiar - they're great. You can write a function that loops thru your event table and generates one or more "row"'s for each day of an event. You can even work your own logic in to skip weekend days if necessary. You would then use the pipelined function as the source of your calendar like this:

select event_name, event_date from table( [your pipelined function] );

Hope this helps,
John

jfosteroracle · Sep 8, 2009 9:32PM

Could you give me an example of how that would work for my example, where i have a field with a start date and a field with an end date?

Thanks,
John

Scott · Sep 8, 2009 9:56PM

John,

I created a demonstration of how to handle this. Basically, you need two tables - let's call them EVENTS and EVENTS_CAL. Next, you can put a trigger on EVENTS that will create a distinct event for every day in between your start and end date. Thus, for a 4-day event, you will have one row in EVENTS and 4 rows in EVENTS_CAL.

You would then point your Calendar to EVENTS_CAL rather than EVENTS, so that for each multi-day event, there is an entry on each date.

You can download the SQL that I used in the triggers in my presentation here: http://sumnertechnologies.com/apex/f?p=10000:395:0::NO::P395_PRESENTATION_KEY:BROWSER_V2

You can also achieve this a little more elegantly with a jQuery plugin called FullCalendar: http://arshaw.com/fullcalendar

I have used FullCalendar on my site as well, as it does a better job of displaying multi-day events than the built-in APEX calendars.

Thanks,

- Scott -

http://spendolini.blogspot.com/
http://sumnertechnologies.com/

jfosteroracle · Sep 9, 2009 6:24AM

This sounds like what I am looking for. Thanks so for your help!!

John

jfosteroracle · Sep 9, 2009 9:12AM

Scott, in your SQL you used in your example for the trigger below, what does ":NEW" represent? Can i substitute my table names and fields, and use :NEW, or how does that work?

Thanks,
John

CREATE OR REPLACE TRIGGER ai_events
AFTER UPDATE OR INSERT ON events
FOR EACH ROW
DECLARE
l_date_diff NUMBER;
BEGIN
IF UPDATING THEN
DELETE FROM events_cal WHERE event_id = :NEW.event_id;
END IF;
l_date_diff := :NEW.end_date - :NEW.start_date;
FOR x IN 0..l_date_diff
LOOP
INSERT INTO events_cal
(event_id, event_date)
VALUES
(:NEW.event_id, TO_DATE(:NEW.start_date + x));
END LOOP;
END;

jfosteroracle · Sep 9, 2009 9:43AM

Scott,

I took the SQL you used in your trigger and substituted my table names and fields. I am getting 3 errors. Here is what my code looks like after the substitutions (my table names are 'meetings_tbl' and 'meetings_tbl_cal'):

CREATE OR REPLACE TRIGGER ai_meetings
AFTER UPDATE OR INSERT ON meetings_tbl
FOR EACH ROW
DECLARE
l_date_diff NUMBER;
BEGIN
IF UPDATING THEN
DELETE FROM meetings_tbl_cal WHERE m_id = meetings_tbl.m_id;
END IF;
l_date_diff := meetings_tbl.meeting_end - meetings_tbl.meeting_start;
FOR x IN 0..l_date_diff
LOOP
INSERT INTO meetings_tbl_cal
(m_id, meeting_date)
VALUES
(meetings_tbl.m_id, TO_DATE(meetings_tbl.meeting_start + x));
END LOOP;
END;
/

I am getting 3 errors:

1) PL/SQL: ORA-00904: "MEETINGS_TBL"."M_ID": invalid identifier

2) PLS-00357: Table,View Or Sequence reference 'MEETINGS_TBL.MEETING_END' not allowed in this context

3) PL/SQL: ORA-00984: column not allowed here

Maybe I'm not supposed to substitute ':NEW' with 'MEETINGS_TBL' ??

Thanks for your help!

John

jfosteroracle · Sep 9, 2009 9:45AM

Having trouble with suggested code.

Scott · Sep 9, 2009 10:32AM

John,

Let's start with the trigger syntax. :NEW references the new value that's passed to the table, and :OLD references the current value for that specific row. More about Triggers can be found here:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

Thus, your code should look something like this:

CREATE OR REPLACE TRIGGER ai_meetings
AFTER UPDATE OR INSERT ON meetings_tbl
FOR EACH ROW
DECLARE
l_date_diff NUMBER;
BEGIN
IF UPDATING THEN
DELETE FROM meetings_tbl_cal WHERE m_id = :NEW.m_id;
END IF;
l_date_diff := :NEW.meeting_end - :NEW.meeting_start;
FOR x IN 0..l_date_diff
LOOP
INSERT INTO meetings_tbl_cal
(m_id, meeting_date)
VALUES
(:NEW.m_id, TO_DATE(:NEW.meeting_start + x));
END LOOP;
END;
/

You also need to make sure that you have a column called M_ID in the table MEETINGS_TBL.

Thanks,

- Scott -

http://spendolini.blogspot.com/
http://sumnertechnologies.com/

jfosteroracle · Sep 9, 2009 11:05AM

Scott,

Thanks so much! That answered my question. I tried it out and it works! I thought I tried it the way you had it, but i must have had something wrong. I'll definitely check out the link to the trigger information.

Here is where I am using your solution. This is an APEX application for the US Army Corps of Engineers - Missouri River Recovery Program website:

http://www.moriverrecovery.org/mrrp/f?p=152:31

John

Scott · Sep 9, 2009 11:09AM

John,

Glad that I can help, and it's always nice seeing the actual solution in action!

Thanks,

- Scott -

http://spendolini.blogspot.com/
http://sumnertechnologies.com/

Forum Stats

Quick Links

Categories

Use multiple day event in Calendar region

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

Use multiple day event in Calendar region

Best Answer

Answers