Change how field outputs are displayed in dropdown

Hi,

I have Jxtables with cardview and have created a view to populate my search boxes. It all works brilliantly except I have this issue with the way the text is showing in bot the drop down boxes and the actual cards. I don't think it's the component, as when i look in the table the text has already been put into the record in this way. The original values come from a multi-selct field and the system that creates them stores them in this way unfortunately.

Is there a way of making it convert the text in the view table to comma separated rather than those |##| separators?





I am not at all competent with sql, but this is my code so far that i used to create the view:

CREATE VIEW GroupLookup AS
SELECT vtiger_account.accountname, phone, website, fax, vtiger_accountbillads.bill_street, bill_city, bill_state, bill_code, vtiger_accountscf.cf_758, cf_760, cf_756, cf_762, cf_750
FROM vtiger_account, vtiger_accountbillads, vtiger_accountscf
WHERE
vtiger_accountscf.cf_750 = 'Yes'
AND vtiger_account.accountid = vtiger_accountbillads.accountaddressid
AND vtiger_account.accountid = vtiger_accountscf.accountid
ORDER BY vtiger_account.accountname DESC;

Hi Tomaz,

This is what I have done so far (sorry I am sql newbie):

CREATE VIEW MyTest13 AS
SELECT vtiger_account.accountname, phone, website, fax, vtiger_accountbillads.bill_street, bill_city, bill_state, bill_code, vtiger_accountscf.cf_758, cf_760, cf_756, cf_762, cf_750

FROM vtiger_account, vtiger_accountbillads, vtiger_accountscf
WHERE
vtiger_accountscf.cf_750 = 'Yes'
AND vtiger_account.accountid = vtiger_accountbillads.accountaddressid
AND vtiger_account.accountid = vtiger_accountscf.accountid
ORDER BY vtiger_account.accountname DESC;

SELECT vtiger_accountscf.cf_758, cf_760, cf_756
REPLACE( ' |##| ', ',', '' ) AS cf_758,
REPLACE( ' |##| ', ',', '' ) AS cf_760,
REPLACE( ' |##| ', ',', '' ) AS cf_756
FROM vtiger_accountscf;

But it produces the following error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REPLACE( ' |##| ', ',', '' ) AS cf_758, REPLACE( ' |##| ', ',', '' ) AS cf_760, ' at line 2

I have messed around with it so much but can't get it to work - it creates the table but doesn't change the values. Can you see what is wrong?

Hi tomaz,

that works perfectly

Only problem is I have been dumb. I didn't think about what i was doing. Looking at the list now it should actually be creating a new record for every entry rather than adding a comma as this keeps it in the same line.

My problem is these values come from a multi select field, so the organisation can belong to several categories i.e. be involved with adults, children, disabled and so on.

In my drop down field it looked like this:

Who they work with: adults |##| Children |##| Disabled ...
Since using the SQL above it now looks like this:

Who they work with: adults |##| Children |##| Disabled ...

It should look like this:
Who they work with (please select one or more):
adults
Children
Disabled ...

Is there anyway I can achieve this? I am not how best to approach it