locked
SQL Query to FetchXML - CRM2013 and ClickDimensions Subscription Preferences RRS feed

  • Question

  • 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


    • Edited by mb.ace Friday, May 27, 2016 7:07 PM
    Friday, May 27, 2016 7:06 PM