I have added a new TextField custom filed called ‘Financial year and quarter’ in to the Opportunity module. This field should be updated by a sheduler job, calculating a value (2022-Q1) based on ‘Closed Date’.
The sheduler job works fine as long as this new field has been set a value by edit. But I need all the historic records to get updated. Why old records are not updating? TIA
function updateFinancialYearQuarter()
{
$GLOBALS['log']->info("----->Scheduler is about to update 'Financial year quarter' fields in Opportunities");
$db = DBManagerFactory::getInstance();
$query = "SELECT opportunities.id, opportunities.date_closed, opportunities_cstm.financial_year_and_quarter_e_c FROM opportunities LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c WHERE opportunities.deleted = 0";
$res = $db->query($query);
while ($row = $db->fetchByAssoc($res))
{
//Expected close date
if (!empty($row['date_closed'])){
$fyqCloseDate = getFinancialYearAndQuarter($row['date_closed']);
$updateQuery = "UPDATE opportunities_cstm SET opportunities_cstm.financial_year_and_quarter_e_c = '".$fyqCloseDate ."' WHERE id_c = '".$row['id']."'";
$db->query($updateQuery, true);
}
}
$GLOBALS['log']->info("----->Scheduler completed the job : updating 'Financial year quarter' fields in Opportunities");
return true;
}