Hi all, we have a challenging problem, for creating a custom report we are trying to write a sql query that retrieves events with their custom values.
We don't have a problem with custom string values or single select lookup.The problem is with multi select custom field values, that we want to retrieve as comma separated values (i.e val1, val2 etc)
We were wondering if someone has some piece of code as an example that you can share with us
thanks!
Best answer by MennovH
Hi Alberto,
I have done this in the past. The following SQL will retrieve the values from the multi-select custom field with the shortcode ‘MULTI TEST’. Just amend the where clause and the jwcp.jptsys_web_cust_prop_sc = 'MULTI TEST' as needed.
select incident.incident_id,
stuff((SELECT distinct ', ' + cast(d.jptsys_web_lkup_data_n as varchar(100))
FROM
jptsys_web_cust_prop_mult m, jptsys_web_cust_prop_cont c, jptsys_web_lkup_data d, jptsys_web_cust_prop jwcp
WHERE
jwcp.jptsys_web_cust_prop_id = c.jptsys_web_cust_prop_id AND
m.jptsys_web_cust_prop_cont_id = c.jptsys_web_cust_prop_cont_id AND
d.jptsys_web_lkup_data_id = m.jptsys_web_lkup_data_id AND
jwcp.jptsys_web_cust_prop_sc = 'MULTI TEST'
and c.entity_id = incident.incident_id
FOR XML PATH('')),1,1,'') "multi_select_values"
from incident where incident.incident_id = 455
Screenshot of results:
Regards,
Menno
Hi Alberto,
I have done this in the past. The following SQL will retrieve the values from the multi-select custom field with the shortcode ‘MULTI TEST’. Just amend the where clause and the jwcp.jptsys_web_cust_prop_sc = 'MULTI TEST' as needed.
select incident.incident_id,
stuff((SELECT distinct ', ' + cast(d.jptsys_web_lkup_data_n as varchar(100))
FROM
jptsys_web_cust_prop_mult m, jptsys_web_cust_prop_cont c, jptsys_web_lkup_data d, jptsys_web_cust_prop jwcp
WHERE
jwcp.jptsys_web_cust_prop_id = c.jptsys_web_cust_prop_id AND
m.jptsys_web_cust_prop_cont_id = c.jptsys_web_cust_prop_cont_id AND
d.jptsys_web_lkup_data_id = m.jptsys_web_lkup_data_id AND
jwcp.jptsys_web_cust_prop_sc = 'MULTI TEST'
and c.entity_id = incident.incident_id
FOR XML PATH('')),1,1,'') "multi_select_values"
from incident where incident.incident_id = 455
Screenshot of results:
Regards,
Menno
if anyone needed ifs support pointed me to the link on the wiki with the examples I was looking for
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
