Workflow Condition Custom Relate Field Error

SuiteCRM Version 7.11.3
Sugar Version 6.5.25 (Build 344)

Issue: Workflow is using incorrect table with custom field, always evaluates false
Recreate: Add a custom RELATE Field to Accounts. Create a workflow condition that identifies the RELATE field to the related table value desired.

Observations:

The SQL code snippet for the where clause is documented as example:

accounts.custom_field_c = blahblah, where this should read accounts_cstm.custom_field_c = blahblah

Hmmm interesting. Are you using a debugger? Can you get to the point in the code that generates that SQL?

I am not using a debugger - just vs code and setting up the environment this week. As for the code - I have “traced” it to this block of code AOW_Workflow.php

if ($data[‘type’] == ‘relate’ && isset($data[‘id_name’])) {
$condition->field = $data[‘id_name’];
}
if ((isset($data[‘source’]) && $data[‘source’] == ‘custom_fields’)) {
$field = $table_alias.’_cstm.’.$condition->field;
$query = $this->build_flow_custom_query_join(
$table_alias,
$table_alias.’_cstm’,
$condition_module,
$query
);
} else {
$field = $table_alias.’.’.$condition->field;
}

and figured out that the underlying data has an issue -

/var/www/morfius-web/custom/modules/Accounts/Ext/Vardefs/vardefs.ext.php

// created: 2019-04-26 14:13:32
$dictionary[“Account”][“fields”][“accounts_morf_workflowcampaign_1”] = array (
‘name’ => ‘accounts_morf_workflowcampaign_1’,
‘type’ => ‘link’,
‘relationship’ => ‘accounts_morf_workflowcampaign_1’,
‘source’ => ‘non-db’,
‘module’ => ‘MORF_WorkflowCampaign’,
‘bean_name’ => ‘MORF_WorkflowCampaign’,
‘side’ => ‘right’,
‘vname’ => ‘LBL_ACCOUNTS_MORF_WORKFLOWCAMPAIGN_1_FROM_MORF_WORKFLOWCAMPAIGN_TITLE’,
);

This part that identified ‘source’ => ‘non-db’ I think should read ‘source’ => ‘custom_fields’. I change that file and attempted to repair/rebuild but as expected - it is restored to the original value. This is where my understanding of the system ends. I did create this relationship with studio - so I am a bit confused at what non-db means and why it doesn’t say custom_fields. The only other oddity is that I did have developers a few years ago modify the 6.5 CE code - and of course - it seems that they modified the code and data tables in a non-upgrade safe way (no modules, just code hacks, main accounts table has custom fields, etc.).

Maybe shed some light on the source part of this - that would be awesome!

Here is the debug from the logs:

Wed May 1 12:24:01 2019 [29676][1][FATAL] Query Failed: SELECT accounts.id AS id FROM accounts WHERE accounts.name = ‘SMB’ AND accounts.morf_workflowcampaign_id_c = ‘81fc9068-b476-49f8-8952-5cb9296a04af’ AND accounts.deleted = 0 : MySQL error 1054: Unknown column ‘accounts.morf_workflowcampaign_id_c’ in ‘where clause’

‘accounts.morf_workflowcampaign_id_c’ is in the accounts_cstm table, not acocunts. Any reason the Workflow Condition would indicate the incorrect table?

I have had devs hack the accounts table and put custom fiields there - so that may have something to do with it. My new custom fields are through Studio and in the cstm table.

This file

custom/modules/Accounts/Ext/Vardefs/vardefs.ext.php

is created during QR&R. You shouldn’t change it directly, but rather, you should change the file used to produce it. That should be in custom/Extension/modules/Accounts somewhere.

Then run the QR&R and it should stick.

Yeah that was a bit of a scare - I FOOLISHLY was working on the production system - and changed custom/Extension/modules/Accounts/Ext/Vardefs/sugarfield_workflow_campaign_id_c.php

from

 // created: 2019-04-18 18:57:17
$dictionary['Account']['fields']['workflow_campaign_id_c']['inline_edit']='1';
$dictionary['Account']['fields']['workflow_campaign_id_c']['labelValue']='Workflow Campaign';
$dictionary['Account']['fields']['workflow_campaign_id_c']['massupdate']='1';

to

// created: 2019-04-18 18:57:17
$dictionary['Account']['fields']['workflow_campaign_id_c']['inline_edit']='1';
$dictionary['Account']['fields']['workflow_campaign_id_c']['labelValue']='Workflow Campaign';
$dictionary['Account']['fields']['workflow_campaign_id_c']['massupdate']='1';
$dictionary['Account']['fields']['workflow_campaign_id_c']['source']='custom_fields';

Ran a Quick Rebuild and Repair and boom - all of my accounts disappeared in the GUI. They were in the database still, and when I reverted the changes - they returned.

Sure made me nervous for a moment - and I stopped there

Somehow that was formatted incorrect -

I added this

$dictionary['Account']['fields']['workflow_campaign_id_c']['source']='custom_fields';

if that isn’t formatted correct it was dictionary Account fields workflow_campaign_id_c source = custom_fields

Does anyone think this was the correct change to file /custom/Extension/modules/Accounts/Ext/Vardefs/sugarfield_workflow_campaign_id_c.php?

Still getting this:

Wed May 8 22:57:01 2019 [19881][-none-][FATAL] Query Failed: SELECT accounts.id AS id FROM accounts WHERE accounts.name = ‘SMB’ AND accounts.workflow_campaign_c = ‘81fc9068-b476-49f8-8952-5cb9296a04af’ AND accounts.deleted = 0 : MySQL error 1054: Unknown column ‘accounts.workflow_campaign_c’ in ‘where clause’

I know for sure that the ‘source’ => ‘non-db’ should read ‘source’ => ‘custom_fields’ for the query to be constructed correct. The accounts_cstm table contains the field.

File custom/Extension/modules/Accounts/Ext/Vardefs/sugarfield_workflow_campaign_id_c.php

Each time I add $dictionary[‘Account’][‘fields’][‘workflow_campaign_id_c’][‘source’]=‘custom_fields’; and QRR - boom. All of my accounts do not list in the gui. I remove and they show up?

Anyone have any advice at all?

I don’t understand the specifics of your problem but I can give you some generic advice that might help you:

  • try creating fields with Studio, try some variations, and watch what it does, see exactly how it creates files.

  • keep an eye on the fields_meta_data table in the database, it depends on the vardefs, but if it gets out of sync, weird things start to happen. This is quite uncommon, though, I’ve only seen it happen when people do wrong manual changes.