Problem changing a custom field with scheduler based on another one

Question

Its been a week now that i am trying to make this code work could someone point out what am i doing wrong? I tried printing a line everytime a new row of the database is processed but it doesn't change anything. I also put in anex the vardefs and SQL schema. $sql = "SELECT T3.id, T5.lftm_mes_referencia_c FROM ger01_gerenciamentocliente T1 INNER JOIN ger01_gerenciamentocliente_contacts_c T2 ON T1.id = T2.ger01_gere60bccliente_idb AND T2.deleted = 0 INNER JOIN contacts T3 ON T3.id = T2.ger01_gerenciamentocliente_contactscontacts_ida AND T3.deleted = 0 INNER JOIN contacts_cstm T4 ON T4.id_c = T3.id INNER JOIN ger01_gerenciamentocliente_cstm T5 ON T5.id_c = T1.id WHERE T1.deleted = 0;"; while ($row = $GLOBALS['db']->fetchByAssoc($result)) { $contact_bean = BeanFactory::retrieveBean('contacts', $row['id'], array('disable_row_level_security' => true)); if(!empty($contact_bean->lftm_data_ativacao_c)) { if($contact_bean->lftm_data_ativacao_c > $row['lftm_mes_referencia_c']) { $contact_bean->lftm_data_ativacao_c = $row['lftm_mes_referencia_c']; $contact_bean->save(); } } else { $contact_bean->lftm_data_ativacao_c = $row['lftm_mes_referencia_c']; $contact_bean->save(); } } 
 7183._MySQLTablesSchema.html.zip 5710._vardefschema.html.zip

Giovanni Marazzi · Accepted Answer

You are selecting the id from Ger01_GerenciamentoCliente

$SQ->select(array('id', 'lftm_mes_referencia_c'));
$SQ->from(BeanFactory::getBean('Ger01_GerenciamentoCliente'));

and then looking for a Contact with the same id as Ger01_GerenciamentoCliente.

$client = BeanFactory::retrieveBean('Contacts', $row['id'], array('disable_row_level_security' => true));

Which of course will return nothing.

Based on your sql you have a relationship between your Contacts and your Ger01_GerenciamentoCliente

So you will need to get your Ger01_GerenciamentoCliente bean:

$gCliente = BeanFactory::retrieveBean('Ger01_GerenciamentoCliente', $row['id'], array('disable_row_level_security' => true));

then find the related Contact:

$link = <the name of the relationship between Contact and Ger01_GerenciamentoCliente>;
if($gCliente->load_relationship($link)){
   foreach($gCliente->$link->getBeans() as $contact)
      <do something with the related contact>
   }
}

Also, Try using newBean

$SQ->from(BeanFactory::newBean('Ger01_GerenciamentoCliente'));

instead of getBean

$SQ->from(BeanFactory::getBean('Ger01_GerenciamentoCliente'));

though I think getBean returns a new bean if no id is specified or the id is not found it will be faster since you know you are not passing an id.

HTH

FrancescaS

P.S.This is a good reference for BeanFactory:

http://support.sugarcrm.com/Documentation/Sugar_Developer/Sugar_Developer_Guide_7.9/Data_Framework/Models/BeanFactory/in…