<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">This is for setting some special
relations for related lists and reporting queries against your
module.<br>
It isn't used to much but is very useful in some cases.<br>
Look at our Timecontrol module, I use it there so you can add
custom fields on the related lists<br>
<br>
<a class="moz-txt-link-freetext" href="https://github.com/tsolucio/Timecontrol">https://github.com/tsolucio/Timecontrol</a><br>
<br>
Joe<br>
TSolucio<br>
<br>
<br>
El 26/11/14 a las 15:44, PlaymaN Tepes escribió:<br>
</div>
<blockquote
cite="mid:CAF6osSmc_Z0LPojjcHoJUWZbeHWvDPqV_71n+tL=XUzPpwtwZQ@mail.gmail.com"
type="cite">
<div dir="ltr">Wow, 1 year later I find the same problem again....
but in vt6.</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">2013-02-18 12:43 GMT+01:00 Adrián
Granado - Refineria Web <span dir="ltr"><<a
moz-do-not-send="true"
href="mailto:adrian@refineriaweb.com" target="_blank">adrian@refineriaweb.com</a>></span>:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="blue" vlink="purple" lang="ES">
<div>
<p class="MsoNormal"><span style="color:#1f497d"
lang="EN-US">In addition my
get_dependents_lists is failing.<br>
<br>
In data\CRMEntity.php – line 1763 , I cant
understand next algorithm:<br>
<br>
</span><span style="font-family:"Courier
New";color:#1f497d" lang="EN-US">if
(!empty($other->related_tables)) {</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
foreach ($other->related_tables as
$tname => $relmap) {</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
$query .= ", $tname.*";</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US"> </span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
// Setup the default JOIN conditions
if not specified</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
if (empty($relmap[1]))</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
$relmap[1] =
$other->table_name;</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
if (empty($relmap[2]))</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
$relmap[2] = $relmap[0];</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
$more_relation .= " LEFT JOIN
$tname ON $tname.$relmap[0] =
$relmap[1].$relmap[2]";</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
}</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US">
}</span></p>
<p class="MsoNormal"><span
style="font-family:"Courier
New";color:#1f497d" lang="EN-US"> </span></p>
<p class="MsoNormal"><span style="color:#1f497d"
lang="EN-US">Because NO ONE of the
modules have variable $related_tables. Can anyone
give me some light?<br>
<br>
Thank you community.</span></p>
<div>
<p class="MsoNormal"><span style="color:#1f497d"> </span></p>
</div>
<p class="MsoNormal"><span style="color:#1f497d"> </span></p>
<div>
<div style="border:none;border-top:solid #b5c4df
1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">De:</span></b><span
style="font-size:10.0pt;font-family:"Tahoma","sans-serif"">
<a moz-do-not-send="true"
href="mailto:vtigercrm-developers-bounces@lists.vtigercrm.com"
target="_blank">vtigercrm-developers-bounces@lists.vtigercrm.com</a>
[mailto:<a moz-do-not-send="true"
href="mailto:vtigercrm-developers-bounces@lists.vtigercrm.com"
target="_blank">vtigercrm-developers-bounces@lists.vtigercrm.com</a>]
<b>En nombre de </b>Adrián
Granado - Refineria Web<br>
<b>Enviado el:</b> lunes, 18 de febrero de 2013
12:17<br>
<b>Para:</b> <a moz-do-not-send="true"
href="mailto:vtigercrm-developers@lists.vtigercrm.com"
target="_blank">vtigercrm-developers@lists.vtigercrm.com</a><br>
<b>Asunto:</b> [Vtigercrm-developers] Issue
while generating Report</span></p>
</div>
</div>
<div>
<div class="h5">
<p class="MsoNormal"> </p>
<p class="MsoNormal"><span lang="EN-US">Hi all,<br>
<br>
I installed a fresh vTiger just for doing some
tests and I noticed that
Reports are failing:<br>
<br>
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.<br>
<br>
I tried to generate a Report listing all my
invoices (which one field is
“Project Name”) and here is the result: “</span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">Report generation failed!</span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f"
lang="EN-US"><br>
</span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec">Unknown
column 'vtiger_invoicecf.cf_640' in
'on clause'</span><span
style="font-size:10.0pt;color:#323d4f;background:#ececec"
lang="EN-US">”</span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"></span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"> </span></p>
<p class="MsoNormal"><span lang="EN-US">This is the
SQL Query:</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">select DISTINCT</span></p>
<p class="MsoNormal"
style="margin-left:35.4pt;text-indent:35.4pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> vtiger_invoice.invoicedate AS
'Invoice_Invoice_Date', </span></p>
<p class="MsoNormal"
style="margin-left:35.4pt;text-indent:35.4pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">vtiger_invoice.duedate AS
'Invoice_Due_Date',
</span></p>
<p class="MsoNormal"
style="margin-left:35.4pt;text-indent:35.4pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">concat(vtiger_invoice.currency_id,'::',vtiger_invoice.total)
as
'Invoice_Total',</span></p>
<p class="MsoNormal"
style="margin-left:35.4pt;text-indent:35.4pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> (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, </span></p>
<p class="MsoNormal"
style="margin-left:35.4pt;text-indent:35.4pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">vtiger_crmentity.crmid AS
"LBL_ACTION" </span></p>
<p class="MsoNormal"
style="margin-left:35.4pt;text-indent:35.4pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> </span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">from vtiger_project </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">inner join vtiger_projectcf as
vtiger_projectcf on
vtiger_projectcf.projectid=vtiger_project.projectid
</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">inner join vtiger_crmentity on
vtiger_crmentity.crmid=vtiger_project.projectid
</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_groups as
vtiger_groupsProject on
vtiger_groupsProject.groupid =
vtiger_crmentity.smownerid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_users as
vtiger_usersProject on
vtiger_usersProject.id =
vtiger_crmentity.smownerid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_users as
vtiger_lastModifiedByProject on
vtiger_lastModifiedByProject.id =
vtiger_crmentity.modifiedby </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_groups on
vtiger_groups.groupid =
vtiger_crmentity.smownerid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_users on <a
moz-do-not-send="true"
href="http://vtiger_users.id" target="_blank">vtiger_users.id</a>
= vtiger_crmentity.smownerid
</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_crmentity as
vtiger_crmentityRelProject624
on vtiger_crmentityRelProject624.crmid =
vtiger_project.linktoaccountscontacts
and vtiger_crmentityRelProject624.deleted=0</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join vtiger_account as
vtiger_accountRelProject624 on
vtiger_accountRelProject624.accountid =
vtiger_crmentityRelProject624.crmid</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join vtiger_contactdetails as
vtiger_contactdetailsRelProject624 on
vtiger_contactdetailsRelProject624.contactid =
vtiger_crmentityRelProject624.crmid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">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
</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_crmentity as
vtiger_crmentityInvoice on
vtiger_crmentityInvoice.crmid=vtiger_invoice.invoiceid
and
vtiger_crmentityInvoice.deleted=0 </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_invoicecf on
vtiger_invoice.invoiceid =
vtiger_invoicecf.invoiceid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_currency_info as
vtiger_currency_infoInvoice
on vtiger_currency_infoInvoice.id =
vtiger_invoice.currency_id </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_salesorder as
vtiger_salesorderInvoice on
vtiger_salesorderInvoice.salesorderid=vtiger_invoice.salesorderid</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join vtiger_invoicebillads on
vtiger_invoice.invoiceid=vtiger_invoicebillads.invoicebilladdressid</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join vtiger_invoiceshipads on
vtiger_invoice.invoiceid=vtiger_invoiceshipads.invoiceshipaddressid</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join
vtiger_inventoryproductrel as
vtiger_inventoryproductrelInvoice on
vtiger_invoice.invoiceid =
vtiger_inventoryproductrelInvoice.id
</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_products as
vtiger_productsInvoice on
vtiger_productsInvoice.productid =
vtiger_inventoryproductrelInvoice.productid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_service as
vtiger_serviceInvoice on
vtiger_serviceInvoice.serviceid =
vtiger_inventoryproductrelInvoice.productid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_groups as
vtiger_groupsInvoice on
vtiger_groupsInvoice.groupid =
vtiger_crmentityInvoice.smownerid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_users as
vtiger_usersInvoice on
vtiger_usersInvoice.id =
vtiger_crmentityInvoice.smownerid </span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_contactdetails as
vtiger_contactdetailsInvoice on
vtiger_invoice.contactid =
vtiger_contactdetailsInvoice.contactid</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join vtiger_account as
vtiger_accountInvoice on
vtiger_accountInvoice.accountid =
vtiger_invoice.accountid</span></p>
<p class="MsoNormal" style="margin-left:70.8pt"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> left join vtiger_users as
vtiger_lastModifiedByInvoice on
vtiger_lastModifiedByInvoice.id =
vtiger_crmentityInvoice.modifiedby </span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">WHERE vtiger_project.projectid >
0 AND
vtiger_crmentity.deleted=0<span> </span></span><span></span></p>
<p class="MsoNormal"><span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> </span></span></p>
<p class="MsoNormal"><span lang="EN-US">As you can
see in the Query, it does:</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">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
</span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">before:</span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US">left join vtiger_invoicecf on
vtiger_invoice.invoiceid =
vtiger_invoicecf.invoiceid </span></p>
<p class="MsoNormal"><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">So, I think
here is the problem.<br>
<br>
Did anyone experimented some of this? Can anyone
bring me some light around
this?<br>
<br>
Thank you community.</span><span
style="font-size:10.0pt;font-family:"Lucida
Grande","serif";color:#323d4f;background:#ececec"
lang="EN-US"></span></p>
<p class="MsoNormal" style="margin-bottom:12.0pt"><span
lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
</div>
</div>
</div>
</div>
<br>
_______________________________________________<br>
<a moz-do-not-send="true" href="http://www.vtiger.com/"
target="_blank">http://www.vtiger.com/</a><br>
</blockquote>
</div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
<a class="moz-txt-link-freetext" href="http://www.vtiger.com/">http://www.vtiger.com/</a></pre>
</blockquote>
<br>
</body>
</html>