locked
Dynamics CRM 365 fetchXML to filter lookup RRS feed

  • Question

  • Greetings.

    I'm trying to workaround all fetchXML limitations to create a fetchXML that will filter a lookup.

    I have 3 tables: Market, Opportunity, OpportunityMarket

    Market contains defined sets of markets.

    Opportunity is the parent of OpportunityMarket which contains all the markets of a given opportunity.

    When user creates an opportunity, automatically, all markets are inserted in OpportunityMarket for that opportunity.
    Then user can delete one or more undesired markets from OpportunityMarket (subgrid).
    In case the user made a mistake by deleting the wrong market, I would them to use a lookup where they can select the market they have deleted by mistake and on the OnChange even of this lookup, I would add the selected market back to OpportunityMarket.

    For a better user experience, I would like the lookup to be filtered and to list only the markets that do not exists in OpportunityMarket.

    I've tried to create a fetchXML using an OUTER JOIN on the OpportunityMarket entity with a filter condition that would check if the link ID of the Market entity is null but because I need also to filter with the Opportunity ID, I haven't been able to make this work.

    Too bad RIGHT JOIN is not allowed because that's exactly what I would need.

    If you have any suggestion or alternative to resolve this issue, it would be greatly appreaciated.

    Thanks,

    -Frank


    Frank

    Monday, December 11, 2017 6:47 AM

Answers

  • If you use a native N:N relation between Market and Opportunity, then the User Experience is much better.

    But in your case with a manual N:N relation you can easily do this by using the not in feature. See this example.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="account">
        <attribute name="name" />
        <attribute name="primarycontactid" />
        <attribute name="telephone1" />
        <attribute name="accountid" />
        <order attribute="name" descending="false" />
        <filter type="and">
          <condition attribute="accountid" operator="not-in">
            <value uiname="Test 2" uitype="account">{84DBEC6B-F1E6-E611-80F8-5065F38A4A21}</value>
            <value uiname="Test 3" uitype="account">{141D895D-82EC-E611-80F9-5065F38A4A21}</value>
            <value uiname="Test 4" uitype="account">{748575F8-6628-E711-8101-5065F38A4A21}</value>
          </condition>
        </filter>
      </entity>
    </fetch>

    You can add the bold part of the FetchXml by retrieving all OpportunityMarkets for the specified Opportunity.  


    • Edited by MGCRM Monday, December 11, 2017 7:05 PM fix
    • Proposed as answer by MGCRM Monday, December 11, 2017 7:05 PM
    • Marked as answer by FrkM Tuesday, December 12, 2017 8:06 AM
    Monday, December 11, 2017 7:04 PM

All replies

  • Better to add new sub grid OpportunityMarketUnlisted

    Add all only the markets that do not exists in OpportunityMarket

    and add on delete event of market.

    Show both sub grid in one tab and parallel section.


    Hope this helps. ----------------------------------------------------------------------- Santosh Bhagat If this post answers your question, please click "Mark As Answer" on the post and "Vote as Helpful"

    Monday, December 11, 2017 6:55 AM
  • If you use a native N:N relation between Market and Opportunity, then the User Experience is much better.

    But in your case with a manual N:N relation you can easily do this by using the not in feature. See this example.

    <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
      <entity name="account">
        <attribute name="name" />
        <attribute name="primarycontactid" />
        <attribute name="telephone1" />
        <attribute name="accountid" />
        <order attribute="name" descending="false" />
        <filter type="and">
          <condition attribute="accountid" operator="not-in">
            <value uiname="Test 2" uitype="account">{84DBEC6B-F1E6-E611-80F8-5065F38A4A21}</value>
            <value uiname="Test 3" uitype="account">{141D895D-82EC-E611-80F9-5065F38A4A21}</value>
            <value uiname="Test 4" uitype="account">{748575F8-6628-E711-8101-5065F38A4A21}</value>
          </condition>
        </filter>
      </entity>
    </fetch>

    You can add the bold part of the FetchXml by retrieving all OpportunityMarkets for the specified Opportunity.  


    • Edited by MGCRM Monday, December 11, 2017 7:05 PM fix
    • Proposed as answer by MGCRM Monday, December 11, 2017 7:05 PM
    • Marked as answer by FrkM Tuesday, December 12, 2017 8:06 AM
    Monday, December 11, 2017 7:04 PM