SharePoint 2013 API OData For Lookup Fields

When using SharePoint data lists as the backend for an application, we can leverage the use of REST and OData to fetch the information from a data list. When the list contains lookup fields, a normal GET operation on the list only returns the id from the lookup field which may not be enough for some cases. 

In the case that more information is needed from the lookup or reference field, we can leverage the OData $expand parameter. This parameter is used to expand the related data so that in addition of the id other information also becomes available. As an example, we can look at a simple Project data list which has a lookup field named Client. This field uses the Client list as reference.

Project List Fields (internal names)	Client List Fields (internal names)
id	Id
Name	Name
Client  (lookup to Client)	CodeProject

How to fetch the data:

Note: my.sharepoint.com should be replaced with a specific domain name.

https://my.sharepoint.com/_api/web/lists/getbytitle('project')/items

In this URL string, we are using the getbytitle API which allows us to query a list by name. The items route suffix indicates to fetch all the items with no explicit filters (where clause) and all the available fields. The resulting data contains the fields Id, Name, ClientId (where is this coming from?) and other fields that are created by the SharePoint platform.  For our purposes, our focus is only on our custom fields (Name, ClientId)

We can notice that there is a ClientId field, but the field that we defined is named Client. SharePoint by default returns the lookup field id using this format: field name + id. In this case, the name is ClientId which as we can guess is the id of the client record.

What about also getting the client name?

In the case that more lookup data is required as in the case of client name, we must use a couple of OData parameters to expand on the selection of the look up fields: $select, $expand

Parameter	Description
$select	Use this parameter to control what fields should be selected.
$expand	Use this field to indicate what lookup field should be expanded to get additional information from that field. Field to expand must be in the $select parameter.

As an example, our request can be changed to this: (no domain name listed)

_api/web/lists/getbytitle('project')/items?$select=Client/Name,ClientId,Name,Id&$expand=Client

URL Segments:

URL Segment	Description
_api/web/lists/getbytitle('project')/items	This is the API and route to query a list by name and retrieve the items (records)
$select=Client/Name,ClientId,Name,Id	OData request parameter to select fields by name
$expand=Client	OData request parameter to indicate what look up field should be expanded to get additional information.

When using the $expand query parameter, the $select query parameter must also be used, and the field to be expanded must be explicitly declared as shown on our example above. The result of this request returns the project name and id as well as client name and client id.  We should note the format used to get the client name, Client/Name. This format is what indicates that we want the Name field from the Client list.

Sample Result:

A sample of the response is shown below. The results array contains each record, for which we can find the selected fields as well as the client property (object) with the name property (client name).

Expanded Results:

As shown above, the USE of OData request parameters are very important when there is a need to get related/lookup data with one request.


Originally Posted on ozkary.com

When using SharePoint data lists as the backend for an application, we can leverage the use of REST and OData to fetch the information fr...

Read Article →

MSCRM Get User Information with JavaScript

When working with Dynamics CRM forms, we can use the XRM client object model to query the fields and controls on the page. A common field on every form is the current user id. This however only provides the internal system id or GUID. When we need more information about the user, we can use the ODATA web services to get additional information.

This script provides us with a helper function to enable us to find additional information about the current logged on user.

if (typeof (OGBIT) == "undefined") { OGBIT = {}; }

OGBIT.UserInfo = {   

    Get: function () {

        var userInfo = {};

        if (self.userName == '') {

        var serverUrl = window.location.protocol + "//" + window.location.host + "/" + Xrm.Page.context.getOrgUniqueName();

           

            var userId = Xrm.Page.context.getUserId();

            var req = serverUrl + "/xrmservices/2011/OrganizationData.svc/SystemUserSet(guid'{0}')?$select=EmployeeId,FullName,InternalEMailAddress,SystemUserId,DomainName".replace('{0}', userId);

            try {

                $.ajax({

                    type: "GET", contentType: "application/json; charset=utf-8", datatype:"json",async:false,

                    url: req,

                    beforeSend: function (XMLHttpRequest) {

                        XMLHttpRequest.setRequestHeader("Accept", "application/json");

                    },

                    success: function (data, textStatus, XmlHttpRequest) {

                        userInfo = data.d;

                        userInfo.status = true; //dynamic property

                    },

                    error: function (XmlHttpRequest, textStatus, errorObject) {

                        userInfo.status = false;

                        //add error handling

                    }

                });

            } catch (e) { }

        }

        return userInfo;

    }

}

The script first calls the getUserId method of the page context. This returns the system id which we can then use to make an ODATA request to get the additional user information. The ODATA query to get the user information has this format:

SystemUserSet(guid'{0}')?$select=EmployeeId,FullName,InternalEMailAddress,SystemUserId,DomainName

The SystemUserSet allows us to query the System User entity.  We query for the current user with the guid’{0}’ parameter. The $select query option is used to indicate what additional fields should be returned. Those fields are listed below:

Parameters:

Field Name	Description
EmployeeId	employee id
FullName	First and last name
InternalEMailAddress	Email
SystemUserId	Internal ID or GUID
DomainName	Username with this format: DomainName\Username

The following snippet can be used to test the script. With this function, we just call the Get method and assign the returning information into local variables.

function GetUserInfo() {

    if (typeof (OGBIT.UserInfo) != 'undefined') {

        var info = OGBIT.UserInfo.Get();

        if (info.status) {

            var empId = info.EmployeeId;

            var name = info.FullName;

            var email = info.InternalEMailAddress;

            var userName = info.DomainName;

            var id = info.SystemUserId;

        } else {

            alert('Unable to find user');

        }

    }

}

I hope this Script helps you get more understanding on how to interact with MS CRM.

When working with Dynamics CRM forms, we can use the XRM client object model to query the fields and controls on the page. A common field o...

Read Article →