Forum Stats

  • 3,855,268 Users
  • 2,264,494 Discussions
  • 7,905,954 Comments

Discussions

Use multiple day event in Calendar region

jfosteroracle
jfosteroracle Member Posts: 349 Blue Ribbon
edited Sep 9, 2009 11:09AM in APEX Discussions
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
Tagged:
Trevis

Best Answer

  • Scott
    Scott Member Posts: 975
    Answer ✓
    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/

Answers

  • 20020
    20020 Member Posts: 526
    edited Sep 8, 2009 4:09PM
    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
    20020
  • jfosteroracle
    jfosteroracle Member Posts: 349 Blue Ribbon
    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
    Scott Member Posts: 975
    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
    jfosteroracle Member Posts: 349 Blue Ribbon
    This sounds like what I am looking for. Thanks so for your help!!

    John
  • jfosteroracle
    jfosteroracle Member Posts: 349 Blue Ribbon
    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
    jfosteroracle Member Posts: 349 Blue Ribbon
    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
    jfosteroracle Member Posts: 349 Blue Ribbon
    Having trouble with suggested code.
  • Scott
    Scott Member Posts: 975
    Answer ✓
    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
    jfosteroracle Member Posts: 349 Blue Ribbon
    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
    Scott Member Posts: 975
    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/
This discussion has been closed.