fetchXML get account and relevant cases

(0) Share

Report

Posted on by sdnd2000

2,510

Hi, I have account and case entity, they are associated by account ID. In case entity, it mapped to customerid lookup field. My question is how can I left join case entity through account entity, since there is no account ID attribute in case entity.

sql query:

select a.LegalName,a.RegistrationID,a.Address1_PostalCode,a.im_OrganizationID,c.[Resolution Date] from [001 Accounts] a
left join dbo.[013Case] c on a.im_OrganizationID = c.[ACCOUNT ID]
where c.[ACCOUNT ID]='11738'

fetchXML:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="new_registrationid" />
    <attribute name="new_legalname" />
    <attribute name="new_im_organizationid" />
    <attribute name="address1_postalcode" />
    <attribute name="address1_primarycontactname" />
    <attribute name="accountid" />
    <link-entity name="incident" from="dev_accountid" to="im_OrganizationID" alias="ab" link-type="outer">
    	<attribute name="Resolution Date"/>
    </link-entity>
    <filter type="and">
    	<condition entityname="ab" attribute="dev_accountid" operator="eq" value="11738"/>
    </filter>
  </entity>
</fetch>

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (1)

Thomas David Dayman 11,323 on at

Like (0)

Report

Are you trying to get all cases related to a certain customer?

Was this reply helpful? Yes No
Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Hello,

It should look like following:

<fetch>

<entity name="account" >

<link-entity name="incident" from="customerid" to="accountid" />

</entity>

</fetch>

Just curious- why don't you want to use Advanced Find to build and test it instead?

Was this reply helpful? Yes No
sdnd2000 2,510 on at

Like (0)

Report

Sorry, I am customizing it. Is it able to get primary contact? it looks like only primary contactid

Was this reply helpful? Yes No
a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Primary Contact for Account or for Case because both have that field?

I'm sorry but I don't understand your last reply. Do you mean you want to get name instead of guid?

Was this reply helpful? Yes No
sdnd2000 2,510 on at

Like (0)

Report
Yes, I want get the name as well as the attribute from link entity, like the "resolution date", "Fullname", here is my query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" > <entity name="account" > <attribute name="new_registrationid" /> <attribute name="new_legalname" /> <attribute name="primarycontactid" /> <link-entity name="incident" from="customerid" to="accountid" alias="af" > <filter type="and" > <condition attribute="new_resolutiondate" operator="not-null" /> </filter> </link-entity> <link-entity name="contact" from="parentcustomerid" to="accountid" alias="ag" > <filter type="and" > <condition attribute="new_im_brokerofrecord" operator="eq" value="1" /> <condition attribute="fullname" operator="not-null" /> </filter> </link-entity> </entity> </fetch>

Was this reply helpful? Yes No
Verified answer

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Try to use something like following:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true" >

<entity name="account" >

<attribute name="new_registrationid" />

<attribute name="new_legalname" />

<attribute name="primarycontactid" />

<link-entity name="incident" from="customerid" to="accountid" alias="af" >

<filter type="and" >

<condition attribute="new_resolutiondate" operator="not-null" />

</filter>

</link-entity>

<link-entity name="contact" from="parentcustomerid" to="accountid" alias="ag" >

<attribute name="fullname" />

<filter type="and" >

<condition attribute="new_im_brokerofrecord" operator="eq" value="1" />

<condition attribute="fullname" operator="not-null" />

</filter>

</link-entity>

</entity>

</fetch>

Was this reply helpful? Yes No

Community site session details

fetchXML get account and relevant cases

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates

Community site session details

fetchXML get account and relevant cases

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

Featured topics

Product updates