[Vtigercrm-developers] Issue while generating Report

Mon Feb 18 03:16:33 PST 2013

Hi all,

I installed a fresh vTiger  just for doing some tests and I noticed that
Reports are failing:

I related Projects with Invoices. I did a field uitype = 10 in Invoice
Module and get_dependents_lists in Projects (from Invoices). So 1 Project
has many Invoices. And 1 Invoice has 1 Project.

I tried to generate a Report listing all my invoices (which one field is
“Project Name”) and here is the result: “Report generation failed!
Unknown column 'vtiger_invoicecf.cf_640' in 'on clause'”

This is the SQL Query:

select DISTINCT

 vtiger_invoice.invoicedate AS 'Invoice_Invoice_Date', 

vtiger_invoice.duedate AS 'Invoice_Due_Date', 

concat(vtiger_invoice.currency_id,'::',vtiger_invoice.total) as
'Invoice_Total',

 (CASE WHEN vtiger_invoicecf.cf_640 NOT LIKE '' THEN (CASE WHEN
vtiger_project.projectname NOT LIKE '' THEN vtiger_project.projectname ELSE
'' END) ELSE '' END) AS Invoice_Proyecto, 

vtiger_crmentity.crmid AS "LBL_ACTION" 

from vtiger_project 

inner join vtiger_projectcf as vtiger_projectcf on
vtiger_projectcf.projectid=vtiger_project.projectid 

inner join vtiger_crmentity on
vtiger_crmentity.crmid=vtiger_project.projectid 

left join vtiger_groups as vtiger_groupsProject on
vtiger_groupsProject.groupid = vtiger_crmentity.smownerid 

left join vtiger_users as vtiger_usersProject on vtiger_usersProject.id =
vtiger_crmentity.smownerid 

left join vtiger_users as vtiger_lastModifiedByProject on
vtiger_lastModifiedByProject.id = vtiger_crmentity.modifiedby 

left join vtiger_groups on vtiger_groups.groupid =
vtiger_crmentity.smownerid 

left join vtiger_users on vtiger_users.id = vtiger_crmentity.smownerid 

left join vtiger_crmentity as vtiger_crmentityRelProject624 on
vtiger_crmentityRelProject624.crmid = vtiger_project.linktoaccountscontacts
and vtiger_crmentityRelProject624.deleted=0

 left join vtiger_account as vtiger_accountRelProject624 on
vtiger_accountRelProject624.accountid = vtiger_crmentityRelProject624.crmid

 left join vtiger_contactdetails as vtiger_contactdetailsRelProject624 on
vtiger_contactdetailsRelProject624.contactid =
vtiger_crmentityRelProject624.crmid 

left join (select vtiger_invoice.* from vtiger_invoice inner join
vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid and
vtiger_crmentity.deleted=0) as vtiger_invoice on
vtiger_invoicecf.cf_640=vtiger_project.projectid 

left join vtiger_crmentity as vtiger_crmentityInvoice on
vtiger_crmentityInvoice.crmid=vtiger_invoice.invoiceid and
vtiger_crmentityInvoice.deleted=0 

left join vtiger_invoicecf on vtiger_invoice.invoiceid =
vtiger_invoicecf.invoiceid 

left join vtiger_currency_info as vtiger_currency_infoInvoice on
vtiger_currency_infoInvoice.id = vtiger_invoice.currency_id 

left join vtiger_salesorder as vtiger_salesorderInvoice on
vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid

 left join vtiger_invoicebillads on
vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid

 left join vtiger_invoiceshipads on
vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid

 left join vtiger_inventoryproductrel as vtiger_inventoryproductrelInvoice
on vtiger_invoice.invoiceid = vtiger_inventoryproductrelInvoice.id 

left join vtiger_products as vtiger_productsInvoice on
vtiger_productsInvoice.productid =
vtiger_inventoryproductrelInvoice.productid 

left join vtiger_service as vtiger_serviceInvoice on
vtiger_serviceInvoice.serviceid =
vtiger_inventoryproductrelInvoice.productid 

left join vtiger_groups as vtiger_groupsInvoice on
vtiger_groupsInvoice.groupid = vtiger_crmentityInvoice.smownerid 

left join vtiger_users as vtiger_usersInvoice on vtiger_usersInvoice.id =
vtiger_crmentityInvoice.smownerid 

left join vtiger_contactdetails as vtiger_contactdetailsInvoice on
vtiger_invoice.contactid = vtiger_contactdetailsInvoice.contactid

 left join vtiger_account as vtiger_accountInvoice on
vtiger_accountInvoice.accountid = vtiger_invoice.accountid

 left join vtiger_users as vtiger_lastModifiedByInvoice on
vtiger_lastModifiedByInvoice.id = vtiger_crmentityInvoice.modifiedby 

WHERE vtiger_project.projectid > 0 AND vtiger_crmentity.deleted=0 

As you can see in the Query, it does:

 left join (select vtiger_invoice.* from vtiger_invoice inner join
vtiger_crmentity on vtiger_crmentity.crmid=vtiger_invoice.invoiceid and
vtiger_crmentity.deleted=0) as vtiger_invoice on
vtiger_invoicecf.cf_640=vtiger_project.projectid 

before:

left join vtiger_invoicecf on vtiger_invoice.invoiceid =
vtiger_invoicecf.invoiceid 

So, I think here is the problem.

Did anyone experimented some of this? Can anyone bring me some light around
this?

Thank you community.

Refineriaweb
 <https://www.facebook.com/refineriaweb1>
http://www.refineriaweb.com/images/fb.jpg <http://twitter.com/refineriaweb>
http://www.refineriaweb.com/images/tw.jpg

Adrián Granado 
Programador 
 <mailto:adrian at refineriaweb.com> adrian at refineriaweb.com 
Refineriaweb
C/ Ramiro de Maeztu 6 - Local 1
07013 Palma de Mallorca
Tel. 902 003 913
Fax 971 780 391 
 <http://www.refineriaweb.com/> www.refineriaweb.com 

RefineriawebDISEÑO WEB 
RefineriawebREGISTRO DE DOMINIOS
RefineriawebALOJAMIENTOS WEB
RefineriawebALTA EN BUSCADORES
RefineriawebMANTENIMIENTOS
RefineriawebIMAGEN CORPORATIVA
RefineriawebSOFTWARE A MEDIDA
RefineriawebFORMACIÓN

Este mensaje se dirige exclusivamente a su destinatario y puede contener
información privilegiada o confidencial. Si no es vd. el destinatario
indicado, queda notificado de que la utilización, divulgación y/o copia sin
autorización está prohibida en virtud de la legislación vigente. Si ha
recibido este mensaje por error, le rogamos que nos lo comunique
inmediatamente por esta misma vía y proceda a su destrucción. 

------------------------- 

This message is intended exclusively for its addressee and may contain
information that is CONFIDENTIAL and protected by professional privilege. If
you are not the intended recipient you are hereby notified that any
dissemination, copy or disclosure of this communication is strictly
prohibited by law. If this message has been received in error, please
immediately notify us via e-mail and delete it. 

http://www.refineriaweb.com/mailing/images/ecology.jpgNo imprimir si no es
necesario

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 2095 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0006.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 1145 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0007.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 1243 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0008.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 654 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0009.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 893 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0010.jpe 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/jpeg
Size: 2236 bytes
Desc: not available
Url : http://lists.vtigercrm.com/pipermail/vtigercrm-developers/attachments/20130218/0ac63e8a/attachment-0011.jpe 