FetchXML OR Condition

Question

I have a FetchXML query and I have it so if participationtypemask equals 5 than it puts the users with a participationtypemask of 5 in the Required Column. But I also want to add it so if a user has a participationtypemask of 6 then it puts those users in the Optional column. Is this even possible in FetchXML? This is what I have so far.

<fetch>
      <entity name="appointment">
        <attribute name="scheduledstart" />
        <link-entity name="systemuser" from="systemuserid" to="ownerid" link-type="outer">
            <attribute name="firstname" alias="ownerFirstName" />
            <attribute name="lastname" alias="ownerLastName" />
        </link-entity>
        <link-entity name="contact" from="contactid" to="new_contactperson" link-type="outer">
            <attribute name="parentcustomerid" alias="parentaccount" />
            <attribute name="new_businessunit" alias="businessunit" />
        </link-entity>
        <attribute name="new_contactperson" />
        <attribute name="subject" />
        <attribute name="new_coldernotes" />
    <link-entity name="activityparty" from="activityid" to="activityid" link-type="outer">
    <attribute name="participationtypemask" alias="participationtypemask" />
        <filter>
        <condition attribute="participationtypemask" operator="eq" value="5" />
        </filter>
        <link-entity name="contact" from="contactid" to="partyid" link-type="outer">
            <attribute name="firstname" alias="RequiredContactFirstName" />
            <attribute name="lastname" alias="RequiredContactLastName" />
        </link-entity>
        <link-entity name="systemuser" from="systemuserid" to="partyid" link-type="outer">
            <attribute name="fullname" alias="RequiredOwners" />
        </link-entity>
        <link-entity name="account" from="accountid" to="partyid" link-type="outer">
            <attribute name="name" alias="RequiredAccount" />
        </link-entity>
    </link-entity>
        <filter type="and">
            <condition attribute="scheduledstart" operator="on-or-after" value="@FromDate" />
            <condition attribute="scheduledstart" operator="on-or-before" value="@ToDate" />
        </filter>
      </entity>
</fetch>

I don't know if I got you: Do you like to include both partitiontypemasks in the result? — ccellar, Nov 18 '11 at 22:00
Yea, I want to include the partitiontypemasks of 5 and of 6. But I want the ones that are 5s to go in the Required column and the ones that are 6 to go in the optional column. Thanks! — , Nov 21 '11 at 17:56

score 0 · Accepted Answer · answered Nov 21 '11 at 18:31

I personally have been busy, so sorry for the longer wait, but I think all you should have to do is make another join to the link-entity Activity Party with the appropriate aliases, as below.

<fetch>
  <entity name="appointment">
    <attribute name="scheduledstart" />
    <link-entity name="systemuser" from="systemuserid" to="ownerid" link-type="outer">
      <attribute name="firstname" alias="ownerFirstName" />
      <attribute name="lastname" alias="ownerLastName" />
    </link-entity>
    <link-entity name="contact" from="contactid" to="new_contactperson" link-type="outer">
      <attribute name="parentcustomerid" alias="parentaccount" />
      <attribute name="new_businessunit" alias="businessunit" />
    </link-entity>
    <attribute name="new_contactperson" />
    <attribute name="subject" />
    <attribute name="new_coldernotes" />
    <link-entity name="activityparty" from="activityid" to="activityid" link-type="outer">
      <attribute name="participationtypemask" alias="participationtypemask" />
      <filter>
        <condition attribute="participationtypemask" operator="eq" value="5" />
      </filter>
      <link-entity name="contact" from="contactid" to="partyid" link-type="outer">
        <attribute name="firstname" alias="RequiredContactFirstName" />
        <attribute name="lastname" alias="RequiredContactLastName" />
      </link-entity>
      <link-entity name="systemuser" from="systemuserid" to="partyid" link-type="outer">
        <attribute name="fullname" alias="RequiredOwners" />
      </link-entity>
      <link-entity name="account" from="accountid" to="partyid" link-type="outer">
        <attribute name="name" alias="RequiredAccount" />
      </link-entity>
    </link-entity>
    <!--the new join-->
    <link-entity name="activityparty" from="activityid" to="activityid" link-type="outer" alias="optionalactivityparty">
      <attribute name="participationtypemask" alias="optionalparticipationtypemask" />
      <filter>
        <condition attribute="participationtypemask" operator="eq" value="6" />
      </filter>
      <link-entity name="contact" from="contactid" to="partyid" link-type="outer" alias="optionalcontact">
        <attribute name="firstname" alias="OptionalContactFirstName" />
        <attribute name="lastname" alias="OptionalContactLastName2" />
      </link-entity>
      <link-entity name="systemuser" from="systemuserid" to="partyid" link-type="outer" alias="systemuser2">
        <attribute name="fullname" alias="OptionalOwners" />
      </link-entity>
      <link-entity name="account" from="accountid" to="partyid" link-type="outer" alias="optionalaccount">
        <attribute name="name" alias="OptionalAccount" />
      </link-entity>
    </link-entity>
    <filter type="and">
      <condition attribute="scheduledstart" operator="on-or-after" value="@FromDate" />
      <condition attribute="scheduledstart" operator="on-or-before" value="@ToDate" />
    </filter>
  </entity>
</fetch>

Thanks for the suggestion. When I try that I get a error. The error is: "An error occured while reading data from the query set. (Microsoft.Crm.Reporting.DataExtension.Common.Fetch)" — , Nov 21 '11 at 20:26
I had to remove all the `link-entity`s and attributes that reference your organization's custom fields (`new_contactperson`, `new_coldernotes`, etc.) before the query was stripped down to its generic elements that should execute in every Dynamics environment. Try doing that first, and if that doesn't work, let us know if there's any additional information available in the exception. — Peter Majeed, Nov 21 '11 at 20:51

FetchXML OR Condition

1 Answers1