CODEWITHSUNDEEP
nestedfetchxmldataverse
redleo85
redleo85

Reputation: 249

Use nested fetchXML with IN operator

I'm trying to get all Model-Driven Apps in a Power Platform environment, which are not part of any solution.

I want use the result of a fetchXML statement as the input condition for another statement, like nested SELECT statements in SQL.

I'm pretty much trying to convert the following SQL to fetchXML:

SELECT DISTINCT appmoduleid FROM solutioncomponent
   INNER JOIN appmodule ON appmodule.appmoduleid = solutioncomponent.objectid 
   WHERE componenttype = 80 AND solutioncomponent.objectid NOT IN 
      (SELECT DISTINCT objectid FROM solutioncomponent 
           WHERE componenttype = 80 AND NOT solutionid = 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' AND NOT solutionid = 'yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' )

I thought the IN operator may be a possibility and tried the following fetchXML statement, but it does not work, so I'm not sure if this is at all possible.

<fetch>
<entity name='solutioncomponent'>
<attribute name='objectid' />
<link-entity name='appmodule' to='objectid' from='appmoduleid' alias='appmodule' link-type='inner'>
  <attribute name='appmoduleid' />
</link-entity>
<filter>
  <condition attribute='componenttype' operator='eq' value='80' />
  <condition attribute='objectid' operator='not-in'>
    <fetch distinct='true'>
     <entity name='solutioncomponent'>
       <attribute name='objectid' />
       <filter>
         <condition attribute='componenttype' operator='eq' value='80' />
         <condition attribute='solutionid' operator='not-eq' value='xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx' />
         <condition attribute='solutionid' operator='not-eq' value='yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy' />
       </filter>
      </entity>
    </fetch>
   </condition>
</filter>
<order attribute='objectid' />
</entity>
</fetch>

I get an error message:

An exception System.FormatException was thrown while trying to convert input value 
'[REDACTED]' to attribute 'solutioncomponent.objectid'. Expected type of attribute 
value: System.Guid. Exception raised: Unrecognized Guid format.

Upvotes: 0

Views: 481

Answers (1)

Luke
Luke

Reputation: 1

You may need to put in the <value> tag after

<condition attribute='objectid' operator='not-in'>

and before

<fetch distinct='true'>

and then put in the </value> closing tag after </fetch>

Upvotes: 0

Related Questions