locked
Advanced search filtered on several related records RRS feed

  • Question

  • Hi,

    I'm trying to search for contacts. To mye contacts, I have a related customized entity called "subsciption". One contact can have several subscriptions. Examples of different subscriptions: "news letter", "general information", "This months offer" and so on. So on subscritions, you define what kind of subscription it is in a field called "TYPE".

    So what i'm trying to build is a search for a contact where:

    Status = Active
    Related record (subscription) - type = "news letters"
    Realted record (subscription) - type = "general information"

    In this way I hope to find all contacts that subscribe to both "news letter" and "general information".

    But this results in a result of 0 records.

    If i just search for
    Status = Active
    Related record (subscription) - type = "news letters"

    I find several

    If i search for
    Status = Active
    Realted record (subscription) - type = "general information"

    I find several.

    And i know i have several customers with both subscriptions. I've also tried to group "AND". No result.

    Anyone who knows why this is the case? Is this a CRM-bug? Is there any kind of workaround?

    Any help would be apprectiated.

    Thanks

    Tuesday, June 29, 2010 1:54 PM

Answers

  • marketing list will be useful in your case..

    step 1. create new marketing list and use advanced find to view find contacts related subscription) - type = "news letters" and add them..

    step 2 . do advanced fine to find contacts related (subscription) - type  "general information" and add them as well..

    step 3 . Now you can do adavanced find on contacts and find the ones which are related to marketing list creating in step 1

    Hope this helps..

     

     

    • Marked as answer by Jim Glass Jr Tuesday, June 29, 2010 5:06 PM
    • Unmarked as answer by Carl I Wednesday, June 30, 2010 7:29 AM
    • Marked as answer by Donna EdwardsMVP Sunday, July 4, 2010 2:52 PM
    Tuesday, June 29, 2010 3:06 PM
    Answerer
  • again marketing list should able to help you out over here..see below step

    1. create markteing list A - add contacts with subscribing for "general information"
    2. create markeing list B - add contacts with subscribing for "news letter"
    3. then search for contact which is in marketing list markteing list A , markeing list B and then it should only return the ones which belongs to both list

    hope this helps..

    Wednesday, June 30, 2010 10:00 AM
    Answerer

All replies

  • If (subscription) - type field is picklist field? if it is then you can add two options and if will get correct values..

    Otherwise you need do Join Twice...something like as follows

    Contact

    Status = Active

       Subscription

            (subscription) - type = "news letters

       Subscription

            (subscription) - type = "general information"

     

    Hope this helps..

     

    Tuesday, June 29, 2010 2:34 PM
    Answerer
  • Thanks MayankP

    Type is actually a new lookup. So every subscription has a lookup-field "type" that defines what kind of subscription it is. You can choose two lookups in the same filerter line.

    Related record (subscription) - type = "news letters" and "general information".

    But also this results in no hits.

    It's not possible to choose the same related entity twice. You would just get a message that this has allready been chosen.

    Strange that i'm able to find all customers that have subscription type "news letter", but not all with the combination of "news letter" and "general information".

    Thanks for any other input.

     

    • Edited by Carl I Tuesday, June 29, 2010 2:47 PM additional info
    Tuesday, June 29, 2010 2:44 PM
  • marketing list will be useful in your case..

    step 1. create new marketing list and use advanced find to view find contacts related subscription) - type = "news letters" and add them..

    step 2 . do advanced fine to find contacts related (subscription) - type  "general information" and add them as well..

    step 3 . Now you can do adavanced find on contacts and find the ones which are related to marketing list creating in step 1

    Hope this helps..

     

     

    • Marked as answer by Jim Glass Jr Tuesday, June 29, 2010 5:06 PM
    • Unmarked as answer by Carl I Wednesday, June 30, 2010 7:29 AM
    • Marked as answer by Donna EdwardsMVP Sunday, July 4, 2010 2:52 PM
    Tuesday, June 29, 2010 3:06 PM
    Answerer
  • You can choose the same attribute in a query twice though and group them with an OR. So you go to Subscription once and then have two lines for the same attribute, grouped with an OR.

    Leon Tribe


    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Wednesday, June 30, 2010 12:18 AM
    Wednesday, June 30, 2010 12:18 AM
  • Thanks Leon and MayankP,

    But both searches will only give me the records that have either "general information", or "news letter". I'm trying to figure out a search that only contains the contacts that have the combination of the two.

    So in a case where i have three contacts:

    Contact 1: Only subscribing for "general information"
    Contact 2: Only subscribing for "news letter"
    Contact 3: Subscribing for both "news letter" and "general information"

    The searches you describe will return contact 1, 2 and 3.

    I'm looking for a search that only will return contact 3.

    Thanks

    Wednesday, June 30, 2010 7:33 AM
  • again marketing list should able to help you out over here..see below step

    1. create markteing list A - add contacts with subscribing for "general information"
    2. create markeing list B - add contacts with subscribing for "news letter"
    3. then search for contact which is in marketing list markteing list A , markeing list B and then it should only return the ones which belongs to both list

    hope this helps..

    Wednesday, June 30, 2010 10:00 AM
    Answerer
  • Your suggested sollution can be done the following two ways:

    Search for: Contacts
    Realted (Marketting lists)
       Marketting list [equals] [ marketting list A ]
       Marketting list [equals] [ marketting list B ] 

    Result = 0 records

    Search for: Contacts
    Related (Marketting lists)
        Marketting list [equals] [ marketting list A and marketting list B ]

    Result = all records that have either "general information" or "news letter". It does not only show the ones with the combination.

    The search should have been:

    Search for: Contacts
    Realted (Marketting lists)
          Marketting list [equals] [ marketting list A ]
    Realted (Marketting lists)
          Marketting list [equals] [ marketting list B ] 

    But this is not possible. You cannot choose markettin lists twice as related records.

    So what i figured out after your help is the following search:

    Search for: Contacts
    Realted (Marketting lists)
            Marketting list [equals] [ marketting list A ]
    Related (Subscriptions)
            Subscription type [equals]  [ general information" ]

    This gives the records that have the combination. And nothing else.

    Does anyone know why this is like this? Why you cannot use advanced find to search for Contacts (or any other entity) that have two of the same type(entity) of related records?

    Wednesday, June 30, 2010 10:54 AM
  • For what you want to do the Marketing List is the way to go. The reason you can't do what you need to do with Advanced Find is that it needs an outer join in the construction of the query and Advanced Find doesn't support those.

    Leon Tribe

    Want to hear me talk about all things CRM? Check out my blog

    http://leontribe.blogspot.com/ 
    or hear me tweet @leontribe


    Want to hear me talk about all things CRM? Check out my blog http://leontribe.blogspot.com/ or hear me tweet @leontribe
    • Proposed as answer by Leon TribeMVP Wednesday, June 30, 2010 11:29 AM
    Wednesday, June 30, 2010 11:28 AM