Hello guys, sorry if this is the wrong category to ask for your help.
I have a problem with a workflow that suppouse to send a birthday email to my contacts but i receive the next error when i check suitecrm.log:
Query Failed: SELECT contacts.id AS id FROM contacts WHERE contacts.birthdate = DATE_ADD(contacts.MonthDate, INTERVAL + 1 minute) AND contacts.deleted = 0 : MySQL error 1054: Unknown column ‘contacts.MonthDate’ in ‘where clause’
The condition in the workflow is:
Module: Contacts // Field: Birthdate // Operator: Equal to // Type: Date // Value: MonthDate + 1 Minute
Thank you so much for your help.
Version 7.9.7
Sugar Version 6.5.25 (Build 344)
Can you check if this “contacts.MonthDate” exists in the contacts table?
No, I have checked and doesn’t exist
That’s the problem then. It’s looking for a column that doesn’t exists. There should be a better query to check for birthdate than the one workflow is using that shouldn’t include MonthDate
Try looking in the contacts_cstm table as well
Perhaps you had first created the field, then the workflow and then deleted the field. In this situation the workflow refers to a no longer existing field.
Another issue, in case the field exists in contacts_cstm may be the default value of the field: it may be that records in which that field is empty or NULL, or where thae value is not a date (by the way month may just be a number and not a date), the query may fail.
Actually the column exists in contacts_cstm and the format is type date and NULL.
How can I do to know if the value in condition where I select MonthDate is a query to a table or if it is a variable to compare day and month of the birthday field. Maybe in the editor can I find that option?
Why don’t t you add a condition in your workflow to check that MonthDate is not empty (or Null or has only valid values) as well. In this way you would avoid invalid values and, possibly, avoid the error.
Additionally you may go in Admin->Studio->module (I believe contact)->fields and edit MonthDate. You may set a default value for example or do other things.