CoreBOSBB

Full Version: comando sql
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
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
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.
Hi, Joe

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

Regards