adding created date to sub pains

[cff_moiseszaragoza]

I am kinda new to the CRM and i dont know where everything is

I am working on the leads mod and i have a couple of sub pains one of them is called history

Here is the Query that loads data into that pain

 
select top 51 * from 
    (
        ( 
            select meetings.id , meetings.name , 0 reply_to_status , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.parent_id , meetings.parent_type , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'meetings' panel_name from meetings left join users jt1 on jt1.id= meetings.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 inner join meetings_leads on (meetings.id=meetings_leads.meeting_id and meetings_leads.lead_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c'
        ) 
        where 
        (
             meetings_leads.deleted=0 and meetings.deleted=0 and (meetings.status='held' or meetings.status='not held')) and meetings.deleted=0 
        ) 
        union all 
        ( 
            select meetings.id , meetings.name , 0 reply_to_status , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.parent_id , meetings.parent_type , meetings.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'oldmeetings' panel_name from meetings left join users jt1 on jt1.id= meetings.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 where 
            ( 
                meetings.parent_id = '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and meetings.parent_type = 'leads' and meetings.id not in 
                ( 
                    select meeting_id from meetings_leads 
                ) 
                and 
                (
                    meetings.status='held' or meetings.status='not held'
                )
            ) 
            and meetings.deleted=0 
        ) 
        union all 
        ( 
            select tasks.id , tasks.name , 0 reply_to_status , tasks.status , isnull(contacts.first_name,''
        ) 
        + ' ' + isnull(contacts.last_name,''
    ) 
    contact_name , tasks.contact_id , contacts.assigned_user_id contact_name_owner , 'contacts' contact_name_mod, tasks.parent_id , tasks.parent_type , tasks.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' panel_name from tasks left join contacts contacts on contacts.id= tasks.contact_id and contacts.deleted=0 and contacts.deleted=0 left join users jt1 on jt1.id= tasks.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 where 
    ( 
        tasks.parent_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and tasks.parent_type='leads' and tasks.deleted=0 and (tasks.status='completed' or tasks.status='deferred')) and tasks.deleted=0 
    ) 
    union all 
    (
         select calls.id , calls.name , 0 reply_to_status , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.parent_id , calls.parent_type , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'calls' panel_name from calls left join users jt1 on jt1.id= calls.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 inner join calls_leads on (calls.id=calls_leads.call_id and calls_leads.lead_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c'
    ) 
    where 
    ( 
        calls_leads.deleted=0 and calls.deleted=0 and 
        (
            calls.status='held' or calls.status='not held'
        )
    ) 
    and calls.deleted=0 
)
 union all 
( 
    select calls.id , calls.name , 0 reply_to_status , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.parent_id , calls.parent_type , calls.date_modified , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'oldcalls' panel_name from calls left join users jt1 on jt1.id= calls.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 where 
    ( 
        calls.parent_id = '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and calls.parent_type = 'leads' and calls.id not in 
        ( 
            select call_id from calls_leads 
        ) 
        and 
        (
            calls.status='held' or calls.status='not held'
        )
    ) 
    and calls.deleted=0
)
 union all 
( 
    select notes.id , notes.name , 0 reply_to_status , ' ' status , isnull
    (
        contacts.first_name,''
    ) 
    + ' ' + isnull(contacts.last_name,'') contact_name , notes.contact_id , contacts.assigned_user_id contact_name_owner , 'contacts' contact_name_mod, notes.parent_id , notes.parent_type , notes.date_modified , ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename , notes.created_by , 'notes' panel_name from notes left join contacts contacts on contacts.id= notes.contact_id and contacts.deleted=0 and contacts.deleted=0 where 
    ( 
        notes.parent_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and notes.parent_type='leads' and notes.deleted=0) and notes.deleted=0 
    ) union all 
    ( 
        select emails.id , emails.name , emails.reply_to_status , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'emails' panel_name from emails left join users jt0 on jt0.id= emails.assigned_user_id and jt0.deleted=0 and jt0.deleted=0 inner join emails_beans on (emails.id=emails_beans.email_id and emails_beans.bean_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and bean_module='leads'
    ) 
    where 
    ( 
        emails_beans.deleted=0 and emails.deleted=0) and emails.deleted=0 
    ) 
    union all 
    ( 
        select emails.id , emails.name , emails.reply_to_status , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type , emails.date_modified , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'linkedemails' panel_name from emails left join users jt0 on jt0.id= emails.assigned_user_id and jt0.deleted=0 and jt0.deleted=0 join 
        (
            select distinct email_id from emails_email_addr_rel eear join email_addr_bean_rel eabr on eabr.bean_id ='27bf6d38-a2e5-105c-98ea-4d596ca1392c' and eabr.bean_module = 'leads' and eabr.email_address_id = eear.email_address_id and eabr.deleted=0 where eear.deleted=0 and eear.email_id not in 
            (
                select eb.email_id from emails_beans eb where eb.bean_module ='leads' and eb.bean_id = '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and eb.deleted=0
            ) 
        ) 
        derivedemails on derivedemails.email_id = emails.id where emails.deleted=0 
    ) 
) 
as top_count order by date_modified desc 
  

I see allots of date_modified but i also need to bring in date_entered. Does any one know where i would add that field in?

[cff_moiseszaragoza]

