Unknown column issue when running workflow

SuiteCRM Forum - English Language SuiteCRM General Discussion
1

I am having an issue running the scheduler and triggering workflow items. The idea is it check when an event matches a couple of criteria for date and location the system will send an email to attendees, but it’s not working. The suitecrm log shows the below error at the time it should run. I have recreated the location fields from scratch and recreated the workflow, but no joy. Any ideas?

Sat Oct 16 04:00:03 2021 [2734471][1][FATAL] Mysqli_query failed.
Sat Oct 16 04:00:03 2021 [2734471][1][FATAL] Query Failed: SELECT fp_events.id AS id FROM fp_events LEFT JOIN fp_events_cstm fp_events_cstm ON fp_events.id = fp_events_cstm.id_c WHERE DATE_FORMAT(DATE(fp_events.date_start), ‘%Y-%m-%d %H:%i’) = DATE_FORMAT(DATE_ADD(Curdate(), INTERVAL - 2 day), ‘%Y-%m-%d %H:%i’) AND fp_events.fp_event_locations_fp_events_1fp_event_locations_ida = ‘6c3a5985-496a-2a93-bfd4-61693fd01984’ AND fp_events_cstm.renewalurl_c LIKE CONCAT(‘https://www.mydomain’ ,’%’) AND fp_events.deleted = 0 : MySQL error 1054: Unknown column ‘fp_events.fp_event_locations_fp_events_1fp_event_locations_ida’ in ‘where clause’
Sat Oct 16 04:00:03 2021 [2734471][1][FATAL] Mysqli_query failed.
Sat Oct 16 04:00:03 2021 [2734471][1][FATAL] Query Failed: SELECT fp_events.id AS id FROM fp_events WHERE DATE_FORMAT(DATE(fp_events.date_start), ‘%Y-%m-%d %H:%i’) = DATE_FORMAT(DATE_ADD(Curdate(), INTERVAL + 2 day), ‘%Y-%m-%d %H:%i’) AND fp_events.fp_event_locations_fp_events_1fp_event_locations_ida = ‘6c3a5985-496a-2a93-bfd4-61693fd01984’ AND fp_events.deleted = 0 : MySQL error 1054: Unknown column ‘fp_events.fp_event_locations_fp_events_1fp_event_locations_ida’ in ‘where clause’
Sat Oct 16 04:00:03 2021 [2734471][1][FATAL] Mysqli_query failed.
Sat Oct 16 04:00:03 2021 [2734471][1][FATAL] Query Failed: SELECT fp_events.id AS id FROM fp_events WHERE DATE_FORMAT(DATE(fp_events.date_start), ‘%Y-%m-%d %H:%i’) = DATE_FORMAT(DATE_ADD(Curdate(), INTERVAL + 7 day), ‘%Y-%m-%d %H:%i’) AND fp_events.fp_event_locations_fp_events_1fp_event_locations_ida = ‘6c3a5985-496a-2a93-bfd4-61693fd01984’ AND fp_events.deleted = 0 : MySQL error 1054: Unknown column ‘fp_events.fp_event_locations_fp_events_1fp_event_locations_ida’ in ‘where clause’

I had a quick look at the mysql dataabase and the fp_events doesn’t appear to have a column refering to location at all, let alone one called fp_event_locations_fp_events_1fp_event_locations_ida

I have already done a repair and rebuild, but that didn’t fix. Could this be some kind of issue related to the relationship between the events and the locations not being setup right?

Any suggestions would be welcome as I am out of my depth now.

Thanks

Andy

2

Just a nudge to see if anybody has any thoughts on this, as I am still stuck with it.

Thanks

3

SuiteCRM Log display the Query failed because fp_event_locations_fp_events_1fp_event_locations_ida field is available in the fp_events_fp_event_locations_1_c table but your query trying to get from fp_events table.

4

Thanks for the feedback jessica1. I think I have a relationship either missing or setup wrong in the studio which is resulting in the location being looked for in the wrong table. Hopefully this will help me narrow down the issue though.

Thanks

5

You are most welcome @Andyg, Let me know if you need any other help.