Dynamics 2011 SOAP - RetrieveMultiple return only single linked record

Question

Using the below SOAP I am able to retrieve all contacts from a Dynamics 2011 IFD install which have been modified since a specific date. Additionally it returns some fields from a linked 'Membership' record.

However where multiple membership records exist it is returning all I need to return just the latest membership record (where the renewal date field has the highest date, and the membership payment date field (a datetime) is not empty).

Can anyone help with how to adjust the below so that it does not return a record for each contact's membership record - just one record per contact with only the latest membership record fields.

          <s:Body>
      <RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
      <query i:type="a:QueryExpression" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
        <a:ColumnSet>
          <a:AllColumns>false</a:AllColumns>
          <a:Columns xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
            <b:string>contactid</b:string>
            <b:string>firstname</b:string>
            <b:string>lastname</b:string>
          </a:Columns>
        </a:ColumnSet>
        <a:Criteria>
          <a:Conditions />
          <a:FilterOperator>And</a:FilterOperator>
          <a:Filters>
            <a:FilterExpression>
              <a:Conditions>
                <a:ConditionExpression>
                  <a:AttributeName>createdon</a:AttributeName>
                  <a:Operator>GreaterThan</a:Operator>
                  <a:Values xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                    <b:anyType i:type="c:dateTime" xmlns:c="http://www.w3.org/2001/XMLSchema">2016-08-23T01:03:34+01:00</b:anyType>
                  </a:Values>
                </a:ConditionExpression>
                <a:ConditionExpression>
                  <a:AttributeName>modifiedon</a:AttributeName>
                  <a:Operator>GreaterEqual</a:Operator>
                  <a:Values xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                    <b:anyType i:type="c:dateTime" xmlns:c="http://www.w3.org/2001/XMLSchema">2016-08-23T01:03:34+01:00</b:anyType>
                  </a:Values>
                </a:ConditionExpression>
              </a:Conditions>
              <a:FilterOperator>Or</a:FilterOperator>
              <a:Filters />
            </a:FilterExpression>
          </a:Filters>
        </a:Criteria>
        <a:Distinct>true</a:Distinct>
        <a:EntityName>contact</a:EntityName>
        <a:LinkEntities>
          <a:LinkEntity>
            <a:Columns>
              <a:AllColumns>false</a:AllColumns>
              <a:Columns xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                <b:string>statecode</b:string>
                <b:string>new_cancelledmembership</b:string>
                <b:string>py3_membershipproduct</b:string>
                <b:string>new_reasoncancelled</b:string>
                <b:string>new_cancelleddate</b:string>
                <b:string>py3_renewaldate</b:string>
                <b:string>modifiedon</b:string>
              </a:Columns>
            </a:Columns>
            <a:EntityAlias i:nil="true" />
            <a:JoinOperator>Inner</a:JoinOperator>
            <a:LinkCriteria>
              <a:Conditions />
              <a:FilterOperator>And</a:FilterOperator>
              <a:Filters>
                <a:FilterExpression>
                  <a:Conditions>
                    <a:ConditionExpression>
                      <a:AttributeName>py3_membershippaymentdate</a:AttributeName>
                      <a:Operator>NotNull</a:Operator>
                    </a:ConditionExpression>
                  </a:Conditions>
                  <a:FilterOperator>Or</a:FilterOperator>
                </a:FilterExpression>
              </a:Filters>
            </a:LinkCriteria>
            <a:LinkEntities />
            <a:LinkFromAttributeName>contactid</a:LinkFromAttributeName>
            <a:LinkFromEntityName>contact</a:LinkFromEntityName>
            <a:LinkToAttributeName>py3_member</a:LinkToAttributeName>
            <a:LinkToEntityName>py3_membership</a:LinkToEntityName>
            <a:Orders>
                <a:OrderExpression>
                  <a:AttributeName>py3_renewaldate</a:AttributeName>
                  <a:OrderType>Ascending</a:OrderType>
                </a:OrderExpression>
            </a:Orders>
          </a:LinkEntity>
        </a:LinkEntities>
        <a:PageInfo>
          <a:Count>0</a:Count>
          <a:PageNumber>0</a:PageNumber>
          <a:PagingCookie i:nil="true" />
          <a:ReturnTotalRecordCount>false</a:ReturnTotalRecordCount>
        </a:PageInfo>
        <a:NoLock>false</a:NoLock>
      </query>
    </RetrieveMultiple>
    </s:Body>

Answer 1

From what I understand, you are trying to limit the link entity count to 1? If so, it is not possible. Link-entity Top/Count is ignored returning all the records which satisfy the join criteria (inner/outer).

You will have to invert your query to find the top 1 "Membership" record by ordering on "Renewal Date" and filtering on "ContactId" and use "Contact" as a link-entity, this will ensure only 1 "Membership" entity is ever returned.