- Thank you received: 62
Please Log in or Create an account to join the conversation.
Hi,
You can try to use the REPLACE() function, to replace |##| with commas.
Here is the manual:
dev.mysql.com/doc/refman/5.0/en/string-f...tml#function_replace
Please Log in or Create an account to join the conversation.
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?
Please Log in or Create an account to join the conversation.
CREATE VIEW MyTest13 AS
SELECT vtiger_account.accountname, phone, website, fax, vtiger_accountbillads.bill_street, bill_city, bill_state, bill_code,
REPLACE(vtiger_accountscf.cf_758, '|##|', ',') as cf_758,
REPLACE(cf_760, '|##|', ',') as cf_760,
REPLACE(cf_756, '|##|', ',') as 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
I think something like this should do the job.
Please Log in or Create an account to join the conversation.
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
Please Log in or Create an account to join the conversation.
This would be complicated to find some generic solution in this state of the code. If you are familiar with php and html, you could hard code a custom selection list for given search field where the search form is generated. This is in file components/com_grid/GridBuilder.php
Please Log in or Create an account to join the conversation.