locked
Need help with many-to-many relationship RRS feed

  • Question

  • I am using CRM 4.0 Online. I have a simple situation here. I have created two custom entities called Candidates and Skillsets. Candidates entity is a master table that contains candidates' contact details. Skillsets entity is a also a mastertable that contains names of skills that a candidate may possess.

     

    I need to create a list of skills associated with a candidate alongwith their proficiency level of that skillset. In order to do this, I created a third custom entity called CandidateSkillsets, which contains Lookup fields for Candidate ID and Skillset ID alongwith another field called Proficiency Level. So basically CandidateSkillset entity contains two N:1 relationships between Candidates and Skillsets.

     

    I am able to add Candidates and associate one or more Skill with the Candidate using the CandidateSkillset entity and also track the proficiency level, without any issues.

     

    However, when it comes to doing an Advanced Find where I want to search for candidates having more than 1 common skills, I am having a problem.

     

    Let's give an example:

    Candidates entity records are Candidate 1 and Candidate 2.

     

    Candidate 1 has Basic Leadership skills and Advanced Sales skills.

    Candidate 2 has Intermediate Sales and Basic Customer Service skills.

     

    I want to search for Candidates who possess both Leadership Skills and Sales skills. This query in this example should return us Candidate1 only.

     

    However, I when I am using the AND condition in the Advanced Find between the 2 skillset names, it is not returning any data.

     

    I have searched on Candidates, CandidateSkillsets and Skillsets, both none of them is returning any data when I specify the AND condition in the Advanced Find between two Skillset name.

     

    Can someone please help me as to how to search for Candidates having more than 1 skills. The names of the skills should be used in Search Filter in the Advanced Find.

     

    Thanks in Advance

     

    Regards

    Kona

     

    Thursday, December 4, 2008 5:29 AM

Answers

  • The current Advanced find would not be able to support what you are trying to do.

    The option would be to check SDK for Microsoft CRM Online to help to search for these type of queries.



    http://www.workopia.com/Links.htm
    Thursday, April 2, 2009 8:14 AM
    Moderator

All replies

  • try searching on Candidate Skillsets, then add two lines for the skillset names. Then highlight the two skillset name lines using the upside triangle on the left and choose select row. Then select Group Or from the toolbar line.

     

    This should returns records where either skillset is found and you'll have to review the list to see who has both.

     

    It won't show candidates who have both skills only ones that have either or both. I don't think you can do this Advanced Find.  [actually it might be possible I've just of thought of something that I need to test = I'll post later when I get a moment to try out my idea].

     

     

    Thursday, December 4, 2008 7:24 AM
    Moderator
  • Feridun:

    Thanks for your reply.

    I have tried using OR and that gives me the list who has either or both. However my requirement here is that I need to find out the candidates who has both the skills, not either or both.

    And the search may yeild a lot of results and going through the manually to figure out who has both would be very time taking.

    If you can suggest some other way, it would help.

    Since I am using CRM Online, I don't have direct access to the database and hence cannot use FilteredViews.

    I can however also do a query using Javascript and SOAPXML if you can think of any, as a last resort.

    Let me know your ideas.

     

    Regards

    Kona

    Thursday, December 4, 2008 12:46 PM
  •  

    Yes, I did say that the OR would who give you people that have either or both.   I'm afraid that my other idea didn't work out.

     

    I would have suggested using SQL statements via filteredviews but you've ruled that out because of CRM Online.

     

    Sorry I have no other suggestions

     

    Thursday, December 4, 2008 1:26 PM
    Moderator
  • The current Advanced find would not be able to support what you are trying to do.

    The option would be to check SDK for Microsoft CRM Online to help to search for these type of queries.



    http://www.workopia.com/Links.htm
    Thursday, April 2, 2009 8:14 AM
    Moderator