locked
Query 1:N relationship entities (Opportunity - Opportunity Product) RRS feed

  • Question

  • Let's say we have this -

    Opportunity O1 is associated to 3 Opportunity Products - P1, P2 & P3

    Opportunity O2 is associated to 1 Opportunity Products - P1

    Opportunity O3 is associated to 2 Opportunity Products - P1 & P3

    Opportunity O4 is associated to 10 Opportunity Products - P1, P2, P3, P4, P5, P6, P7, P8, P9 & P10.

    I would like to see all opportunities that have only P1. Meaning I would want to see Opportunity O2 in the result set.

    It would be wonderful if there is anyway to do it through advance find so that I could take further actions like mail merge on the result set. I was able to get that using marketing list to add all opportunities with Opportunity Product P1 and then removing all opportunities products except P1. But I am looking for something easy than this for clients.

    I have these ideas and wonder if this are possible in CRM.

    1. Add a custom operator in Advance find options like 'NOT IN'. I am not sure if this is even possible. If its possible how can I achieve this?

    2. We can manipulate the fetch xml from advance find but I wonder if I can alter the fetch xml and upload it again to get results?

    Other possible solutions is to create SSRS report but clients won't be able to do further actions (like mail merge) using the results. Or is there any way we could design the entity differently to achieve this easily?

    I would love to hear the suggestions and ideas from you all!

    Thanks!

    Friday, November 14, 2014 6:51 PM

Answers

  • I found a work around as follow.

    I create a text field on an opportunity record which will basically store all the products associate to a opportunity records separated by ";". E.g. P1; P2; P3 if Opportunity O1 is associated to 3 Opportunity Products - P1, P2 & P3. This new field is updated by plugin on create, update or delete operations on opportunity record. This way the clients could easily do query on advance find as required and also could take further actions such as mail merge on the result set.

    • Marked as answer by Anna' Wednesday, September 2, 2015 11:00 PM
    Wednesday, September 2, 2015 11:00 PM

All replies

  • unfortunately, you have already listed all possible solutions. There is nothing much to add.

    regards

    Jithesh

    Saturday, November 15, 2014 1:04 PM
  • I found a work around as follow.

    I create a text field on an opportunity record which will basically store all the products associate to a opportunity records separated by ";". E.g. P1; P2; P3 if Opportunity O1 is associated to 3 Opportunity Products - P1, P2 & P3. This new field is updated by plugin on create, update or delete operations on opportunity record. This way the clients could easily do query on advance find as required and also could take further actions such as mail merge on the result set.

    • Marked as answer by Anna' Wednesday, September 2, 2015 11:00 PM
    Wednesday, September 2, 2015 11:00 PM