locked
Many To Many Relationship Creation Time RRS feed

  • Question

  • Hi all,

    We have two custom entities 'A' and 'B' and the relationship between them is N:N. We want to know when a record from entity 'B' was associated with Entity 'A'. That means, we want to know when N:N relationship link was created for that particular record between 'A' and 'B'.

    As CRM creates an intersect/bridging table when you create a N:N relationship between two entities. I have looked into that bridging table but it has nothing like 'Created On' etc. It only has keys from both the associated entities along with its own primary key and a version number column in it. 

    I just wonder, is there a SUPPORTED way to populate the intersecting table with 'Created On' date time information? I know of unsupported ways to achieve my desired result but I don't want to go there without checking for any available supported way. 

    Thanks in advance,

    Regards,


    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" Thanks, Imran Abbasi

    Wednesday, November 14, 2012 3:12 AM

Answers

All replies

  • Hi Imran,

    No we cannot do that. The created on and modified on is always on the entities. It represents the records created and not when relationship created. Unfortunately it cannot be found out. If the unsupported way you are talking is to add fields to that table, then this is highly unsupported.

    Any schema changes should always be done through the front end as CRM updates views, etc. based on those changes.

    I hope this helps. If my response answered your question, please mark the response as an answer and also vote as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Wednesday, November 14, 2012 4:13 AM
  • Hi Imran,

    Instead of creating N:N relationship between A and B, why not create another landing/intermediate entity which relates A record with B record. Create entity C and have it related as N:1 with A and N:1 with B. So C entity record form will have A lookup and B lookup. Now this entity C record will have both created on and modified on fields. By this you can know when the relationship was created and who created it and also when was it modified and who modified it.

    So instead of creating N:N relationship between A and B create entity C having N:1 relationship to both A & B.

    I hope this helps. If my responses answered your question, please mark all these responses as an answer and also vote all of them as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Wednesday, November 14, 2012 4:21 AM
  • Hi Ashish,

    Thanks for the replies. I know making an intermediate entity and changing N:N to two N:1 relationships but the problem is that we can't break the current relationship between entities as there is a lot of data already in the system using that N:N relationship. 



    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" Thanks, Imran Abbasi

    Wednesday, November 14, 2012 7:28 AM
  • Hi Imran,

    How about using the audit to get the information you are after - this would be fully supported.

    You can use the SDK Webservices : http://msdn.microsoft.com/en-us/library/gg328218.aspx

    or you could query the <Org>_MSCRM database Audit View:

    SELECT CreatedOn,value,ObjectId,changedata FROM Audit 
    INNER JOIN stringmap on attributename='action' and action = attributevalue
    AND Action IN (33,34)
    ORDER BY CreatedOn

    Scott


    Scott Durow
    Read my blog: www.develop1.net/public     Follow Me on Twitter
    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"

    Wednesday, November 14, 2012 9:05 AM
    Answerer
  • Hi Imran,

    To query and find out the relationship creation date/time you can use Scott's query. Audit in CRM 2011 is out of Box and this query will do the job.

    If you want to do have a permanent solution, there is not simple supported way other than landing entity. The only other non-conventional way I can think of is to add two attribute on both the entities. Let's say for sake of argument you want to find the datetime and user who create the relationship. Add 2 attributes on both the entities A and B. One attribute 'Created'  and the other 'UserCreated'. Write one plugin each on both the entities. These plugins will run on update of A and on update of B. Now taking pre image check whether the value of the lookup has changed. So check whether the lookup value of A in form B has changed. If yes, then in form B populate the attributes 'Created' and 'UserCreated'. You can display this on the form or keep it hidden.

    Again we are doing lots of tweaking for this :)

    I hope this helps. If my responses answered your question, please mark all these responses as an answer and also vote all of them as helpful.


    Ashish Mahajan, Microsoft Dynamics CRM Solutions Architect, CSG (Melbourne)
    My Personal Website: http://www.ashishmahajan.com
    My Blogs: http://ashishmahajancrm.blogspot.com.au and http://ashishmahajancrm.wordpress.com
    My Youtube Channel: http://www.youtube.com/user/ashishmahajanmscrm

    My Linkedin: View Ashish Mahajan's profile on LinkedIn
    My Twitter: https://twitter.com/#!/ashishmahajan74

    Wednesday, November 14, 2012 10:09 AM
  • Thanks Scott for your reply, we are using CRM 4.0 :-) I will leave it as it is until we upgrade to 2011.

    If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" Thanks, Imran Abbasi

    Wednesday, November 14, 2012 10:28 PM