Hi Teh,
Based on the information you provided, I created a custom entity "new_country" for testing,
and set the entity as a lookup field of Account entity.
As per my understanding, I used Contact entity to refer to your ads_keycontact entity, and new_country entity to "ads_country", thus the relationships of those three entities are contact <-> account (N:1),
account <-> new_country(N:1).
Here is a demo dataset based on your query without aggregation and distinction, and the filter is changed to fullname contains 'Mao':
| contact fullname |
company country |
company region |
| Clofly Mao |
USA |
NA |
| Alex Mao |
China |
Asia |
| Admin Mao |
Japan |
Asia |
| Office Mao |
Germany |
Europe |
| Solid Mao |
USA |
NA |
So the expected of your original Fetchxml query is 3.
If my test is correct, then the reason of your issue would be caused by returntotalrecordcount property.
Having searched many web sites, it seems that the property was only supported in CRM2011, I'm not sure whether this property is still supported by Dynamics 365 for Marketing application.
Because your fetchxml is able to work when I remove returntotalrecordcount. (However, if I keep it, the query still encountered same error in your posted picture even in FetchXMl Builder in xrmtoolbox)
You could replace the original two rows
with another row:
From my test, the modification got the same expected total record as returntotalrecordcount.
Regards,
Clofly
