Workflow date comparison not working

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

I’m setting up a workflow like the one in the attached image, the custom field of Last Contacted should be updated to the value of the saved notes Date Contacted field if Last Contacted < Date Contacted. However this isn’t working, the workflow never triggers. Digging into the SQL query that the workflow is generating has revealed that SuiteCRM might not be building the query correctly

SELECT notes.id AS id FROM notes LEFT JOIN contacts contact ON notes.contact_id=contact.id AND contact.deleted=0
LEFT JOIN contacts_cstm contact_cstm ON contacts.id = contact_cstm.id_c LEFT JOIN notes_cstm notes_cstm ON notes.id = notes_cstm.id_c  
WHERE contact_cstm.last_contacted_c < notes_cstm.date_contacted_c AND notes.id = 'a5a2e1b4-bbdf-86d4-88c4-594ac9f763bd'

is the output from SuiteCRM, when executed against MySQL as a standalone query it fails on line 2 with the error Unknown Column 'contacts.id in ‘on clause’, this appears to be caused by the use of an alias for the contacts table in the first left join then not using the alias in the second left join

If the query is modified to

SELECT notes.id AS id FROM notes LEFT JOIN contacts contact ON notes.contact_id=contact.id AND contact.deleted=0
LEFT JOIN contacts_cstm contact_cstm ON contact.id= contact_cstm.id_c LEFT JOIN notes_cstm notes_cstm ON notes.id = notes_cstm.id_c  
WHERE contact_cstm.last_contacted_c < notes_cstm.date_contacted_c AND notes.id = 'a5a2e1b4-bbdf-86d4-88c4-594ac9f763bd'

It executes successfully.

Any thoughts on this, did I build my workflow wrong or do I need to submit a bug report on GitHub?

2

What version of SuiteCRM are you running?

If it’s not the latest, can you please try reproducing this bug on an online demo?

https://www.softaculous.com/demos/SuiteCRM

If it doesn’t work there, we’ll know with greater security that it is a bug…

Thanks

3

Confirmed issue on demo CRM as well, I found a fix on github for this.

https://github.com/salesagility/SuiteCRM/pull/3293

Confirmed that the fix works as well.

4

Can you please add a comment there on that Issue, saying you applied it and it works?

It also needs some clarification because I think some commits there aren’t really part of the fix. I don’t suppose you applied all that Calendar stuff, did you? I suppose you just applied the changes to the workflow code…

5

Will do, and yeah that commit needs clarification, I only applied the changes to the AOW_WorkFlow.php file.

1 Like