Converting to SQL Query, Power Automate Parameters, and OData all fail with "value cannot be null. Parameter name: source"

[rgbeach2112]

Probably doing something stupid...but trying to convert FetchXML to SQL, OData, or Power Automate Parms all result in the same error (see below).

Looks like maybe expecting the "Dataverse long term data retention Data Source" parameter to have a value, as when I set it to the only available option "archive", it gets past the initial null value error only to get other errors e.g. "Only live data is supported in web api".

[rappen]

Sorry about that. Can you send the Fetch XML as well? If it's a bit too sensitive, can you email me about it?

[rgbeach2112]

Hi....thanks for your help. As I said, I'm probably doing something wrong. Below is the FetchXML. It is a Dataverse view, and successfully executes and returns results in FextchXMLBuilder. Maybe it's a problem with the linked-entities? <fetch version="1.0" output-format="xml-platform" mapping="logical" no-lock="false" distinct="true" userqueryid="74975aab-7074-ef11-a670-000d3a4f194f"> <entity name="contact"> <attribute name="entityimage_url"/> <attribute name="ptl_reportsto"/> <attribute name="statecode"/> <attribute name="fullname"/> <order attribute="fullname" descending="false"/> <attribute name="parentcustomerid"/> <attribute name="telephone1"/> <attribute name="emailaddress1"/> <attribute name="ptl_contacttags"/> <attribute name="jobtitle"/> <attribute name="contactid"/> <attribute name="poa_clientstatus"/> <attribute name="poa_employerclientstatus"/> <attribute name="poa_employeestatus"/> <filter type="and"> <condition attribute="statecode" operator="eq" value="0"/> </filter> <link-entity name="listmember" intersect="true" visible="false" from="entityid" to="contactid"> <link-entity name="list" alias="ag" from="listid" to="listid"> <link-entity name="campaignitem" intersect="true" visible="false" from="entityid" to="listid"> <link-entity name="campaign" alias="ah" from="campaignid" to="campaignid"/> </link-entity> </link-entity> </link-entity> </entity> </fetch>

…

On Tue, Sep 17, 2024 at 2:08 AM Jonas Rapp ***@***.***> wrote: Sorry about that. Can you send the Fetch XML as well? If it's a bit too sensitive, can you email me about it? — Reply to this email directly, view it on GitHub <#1069 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BG2PIIHUDDAHNXTQ2JPOVR3ZW7IPNAVCNFSM6AAAAABOKGU4JKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNJUG4YTQNBTGE> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[rappen]

I simplifying the issue a lot, with the same problem:

<fetch>
  <entity name='contact'>
    <attribute name='fullname' />
    <link-entity name='listmember' from='entityid' to='contactid' alias='MM' intersect='true'>
      <link-entity name='list' from='listid' to='listid' alias='ag'>
        <link-entity name='campaignitem' from='entityid' to='listid' intersect='true'>
          <link-entity name='campaign' from='campaignid' to='campaignid' alias='ah' />
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>

I can make it work if I comment out all link-entity under the first one, AND make the link-entity outer.
Adding anything more, it can't convert it.

<fetch>
  <entity name='contact'>
    <attribute name='fullname' />
    <link-entity name='listmember' from='entityid' to='contactid' link-type='outer' alias='MM' intersect='true'>
      <!--
<link-entity name='list' from='listid' to='listid' alias='ag'>
  <link-entity name='campaignitem' from='entityid' to='listid' intersect='true'>
    <link-entity name='campaign' from='campaignid' to='campaignid' alias='ah' />
  </link-entity>
</link-entity>
-->
    </link-entity>
  </entity>
</fetch>

Now my knowledge is ended, and I call a friend, my expert Mr. @MarkMpn ☎️

[rgbeach2112]

Thank you for your help in looking into this - and for making such a great tool!

…

On Tue, Sep 17, 2024 at 10:54 AM Jonas Rapp ***@***.***> wrote: I simplifying the issue a lot, with the same problem: <fetch> <entity name='contact'> <attribute name='fullname' /> <link-entity name='listmember' from='entityid' to='contactid' alias='MM' intersect='true'> <link-entity name='list' from='listid' to='listid' alias='ag'> <link-entity name='campaignitem' from='entityid' to='listid' intersect='true'> <link-entity name='campaign' from='campaignid' to='campaignid' alias='ah' /> </link-entity> </link-entity> </link-entity> </entity> </fetch> I can make it work if I comment out all link-entity under the first one, AND make the link-entity outer. Adding anything more, it can't convert it. <fetch> <entity name='contact'> <attribute name='fullname' /> <link-entity name='listmember' from='entityid' to='contactid' link-type='outer' alias='MM' intersect='true'> <!--<link-entity name='list' from='listid' to='listid' alias='ag'> <link-entity name='campaignitem' from='entityid' to='listid' intersect='true'> <link-entity name='campaign' from='campaignid' to='campaignid' alias='ah' /> </link-entity></link-entity>--> </link-entity> </entity> </fetch> Now my knowledge is ended, and I call a friend, my expert Mr. @MarkMpn <https://github.com/MarkMpn> ☎️ — Reply to this email directly, view it on GitHub <#1069 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/BG2PIIDRMPKVUXWURKHZ5HTZXBGBXAVCNFSM6AAAAABOKGU4JKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNJWGMZDGNZRHE> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[MarkMpn]

I believe the OData & therefore the Power Automate conversion is fixed in MarkMpn/MarkMpn.FetchXmlToWebAPI#6 if you want to pull the latest version of this submodule @rappen. The SQL conversion works already if you enable the SQL 4 CDS option - I haven't investigated the source of this error in the built-in conversion but is likely a missing null check on the link-entity items.