Contact Last email

[twick100]

We do email campaigns, is it possible to display a field in contacts with the last email and be able to use it in search filters?

[rafael.q.g@hotmail.com]

Yes it is. Once the search filters works based on database values, my option is that the esiest way to do that is:
1. creating a new field on contacts to store this information.
2. Program an scheduled task to get this info from mails and write in this new field.
3. Add the new concacts field in contacts search panel.

[christianknoll]

Hmmm .. why not do it as follows:

(1) overwrite view.detail.php for the module you want his in read the info dynamically into a nonDB field on the module that you can display in the view
(2) to search by look e.g. at modules/Contacts/metadata/SearchFields.php and see how the email address is used to search by. Define the same subquery with a may statement on the last email.

With that no need to store redundant information and also no need to monitor a scheduled job running ... ;-)

christian.

Yes it is. Once the search filters works based on database values, my option is that the esiest way to do that is:
1. creating a new field on contacts to store this information.
2. Program an scheduled task to get this info from mails and write in this new field.
3. Add the new concacts field in contacts search panel.

[twick100]

Thanks, going to try the suggested methods. Will be back.

[twick100]

Hmmm .. why not do it as follows:

(1) overwrite view.detail.php for the module you want his in read the info dynamically into a nonDB field on the module that you can display in the view
(2) to search by look e.g. at modules/Contacts/metadata/SearchFields.php and see how the email address is used to search by. Define the same subquery with a may statement on the last email.

With that no need to store redundant information and also no need to monitor a scheduled job running ... ;-)

christian.

Ok after much research I still don't understand this. I don't understand why I would be overwriting view.detail.php. There doesn't appear to be anything in that file I should modify.

Can you give me an example?

[twick100]

Ok... there has got to be an easier way to do this.

I am going to reiterate for a moment.

What I want is to search contacts based on when they were last emailed. I can easily display the last time they were emailed in a field via logic hook but that does not allow me to search based on the last email date.

I am looking in SearchFields.php for Contacts module and I see this. Which is obviously getting the email address for the contact.

 'email' => 
  array (
    'query_type' => 'default',
    'operator' => 'subquery',
    'subquery' => 'SELECT eabr.bean_id FROM email_addr_bean_rel eabr JOIN email_addresses ea ON (ea.id = eabr.email_address_id) WHERE eabr.deleted=0 AND ea.email_address LIKE',
    'db_field' => 
    array (
      0 => 'id',
    ),
  ), 
  

So I create this. Which should technically get the last email date that was sent to a particular contact.

 'last_email_c' => 
  array (
    'query_type' => 'default',
    'operator' => 'subquery',
    'subquery' => 'SELECT e.date_sent FROM emails e WHERE e.parent_type = \'Contacts\' AND e.parent_id = \'{0}\'',
    'db_field' => 
    array (
      0 => 'id',
    ),
  ), 
  

However, I am not sure how to actually display the data in the list view. I checked through listviewdefs.php and I see no reference to either of these functions.

What other steps am I missing?

[twick100]

Update on this. I kind of have something but the logic is a bit off..

in serchfields.php

 '_last_email' => 
  array (
    'query_type' => 'format',
    'operator' => 'subquery',
    'subquery' => 'SELECT e.parent_id id FROM emails e LEFT OUTWHERE DATEDIFF(NOW(),e.date_sent) > '.$_POST["_last_email_basic"].' OR e.date_sent is null',
    'db_field' => 
    array (
      0 => 'id',
    ),
  ), 
  

in searchdefs.php

 '_last_email' => 
      array (
        'label' => 'Last Email Touched',
        'type' => 'int',
        'default' => true,
        'width' => '10%',
        'name' => '_last_email',
      ), 
  

This does work, however, it only brings back records that are in the emails table. If there is a contact id that is not in that table than it doesn't show it.

Due to the fact that it uses this as its subquery "

 ((contacts.id IN (SELECT e.parent_id id FROM emails e WHERE e.date_sent is null OR DATEDIFF(NOW(),e.date_sent) > 2))) 
  

"

Is it possible to supply NOT IN rather than IN?

[christianknoll]

You need to determine what the date is the last email was sent and populate this to the bean. You can also overwirt ethe class of the module that is inherited from SugarBean and overwrite the retrieve statement calling parent::retrieve and when this returns additonally select the max date of the retrieved email and populate this to the bean so the view can pick it up.

View.detail.php is just onbe place where you can do this and the nice thing is that this can be done upgrade safe in the customs directory. The Modification of the core mOdule is not upgradesafe since you have to change Contact.php ..

christian.

Ok after much research I still don't understand this. I don't understand why I would be overwriting view.detail.php. There doesn't appear to be anything in that file I should modify.

Can you give me an example?