[Vtigercrm-developers] Custom filter on Contacts

[Karyn Cassio]

Hi,

 

I've created quite a few custom, cross module filters to look for Contacts
with specific Accounts related criteria.

I'm having a hard time getting the filter to work on the Account assigned to
field.

I have created a custom field in the field table with the following
properties:

 
vtiger_acct_crmentity:smownerid:assigned_acct_user_id:Contacts_Account_Assig
ned_To:V e TestRep7

 

I have been able to edit the initial WHERE statement so that the search
gives the result rows I would expect, but I cannot get the
assigned_acct_user_id column to return the Account's owner (related
smownerid name.)

 

I can manually run the query and get the results I want, but have not been
able to get vTiger to return the results to the interface.

Any thoughts?

 

Here's the query the logs result:

 

select
vtiger_contactdetails.lastname,vtiger_contactdetails.firstname,vtiger_contac
tdetails.firstname,vtiger_contactdetails.email,vtiger_account.accountname,vt
iger_account.account_type,case when (vtiger_users.user_name not like '')
then vtiger_users.user_name else vtiger_groups.groupname 

end as user_name,vtiger_accountscf.cf_460, vtiger_acct_users.user_name as
account_user_name,
vtiger_acct_crmentity.crmid,vtiger_contactdetails.reportsto
,vtiger_crmentity.crmid FROM vtiger_contactdetails

                                    INNER JOIN vtiger_crmentity

                                                ON vtiger_crmentity.crmid =
vtiger_contactdetails.contactid

                                    INNER JOIN vtiger_contactaddress

                                                ON
vtiger_contactdetails.contactid = vtiger_contactaddress.contactaddressid

                                    INNER JOIN vtiger_contactsubdetails

                                                ON
vtiger_contactaddress.contactaddressid =
vtiger_contactsubdetails.contactsubscriptionid

                                    INNER JOIN vtiger_contactscf

                                                ON
vtiger_contactdetails.contactid = vtiger_contactscf.contactid

                                    LEFT JOIN vtiger_account

                                                ON vtiger_account.accountid
= vtiger_contactdetails.accountid

                                    LEFT JOIN vtiger_accountscf

                                                ON vtiger_account.accountid
= vtiger_accountscf.accountid

                                    LEFT JOIN vtiger_accountbillads

                                                ON vtiger_account.accountid
= vtiger_accountbillads.accountaddressid

 

                                    LEFT JOIN vtiger_contactdetails
vtiger_contactdetails2

                                                ON
vtiger_contactdetails.reportsto = vtiger_contactdetails2.contactid

                                    LEFT JOIN vtiger_contactgrouprelation

                                                ON
vtiger_contactscf.contactid = vtiger_contactgrouprelation.contactid

                                    LEFT JOIN vtiger_groups

                                                ON vtiger_groups.groupname =
vtiger_contactgrouprelation.groupname

                                    LEFT JOIN vtiger_users

                                                ON vtiger_users.id =
vtiger_crmentity.smownerid                                    

                                    LEFT JOIN vtiger_crmentity as
vtiger_acct_crmentity

                                                ON
vtiger_acct_crmentity.crmid = vtiger_account.accountid

                                    LEFT JOIN vtiger_users as
vtiger_acct_users

                                                ON vtiger_acct_users.id =
vtiger_acct_crmentity.smownerid

                                    LEFT JOIN vtiger_customerdetails

                                                ON
vtiger_customerdetails.customerid = vtiger_contactdetails.contactid WHERE
vtiger_crmentity.deleted = 0  and vtiger_account.account_type = 'Reseller'
and ( vtiger_acct_users.user_name = 'TestRep7') 

ORDER BY vtiger_contactdetails.firstname ASC LIMIT 0,20

 

Thank you, any help, suggestions, etc. would be so greatly appreciated.

 

Karyn 
Web Developer



 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20080616/cef54301/attachment-0003.html