Dynamics Community Forum Thread Details

Fetch XML query

I have been trying to work through the minefield that is Fetch XML for a custom report in CRM

Although the code below isn't exactly what i want, i am struggling just to get this working.

I have a custom entity called new_transactionsets which is linked to Accounts. 1:N relationship account.accountid to new_transactionsets.new_licenceid
I have a custom entity called new_origonatingaccounts which is linked to Accounts. 1:N relationship account.accountid to new_origonatingaccounts.new_licenceid

new_transactionsets is linked to new_origonatingaccounts new_origonatingaccounts.new_originatingaccountsid to new_transactionsets.new_oaccountid

my attempt so far is bringing all records from new_transactionsets with a new_submittedon (date) of last month instead of only those where account.name = '110022'

If anyone can help me resolve this , i can then try and work out my final goal, and if anyone is willing to try and help me achieve my final goal i will be very greatful

My final goal is to get a count of new_noitems and a sum of new_submissionvalue for each record for account.name which had a submitted date of last month, grouped by new_origonatingaccounts.new_accountid

ie
if i had 4 records in new_transactionsets linked to accounts.name = '110022'

new_noitems new_submissionvalue new_accountname new_accointid

   2                  2.00          account 1           001     ( 1 record for new_accountid 001)
   7                 38.50          account 5           005     ( 3 records for new_accountid 005)

   <fetch version="1.0" mapping="logical" distinct="false" >
    <entity name="new_transactionsets" >
        <attribute name="new_noitems" />
        <attribute name="new_submissionvalue" />
        <attribute name="new_submittedon" />
        <filter type="and" >
            <condition attribute="new_submittedon" operator="last-month" />
        </filter>
        <link-entity name="new_originatingaccounts" from="new_originatingaccountsid" to="new_oaccountid" visable="false" link-type="outer" >
            <attribute name="new_accountname" />
            <attribute name="new_accountid" />
            <order attribute="new_accountid" ascending="true" />
            <link-entity name="account" from="accountid" to="new_licenceid" visable="false" link-type="outer" >
                <attribute name="name" />
                <filter type="and" >
                    <condition attribute="name" operator="eq" value="110022" />
                </filter>
            </link-entity>
        </link-entity>
    </entity>
</fetch>

Hi,

You can use groupby, however i see you put your 'name' criteria in the second level of relation, so you cannot show it in the result set, which means it cannot group the result. My understanding is that you have LicenceId field on new_transactionsets as well, so use that field instead.