Author Topic: Timestamp? (Read 2684 times)

gwiz · May 22, 2007, 06:05:03 pm

Is there a simple way of adding a timestamp at the time of adding a new contact. I am looking for a way to query new contacts on a weekly basis and so far have been eluded. Any help is much appreciated. Thanks in advance.

Donald Lobo · May 23, 2007, 02:05:04 am

yes, this is part of 1.7 (and has been there since 1.5 i think)

Check the "Change Log" section of advanced search. Gives you all the records modified in a given date range. This includes all newly created records

You can write a custom query to find newly created records. Check the civicrm_log table. The first entry for a contact is the creation date

lobo

gwiz · May 23, 2007, 01:12:57 pm

Thank you lobo, I overlooked the Change Log advanced search. This does get me closer to what I'm trying to achieve, but it doesn't seem to allow me to save a search for the past week only. I would have to create a new search weekly in order to maintain an ongoing report of the previous week.

So I came up w/ the following SQL query:

Code: [Select]

SELECT *
FROM civicrm_log 
WHERE WEEK(NOW()) - WEEK(modified_date) <= 1;

This seems to work for starters. However, I'm still a noob so I'm not sure what to do with my query to create a report on the front end. Another issue that I may be on a collision course with is that this date is of course the modified date. While it logs the creation of a new contact, if any changes are made to a contact, I'll get false returns in my report as I only want to monitor new contacts from the previous week.

If anyone's interested and/or unclear of what my purpose is, I can provide a more detailed use case. Again, any help is greatly appreciated.

Donald Lobo · May 23, 2007, 04:07:14 pm

gwiz:

1. unfortunately there is no easy way right now to link a query with a web report (or parts of civicrm). I'm not sure how we could go about enabling this, but any ideas or suggestions would be welcome. That said, your best bet would be to write a small custom page that gives u this report, accepts parameters etc. We'll be happy to help guide you with this on the IRC channel

2. with regard to creation vs modification and the false results issue, i suspect a good sql coder could come up with the right sql for your needs. (i'm not too good at sql

lobo

Kurund Jalmi · May 25, 2007, 05:30:16 am

try this statement

Code: [Select]

SELECT *
FROM civicrm_log 
WHERE WEEK(NOW()) - WEEK(modified_date) <= 1 GROUP BY entity_id;

This will give you first entry for each contact in civicrm_log table (i.e when contact was created)

- kurund

gwiz · May 25, 2007, 03:13:31 pm

Thank you lobo and kurund.

I'm definitely getting closer to my goal. GROUP BY entity_id does the trick. My bad in referring to contacts so generically. I forget that CiviCRM contacts can be individuals, households, and organizations. I'm really after recent Individuals.

So I tried this statement:

Code: [Select]

SELECT *
FROM civicrm_log 
WHERE WEEK(NOW()) - WEEK(modified_date) <= 1
AND entity_table 
IN ('civicrm_individual')
GROUP BY entity_id;

It returns individuals like it should but it breaks the GROUP BY entity_id magick since it now returns modified records as well. I tried a few variations on this but this was the closest to getting it right. Once I get my SQL statement figured out, I'll take you up on your offer, lobo, and join in the IRC sometime next week.

I hope everyone here has a great weekend and hopefully can take the time off to enjoy it.

CiviCRM Community Forums (archive)

News:

Author Topic: Timestamp? (Read 2684 times)

gwiz

Timestamp?

Donald Lobo

Re: Timestamp?

gwiz

Re: Timestamp?

Donald Lobo

Re: Timestamp?

Kurund Jalmi

Re: Timestamp?

gwiz

Re: Timestamp?