I’ve added a new field to the Contacts module labelled ‘renewal date’ and created a workflow process to check the renewal date and send an email reminder if the date is due. It work fine if I enter or change a renewal date but fails in the scheduled job. The logs shows the query failed “MySQL error 1054: Unknown column ‘contacts_cstm.barp_renewal_date_c’ in ‘where clause’”
The query does indeed fail when I try it in sql
“SELECT contacts.id AS id
FROM contacts
WHERE contacts_cstm.barp_renewal_date_c <= DATE_ADD(NOW(), INTERVAL + 7 day)
AND NOT EXISTS (SELECT * FROM aow_processed
WHERE aow_processed.aow_workflow_id=‘1c7da2db-e88b-bbae-a823-53449e03a711’
AND aow_processed.parent_id=contacts.id
AND aow_processed.status = ‘Complete’
AND aow_processed.deleted = 0)
AND contacts.deleted = 0”
The reason being that the join statement is missing for the contacts_cstm table, if I execute this, it works
“SELECT contacts.id AS id
FROM contacts
left join contacts_cstm on contacts.id = contacts_cstm.id_c
WHERE contacts_cstm.barp_renewal_date_c <= DATE_ADD(NOW(), INTERVAL + 7 day)
AND NOT EXISTS (SELECT * FROM aow_processed
WHERE aow_processed.aow_workflow_id=‘1c7da2db-e88b-bbae-a823-53449e03a711’
AND aow_processed.parent_id=contacts.id
AND aow_processed.status = ‘Complete’
AND aow_processed.deleted = 0)
AND contacts.deleted = 0”
How can I correct this?
This is a critical feature for a client and this is the latest patch of SuiteCRM, whatever is creating the query isnt creating the join statement.