Custom client fields in Reports

[soteha]

Hi all,

I have a problem displaying 2 custom client fields in Invoices report.

I have made a copy of the original report so that i could modify it without problems, adding 2 client custom fields.

The problem is that when launching the query inside mysql everything is returned correctly, inside the script it is not.

i am attaching the code i used maybe someone could please help me figure it out:

 

$filterfields = array("invoicenum"=>"Fattura Num.","id"=>"ID Fattura","userid"=>"ID Utente","companyname"=>"Azienda","clientname"=>"Nome e Cognome","date"=>"Data Fattura","duedate"=>"Data Scadenza","datepaid"=>"Pagata il","subtotal"=>"Totale senza iva","tax"=>"Iva","total"=>"Totale da pagare","taxrate"=>"Aliquota","status"=>"Stato","paymentmethod"=>"Metodo di Pagamento","notes"=>"Note",[b][color="#FF0000"]"customfields1"=>"Partita IVA","customfields2"=>"Codice Fiscale"[/color][/b]);

 

and now the filter:

	if ($val == 'customfields1') {
               $val = "(SELECT tblcustomfieldsvalues.value FROM tblcustomfieldsvalues WHERE tblcustomfieldsvalues.relid in (SELECT tblinvoices.userid FROM tblinvoices JOIN tblclients on tblclients.id=userid))";
           }

	if ($val == 'customfields2') {
               $val = "(SELECT tblcustomfieldsvalues.value FROM tblcustomfieldsvalues WHERE tblcustomfieldsvalues.relid in (SELECT tblinvoices.userid FROM tblinvoices JOIN tblclients on tblclients.id=userid))";
           }

 

 

and now the data:

 

 

	if ($fieldname=="customfields1") $fieldname = "(SELECT tblcustomfieldsvalues.value FROM tblcustomfieldsvalues WHERE tblcustomfieldsvalues.relid in (SELECT tblinvoices.userid FROM tblinvoices JOIN tblclients on tblclients.id=userid))";	 
           if ($fieldname=="customfields2") $fieldname = "(SELECT tblcustomfieldsvalues.value FROM tblcustomfieldsvalues WHERE tblcustomfieldsvalues.relid in (SELECT tblinvoices.userid FROM tblinvoices JOIN tblclients on tblclients.id=userid))";	

 

 

 

 

Runing that query in mysql is fine, selecting those fields in report produces nothing and no error at all. Just no data.

Can anyone please help?

Thank you in advance!

[mlungu]

Hi

 

I didn't see this query - it is similar to my question posted here so I will be watching this thread with interest

 

http://forum.whmcs.com/showthread.php?109115-Using-reports