Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / Dynamic Marketing List...
Microsoft Dynamics CRM (Archived)

Dynamic Marketing List Query

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi All,

I am wondering if there is a bug in Dynamic Marketing List Query. 

There are two marketing lists with contacts. When I want to get contact from both lists, query is " List equals <List1> OR List equals <List2>". That does produce the list I expect.

However, when I want to get only members from first list, which are NOT members of list 2, the query I use " List equals <List1> AND List does not equal <List2>". That produces all contacts from list 1.

So how do I select ONLY members from LIST1 which do not belong to the LIST2?

Regards.

*This post is locked for comments

  • Suggested answer
    Neil Parkhurst Profile Picture
    Neil Parkhurst 10,727 User Group Leader on at
    RE: Dynamic Marketing List Query

    CRM doesn't support not-in queries via advanced find.

    BUT you could try this add-in (it may deliver the capability you require) ....

    www.cobalt.net/cobaltintelligentquery

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic Marketing List Query

    Hi Thomas,

    In my case the problem is that not me, but my users (most of them w/o technical background) need that exclude feature, so fiddling with XML definitely not an option for them.

    Just did a sample test now. I have two static lists. When I use "List does not equal <List2>" results are what I expect - all members from all lists (in my case the other one), EXCLUDING the list I wanted.

    So it looks like my original assumption was wrong. Exclusion does work. What doesn't - is when you use " AND " close with exclusions.

    All other scenarios work as well:

    - "OR" with two lists produces list with members from two lists

    - "AND" with two lists produces empty results - as I would expect.

    To summarize: in my opinion, the current implementation of "AND" and "DOES not EQUAL" doesn't make sense (or at least I don't get it) - query builder allows me to build (OR and Not) clauses, but doesn't produces result it should.

  • Suggested answer
    tpeschat Profile Picture
    tpeschat 4,926 on at
    RE: Dynamic Marketing List Query

    Hi.

    NOT IN queries can not be created via advanced find so far. But you could create a saved view via advanced find.  Then you could export that saved view (add all columns) and then edit the fetch xml like outlined in the following article:

    msdn.microsoft.com/.../dn531006.aspx

    And then reimport the view.

    Then you could create a dynamic marketing list based on that view.

    But I agree that's not the standard user approach.

    Hth Thomas

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic Marketing List Query

    Hi Drew,

    I see what you are saying, but the statement you are considering makes no sense from the database point of view. List id is a foreign key for the list member, so if you want to exclude members from the second list, SQL statement should be different.

    I understand that Neil's approach does work for static lists, but it is a workaround. And if my goal is to create dynamic list from static ones, then his approach doesn't help me at all.

    My company now considering switching from Sage Act to Dynamics CRM. The most often used task for many of users is to work with lists (include/exclude). That task is trivial in Act.

    So the question still remains - is that a bug. If it is not, and is functioning by design, I would really like to understand what is "meant" to do, when I select "List does not equal <List2>".

    We are just evaluating the Dynamics CRM, so I can't ask MS Support. Do they read this forum?

  • Suggested answer
    Drew Poggemann Profile Picture
    Drew Poggemann 4 on at
    RE: Dynamic Marketing List Query

    Hi Gary,

    I think the query that is probably generated behind the scenes is not exactly what were thinking.

    I would assume it would generate something more like this:

    SELECT *

    FROM [crm_MSCRM].[dbo].[ListMemberBase]

    where listid='D43EDE56-72D7-E611-80E0-0A1593716EFD'

       and listid != '835BF318-13D8-E611-80E0-0A1593716EFD';

    which would return you the first list results as you are seeing.  Overall I don't think this is a bug, it is just different than you expected.

    Are the two lists static or dynamic?  If they are dynamic then see if you can go after the source records to build a query that will include what you need in the final list.  If they are both static then I would look at Neil's approach to accomplish.

    Hope this helps.

    Thanks,

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Dynamic Marketing List Query

    Hi Neil,

    Thank you for the reply.

    I understand that dynamic list is a advanced find. The interactive tool allows to create pretty complex conditions.

    The query in dynamic list I have is:

    Marketing List Equals "Name 1"

    AND

    Marketing List Does Not Equal "Name 2"

    I would assume it will generate something like this (actual SQL script I was able to run in DB):

    SELECT *

     FROM [crm_MSCRM].[dbo].[ListMemberBase]

     where listid='D43EDE56-72D7-E611-80E0-0A1593716EFD'

     and EntityId not in (select EntityId from   [crm_MSCRM].[dbo].[ListMemberBase]

     where listid='835BF318-13D8-E611-80E0-0A1593716EFD')

    However, that is not the case, because Dynamic list returns me all members of the First list, ignoring second condition.

    If I change the query to:

    Marketing List Equals "Name 1"

    OR

    Marketing List Does Not Equal "Name 1"

    then dynamic list produces proper results: combined from both lists.

    So I guess the interim question is - what kind of SQL script my dynamic list generates? Is it possible there is a bug there?

  • Suggested answer
    Neil Parkhurst Profile Picture
    Neil Parkhurst 10,727 User Group Leader on at
    RE: Dynamic Marketing List Query

    I assume list1 and list2 are static lists? And you are trying to create a third dynamics list based on these.

    The problem you have is that a is not equal will not work in the way you require. A dynamic list is effectively an advanced find. I think you will find that your query is not possible with a single advanced find.

    If you use a static list instead. You can then include members from list one. Then use manage members option to remove members from list 2. I think this will give you the results required.

    You can't use the remove option with a dynamic list, so I think you'll need to convert to a static list.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,711 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,458 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans