vt7 report breaks if we have multiple reference field to same module

Scenario:

1. add a new contact reference (Alternate Contact) field in Potentials
2. create report with primary module: Potentials, Secondary module: Contacts, select columns Opportunity name, Contact, Alternate Contact, Contact Last Name
3. save and generate report

Outcome: reports shows "No Data found", but record count is 1.

@nilay.automatesmb Please enable die on error in PearDatabase.php and check the database error

I have a similar issue but in vt650. This has been a know problem for years, but I thought it had been fixed.

We have added a new field to the Organisation module (UIType 10) which is related to an Organisation. The field is called "Distributor".

When I run very simple report to get a list of Organisations, with a Distributor and show the Distributor field in the results it is incorrect:

The query which is generated by vtiger is incorrect:

select DISTINCT 
vtiger_account.accountname AS 'Accounts_Account_Name', 
vtiger_account.phone AS 'Accounts_Phone', 
vtiger_account.email1 AS 'Accounts_Email', 
(CASE WHEN vtiger_account.distributor NOT LIKE '' 
    THEN (CASE WHEN trim(vtiger_accountAccounts.accountname) NOT LIKE '' 
        THEN trim(vtiger_accountAccounts.accountname) 
            ELSE '' 
        END) 
        ELSE '' 
    END) AS 'Accounts_Distributor', 
vtiger_crmentity.crmid AS "Accounts_LBL_ACTION" 
from vtiger_account 
inner join vtiger_crmentity on vtiger_crmentity.crmid=vtiger_account.accountid 
left join vtiger_account as vtiger_accountAccounts on vtiger_accountAccounts.accountid = vtiger_account.parentid 
left join vtiger_groups on vtiger_groups.groupid = vtiger_crmentity.smownerid 
left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid 
WHERE vtiger_account.accountid > 0  AND vtiger_crmentity.deleted=0 and (
    (vtiger_account.distributor IS NOT NULL AND vtiger_account.distributor != '' AND vtiger_account.distributor != '0') ) LIMIT 0, 1000

It should be joining on the Distributor field (column name = 'distributor') and not the Member Of field (columnname = 'parentid').

Thanks @lord_alan this is exactly what I was talking about. We tried even with disabling the temporary table creation.

To resolve we had to tweak getReferenceFieldColumnList function and add code for each such field like:

if($moduleName == 'HelpDesk' && $referenceModule == 'Accounts' && $fieldInfo['fieldname'] == 'distributor_id') { $referenceTableName = 'vtiger_accountRelDistributor'; }

and and then add suitable query in getReportsQuery function for the relation defined above.

@nilay.automatesmb Thanks. In that function I can see where the problem is:

				} elseif ($moduleName == 'Contacts' && $referenceModule == 'Contacts') {
					$referenceTableName = 'vtiger_contactdetailsContacts';
				} elseif ($moduleName == 'Accounts' && $referenceModule == 'Accounts') {
					$referenceTableName = 'vtiger_accountAccounts';
				} elseif ($moduleName == 'Campaigns' && $referenceModule == 'Products') {

It is not checking for a fieldname. Just if Accounts && Accounts :-(

Multi-UIType10 support is enhanced in #809 (closed)

Status changed to closed