I have two lists with look up column. How do i join two lists using REST API call.
Thanks in advance
1 Answer
You use the $expand query option.
For example, you have two lists: ListA and ListB. ListA has two fields: Field1 and Field2. ListB has three fields: FieldA, FieldB and LookupField. LookupField looks up to ListA. REST query should be as such:
/_api/web/lists/GetByTitle('ListB')/items?$select=FieldA,FieldB,LookupField/Field1,LookupField/Field2&$expand=LookupField
You select the fields from the list by adding their internal name to the $select query option, and you select fields from the lookup list by using the format LookupField/FieldFromLookupList, and then you add the lookup field to the $expand query option.
-
How do i get current list fields with $expandVijay– Vijay2015-06-11 12:04:21 +00:00Commented Jun 11, 2015 at 12:04
-
For fields from the child list (the one with the lookup field), simply add them to the
$selectportion. For fields from the parent (the lookup list), add them to the$selectportion in the formatLookupField/FieldFromLookupList, whereLookupFieldis the internal name of the lookup field in the child list (all field names must be the internal names).wjervis– wjervis2015-06-11 12:06:57 +00:00Commented Jun 11, 2015 at 12:06 -
Means correct me if am wrong, I can directly specify the child list field name in $select portion, If i need parent list fields i should use lookupfield/fieldname right ?Vijay– Vijay2015-06-11 12:23:52 +00:00Commented Jun 11, 2015 at 12:23
-
Fields from the list that contains the lookup field can be directly specified in the $select portion. Fields from the list being looked up to have to be specified using
lookupfield/fieldname.wjervis– wjervis2015-06-11 12:33:53 +00:00Commented Jun 11, 2015 at 12:33 -
See edits, I hope it explains it better.wjervis– wjervis2015-06-11 12:54:48 +00:00Commented Jun 11, 2015 at 12:54