Text Autocomplete with concat fields

Developers Programming
#1

All,
I am wondering why this doesnt work…

I have a code that can contain a modifier, the modifer can be a null value in the database.

I am working within a detail section of a master detail form.

Here is the select I am using to get the code and the modifier.
SELECT HCPCS_CODE, CONCAT(HCPCS_CODE,’ ',MODIFIER)
FROM hcpcs_codes_new
WHERE (CATEGORY = 1 and FACILITY = 1 and code_year = 2012) AND HCPCS_CODE = ‘’
ORDER BY HCPCS_CODE

When I type in any code I get nothing listed.

The Original code which works fine is this:
SELECT HCPCS_CODE, HCPCS_CODE
FROM hcpcs_codes_new
WHERE (CATEGORY = 1 and FACILITY = 1 and code_year = 2012) AND HCPCS_CODE = ‘’
ORDER BY HCPCS_CODE

Any Ideas??
Kevin

#2

Hello Kevin,

I highly suggest you to get in touch with [url=http://www.scriptcase.net/phpgenerator/support/support.php]our support. As the second code is working fine, there is no reason why the first shouldn’t.

Our chat is active on workdays from 8:00am to 6:00pm, and our ticket system is always available.

A possible workaround would be to try to perfom the CONCAT on the grid lookup. Or perhaps on the onRecord() or onLoad(), depending on what sort of application you are working with.

regards,
Bernhard Bernsmann

#3

The problem is that the concat function in scriptcase wont work with the IFNULL , using mysql if any of the concat values is null the result is null.
think sc needs to be aware of IFNULL option

#4

Hello,

Have you tried to set an alias to the concatenated field? See below…

SELECT HCPCS_CODE, CONCAT(HCPCS_CODE,’ ',MODIFIER) AS MHCPCS_MODIFIER
FROM hcpcs_codes_new
WHERE (CATEGORY = 1 and FACILITY = 1 and code_year = 2012) AND HCPCS_CODE = ‘’
ORDER BY HCPCS_CODE

regards,
Bernhard Bernsmann

#5

[QUOTE=bartho;11485]Hello,

Have you tried to set an alias to the concatenated field? See below…

SELECT HCPCS_CODE, CONCAT(HCPCS_CODE,’ ',MODIFIER) AS MHCPCS_MODIFIER
FROM hcpcs_codes_new
WHERE (CATEGORY = 1 and FACILITY = 1 and code_year = 2012) AND HCPCS_CODE = ‘’
ORDER BY HCPCS_CODE

regards,
Bernhard Bernsmann[/QUOTE]

Hi Bernhard, I’ve same problem in a form with autocomplete field with 2 fields using “concat”

I’ve tried:

SELECT id, CONCAT (apellido," ", nombre) AS usuario_buscar
FROM usuarios
ORDER BY apellido

with no results (autocomplete field no show anything)

table “usuarios” are defined in this way

CREATE TABLE IF NOT EXISTS usuarios (
id int(11) NOT NULL AUTO_INCREMENT,
nombre varchar(30) NOT NULL,
apellido varchar(30) NOT NULL,
id_grupo int(11) NOT NULL,
id_ip int(11) NOT NULL,
tipo_de_conexion enum(‘ip_fija’,‘pppoe’,‘dhcp’) NOT NULL,
pppoe_user varchar(30) DEFAULT NULL,
pppoe_pass varchar(30) DEFAULT NULL,
nat enum(‘comun’,‘mapeo’,‘no’) NOT NULL,
id_ip_nat int(11) DEFAULT NULL,
Mac varchar(17) DEFAULT NULL,
id_cpe_class int(11) DEFAULT NULL,
id_ip_cpe int(11) DEFAULT NULL,
mac_cpe varchar(17) DEFAULT NULL,
info_adicional text,
activo tinyint(1) NOT NULL DEFAULT ‘1’,
id_nodo int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;

the only query that works with autocomplete is without Concat

SELECT id, apellido
FROM usuarios
ORDER BY apellido

any hints?

#6

Hello Mike,

Please contact our support regarding this issue. Our chat is active on workdays from 8:00am to 6:00pm (GMT -3), and our ticket system is always available.

regards,
Bernhard Bernsmann

#7

[QUOTE=bartho;13646]Hello Mike,

Please contact our support regarding this issue. Our chat is active on workdays from 8:00am to 6:00pm (GMT -3), and our ticket system is always available.

regards,
Bernhard Bernsmann[/QUOTE]

My support period available has expired.
There is possible to fix this ?

#8

Mike,

Yeah, due to the circumstance of the issue you can contact our support. If you cannot even open a ticket / chat with our support, logout from our site and contact us as a “Trial User”, and once you explain the issue you will get support.

regards,
Bernhard Bernsmann

#9

The problem with CONCAT is still there with AJAX auto-complete and multi select search … please fix it
EDIT :
i have found that if you use CONCAT_WS() it will work fine. also CONCAT_WS() is better if you have a NULL value in any field.

#10

But this works in MySQL only?

#11

Yes i am using MySQL