Exporting Opportunities "All opportunities" when campaign is filtered

[Referro]

Issue

The issue described here: (#4958) is still present for exporting All Opportunities when filtering on a related field.

Expected Behavior

Filter on Campaign in Opportunities module.
Select all opportunities (not all on page, but complete list)
Export selected opportunities
A csv file with all opportunities will be downloaded.

Actual Behavior

Filter on Campaign in Opportunities module.
Select all opportunities (not all on page, but complete list)
Export selected opportunities
A database error occurs.
In suitecrm.log the error is that "campaign_name = '{filtered campaign}'" is included in the query to lookup all opportunities. However, the field "campaign_name" does not exist in the "opportunities" table.

Possible Fix

Filter on ID field when filtered on a related module instead of filtering on the name.

Steps to Reproduce

See expected behavior and actual behavior.

Context

This issue was previously fixed for Accounts, but it still occurs for Opportunities. It needs fixing because now the Opportunities cannot be exported all at once when filtering on a campaign or something (related module).

Your Environment

SuiteCRM version 7.10.11
CentOS 7 server

Thanks in advance.

[jack7anderson7]

Hi @Referro,
I have tried but I am unable to replicate this, do you have any more information I could use in another attempt to replicate?

[Referro]

Mon Jan 7 10:40:05 2019 [1467][1][FATAL] Mysqli_query failed.
Mon Jan 7 10:40:05 2019 [1467][1][FATAL] Fout bij exporteren Opportunities:
.SELECT
opportunities.,
accounts.name as account_name,
users.user_name as assigned_user_name ,opportunities_cstm.competentie_c,opportunities_cstm.funnel_c,opportunities_cstm.cross_upsell_c,opportunities_cstm.jjwg_maps_address_c,opportunities_cstm.jjwg_maps_geocode_status_c,opportunities_cstm.jjwg_maps_lat_c,opportunities_cstm.jjwg_maps_lng_c,opportunities_cstm.sjv_c FROM opportunities LEFT JOIN users
ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities
ON opportunities.id=accounts_opportunities.opportunity_id
LEFT JOIN accounts
ON accounts_opportunities.account_id=accounts.id LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c where (campaign_name like 'Contactformulier%') AND
(accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0)
AND (accounts.deleted is null OR accounts.deleted=0)
AND opportunities.deleted=0 ORDER BY opportunities.name Query Failed: SELECT
opportunities.,
accounts.name as account_name,
users.user_name as assigned_user_name ,opportunities_cstm.competentie_c,opportunities_cstm.funnel_c,opportunities_cstm.cross_upsell_c,opportunities_cstm.jjwg_maps_address_c,opportunities_cstm.jjwg_maps_geocode_status_c,opportunities_cstm.jjwg_maps_lat_c,opportunities_cstm.jjwg_maps_lng_c,opportunities_cstm.sjv_c FROM opportunities LEFT JOIN users
ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities
ON opportunities.id=accounts_opportunities.opportunity_id
LEFT JOIN accounts
ON accounts_opportunities.account_id=accounts.id LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c where (campaign_name like 'Contactformulier%') AND
(accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0)
AND (accounts.deleted is null OR accounts.deleted=0)
AND opportunities.deleted=0 ORDER BY opportunities.name: MySQL error 1054: Unknown column 'campaign_name' in 'where clause'
Mon Jan 7 10:40:05 2019 [1467][1][FATAL] Exception handling in include/MVC/Controller/SugarController.php:400
Mon Jan 7 10:40:05 2019 [1467][1][FATAL] Exception in Controller: Database fout. Kijk voor details in de error log van SuiteCRM
Mon Jan 7 10:40:05 2019 [1467][1][FATAL] backtrace:
#0 include/database/DBManager.php(353): sugar_die('Database fout. ...')
#1 include/database/DBManager.php(328): DBManager->registerError('Fout bij export...', 'Fout bij export...', true)
#2 include/database/MysqliManager.php(179): DBManager->checkError('Fout bij export...', true)
#3 include/export_utils.php(167): MysqliManager->query('SELECT\n ...', true, 'Fout bij export...')
#4 export.php(69): export('Opportunities')
#5 include/MVC/Controller/SugarController.php(1020): require_once('/home/customer0...')
#6 include/MVC/Controller/SugarController.php(468): SugarController->handleEntryPoint()
#7 include/MVC/Controller/SugarController.php(373): SugarController->process()
#8 include/MVC/SugarApplication.php(113): SugarController->execute()
#9 index.php(52): SugarApplication->execute()
#10 {main}

Hi Jack,
If it helps, I included the logs above. It is related to the query:
SELECT
opportunities.*,
accounts.name as account_name,
users.user_name as assigned_user_name ,opportunities_cstm.competentie_c,opportunities_cstm.funnel_c,opportunities_cstm.cross_upsell_c,opportunities_cstm.jjwg_maps_address_c,opportunities_cstm.jjwg_maps_geocode_status_c,opportunities_cstm.jjwg_maps_lat_c,opportunities_cstm.jjwg_maps_lng_c,opportunities_cstm.sjv_c FROM opportunities LEFT JOIN users
ON opportunities.assigned_user_id=users.id LEFT JOIN accounts_opportunities
ON opportunities.id=accounts_opportunities.opportunity_id
LEFT JOIN accounts
ON accounts_opportunities.account_id=accounts.id LEFT JOIN opportunities_cstm ON opportunities.id = opportunities_cstm.id_c where (campaign_name like 'Contactformulier%') AND
(accounts_opportunities.deleted is null OR accounts_opportunities.deleted=0)
AND (accounts.deleted is null OR accounts.deleted=0)
AND opportunities.deleted=0 ORDER BY opportunities.name
Where the part "campaign_name like 'Contactformulier%'" is wrong, as the field "campaign_name" does not exist in the table Opportunities.

I tested it in two SuiteCRM 7.10.11 instances and both appear with the same error and the same logs. You need to filter on a Campaign and export the complete list (not all records independently or complete page), that's crucial to replicate.

If it helps, I made a small video of what happens, but because of law and regulations I will only share this with you through email and not in public, hope you understand. So if you want to see the video, could you please share your email address with me so I can send the video to you? I've uploaded it to a disclosed website, so it will only be a link and not a large attachment.

Many thanks!

[jack7anderson7]

@Referro, if you could send the video to the email provided that would be great,

security@suitecrm.com

[Referro]

@jack7anderson7 Hi Jack, thanks! I've send you an email. Hope that helps! Let me know. Thanks.

[jack7anderson7]

@Referro I am able to replicate it so you're now able to delete what you wanted to and thank you for letting us know about it we'll get on to it as soon as we can. A possible work around would be to export them page by page.

[Referro]

@jack7anderson7 Thank you very much Jack! Hope you manage to find a solution soon :). Good luck resolving it, and I'd like to know it when you manage to find a fix and it is available in an upgrade. Thanks!!