Workflow not looking in *_cstm table?

SuiteCRM Forum - English Language Installation & Upgrade Help
#1

Hi,

I’ve created a workflow that uses a case field made in studio:
The field “kind” is renamed “kind_c” and placed in the “cases_cstm” table

I see my log being filled with error messages like:
Tue Jun 10 13:59:08 2014 [5738][1][FATAL] Query Failed: SELECT cases.id AS id FROM cases WHERE cases.date_modified <= DATE_ADD(NOW(), INTERVAL - 5 day) AND cases.date_modified > DATE_ADD(NOW(), INTERVAL - 7 day) AND cases_cstm.kind_c = ‘Support’ AND cases.status = ‘Pending Input’ AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id=‘a2aa181d-2c1b-30b8-5c6b-539768f4e70e’ AND aow_processed.parent_id=cases.id AND aow_processed.status = ‘Complete’ AND aow_processed.deleted = 0) AND cases.deleted = 0 :
MySQL error 1054: Unknown column ‘cases_cstm.kind_c’ in ‘where clause’

Why isn’t workflow adding the cases_cstm table to the query?

Thanks,

#2

Hi Gilles,

This is a known issue which will be resolved in the SuiteCRM 7.1.2 hotfix release.

Thanks,

Will.

#3

Thanks Will! What’s your current guess on when will 7.1.2. be available?

Thanks,

#4

Hi Gilles,

SuiteCRM 7.1.2 is imminent/close to release but I cannot confirm a date.

Thanks,

Will.

#5

It seems that Workflow is completely broken when using custom fields which is ironic since I upgraded for the additional Workflow capabilities.:

SELECT accounts.id AS id FROM accounts WHERE accounts_cstm.payment_date_c < DATE_ADD(accounts.today, INTERVAL - 13 day) AND accounts_cstm.payment_date_c >= DATE_ADD(accounts.today, INTERVAL - 14 day) AND accounts_cstm.join_date_c > DATE_ADD(NOW(), INTERVAL - 6 month) AND NOT EXISTS (SELECT * FROM aow_processed WHERE aow_processed.aow_workflow_id=‘46e8973a-bfd6-8f98-fbb8-530cdeb39372’ AND aow_processed.parent_id=accounts.id AND aow_processed.status = ‘Complete’ AND aow_processed.deleted = 0) AND accounts.deleted = 0

MySQL error 1054: Unknown column ‘accounts_cstm.payment_date_c’ in ‘where clause’ fields.

This SQL can never work can it? It’s missing a JOIN and accounts.today should be CURDATE() or similar.

Will, when will 7.1.2 hotfix be released? This is pretty serious. Has anyone found a workaround?

Cheers
Tony

#6

Ooops. today is a local hack so that we can refer to today (rather than only now) in a comparison. Code available.

Cheers
Tony

#7

Here’s a workaround: in modules/AOW_WorkFlow/AOW_WorkFlow.php

Change


                    if(  (isset($data['source']) && $data['source'] == 'custom_fields')) {
                        $field = $table_alias.'_cstm.'.$condition->field;
                    } else {
                        $field = $table_alias.'.'.$condition->field;
                    }

to


                   if(  (isset($data['source']) && $data['source'] == 'custom_fields')) {
			$query = $this->build_flow_query_join($table_alias.'_cstm', $condition_module, 'custom', $query);
                        $field = $table_alias.'_cstm.'.$condition->field;
                    } else {
                        $field = $table_alias.'.'.$condition->field;
                    }

Works for me but may not work in all cases.

Cheers
Tony