HOW TO SEND Workflow WHEN Email Expected Closed Date Past 1 Day

I solved this problem by setting up a SugarBPM process with a feedback loop (see image). The process sends an email when the closed date has passed for an open opportunity, waits one day, and then the Gateway checks the Expected Close Date and the Sales Stage. If the Expected Close Date has not been changed to a future date, and if the Sales Stage has not been set to Closed, then another email is sent. This happens once per day until either the Expected Closed Date is set to a future date or the opportunity is closed. Hopefully this will help.

This solution will work great but I'd caution on looping BPM processes. The supporting tables can get bloated quite quickly and cause system issues and/or eat up your storage. Especially for modules with lots of recors.

What you may need is a job that finds records meeting the criteria and performs some updates on the records and then the BPM process and trigger an email or whatever action you want.

That's an important point about bloated tables. In our case, the loop only sends out emails and does not create any new records, so its safe in that regard.

Hi Yury Voloshin,

If you are running a process definition, you are creating rows in the database.

The pmse_bpm_flow table stores a new row for every step of every process, including every element the record passes through, and every sequences flow line.

For example, if you have a simple process definition that has only a start event and an end event and a single sequences flow line between them, every time that process runs it will create at a least the following:
One row in pmse_inbox (per process run)

One row in pmse_bpm_thread (can be more than one per process run)

Three rows in pmse_bpm_flow

If a process loops, it will record to the pmse_bpm_flow table a new row each time the record passes through the elements in that loop. This might be what was meant by bloat.

I hope this helps!

Patrick, thank you for the clarification. It does help.

hi Patrick McQueen

is it possible to run a report to help identify any processes that are causing 'bloat' or how much system resources each is taking?

If a report isn't possible, maybe an addon module or enhancement to display such statistics within the SugarBPM screens?

Would it be more efficient to start with filtering out 'Closed' opportunities first?

Hi Vincent Amari,

Yes!

Enterprise has both SugarBPM and Advanced Reports. Advanced Reports is a user interface for direct database access, making reporting on any and all data in the instance possible with schema familiarity and a basic understanding of MySQL.

This custom query will list the number of rows in the pmse_bpm_flow table written by each Process Definition in descending order:

SELECT COUNT(a.id) Count, b.name "Process Definition Name", b.prj_id "Process Definition ID" FROM pmse_bpm_flow a JOIN pmse_bpmn_process b ON a.pro_id = b.id GROUP BY b.prj_id ORDER BY Count DESC;

I hope this helps!

Hi Patrick McQueen

Thank you, what a diamond geeza you are!  :-)

(old London terminology)

I'll give this a try next week and report back.

Thanks for sharing the information Kroger Feedback.