Previously we would have used workflows for some of this. But they will be deprecated next year. And they only update on save, and would not calculate things that we want, each day.
So we need to use a scheduled task.
The goal is to create pretty funnels and reports to see how long opportunities are in the pipeline, and at the current stage, and how long since edited.
We were planning to create these fields:
1. date_stage_modified_c is a calculated field updated when a record is edited and the stage changes.
2. days_in_current_stage_c = today - opportunities_c.date_stage_modified_c
3. days_in_pipeline_c = today - opportunities.date_created
4. days_not_edited_c = today - opportunities.date_modified
Fields 2-4 will need a scheduled task.
I'm trying to work out the scheduled task, but don't know how to update the
opportunies_cstm table based on opportunities values (see below).
Field 1 - I haven't worked out either yet. It would have been a classic workflow scenario.
I can see that opportunities_audit contains useful info, but I can't reference it in the calculate field as I had hoped. I probably need to create a webhook for this one, although I haven't done any hooks for around 4 years..
And I'm working locally on a MAMP copy of the site.
Is there an easy way to test the task through the browser?
Here's what I have been playing with today....
1. Define a function label
/custom/Extension/modules/Schedulers/Ext/Language/en_us.custom_oppdates.php
<?php
$mod_strings['LBL_CUSTOM_OPPDATES'] = 'Custom Opp Dates update';
2. Create the function
/custom/Extension/modules/Schedulers/Ext/ScheduledTasks/custom_oppdates.php
// Add the job key to the list of job strings
array_push($job_strings, 'custom_oppdates');
function custom_oppdates() {
require_once 'data/BeanFactory.php';
require_once 'data/SugarBean.php';
// Get the module bean
$myModule = BeanFactory::getBean('Opportunities');
$myWhere = "opportunities.sales_stage NOT LIKE 'Closed%' AND opportunities.deleted = '0' ";
// Get all the opportunities that aren't deleted or closed
$myList = $myModule->get_full_list('',$mywhere,true);
if($myList != null) {
foreach($myList as $mainrec) {
// do I need to do this to update custom opportunities table?
// or can I just treat the custom fields as part of the main record?
// and date_diff doesn't seem to work?
$mycustid = $mainrec->id;
$mycustrec = BeanFactory::getBean('Opportunities',$mycustid,array('use_cache'=<false));
$calctoday = date('Y-m-d h:i:s');
$calcdiff1 = date_diff($mainrec->date_modified,$calctoday);
$mycustrec->days_not_edited_c = $calcdiff1->format('d');
$calcdiff2 = date_diff($mainrec->date_entered,$calctoday);
$mycustrec->days_in_pipeline_c = $calcdiff2->format('d');
$calcdiff3 = date_diff($mainrec->date_stage_modified_c,$calctoday);
$mycustrec->days_in_pipeline_c = $calcdiff3->format('d');
// Prevent changes to the opportunity modified date and user
$mainrec->update_date_modified = false;
$mainrec->update_modified_by = false;
$mainrec->save();
}
}
return true;
}
I did try a shortcut:
created the new field, days_not_edited_c
Defined it as a calculated field:
abs(subtract(daysUntil(today()),daysUntil($date_modified)))
Used the Recalculate Values option to update the record.
However this changed the date_modified, so it made it useless - I would want to exclude
the date_modified field from recalculations, which you can do with scheduled tasks.
It would work for days_in_pipeline_c, as long as we manually Recalculate each day,
although it still hits the date_modified.
abs(subtract(daysUntil(today()),daysUntil($date_entered)))
Is there a way to easily automate a recalculation?