ok i am getting close i was able to get the SQL to look like

 
select top 51 * from (( select meetings.id , meetings.name , 0 reply_to_status , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.parent_id , meetings.parent_type 
, meetings.date_modified , meetings.date_entered , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'meetings' panel_name from meetings left join users jt1 on jt1.id= meetings.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 inner join meetings_leads on (meetings.id=meetings_leads.meeting_id and meetings_leads.lead_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c') where ( meetings_leads.deleted=0 and meetings.deleted=0 and (meetings.status='held' or meetings.status='not held')) and meetings.deleted=0 ) union all ( select meetings.id , meetings.name , 0 reply_to_status , meetings.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , meetings.parent_id , meetings.parent_type 
, meetings.date_modified , meetings.date_entered , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , meetings.assigned_user_id , 'oldmeetings' panel_name from meetings left join users jt1 on jt1.id= meetings.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 where ( meetings.parent_id = '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and meetings.parent_type = 'leads' and meetings.id not in ( select meeting_id from meetings_leads ) and (meetings.status='held' or meetings.status='not held')) and meetings.deleted=0 ) union all ( select tasks.id , tasks.name , 0 reply_to_status , tasks.status , isnull(contacts.first_name,'') + ' ' + isnull(contacts.last_name,'') contact_name , tasks.contact_id , contacts.assigned_user_id contact_name_owner , 'contacts' contact_name_mod, tasks.parent_id , tasks.parent_type 
, tasks.date_modified , tasks.date_entered , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , tasks.assigned_user_id , 'tasks' panel_name from tasks left join contacts contacts on contacts.id= tasks.contact_id and contacts.deleted=0 and contacts.deleted=0 left join users jt1 on jt1.id= tasks.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 where ( tasks.parent_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and tasks.parent_type='leads' and tasks.deleted=0 and (tasks.status='completed' or tasks.status='deferred')) and tasks.deleted=0 ) union all ( select calls.id , calls.name , 0 reply_to_status , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.parent_id , calls.parent_type 
, calls.date_modified , calls.date_entered , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'calls' panel_name from calls left join users jt1 on jt1.id= calls.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 inner join calls_leads on (calls.id=calls_leads.call_id and calls_leads.lead_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c') where ( calls_leads.deleted=0 and calls.deleted=0 and (calls.status='held' or calls.status='not held')) and calls.deleted=0 ) union all ( select calls.id , calls.name , 0 reply_to_status , calls.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , calls.parent_id , calls.parent_type 
, calls.date_modified , calls.date_entered , jt1.user_name assigned_user_name , jt1.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , calls.assigned_user_id , 'oldcalls' panel_name from calls left join users jt1 on jt1.id= calls.assigned_user_id and jt1.deleted=0 and jt1.deleted=0 where ( calls.parent_id = '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and calls.parent_type = 'leads' and calls.id not in ( select call_id from calls_leads ) and (calls.status='held' or calls.status='not held')) and calls.deleted=0 ) union all ( select notes.id , notes.name , 0 reply_to_status , ' ' status , isnull(contacts.first_name,'') + ' ' + isnull(contacts.last_name,'') contact_name , notes.contact_id , contacts.assigned_user_id contact_name_owner , 'contacts' contact_name_mod, notes.parent_id , notes.parent_type 
, notes.date_modified , notes.date_entered , ' ' assigned_user_name , ' ' assigned_user_owner , ' ' assigned_user_mod , notes.filename , notes.created_by , 'notes' panel_name from notes left join contacts contacts on contacts.id= notes.contact_id and contacts.deleted=0 and contacts.deleted=0 where ( notes.parent_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and notes.parent_type='leads' and notes.deleted=0) and notes.deleted=0 ) union all ( select emails.id , emails.name , emails.reply_to_status , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type 
, emails.date_modified , emails.date_entered , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'emails' panel_name from emails left join users jt0 on jt0.id= emails.assigned_user_id and jt0.deleted=0 and jt0.deleted=0 inner join emails_beans on (emails.id=emails_beans.email_id and emails_beans.bean_id= '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and bean_module='leads') where ( emails_beans.deleted=0 and emails.deleted=0) and emails.deleted=0 ) union all ( select emails.id , emails.name , emails.reply_to_status , emails.status , ' ' contact_name , ' ' contact_id , ' ' contact_name_owner , ' ' contact_name_mod , emails.parent_id , emails.parent_type 
, emails.date_modified                         , jt0.user_name assigned_user_name , jt0.created_by assigned_user_name_owner , 'users' assigned_user_name_mod, ' ' filename , emails.assigned_user_id , 'linkedemails' panel_name from emails left join users jt0 on jt0.id= emails.assigned_user_id and jt0.deleted=0 and jt0.deleted=0 join (select distinct email_id from emails_email_addr_rel eear join email_addr_bean_rel eabr on eabr.bean_id ='27bf6d38-a2e5-105c-98ea-4d596ca1392c' and eabr.bean_module = 'leads' and eabr.email_address_id = eear.email_address_id and eabr.deleted=0 where eear.deleted=0 and eear.email_id not in (select eb.email_id from emails_beans eb where eb.bean_module ='leads' and eb.bean_id = '27bf6d38-a2e5-105c-98ea-4d596ca1392c' and eb.deleted=0) ) derivedemails on derivedemails.email_id = emails.id where emails.deleted=0 ) ) as top_count order by date_modified desc 
  

every date_modified is fallowed bv a date_entered but i am just missing one any one know where that part of the SQL comes from

So far I used:
modules/Meetings/metadata/subpanels/ForHistory.php
modules/Tasks/metadata/subpanels/ForHistory.php
modules/Calls/metadata/subpanels/ForHistory.php
modules/Notes/metadata/subpanels/ForHistory.php
modules/Emails/metadata/subpanels/ForHistory.php

LAST FILE I USED WAS

modules/Emails/metadata/subpanels/ForUnlinkedEmailHistory.php