{"action":"created","issue":{"url":"https://api.github.com/repos/salesagility/SuiteCRM/issues/235","labels_url":"https://api.github.com/repos/salesagility/SuiteCRM/issues/235/labels{/name}","comments_url":"https://api.github.com/repos/salesagility/SuiteCRM/issues/235/comments","events_url":"https://api.github.com/repos/salesagility/SuiteCRM/issues/235/events","html_url":"https://github.com/salesagility/SuiteCRM/issues/235","id":74016085,"number":235,"title":"AOR - Custom fields query fails","user":{"login":"willrennie","id":5641419,"avatar_url":"https://avatars.githubusercontent.com/u/5641419?v=3","gravatar_id":"","url":"https://api.github.com/users/willrennie","html_url":"https://github.com/willrennie","followers_url":"https://api.github.com/users/willrennie/followers","following_url":"https://api.github.com/users/willrennie/following{/other_user}","gists_url":"https://api.github.com/users/willrennie/gists{/gist_id}","starred_url":"https://api.github.com/users/willrennie/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/willrennie/subscriptions","organizations_url":"https://api.github.com/users/willrennie/orgs","repos_url":"https://api.github.com/users/willrennie/repos","events_url":"https://api.github.com/users/willrennie/events{/privacy}","received_events_url":"https://api.github.com/users/willrennie/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/salesagility/SuiteCRM/labels/bug","name":"bug","color":"fc2929"}],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":2,"created_at":"2015-05-07T14:57:29Z","updated_at":"2015-12-03T11:00:01Z","closed_at":null,"body":"AOR report returns 0 results if a user adds a custom field as a Condition on the Report.\r\n\r\nExample:\r\n\r\nQuery Failed: SELECT `contacts`.id AS 'contacts_id', `contacts:accounts`.id AS 'contacts:accounts_id', `contacts:accounts`.name \r\nAS Name0', `contacts`.first_name AS 'First_Name1', `contacts`.last_name AS 'Last_Name2' FROM `contacts` LEFT JOIN accounts_contacts `contacts|contacts:accounts` \r\nON `contacts`.id=`contacts|contacts:accounts`.contact_id AND `contacts|contacts:accounts`.deleted=0\r\n\r\nLEFT JOIN accounts `contacts:accounts` ON `contacts:accounts`.id=`contacts|contacts:accounts`.account_id AND `contacts:accounts`.deleted=0\r\n\r\nThu May  7 10:45:42 2015 [18099][1][FATAL]  Query Failed: SELECT `contacts`.id AS 'contacts_id', `contacts:accounts`.id AS 'contacts:accounts_id', `contacts:accounts`.name \r\nAS 'Name0', `contacts`.first_name AS 'First_Name1', `contacts`.last_name AS 'Last_Name2' FROM `contacts` LEFT JOIN accounts_contacts `contacts|contacts:accounts` \r\nON `contacts`.id=`contacts|contacts:accounts`.contact_id AND `contacts|contacts:accounts`.deleted=0\r\n\r\nLEFT JOIN `accounts_cstm` `contacts:accounts_cstm` ON accounts.id = `contacts:accounts_cstm`.id_c  WHERE `contacts:accounts_cstm`.industryimported_c = 'Chemicals' AND contacts.deleted = 0 ) as n: \r\nMySQL error 1054: Unknown column 'accounts.id' in 'on clause'\r\n\r\nLEFT JOIN accounts `contacts:accounts` ON `contacts:accounts`.id=`contacts|contacts:accounts`.account_id AND `contacts:accounts`.deleted=0\r\n\r\nLEFT JOIN `accounts_cstm` `contacts:accounts_cstm` ON accounts.id = `contacts:accounts_cstm`.id_c  WHERE `contacts:accounts_cstm`.industryimported_c = 'Chemicals' AND contacts.deleted = 0  LIMIT 0,20:\r\nMySQL error 1054: Unknown column 'accounts.id' in 'on clause'\r\n\r\nLEFT JOIN `accounts_cstm` `contacts:accounts_cstm` ON accounts.id = `contacts:accounts_cstm`.id_c  WHERE `contacts:accounts_cstm`.industryimported_c = 'Chemicals' AND contacts.deleted = 0 : \r\nMySQL error 1054: Unknown column 'accounts.id' in 'on clause'"},"comment":{"url":"https://api.github.com/repos/salesagility/SuiteCRM/issues/comments/161598861","html_url":"https://github.com/salesagility/SuiteCRM/issues/235#issuecomment-161598861","issue_url":"https://api.github.com/repos/salesagility/SuiteCRM/issues/235","id":161598861,"user":{"login":"vladbar","id":13965636,"avatar_url":"https://avatars.githubusercontent.com/u/13965636?v=3","gravatar_id":"","url":"https://api.github.com/users/vladbar","html_url":"https://github.com/vladbar","followers_url":"https://api.github.com/users/vladbar/followers","following_url":"https://api.github.com/users/vladbar/following{/other_user}","gists_url":"https://api.github.com/users/vladbar/gists{/gist_id}","starred_url":"https://api.github.com/users/vladbar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vladbar/subscriptions","organizations_url":"https://api.github.com/users/vladbar/orgs","repos_url":"https://api.github.com/users/vladbar/repos","events_url":"https://api.github.com/users/vladbar/events{/privacy}","received_events_url":"https://api.github.com/users/vladbar/received_events","type":"User","site_admin":false},"created_at":"2015-12-03T11:00:01Z","updated_at":"2015-12-03T11:00:01Z","body":"I also added a custom field from the Accounts. It works fine: there was a custom field ABCD For MC in the Accounts, so I added it to the report and everything displays fine. Log has no errors."}}