We hope you'll join us for our 4/23 webinar on using data tables to apply reference ranges and AE codes in OC4. For more information and to register, visit https://register.gotowebinar.com/register/2882170018956684555

Event completion date

Hey Folks -
I'm writing a few custom reports for a client, and one of the things I need to reference is the date an event/crf is marked complete (The "Mark CRF Complete" box is checked). What table/field is this in within the database?
Thanks!
-Brian

Comments

  • mochisoftmochisoft Posts: 46
    Hi Brian,
    The table is study_event and the field is subject_event_status_id. subject_event_status_id=4 for events marked complete.
    Hope this helps,
    Michael.
    On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson wrote:
    Hey Folks -
    I'm writing a few custom reports for a client, and one of the things I need to reference is the date an event/crf is marked complete (The "Mark CRF Complete" box is checked). What table/field is this in within the database?
    Thanks!
    -Brian
  • ServerSageServerSage Posts: 32
    That only tells the status. I need to know the date that it was marked as Complete.
    Thanks.
    -Brian
    Michael Ochieng wrote:
    > Hi Brian,
    >
    > The table is study_event and the field is subject_event_status_id.
    > subject_event_status_id=4 for events marked complete.
    >
    > Hope this helps,
    >
    > Michael.
    >
    >
    > On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson > wrote:
    >
    > Hey Folks -
    >
    > I'm writing a few custom reports for a client, and one of the things
    > I need to reference is the date an event/crf is marked complete (The
    > "Mark CRF Complete" box is checked). What table/field is this in
    > within the database?
    >
    > Thanks!
    >
    > -Brian
    >
  • mochisoftmochisoft Posts: 46
    Use date_updated field in the event_status table.
    Kind Regards,
    Michael.
    On Thu, Jan 9, 2014 at 11:58 AM, Brian Johnson wrote:
    That only tells the status. I need to know the date that it was marked as Complete.
    Thanks.
    -Brian
    Michael Ochieng wrote:
    Hi Brian,
    The table is study_event and the field is subject_event_status_id.
    subject_event_status_id=4 for events marked complete.
    Hope this helps,
    Michael.
    On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson > wrote:
    Hey Folks -
    I'm writing a few custom reports for a client, and one of the things
    I need to reference is the date an event/crf is marked complete (The
    "Mark CRF Complete" box is checked). What table/field is this in
    within the database?
    Thanks!
    -Brian
  • ServerSageServerSage Posts: 32
    Do you mean the study_event.date_updated field? There isn't an event_status table that I can see. There is a subject_event_status, but that's simply the table that correlates 4 = Complete.
    If I'm not mistaken the study_event.date_updated will get set to now() every time an update is made to an Event/CRF, which means administrative edits performed after it's marked Complete will cause the date_updated to get set. Correct? Or can I rely upon that field to only be set when the Event/CRF is marked as Complete?
    Thanks again for the assistance.
    -Brian
    Michael Ochieng wrote:
    > Use date_updated field in the event_status table.
    >
    > Kind Regards,
    > Michael.
    >
    >
    > On Thu, Jan 9, 2014 at 11:58 AM, Brian Johnson > wrote:
    >
    > That only tells the status. I need to know the date that it was
    > marked as Complete.
    >
    > Thanks.
    >
    > -Brian
    >
    > Michael Ochieng wrote:
    >
    > Hi Brian,
    >
    > The table is study_event and the field is subject_event_status_id.
    > subject_event_status_id=4 for events marked complete.
    >
    > Hope this helps,
    >
    > Michael.
    >
    >
    > On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson
    >
    > >> wrote:
    >
    > Hey Folks -
    >
    > I'm writing a few custom reports for a client, and one of
    > the things
    > I need to reference is the date an event/crf is marked
    > complete (The
    > "Mark CRF Complete" box is checked). What table/field is this in
    > within the database?
    >
    > Thanks!
    >
    > -Brian
    >
  • mochisoftmochisoft Posts: 46
    Hi Brian,
    I agree with your observation that the date_updated field will change if administrative editing is done but from my understanding of the underlying oc database structure ( which is limited), that is the only way I could achieve the scenerio you have described.
    Lets hope we will get more insights from the forum.
    Kind Regards,
    Michael.
    On Thu, Jan 9, 2014 at 12:14 PM, Brian Johnson wrote:
    Do you mean the study_event.date_updated field? There isn't an event_status table that I can see. There is a subject_event_status, but that's simply the table that correlates 4 = Complete.
    If I'm not mistaken the study_event.date_updated will get set to now() every time an update is made to an Event/CRF, which means administrative edits performed after it's marked Complete will cause the date_updated to get set. Correct? Or can I rely upon that field to only be set when the Event/CRF is marked as Complete?
    Thanks again for the assistance.
    -Brian
    Michael Ochieng wrote:
    Use date_updated field in the event_status table.
    Kind Regards,
    Michael.
    On Thu, Jan 9, 2014 at 11:58 AM, Brian Johnson > wrote:
    That only tells the status. I need to know the date that it was
    marked as Complete.
    Thanks.
    -Brian
    Michael Ochieng wrote:
    Hi Brian,
    The table is study_event and the field is subject_event_status_id.
    subject_event_status_id=4 for events marked complete.
    Hope this helps,
    Michael.
    On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson

    >> wrote:
    Hey Folks -
    I'm writing a few custom reports for a client, and one of
    the things
    I need to reference is the date an event/crf is marked
    complete (The
    "Mark CRF Complete" box is checked). What table/field is this in
    within the database?
    Thanks!
    -Brian
  • toskriptoskrip Posts: 279 ✭✭✭
    Hi Brian,
    I believe that this information is part of Study Audit Log. Just go to
    the audit_log_event table. The column:
    audit_log_event_type_id=8
    is referring to: Event CRF marked complete
    in table audit_log_event_type
    best
    Tomas
    On 09.01.2014 10:06, Brian Johnson wrote:
    > > That only tells the status. I need to know the date that it was marked
    > > as Complete.
    > >
    > > Thanks.
    > >
    > > -Brian
    > >
    > > Michael Ochieng wrote:
    >> >> Hi Brian,
    >> >>
    >> >> The table is study_event and the field is subject_event_status_id.
    >> >> subject_event_status_id=4 for events marked complete.
    >> >>
    >> >> Hope this helps,
    >> >>
    >> >> Michael.
    >> >>
    >> >>
    >> >> On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson > >> > wrote:
    >> >>
    >> >> Hey Folks -
    >> >>
    >> >> I'm writing a few custom reports for a client, and one of the things
    >> >> I need to reference is the date an event/crf is marked complete (The
    >> >> "Mark CRF Complete" box is checked). What table/field is this in
    >> >> within the database?
    >> >>
    >> >> Thanks!
    >> >>
    >> >> -Brian
    >> >>
  • ServerSageServerSage Posts: 32
    Hi Tomas -
    Brilliant! That was exactly what I was looking for, thanks!
    -Brian
    Skripcak, Tomas wrote:
    > Hi Brian,
    >
    > I believe that this information is part of Study Audit Log. Just go to
    > the audit_log_event table. The column:
    >
    > audit_log_event_type_id=8
    >
    > is referring to: Event CRF marked complete
    > in table audit_log_event_type
    >
    > best
    >
    > Tomas
    >
    > On 09.01.2014 10:06, Brian Johnson wrote:
    >> That only tells the status. I need to know the date that it was marked
    >> as Complete.
    >>
    >> Thanks.
    >>
    >> -Brian
    >>
    >> Michael Ochieng wrote:
    >>> Hi Brian,
    >>>
    >>> The table is study_event and the field is subject_event_status_id.
    >>> subject_event_status_id=4 for events marked complete.
    >>>
    >>> Hope this helps,
    >>>
    >>> Michael.
    >>>
    >>>
    >>> On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson>> > wrote:
    >>>
    >>> Hey Folks -
    >>>
    >>> I'm writing a few custom reports for a client, and one of the things
    >>> I need to reference is the date an event/crf is marked complete (The
    >>> "Mark CRF Complete" box is checked). What table/field is this in
    >>> within the database?
    >>>
    >>> Thanks!
    >>>
    >>> -Brian
    >>>
  • lindsay.stevenslindsay.stevens Posts: 404 ✭✭✭
    Hi Brian
    Aside from the audit log table, you can use event_crf.date_completed, which is blank until the CRF is marked complete.
    The attached script has aliased the database column names to more familiar names in case that helps you for other reporting things. The one dated 2013-09-17 is for pg9.2; the on 2013-12-04 is for pg8.4.
    Best regards,
    Lindsay
    On 9 January 2014 21:06, Brian Johnson wrote:
    Hi Tomas -
    Brilliant! That was exactly what I was looking for, thanks!
    -Brian
    Skripcak, Tomas wrote:
    Hi Brian,
    I believe that this information is part of Study Audit Log. Just go to
    the audit_log_event table. The column:
    audit_log_event_type_id=8
    is referring to: Event CRF marked complete
    in table audit_log_event_type
    best
    Tomas
    On 09.01.2014 10:06, Brian Johnson wrote:
    That only tells the status. I need to know the date that it was marked
    as Complete.
    Thanks.
    -Brian
    Michael Ochieng wrote:
    Hi Brian,
    The table is study_event and the field is subject_event_status_id.
    subject_event_status_id=4 for events marked complete.
    Hope this helps,
    Michael.
    On Thu, Jan 9, 2014 at 11:07 AM, Brian Johnson> wrote:
    Hey Folks -
    I'm writing a few custom reports for a client, and one of the things
    I need to reference is the date an event/crf is marked complete (The
    "Mark CRF Complete" box is checked). What table/field is this in
    within the database?
    Thanks!
    -Brian
This discussion has been closed.