Report Generator fails with UI10 to Products in an Opportunity
I recently migrated a customer from an old version of vtiger 5.2.1 to 6.4.0.
They have a custom field in the Potential with a field label of "Product" and a field name of "pdoid" It is a UIType 10 to the Products module.
If I include this field in an Opportunity report the report returns no entries because the Query Generator has created a bad Query:
Mon Jun 20 14:48:02 2016,370 [14714] INFO VT - PearDatabase ->ADODB error Query Failed:select DISTINCT vtiger_potential.potentialname AS 'Potentials_Potential_Name', vtiger_potential.amount AS 'Potentials_Amount', vtiger_potential.potential_no AS 'Potentials_Potential_No', (CASE WHEN vtiger_potential.related_to NOT LIKE '' THEN (CASE WHEN trim(vtiger_accountPotentials.accountname) NOT LIKE '' THEN trim(vtiger_accountPotentials.accountname) ELSE '' END) ELSE '' END) AS Potentials_Related_To, (CASE WHEN vtiger_potential.contact_id NOT LIKE '' THEN (CASE WHEN trim(CONCAT(vtiger_contactdetailsPotentials.firstname,' ',vtiger_contactdetailsPotentials.lastname)) NOT LIKE '' THE
N trim(CONCAT(vtiger_contactdetailsPotentials.firstname,' ',vtiger_contactdetailsPotentials.lastname)) ELSE '' END) ELSE '' END) AS Potentials_Contact_Name, (CASE WHEN vtiger_potential.pdoid NOT LIKE '' THEN (CASE WHEN trim(vtiger_productsRelPotentials641.productname) NOT LIKE '' THEN trim(vtiger_productsRelPotentials641.productname) ELSE
'' END) ELSE '' END) AS Potentials_Product, vtiger_crmentity.crmid AS "Potentials_LBL_ACTION" from vtiger_potential inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_potential.potentialid left join vtiger_account as vtiger_accountPotentials on vtiger_potential.related_to = vtiger_accountPotentials.accountid left join vtiger_contactdetails as vtiger_contactdetailsPotentials on vtiger_potential.contact_id = vtiger_contactdetailsPotentials.contactid left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid WHERE vtiger_potential.potentialid > 0 AND vtiger_crmentity.deleted=0 LIMIT 0, 1001::->[1054]Unknown column 'vtiger_productsRelPotentials641.productname' in 'field list'
Why is this happening and how do we fix it?