Wednesday, December 18, 2013

Dynamic Custom Fields and the RGFQ

Part of the problem with Custom Fields is that they're so custom! So they change. A lot. Meaning reporting on them isn't exactly easy without a lot of work. So here's a little snippet I cooked up with Alex's help to query the members of a tag with their values for any custom fields that happen to be on the Tag:

DECLARE @profile_id INT
SET @profile_id = 350


DECLARE @fields VARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX), @parameters NVARCHAR(MAX)


SELECT @fields=COALESCE(@FIELDS+',','')+'['+x.title+']'
FROM
(SELECT DISTINCT ccf.title
FROM
core_profile_member_field AS cpmf
JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
JOIN core_person AS cp ON cp.person_id=cpm.person_id
JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
WHERE cpro.profile_id=@profile_id
) AS x

SET @sql = 'SELECT *
FROM
(
SELECT cp.person_id, cp.last_name+'', ''+cp.first_name AS [person_name], ccf.title, REPLACE(ISNULL(cpmfv.selected_value,''''),''^'','' '') AS selected_value
FROM core_profile_member_field AS cpmf
JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
JOIN core_person AS cp ON cp.person_id=cpm.person_id
JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
WHERE cpro.profile_id = @profileID
) AS s
PIVOT
(
MAX(selected_value)
FOR [title] IN ('+@fields+')
) AS p'

SELECT @sql

SET @parameters = '@profileID INT'

EXEC sp_executesql @sql=@sql, @params=@parameters, @profileID=@profile_id

There's actually a lot of room for expansion from this point, including adding custom field groups, an active-only option, or adapting it to work for Groups/Missions/Appointments/etc. Maybe I'll come back and post some of those later.

1 comment:

  1. This query is AMAZING! Thanks for sharing it. Took it and updated it to be a SPROC. Also added some checks for valid ProfileIDs and the OrgID setting. Here's a link to the file on Dropbox.

    https://www.dropbox.com/s/1gi5qgijgzte24o/cust_a91114_get_profile_custom_fields.sql

    ReplyDelete