locked
Finding all Real Instances of a Custom Many-to-Many Relationship RRS feed

  • Question

  • UPDATE: The title of this thread has been changed and the definition of the question has been refined. It is this: When I make a custom N:N (many-to-many) relationship between entity A and entity B, how can I find all instances of entity A that have that relationship with any instance of entity B?

    I have a bunch of Orders, which are repeatedly renewed subscriptions, so each one is a long-term piece of business. I need to have certain categories of people associated with each Order - let's say, for example, Red Widget Recipients (RWRs) and White Widget Recipients (WWRs). Any Contact can be an RWR, a WWR, or both in one *or more* Orders.

    I need to be able to see, within each Order, who the RWRs are and who the WWRs are -- in other words, when I open an Order, I can click on RWRs, for example, on the left-hand nav of the Order window, and get a list of all Contacts who are RWRs for that order.

    To achieve this, I created two N:N (many to many) relationships between the Contact entity and the Order entity - let's call those relationships RWR Contacts and WWR Contacts. I set the display options so that I would see these on the left-hand nav of an Order window, and the relevant Contacts would be listed in the Window.

    *But* I also need to be able to find ALL RWRs -- regardless of what Order they are linked to -- for certain things. I cannot find a way to do that. When I go to Advanced Find, there just doesn't seem to be a way for me to find Contacts that have been given a certain relationship, regardless of the records to which they have been given that relationship.

    Is there a way to do this? If not, then is there a better strategy for me to be able to a) record these relationships between people and orders, and b) find these classes of people when I need to?

    Thanks!

    Thursday, November 4, 2010 2:07 PM

Answers

  • I figured this out. It just simply doesn't work on many-to-many relationships. But it does work very nicely on one-to-many relationships. So that's what I'm using, since I didn't need many-to-many anyway. Thanks everyone for your help!
    Monday, February 7, 2011 5:09 PM

All replies

  • Have you tried using products for WWR and RWR. It seems most natural to use the native functionality which owuld easily allow you to see this information in native reports that come out of the box.
    Jeff Loucks | Available Technology | 888-474-2237 | 905-646-1927 | http://msmvps.com/blogs/jeffloucks
    • Proposed as answer by Jeff Loucks Thursday, November 4, 2010 7:38 PM
    Thursday, November 4, 2010 7:38 PM
  • You can do that using advance find.

    Create a new view for contact and choose RWSs from "related".

    It will give you all the contact that have that relationship irrespect of the order.

    I hope it helps.


    Amreek singh Senior CRM Consultant CDC Praxa Sydney, Australia http://mscrmshop.blogspot.com
    • Proposed as answer by Amreek Singh Friday, November 5, 2010 12:39 AM
    Friday, November 5, 2010 12:38 AM
  • Thank you Amreek. I tried that but I do not see RWR under "related". I think maybe that is becuase in fact "RWR" is a relationship (between the Order entity and the Contact entity) and not an entity in its own right, and also not an attribute of the Contact entity.
    Friday, November 5, 2010 7:02 AM
  • Thank you Jeff. I thought about it a lot before I went this way, and I agree that the best basic philosphy for someone like me is to use native functionality where-ever possible. The thing is that I am already using Products inside each Order - for example, some of my products are Red Widgets and White Widgets. Given that Red Widget Recipients and White Widget Recipients are, in fact, people -- i.e., Contacts -- I thought it logical to go the way I described above.
    Friday, November 5, 2010 7:07 AM
  • Amreek, a correction to my previous post. I can see RWR under "related" if I first choose "Order" under "related". Then, under "related" under "Order", I finally find RWR. However, now cannot find a set of critiera that includes all RWRs. I tried, for example, using "Status" equals "Active" for "Order" and also underneath for "RWR" and the result is two RWRs, while I have many more than that....
    Friday, November 5, 2010 7:46 AM
  • Jeff, just out of curiosity, I took one of the Products that I have within many of my Orders, and tried to find all instances of where that Product is included in a given order, but searching for the Product instances rather than for Orders that contain the Product, because what I need at the end is a list, of all those instances, that shows me a particular attribute of the Product and not of the parent Order. (Because, if I were to go with your idea, then I would have to add an attribute to the Product entity, namely the e-mail address of the product recipient.) I was not able to achieve this either. But it made me realise that what I am trying to do, in fact, is do a search not on an entity but on a relationship. So I am going to re-name this thread accordingly (if I can) because I think that's what it comes down to -- when I make a custom N:N (many-to-many) relationship between entity A and entity B, how can I find all instances of entity A that have that relationship with any instance of entity B?

    Friday, November 5, 2010 7:55 AM
  • I created a N:N relationship between orders and contacts and then created a test relationship record between an order and a contact. I then used Advanced Find to search for all contacts related to any order and my test record was listed in the results.

    Here's the query: Find Contacts where related Order created on contains data.

    Let me know if this helps.


    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    Friday, November 5, 2010 3:21 PM
    Moderator
  • Here is how to search on relationships:

    http://www.microsoft.com/dynamics/crm/using/sales/relationshiproles.mspx


    Jeff Loucks | Available Technology | 888-474-2237 | 905-646-1927 | http://msmvps.com/blogs/jeffloucks
    Friday, November 5, 2010 5:37 PM
  • Thanks Neil. When I do that, it strangely finds just a handful although I know there are hundreds, and I can't see any pattern....
    Monday, November 8, 2010 7:07 AM
  • Thanks Jeff. Unfortunately this doesn't work for me because the relationship needs to be between Contacts and Orders and that option doesn't seem to be available.
    Monday, November 8, 2010 7:08 AM
  • If your search is only showing some of the records, then you'll need to ask your database administrator to take a look at the actual database records in SQL Server to see what's different between the records that are showing and not showing in the search. Perhaps some of the records have been deactivated, or you don't have privileges to see them, or some other reason.

    Neil Benson, CRM Addict and MVP at Customery Ltd. You can reach me on LinkedIn or Twitter. Join over 10,000 other CRM professionals on the Microsoft Dynamics CRM group on LinkedIn.

    Monday, November 8, 2010 9:21 AM
    Moderator
  • I figured this out. It just simply doesn't work on many-to-many relationships. But it does work very nicely on one-to-many relationships. So that's what I'm using, since I didn't need many-to-many anyway. Thanks everyone for your help!
    Monday, February 7, 2011 5:09 PM