FetchXML - Query multiple tables to display one field in a report

(0) Share

Report

Posted on by EBMRay

873

Hello everyone,

I built a very simple sub-report that is connected to the Work Order entity.
That sub-report is related to a parent report that will be generated from "Customer Asset" entity to show the related work orders of the asset.

I have a custom entity that is related to the Work order, and the customer asset, and both has 1:N relationship which are the below fields:

Related to asset table Related to WO
I would like to add to my fetch XML a link query to my custom entity to fetch only one attribute and display it in the report once it got generated from the Customer Asset entity.

The fetch XML that I tried:

However, when I tried to import the report it is showing the below error:

If I removed from the above XML line 21 until 23 the report will be uploaded successfully but the problem without that custom field that I want to show.
It seems that because my query logic is wrong on those lines.

I tried to use Advanced find just to query directly from WO entity to my custom entity and it worked fine.
XML query:

Can please someone advise what should be the updated XML query in order to achieve the above to display that custom field in the report?

Please let me know if you need any further information.
Any help is highly appreciated.

All responses (2)

Answers (1)

EBMRay 873 on at

Like (0)

Report

RE: FetchXML - Query multiple tables to display one field in a report

Hey ,

Thank you for your reply and for providing the updated XML query.

Yes, the Asset detail is not related to the bookable resource entity.

I used the code mentioned, I was able to import it and it is showing that field value successfully in the report.

Your help is highly appreciated!
Verified answer

Community Member Microsoft Employee on at

Like (1)

Report
RE: FetchXML - Query multiple tables to display one field in a report

Hi EBMRay,

According to your description, Asset Detail entity is related to Customer Asset entity and Work Order entity, not Bookable Resource Booking entity.

However, in your XML query your Asset Detail entity is linked to your Bookable Resource Booking entity which causes the error.

Please change your code like this to have a try(Make Asset Detail entity link to Work Order entity):

I previewed it on my side and it should work: