Sharepoint REST Query including a Person Reference

Mon, 09 Mar 2015 13:21:32 GMT

I posted this as a sub-questions to this discussion: http://community.qlik.com/message/511818#511818

But I thought it might be valuable as a stand-alone.

I am using the REST service successfully but have run in to a bit of a snag. I can get all the data from the primary list without issue. But when I try to select a data type "nested" data, that data is not returned. I do not get an error, but the field is simply blank. Below is a sample dealing specifically with a field called AssignedTo which is a person reference field. The bolded field (AssignedTo/Title) is coming back blank.

CorrectiveActions:

LOAD

[content/properties/ID] as [%%Key_CorrectiveActionId],

[content/properties/Solution] as Solution,

[content/properties/Problem] as Problem,

[content/properties/Resolved] as Resolved,

[content/properties/LPA_QuestionId] as [%%Key_LPA_QuestionId],

[content/properties/AssignedTo/Title] as [CorrectiveActionAssignee]

FROM [http://[MYSHAREPOINTLOCATION]/_api/web/lists/getbytitle('Corrective%20Actions')/items?$top=10000&$select=Id,Solution,LPA_QuestionId,Problem,Resolved,AssignedTo/Title&$expand=AssignedTo] (XmlSimple, Table is [feed/entry]);

This query returns everything I want (Solution, Problem, Resolved, etc.) But theCorrectiveActionAssignee field is coming back blank. If I take the http query and paste it into my browser, all the data is coming back as expected including the person reference with Title (name). Do you have any idea why?

I've tried [content/properties/AssignedTo/properties/Title] and that doesn't work either.

Re: Sharepoint REST Query including a Person Reference

Mon, 09 Mar 2015 15:03:23 GMT

It seems the complication is in the format of the responses. Looks like [content/properties] is used to navigate the returned values in QlikView to find each data element. However, when an $expand= statement is used, the resulting structure for the expanded data must be different. I suppose the key question I'm asking here is how to access the Title of the AssignedTo data attribute - clearly [content/properties/AssignedTo/Title] is not mapping to the structure. I've picked apart the data response from sharepoint and a typical result would look like the image below.

What I find interesting about this structure is that there are two "sections" that contain data. The first section is in a path:

<entry>

<link>

<m:inline>

<entry>

<title>

This is the path that contains my Auditor's name (the "nested" or "relational" value to my list.

The primary data on the list is in a path which seems to be reflected in the accessor in the QlikView file: [content/properties]:

<entry>

contet

topic Re: Sharepoint REST Query including a Person Reference in QlikView

Sharepoint REST Query including a Person Reference

Re: Sharepoint REST Query including a Person Reference