1

I want join 2 custom lists in add-ins:

Data of Lists: QuanLyMembers.PhongBanId is ID value of QuanLyPhongBan

QuanLyPhongBan list:

enter image description here

QuanLyMembers list:

enter image description here

This is my code:

function getListMem() {
    //var hostWebContext = new SP.AppContextSite(clientContext, hostWebURL);
    var listStatus = clientContext.get_web().get_lists().getByTitle('QuanLyMembers');
    var camlQuery = new SP.CamlQuery();
    camlQuery.set_viewXml("<View><ViewFields><FieldRef Name='Id'/><FieldRef Name='Title'/>" +
        "<FieldRef Name='EmailContact'/><FieldRef Name='PhongBanName'/></ViewFields>" +
        "<Joins><Join Type ='INNER' ListAlias ='QuanLyPhongBan'><Eq><FieldRef Name='PhongBanId' RefType='Id' /><FieldRef List='QuanLyPhongBan' Name='ID' /></Eq>" +
        "<ProjectedFields><Field Name='PhongBanName' Type='Lookup' List='QuanLyPhongBan' ShowField='Title'/> </ProjectedFields></Join>" +
        "</Joins></View>");
    var Items = listStatus.getItems(camlQuery);
    clientContext.load(Items, 'Include(Title,EmailContact,Id,PhongBanName)');
    clientContext.executeQueryAsync(Function.createDelegate(this, function () {
      
        var oEnumerator = Items.getEnumerator();    
        while (oEnumerator.moveNext()) {           
            var id = oEnumerator.get_current().get_id();
            var title = oEnumerator.get_current().get_item('Title');
            var PhongBanName = oEnumerator.get_current().get_item('PhongBanName ');
            var EmailContact = oEnumerator.get_current().get_item('EmailContact');
           

        }
       

    }), Function.createDelegate(this, function (sender, args) {
        alert('request failed ' + args.get_message() + '\n' + args.get_stackTrace());

    }));
}

But it throws an error:

"Value does not fall within the expected range."

How can I join data from two custom lists in SharePoint add-ins?

1
  • Why aren't you using lookup column for this? Using lookup columns will make joining data from two lists easier. Jan 18 at 9:45
1

I think you have to create a lookup column for this.

Create a lookup column in QuanLyMembers list referencing to QuanLyPhongBan list.

Check below note from Microsoft:

There are requirements to keep in mind when creating list joins. You cannot join just any two lists, regardless of type. And if two lists can be joined, not just any primary and foreign field can be used as the "join on" pair of fields. The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list. For this reason, all joins mirror existing lookup relations between lists.

Source: List Joins and Projections

4
  • Because i creating 2 custom list in addins. so I don't know create a lockup column. How can create a lockup column?
    – D T
    Jan 18 at 10:14
  • 1
  • Thank you, If I use Lookup Columns, i can get 'get_lookupValue' of Lookup Columns. So will must not use CamlQuery. But When do we use JOIN?
    – D T
    Jan 19 at 2:35
  • Yes, if you are using lookup column then I guess no need to use joins in your case. Not completely sure but I think using JOIN will be ideal where you want to join more than two lists, like given in example here, they are joining five lists. Jan 19 at 4:54

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.