We've been trying to create a view in CRM2013 that would show a Contacts correct current set of subscribed subscription lists, and it's been much more difficult than we imagined. We can find what we want if we query the database directly, but not in FetchXML.
I'll post both, in hopes that someone else has already figured this out. (It seems like a reasonably common request - to list the active subscription by email address...)
The SQL that does work in CRMDB:
-- #2 check for specific subscriptions - excluding specific unsubscribes
SELECT DISTINCT SP.cdi_SubscriptionListId
,SP.cdi_Email
,SLB.cdi_name
,SLB.statusCode
FROM cdi_subscriptionPreferenceBase SP
JOIN cdi_subscriptionlistBase SLB ON SLB.cdi_subscriptionlistId = SP.cdi_SubscriptionListId
WHERE SLB.statusCode = 1
AND SP.cdi_email = 'user@emailaddress.com'
AND SP.cdi_SubscriptionListId NOT IN (
SELECT USLB.cdi_subscriptionlistid
FROM cdi_unsubscribe_cdi_subscriptionlistBase USLB
JOIN cdi_unsubscribeBase UB ON USLB.cdi_unsubscribeid = UB.cdi_unsubscribeId
WHERE UB.cdi_email = 'user@emailaddress.com'
)
And this was my last attempt with FetchXML:
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >
<entity name="cdi_subscriptionpreference" >
<attribute name="cdi_subscriptionlistid" />
<attribute name="cdi_email" />
<filter type="and" >
<condition attribute="cdi_email" operator="eq" value="user@emailaddress.com" />
<condition attribute="cdi_preference" operator="eq" value="1" />
</filter>
<link-entity name="cdi_unsubscribe_cdi_subscriptionlist" from="cdi_subscriptionlistid" to="cdi_subscriptionlistid" link-type="outer" >
<link-entity name="cdi_unsubscribe" from="cdi_unsubscribeid" to="cdi_unsubscribeid" >
<filter type="and" >
<condition attribute="cdi_email" operator="eq" value="user@emailaddress.com" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>
Any help would be greatly appreciated.
- Michael
