locked
Retrieving OptionSetValue via Fetch XML Request RRS feed

  • Question

  • Hey All,

    I've looked around and found some responses for c# api based and other info but nothing directly for fetch xml requests. I'm retrieving a list of contacts with no issues via the fetch xml on dynamics 2011. I'm retrieving a field which is called dba_title (the contacts title).

    The value is an integeger which is obviously a foreign key to a OptionSetValue. I'm still not 100% sure how this works but i'm presuming they are just generalied key value associative entities.All I want to be able to do is retrieve the value of the link instead of the key. Code is below. I've tried a few different linking entites with no luck.

    Any thoughts appreciated..

    <s:Body>
                        <Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">
                                <request i:type="b:RetrieveMultipleRequest" xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
                                        <b:Parameters xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
                                                <b:KeyValuePairOfstringanyType>
                                                        <c:key>Query</c:key>
                                                        <c:value i:type="b:FetchExpression">
                                                                <b:Query>&lt;fetch mapping="logical" page="'.$page.'" count="'.$count.'" version="1.0" paging-cookie="'.$_SESSION['cookie'].'" &gt;&#xD;
                                                                        &lt;entity name="contact"&gt;&#xD;
                                                                            &lt;attribute name="contactid" /&gt;&#xD;
                                                                            &lt;attribute name="firstname" /&gt;&#xD;
                                                                            &lt;attribute name="lastname" /&gt;&#xD;
                                                                            &lt;attribute name="emailaddress1" /&gt;&#xD;
                                                                            &lt;attribute name="mobilephone" /&gt;&#xD;
                                                                            &lt;attribute name="dba_title" /&gt;&#xD;
                                                                            &lt;attribute name="telephone2" /&gt;&#xD;
                                                                            &lt;attribute name="telephone1" /&gt;&#xD;
                                                                            &lt;attribute name="address1_line1" /&gt;&#xD;
                                                                            &lt;attribute name="address1_line2" /&gt;&#xD;
                                                                            &lt;attribute name="address1_city" /&gt;&#xD;
                                                                            &lt;attribute name="address1_stateorprovince" /&gt;&#xD;
                                                                            &lt;attribute name="address1_postalcode" /&gt;&#xD;
                                                                            &lt;attribute name="address1_country" /&gt;&#xD;
                                                                            &lt;link-entity name="listmember" from="entityid" to="contactid" intersect="true"&gt;
                                                                                &lt;filter type="and"&gt;
                                                                                    &lt;condition attribute="listid" operator="eq" value="'.$listId.'" /&gt;
                                                                                    &lt;condition attribute="entitytype" operator="eq" value="2" /&gt;
                                                                                &lt;/filter&gt;
                                                                            &lt;/link-entity&gt;
                                                                        &lt;/entity&gt;&#xD;
                                                                        &lt;/fetch&gt;
                                                                </b:Query>
                                                        </c:value>
                                                </b:KeyValuePairOfstringanyType>
                                        </b:Parameters>
                                        <b:RequestId i:nil="true"/><b:RequestName>RetrieveMultiple</b:RequestName>
                                </request>
                        </Execute>
                  </s:Body>

    Tuesday, September 3, 2013 3:02 AM

