Author Topic: Contact Created Date (Read 1680 times)

kyle2012 · January 17, 2012, 01:21:34 am

How can I display the created date of a contact in the summary?

Alternatively, how do I get the first record in the change log which presumably would be the created date?

Kurund Jalmi · January 17, 2012, 10:38:51 pm

Quote

How can I display the created date of a contact in the summary?
Alternatively, how do I get the first record in the change log which presumably would be the created date?

civicrm_log table stores the changes for contact. Below sql should give you created date:

Quote

SELECT `modified_date` FROM `civicrm_log` WHERE entity_table="civicrm_contact" and entity_id=102 order by modified_date limit 1

where 102 is contact id

Then use contact summary hook to add this information on summary page.
http://wiki.civicrm.org/confluence/display/CRMDOC40/CiviCRM+hook+specification#CiviCRMhookspecification-hookcivicrmsummary

Hth
Kurund

websynapse · March 12, 2012, 03:37:09 pm

How can you tell if this is the created or modified date? I'm doing comparisons with Advanced Search 'Added' vs 'Modified' and this seems to catch both.

EDIT: OK I can see that that query shows only the first 'modified by' date, which limits the search to when a single contact was created.

I'm trying to build a query that shows me how many contacts were created over time periods - can anyone help please?

This catches all contact modifications:

Code: [Select]

select count(*), left(modified_date, 7) as month
    from civicrm_log
    where modified_date like '2012-%-%'
    and entity_table="civicrm_contact"
     group by month
     order by month desc;

I think it needs something like after where modified_date like '2012-%-%' that says: 'and only if it is the first modified date for this contact' but I don't know how to say that in mysql.

xavier · March 13, 2012, 03:27:12 am

IMO, we should add an enum action field to put ADD,UPDATE,DELETE, would make it way easier, even when using the "complete" log system

X+

websynapse · March 13, 2012, 03:57:21 pm

I agree, some help with my query would be nice though...

CiviCRM Community Forums (archive)

News:

Author Topic: Contact Created Date (Read 1680 times)

kyle2012

Contact Created Date

Kurund Jalmi

Re: Contact Created Date

websynapse

Re: Contact Created Date

xavier

Re: Contact Created Date

websynapse

Re: Contact Created Date