Query failed: SELECT project.*

[jdelgado]

My SuiteCRM is failing. When I export data from the Project module it throws me this error.

MySQL error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ’ LTRIM(RTRIM(CONCAT(IFNULL(relJoin4.first_name,’’),’ ‘,IFNULL(relJoin4.last_n…’ at line 3

SELECT project.*, users.user_name as assigned_user_name ,project_cstm.fech_cap_gestiondigital_c,project_cstm.jjwg_maps_lng_c,project_cstm.fecha_cap_elegajos_c,
project_cstm.tipopersonalizacion_c,project_cstm.cantidadcertf_c,project_cstm.user_id4_c,project_cstm.user_id3_c,project_cstm.account_id1_c,
project_cstm.estatus_proyecto_c,project_cstm.migroanuevaplataforma_c,project_cstm.fechaterminocontrato_c,project_cstm.tipo_pago_c,project_cstm.contact_id2_c,
project_cstm.fechavenccertificado_c,project_cstm.entidadcertificacion_c,project_cstm.portal_personaliza_c,project_cstm.fech_ini_sms_c,
project_cstm.categorizacion_c,project_cstm.fech_ult_adenda_c,project_cstm.venta_certificado_c,project_cstm.requierefacturacion_c,project_cstm.contact_id1_c,
project_cstm.tipodeintegracion_c,project_cstm.niciodemigracion_c,project_cstm.calificaciondepago_c,project_cstm.tipo_servicio_c,project_cstm.findemigracion_c,
project_cstm.contact_id_c,project_cstm.fech_fin_upgrade_gd_c,project_cstm.contact_id3_c,project_cstm.fech_int_gd_c,project_cstm.fech_inicio_upgrade_c,
project_cstm.jjwg_maps_address_c,project_cstm.user_id1_c,project_cstm.tip_integracion_3_c,project_cstm.user_id6_c,project_cstm.reunionkickoff_c,
project_cstm.user_id5_c,project_cstm.cumplestakeholder_c,project_cstm.tipo_proyecto_c,project_cstm.aniversariocliente_c,project_cstm.user_id2_c,
project_cstm.account_id_c,project_cstm.fech_capa_smspremium_c,project_cstm.catego_c,project_cstm.tip_integracion_1_c,project_cstm.fech_ini_upgradeelegajos_c,
project_cstm.fechafirmacontrato_c,project_cstm.fech_fin_upgrade_elegajos_c,project_cstm.tipodesector_c,project_cstm.fechainiserv_c,
project_cstm.jjwg_maps_geocode_status_c,project_cstm.observaciones_c,project_cstm.estadoimplemen_c,project_cstm.user_id_c,project_cstm.plataformamodulos_c,
project_cstm.jjwg_maps_lat_c,project_cstm.contrato_firmado_c,project_cstm.tip_integracion_2_c,project_cstm.estadocertificado_c,project_cstm.fech_fin_sms_c,
project_cstm.contact_id3_c, LTRIM(RTRIM(CONCAT(IFNULL(relJoin1.first_name,’’),’ ‘,IFNULL(relJoin1.last_name,’’)))) responsablefacturacion_c ,
project_cstm.user_id1_c, LTRIM(RTRIM(CONCAT(IFNULL(relJoin2.first_name,’’),’ ‘,IFNULL(relJoin2.last_name,’’)))) respons_upgrade_legajos_c ,
project_cstm.user_id4_c, LTRIM(RTRIM(CONCAT(IFNULL(relJoin3.first_name,’’),’ ‘,IFNULL(relJoin3.last_name,’’)))) responsablefacturacion_i_c , project_cstm.,
LTRIM(RTRIM(CONCAT(IFNULL(relJoin4.first_name,’’),’ ‘,IFNULL(relJoin4.last_name,’’)))) respons_upgrade_gd_c , project_cstm.contact_id1_c,
LTRIM(RTRIM(CONCAT(IFNULL(relJoin5.first_name,’’),’ ‘,IFNULL(relJoin5.last_name,’’)))) stakeholder_comercial_c , project_cstm.account_id1_c,
relJoin6.name pertenecegrupo_c , project_cstm.contact_id2_c, LTRIM(RTRIM(CONCAT(IFNULL(relJoin7.first_name,’’),’ ‘,IFNULL(relJoin7.last_name,’’)))) stakeholder_c ,
project_cstm.user_id3_c, LTRIM(RTRIM(CONCAT(IFNULL(relJoin8.first_name,’’),’ ‘,IFNULL(relJoin8.last_name,’’)))) respons_upgrade_c , project_cstm.contact_id_c,
LTRIM(RTRIM(CONCAT(IFNULL(relJoin9.first_name,’’),’ ‘,IFNULL(relJoin9.last_name,’’)))) contactofacturacion_c , project_cstm.user_id5_c,
LTRIM(RTRIM(CONCAT(IFNULL(relJoin10.first_name,’’),’ ‘,IFNULL(relJoin10.last_name,’’)))) responsabledeimplementacion__c , project_cstm.user_id_c,
LTRIM(RTRIM(CONCAT(IFNULL(relJoin11.first_name,’’),’ ‘,IFNULL(relJoin11.last_name,’’)))) respons_upgrade_sms_c , project_cstm.user_id6_c,
LTRIM(RTRIM(CONCAT(IFNULL(relJoin12.first_name,’’),’ ‘,IFNULL(relJoin12.last_name,’’)))) jefe_proyecto_c , project_cstm.account_id_c,
relJoin13.name tipo_de_cuenta_c FROM project LEFT JOIN project_cstm ON project.id = project_cstm.id_c LEFT JOIN contacts relJoin1 ON
project_cstm.contact_id3_c = relJoin1.id AND relJoin1.deleted=0 LEFT JOIN users relJoin2 ON project_cstm.user_id1_c = relJoin2.id AND relJoin2.deleted=0
LEFT JOIN users relJoin3 ON project_cstm.user_id4_c = relJoin3.id AND relJoin3.deleted=0 LEFT JOIN users relJoin4 ON project_cstm. = relJoin4.id AND
relJoin4.deleted=0 LEFT JOIN contacts relJoin5 ON project_cstm.contact_id1_c = relJoin5.id AND relJoin5.deleted=0 LEFT JOIN accounts relJoin6 ON
project_cstm.account_id1_c = relJoin6.id AND relJoin6.deleted=0 LEFT JOIN contacts relJoin7 ON project_cstm.contact_id2_c = relJoin7.id AND relJoin7.deleted=0
LEFT JOIN users relJoin8 ON project_cstm.user_id3_c = relJoin8.id AND relJoin8.deleted=0 LEFT JOIN contacts relJoin9 ON project_cstm.contact_id_c = relJoin9.id
AND relJoin9.deleted=0 LEFT JOIN users relJoin10 ON project_cstm.user_id5_c = relJoin10.id AND relJoin10.deleted=0 LEFT JOIN users relJoin11 ON
project_cstm.user_id_c = relJoin11.id AND relJoin11.deleted=0 LEFT JOIN users relJoin12 ON project_cstm.user_id6_c = relJoin12.id AND relJoin12.deleted=0
LEFT JOIN accounts relJoin13 ON project_cstm.account_id_c = relJoin13.id AND relJoin13.deleted=0 LEFT JOIN users ON project.assigned_user_id=users.id where
(project.id in (‘5ca8d7fb-e43d-1e90-5203-602c9c1dcd70’)) AND project.deleted=0

Can someone help me?

[jansiero]

Hi @jdelgado,

Did you set a filter to a relate field? (field of another module)
This is currently not supported and also described here:

How to add a Target Lists Subpanel to Leads or Contacts etc?

When i add this filter to Targets module and search, it is working good. But when i try to exports all list, there is a database error. What is wrong? “Database failure. Please refer to suitecrm.log for details.” In log: Sun Dec 6 22:35:20 2020 [20131][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] Mysqli_query failed. Sun Dec 6 22:35:20 2020 [20131][e433d2f0-c23e-3ad2-bd4d-5ee727929421][FATAL] Error exporting Prospects: .SELECT prospects., email_addresses.email_address email_address, ‘…

Github issue:

Database failure when filtering on relate field and exporting

You could fix the export by applying the suggested changes, but they have side effects. I was working on a fix, but it is not as straightforward as I hoped.

You might want to try creating a report with the selected filters and exporting from this report.