Skip to main content
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sharepoint REST Query including a Person Reference

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&$se...] (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.

1 Reply
Not applicable
Author

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>

                    <content>

                         <properties>

                              <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>

     <content>

          <properties>

3-9-2015 10-59-40 AM.pngcontet