All replies

  • Hi Wes,

    The result of a RetrieveMultipleRequest will have a FormattedValue node containing the textual values of all Option Set field in it which you can use to pull the textual value out of without having to make a second call. The result of the following RetrieveMultiple request

    <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
      <s:Body>
        <Execute xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
          <request i:type="a:RetrieveMultipleRequest" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
            <a:Parameters xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
              <a:KeyValuePairOfstringanyType>
                <b:key>Query</b:key>
                <b:value i:type="a:QueryExpression">
                  <a:ColumnSet>
                    <a:AllColumns>false</a:AllColumns>
                    <a:Columns xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                      <c:string>contactid</c:string>
                      <c:string>gendercode</c:string>
                    </a:Columns>
                  </a:ColumnSet>
                  <a:Criteria>
                    <a:Conditions>
                      <a:ConditionExpression>
                        <a:AttributeName>contactid</a:AttributeName>
                        <a:Operator>Equal</a:Operator>
                        <a:Values xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
                          <c:anyType i:type="d:string" xmlns:d="http://www.w3.org/2001/XMLSchema">{4a7f3e50-7614-e311-992d-00155db7e812}</c:anyType>
                        </a:Values>
                      </a:ConditionExpression>
                    </a:Conditions>
                    <a:FilterOperator>And</a:FilterOperator>
                    <a:Filters />
                  </a:Criteria>
                  <a:Distinct>false</a:Distinct>
                  <a:EntityName>contact</a:EntityName>
                  <a:NoLock>false</a:NoLock>
                </b:value>
              </a:KeyValuePairOfstringanyType>
            </a:Parameters>
            <a:RequestId i:nil="true" />
            <a:RequestName>RetrieveMultiple</a:RequestName>
          </request>
        </Execute>
      </s:Body>
    </s:Envelope>

    would be 

    <s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
      <s:Body>
        <ExecuteResponse xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services">
          <ExecuteResult i:type="a:RetrieveMultipleResponse" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
            <a:ResponseName>RetrieveMultiple</a:ResponseName>
            <a:Results xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
              <a:KeyValuePairOfstringanyType>
                <b:key>EntityCollection</b:key>
                <b:value i:type="a:EntityCollection">
                  <a:Entities>
                    <a:Entity>
                      <a:Attributes>
                        <a:KeyValuePairOfstringanyType>
                          <b:key>contactid</b:key>
                          <b:value i:type="c:guid" xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/">4a7f3e50-7614-e311-992d-00155db7e812</b:value>
                        </a:KeyValuePairOfstringanyType>
                        <a:KeyValuePairOfstringanyType>
                          <b:key>gendercode</b:key>
                          <b:value i:type="a:OptionSetValue">
                            <a:Value>1</a:Value>
                          </b:value>
                        </a:KeyValuePairOfstringanyType>
                      </a:Attributes>
                      <a:EntityState i:nil="true"/>
                      <a:FormattedValues>
                        <a:KeyValuePairOfstringstring>
                          <b:key>gendercode</b:key>
                          <b:value>Male</b:value>
                        </a:KeyValuePairOfstringstring>
                      </a:FormattedValues>
                      <a:Id>4a7f3e50-7614-e311-992d-00155db7e812</a:Id>
                      <a:LogicalName>contact</a:LogicalName>
                      <a:RelatedEntities/>
                    </a:Entity>
                  </a:Entities>
                  <a:EntityName>contact</a:EntityName>
                  <a:MinActiveRowVersion>-1</a:MinActiveRowVersion>
                  <a:MoreRecords>false</a:MoreRecords>
                  <a:PagingCookie>&lt;cookie page="1"&gt;&lt;contactid last="{4A7F3E50-7614-E311-992D-00155DB7E812}" first="{4A7F3E50-7614-E311-992D-00155DB7E812}" /&gt;&lt;/cookie&gt;</a:PagingCookie>
                  <a:TotalRecordCount>-1</a:TotalRecordCount>
                  <a:TotalRecordCountLimitExceeded>false</a:TotalRecordCountLimitExceeded>
                </b:value>
              </a:KeyValuePairOfstringanyType>
            </a:Results>
          </ExecuteResult>
        </ExecuteResponse>
      </s:Body>
    </s:Envelope>


    Note the 

    <a:FormattedValues>
      <a:KeyValuePairOfstringstring>
        <b:key>gendercode</b:key>
        <b:value>Male</b:value>
      </a:KeyValuePairOfstringstring>
    </a:FormattedValues>

    section which contains the textual value of the gendercode attribute of the contact.


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    • Proposed as answer by MJFPalmer Tuesday, September 3, 2013 9:14 AM
    Tuesday, September 3, 2013 9:13 AM
  • Thanks for that Michael. I had dumped out the Result but somehow missed spotting that :O

    Cheers

    Wes

    Wednesday, September 4, 2013 1:13 AM
  • Glad to help. Please mark the post as an answer to help others with this kind of query in future.


    Michael Palmer
    xRMPalmer     @MJFPalmer     Rockstar365

    Thursday, September 5, 2013 7:39 AM