1

I have researched this topic, but I can't find a solution for multiple list items needing $expand:.

The script is below, with the following fields:

  1. Title: text/Name of the Company
  2. CompanyStatus: choice/Datatable is filtered from this, so it renders properly
  3. CompanyClass: choice/This shows up as [object object]
  4. Modified: date/This shows up as unfriendly date format
  5. EditorId: user/This shows up as a numeric value

     <script type="text/javascript">
    
    function LoadTitles(companystatus)
    {
        var call = $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('Companies')/items?"+
    "$select=Title,CompanyStatus,CompanyClass,Modified,EditorId&$filter=(CompanyStatus eq '"+companystatus+"')&$top=5000",
        type: "GET",
        dataType: "json",
        headers: {
            Accept: "application/json;odata=verbose"
        }
    
    });
    call.done(function (data,textStatus, jqXHR){
        $('#example').dataTable({
            "bDestroy": true,
            "bProcessing": true,
            "aaData": data.d.results,
            "aoColumns": [
                { "mData": "Title" },
                { "mData": "CompanyStatus" },
        { "mData": "CompanyClass" },
                { "mData": "Modified" },
                { "mData": "EditorId" }
            ]
          });
    });
    call.fail(function (jqXHR,textStatus,errorThrown){
        alert("Error retrieving Tasks: " + jqXHR.responseText);
    });
    }
    </script>
    
1

Assuming CompanyClass is a multi-valued choice field, the following example demonstrates how to retrieve the specified field values.

First of all, change your query to request Editor properties such as Title (SharePoint REST by default returns User field id, in your case EditorId field )

url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('Companies')/items?"+
"$select=Title,CompanyStatus,CompanyClass,Modified,Editor/Title&$filter=(CompanyStatus eq '"+companystatus+"')&$top=5000&$expand=Editor"

Then apply the following changes to data source and mapping for .dataTable:

var tableData = data.d.results.map(function(item){
        item['EditorTitle'] = item['Editor']['Title']; //extract Editor Title
        item['CompanyClassTitles'] = item['CompanyClass'].results.join(','); //concat multiple choice field values
        return item;
    }); 

    $('#example').dataTable({
        "bDestroy": true,
        "bProcessing": true,
        "aaData": tableData,
        "aoColumns": [
            { "mData": "Title" },
            { "mData": "CompanyStatus" },
            { "mData": "CompanyClassTitles" },
            { "mData": "Modified" },
            { "mData": "EditorTitle" }
        ]
      });
  • Hi, apologies for late reply, but thak you for responding and helping me. Does the var tableData script above replace the 'call done' function or go under it? – TheJDScott Sep 24 '15 at 13:44
  • Hi, second option: goes under it. The intention of tableData is to prepare proper data for dataTable – Vadim Gremyachev Sep 24 '15 at 13:48
  • Thanks -- I'm going to try it out and get back to you asap – TheJDScott Sep 24 '15 at 14:13
  • I got the following error: "DataTables warning (tableid = 'example'): Requested unknown parameter 'EditorId' from the data source". Nothing has changed in the results, and [object][object] is still there, and nothing is coming up under the 'Last Modified' column. – TheJDScott Sep 24 '15 at 14:55
  • Have you noticed i changed .dataTable intitialization (column names)? – Vadim Gremyachev Sep 24 '15 at 14:58
0

You need to expand the fields that point to users, according your call:

$select=Title,CompanyStatus,CompanyClass,Modified,Editor/Id,Editor/Title[or Name],Editor/EMail&expand=Editor

Whit that you will get all the information of the user.

For the field CompanyClass it is an array and you can get the values like this:

data.d.results[0].Choices.results;

Where results is the array. And finally the dates, I use moment.js to format it.

  • Thank you for the quick response -- unfortunately, it didnt't work, and I keep getting errors:It's saying the expression isn't valid. Also, Where do I put the dad.d result part in the script? – TheJDScott Sep 17 '15 at 14:22
  • Please paste your rest uri to check it out – Marco Sep 17 '15 at 14:26
  • Here is the updated REST, as per your edits url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('Companies')/items?"+"$select=Title,CompanyStatus,CompanyClass,Modified,Editor/Id,Editor/Title[or Name],Editor/EMail& $expand=Editor& $filter=(CompanyStatus eq '"+companystatus+"')&$top=5000", – TheJDScott Sep 17 '15 at 17:13
  • Here is the updated d.dat part, as per you edits "aaData": data.d.results, data.d.results[0].Choices.results; "aoColumns": [ – TheJDScott Sep 17 '15 at 17:15
  • I'm pretty sure this is a mess-up on my end, so thank you for being patient and helping me. – TheJDScott Sep 17 '15 at 17:15
0

FYI - the above join for multiple choice didn't work for me on SP 2010 as results array contained an object with a "Value" properties that held the value.

I replaced:

item['CompanyClassTitles'] = item['CompanyClass'].results.join(','); //concat multiple choice field values

with

item['CompanyClassTitles'] = item['CompanyClass'].results.map(function(elem){
    return elem.Value;
}).join(",");

Not sure if the same applies to SP 2013 but wanted to mention it in case it could be useful to someone out there...

  • Thanks for taking the time to answer. Very much appreciated. – TheJDScott Jan 11 '16 at 18:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.