comando sql

[gmarcog]

Hola a todos:

Estoy intentando crear un informe con un comando sql, pero no me admite la clausula WHERE. Si la sentencia mysql es, por ejemplo, SELECT * FROM `vtiger_accountscf` WHERE `nombre` = 'guillermo' no me devuelve ningun valor, sin embargo si es SELECT * FROM `vtiger_accountscf` me devuelve valores (todos los de la tabla). Esas sentencias tienen una sintaxis especial? he probado con y sin comillas, pero no me devuelve nada

Gracias y saludos

[joebordes]

Hi,

This functionality is still a WIP.

For the reporting extension to work correctly with the SQL command there are some rules that must be respected.

• each column must have an alias that is the module name followed by the field label with all spaces substituted by underscores.
• the last column must be the crmid of the record of the row with an alias of LBL_ACTION
• you must select the main module of the SQL command when creating the report in order to support adhoc conditions. THIS IS IMPORTANT
• you cannot use conditions on groups (having)
  

Here is an example:

Code:

select assetname as 'Assets_Asset_Name', serialnumber as 'Assets_Serial_Number', vtiger_crmentity.crmid AS "LBL_ACTION"
from vtiger_assets
inner join vtiger_crmentity on crmid=assetsid where assetname like 's%'

As you can see there is a "where" condition and it will work if you select Assets as the main module of the direct SQL.

I have planned and already started a blog series on the reporting functionality which I will release soon.

Give it a try and let me know how it goes.

[gmarcog]

Hi, Joe

It works. Thank you. This idea of a blog series I think is fantastic. I hope read it soon

Regards