.retrieve returning lookup to another entity

Apr 23, 2013 at 12:10 AM
I've poked around and can't seem to find an example of using CrmRestKit to return a lookup type (guid) from another entity... for example, I have added a "defaultTeam" attribute to the Contact entity as a lookup type. I want to get that GUID for the current user and then use it to do a query for other SystemUsers with the same DefaultTeam.

when I do the CrmRestKit.Retrieve, the returning data object works for all the data types other than Lookup. I think it's returning an object of some kind.... but I can't get to the guid.

any help would be appreciated!
Coordinator
Apr 23, 2013 at 8:10 AM
Edited Apr 23, 2013 at 8:39 AM
Hello Pezzelle,

you are looking for an "Expand Query".

In the unit-test you will find an example how to use the method "CrmRestKit.ByExpandQuery":
var filter = "AccountId eq guid'" + ctx.accountSet[0] + "'",
    columns = ['Name', 'accountleads_association/FullName'],
    relName = 'accountleads_association';

 CrmRestKit.ByExpandQuery( 'Account', columns, relName, filter, false ).then( function ( data ) {

        var set = data.d.results[0].accountleads_association.results;

        // Assert - n:m relationship exists
        QUnit.equal( set.length, 1, 'Associate failed' );

        // continue with the other tests
        QUnit.start();

 }, onRestError );
Hope this helps

Daniel
Coordinator
Apr 23, 2013 at 11:27 AM
Hello Pezzelle,

I tried to create a sample for you and it turns out that something is odd:

When using the expand-query-option for the contact entity everything is fine:
http://xxx/xrmservices/2011/OrganizationData.svc/ContactSet?$select=contact_customer_accounts/Name&$expand=contact_customer_accounts&$filter=ContactId eq guid'32207A0D-C1A2-E211-8E49-001CC0276A17'
But for the account entity an error occures:
 http://xxx/XRMServices/2011/OrganizationData.svc/AccountSet?$select=Name,account_parent_account/Name&$expand=account_parent_account&$filter=AccountId eq guid'8E2915FC-F8AB-E211-841D-001CC0276A17'
I started a discussion:
Dynamics Forum

Regards,

Daniel
Apr 23, 2013 at 7:04 PM
Daniel


thanks so much for responding and starting that discussion. and thanks for building a great tool to simplify oData routines.

I tried the ByExpandQuery as you suggested, but i'm not sure I have all the elements correct. to be clear, all I want to do is capture the value of a field called "stat_DefaultTeam" for the currently logged in user on the SystemUser entity. that field happens to be a lookup to the Team entity. so, what I want is the TeamID. once I capture that piece of data, I want to get all the SystemUsers who also have that team on their SystemUser record. that should be pretty easy.

the code below runs, but returns a null reference only for the related entity. the columns from the SystemUser entity are fine (even though I don't need them) this could be because I don't really understand how to construct the strings or the exact meaning of each element... or that something isn't working in the CrmRestKit wrapper...
function contactLoaded(){
var myTeam;
var myID = Xrm.Page.context.getUserId();
alert(myID.substring(1,37));

var filter = "SystemUserId eq guid'" + myID + "'",
    columns = ['LastName','FirstName','stat_team_systemuser_DefaultTeam/TeamID'],
    relName = 'stat_team_systemuser_DefaultTeam';

alert(filter);
alert(columns);
alert(relName);

CrmRestKit.ByExpandQuery('SystemUser', columns, relName, filter, false )
.fail(function(){alert('call failed');})
.then( function ( data ) {

//at this point, "set" becomes null or invalid
//I was expecting data.d.results[0].stat_team_systemuser_DefaultTeam.results[0].TeamID to have the GUID
        var set = data.d.results[0].stat_team_systemuser_DefaultTeam.results;
        alert(set.length);
})
sorry if these questions are annoying!
Coordinator
Apr 24, 2013 at 1:39 PM
Hi Pezzelle,

don’t worry it is not annoying.

Just to confirm that I understood your requirements correctly:
  • You have to load all team-members of a certain team
  • You identify the correct team by loading the "Default Team" id of the current-user
In case these are the requirements I would recommand a FetchXML based approch, because with ODATA you would have to perform at least two requests and with fetch-xml only one.

You could construct the query with the advanced find and download fetchxml query. I gave it a try and this should be the query (you have to change the attribute Name from "new_defaultteamid" to "stat_defaultteam"):
 <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="systemuser">
    <attribute name="fullname" />
    <attribute name="title" />
    <attribute name="address1_telephone1" />
    <attribute name="businessunitid" />
    <attribute name="siteid" />
    <attribute name="systemuserid" />
    <order attribute="fullname" descending="false" />
    <link-entity name="teammembership" from="systemuserid" to="systemuserid" visible="false" intersect="true">
      <link-entity name="team" from="teamid" to="teamid" alias="ac">
        <link-entity name="systemuser" from="new_defaultteamid" to="teamid" alias="ae">
          <filter type="and">
            <condition attribute="systemuserid" operator="eq-userid" />
          </filter>
        </link-entity>
      </link-entity>
    </link-entity>
  </entity>
</fetch>
To execute the query via JavaScript you could use the CrmFetchKit. This library works just like the CrmRestKit.

Hope this helps

Daniel
Coordinator
Apr 24, 2013 at 2:10 PM
Hello again,

in case you prefer a pure CrmRestKit solution you would need two requests:
  • Get the default-team-id: For this you do not need a Expand-query, a simple retrieve for the systemuser entity will do the job.
  • Use the expand query (just like in the sample with the lead-query). You have to find the relationship name for the N:M relationship between team and user
Regards,

Daniel