7 Replies Latest reply: Aug 11, 2011 2:33 PM by Ellen Blackwell RSS

    Link to generic calendar

    Ellen Blackwell

      I am using various date fields across the tables in a dashboard. I have a generic calendar but have not been able to link the dates and calendar without creating 'loosely coupled relationships' and/or synthetic keys. Any assistance getting this sorted out would be greatly appreciated.

      For example, I want to link the activity_date in the Paid_ACP table to the master calendar; and, I'd like to link both the submit_date and bridge_date fields in the Policy table to the master calendar.

        • Re: Link to generic calendar
          John Witherspoon

          Let's say you have data like this currently (and I'm totally guessing at the unique key for each table - each table should have a unique key, and that unique key is what you should use):

           

          Paid_ACP:
          pd_said_code, activity_date
          PA1, Jan 10

           

          Opportunity_Illustration:
          illustration_plan_id, sent_date
          OI1, Jan 15

           

          Policy:
          ss_policy_number, submit_date, application_date, bridge_date
          P1, Jan 5, Feb 12, Feb 20

           

          Meeting:
          rn_interations_id, appt_date
          M1, Feb 1

           

          Claims4HowTo:
          Ex_Claim Number, Ex_Issue Date, Ex_Reported Date
          C1, Jan 20, Jan 15

           

          You could probably link these to the Calendar like this:

           

          CalendarLinkage:
          CalendarDate, DateType, pd_said_code, illustration_plan_id, ss_policy_number, rn_interations_id, Ex_Claim Number
          Jan 10, Paid Activity Date, PA1
          Jan 15, Opportunity Illustration Sent Date, , OI1
          Jan 5, Policy Submit Date, , , P1
          Feb 2, Policy Application Date, , , P1
          Feb 20, Policy Bridge Date, , , P1
          Feb 1, Meeting Appt Date, , , , M1
          Jan 20, Claim Issue Date, , , , , C1
          Jan 15, Claim Reported Date, , , , C1

           

          I believe that will give you a central linking table and no loops. Whether you still keep the separate dates on each table is up to you. I typically either choose separate dates and separate calendars, or a single date and a single calendar, but they CAN be mixed.

          • Link to generic calendar

            maybe you can use link tables...

            but i'm not quite sure about it..