Create tree-view for 3 sharepoint listd which are connected using lookup fields

Question

I have the below 3 SharePoint lists, and using the REST APIs i am getting the data as follow:-

1- List named named "Department", here is the json for the items

"value":[
{"odata.type":"SP.Data.DepartmentsListItem","odata.id":"5a56de12-7ea6-433f-9f94-716ae1d80087","odata.etag":"\"1\"","odata.editLink":"Web/Lists(guid'cd1c4944-b3a4-4909-84ce-87aa023055af')/Items(1)","FileSystemObjectType":0,"Id":1,"ServerRedirectedEmbedUri":null,"ServerRedirectedEmbedUrl":"","ID":1,"ContentTypeId":"0x010086D7A470A7B9764BA4DC0A72B1BEC8DD","Title":"IT","Modified":"2021-01-19T22:20:02Z","Created":"2021-01-19T22:20:02Z","AuthorId":6,"EditorId":6,"OData__UIVersionString":"1.0","Attachments":false,"GUID":"ebb7b219-6281-4879-b88b-8c10e70a1d1b","ComplianceAssetId":null},
{"odata.type":"SP.Data.DepartmentsListItem","odata.id":"1c8dc258-2cef-4abe-8495-c83f99af26fc","odata.etag":"\"1\"","odata.editLink":"Web/Lists(guid'cd1c4944-b3a4-4909-84ce-87aa023055af')/Items(2)","FileSystemObjectType":0,"Id":2,"ServerRedirectedEmbedUri":null,"ServerRedirectedEmbedUrl":"","ID":2,"ContentTypeId":"0x010086D7A470A7B9764BA4DC0A72B1BEC8DD","Title":"HR","Modified":"2021-01-19T22:20:06Z","Created":"2021-01-19T22:20:06Z","AuthorId":6,"EditorId":6,"OData__UIVersionString":"1.0","Attachments":false,"GUID":"f43335c5-2a9d-45a0-977a-fbdb20df4540","ComplianceAssetId":null},
{"odata.type":"SP.Data.DepartmentsListItem","odata.id":"6d62a89e-6767-4925-b7ee-6ac5bb09fc6f","odata.etag":"\"1\"","odata.editLink":"Web/Lists(guid'cd1c4944-b3a4-4909-84ce-87aa023055af')/Items(3)","FileSystemObjectType":0,"Id":3,"ServerRedirectedEmbedUri":null,"ServerRedirectedEmbedUrl":"","ID":3,"ContentTypeId":"0x010086D7A470A7B9764BA4DC0A72B1BEC8DD","Title":"3rd Parties","Modified":"2021-01-19T22:30:45Z","Created":"2021-01-19T22:30:45Z","AuthorId":6,"EditorId":6,"OData__UIVersionString":"1.0","Attachments":false,"GUID":"7a271fc6-b329-4756-be2a-7098f1048aa2","ComplianceAssetId":null}
]

2- List named "Category", which have a foreign key named "AssetCategoryDepartmentId" to the "Department" list, here is the json for all the items:-

"value":[
{"odata.type":"SP.Data.AssetCategoryListItem","odata.id":"262cbb2a-9edd-4b0a-84de-8b93e99dbc3b","odata.etag":"\"1\"","odata.editLink":"Web/Lists(guid'6220cbc6-f462-430f-88d4-b248922dee09')/Items(1)","FileSystemObjectType":0,"Id":1,"ServerRedirectedEmbedUri":null,"ServerRedirectedEmbedUrl":"","ContentTypeId":"0x010069C0683E79F8434DAB09B11838AACD83004A5A3F98639F2E44A772F8A25D075EDE","Title":"Servers","ComplianceAssetId":null,"AssetCategoryDepartmentId":1,"ID":1,"Modified":"2021-01-19T22:31:24Z","Created":"2021-01-19T22:31:24Z","AuthorId":6,"EditorId":6,"OData__UIVersionString":"1.0","Attachments":false,"GUID":"4421bcc8-2e3e-4eca-a096-2534ae77bb74"}
]

3- list named "Asset", which have a foreign key name "AssetCategoryId" to the Category list, here is the json for the items:-

"value":[
{"odata.type":"SP.Data.AssetsListItem","odata.id":"465ee018-a0b5-48ad-9e9f-dd87d885a427","odata.etag":"\"5\"","odata.editLink":"Web/Lists(guid'fabf8117-9825-4488-aa79-f3cc47e92409')/Items(5)","FileSystemObjectType":0,"Id":5,"ServerRedirectedEmbedUri":null,"ServerRedirectedEmbedUrl":"","ContentTypeId":"0x0100D485035FD8061142A7B8C1A667BDDD6400F0D86F65CE31D04B8EB32E6ED36966EA","Title":"First test asset","ComplianceAssetId":null,"Availability":"4","Integrity":"3","Confidentiality":"2","AssetValuation":"","DateofEvaluation":"2020-12-18T00:00:00Z","LinkToRiskValue":{"Description":"?FilterField1=RiskValueAsset&FilterValue1=First test asset&FilterType1=Lookup","Url":"?FilterField1=RiskValueAsset&FilterValue1=First test asset&FilterType1=Lookup"},"AssetNumber":"Asset 5","AssetCategoryId":null,"ID":5,"Modified":"2021-01-19T22:15:26Z","Created":"2020-12-18T10:43:40Z","AuthorId":13,"EditorId":6,"OData__UIVersionString":"1.0","Attachments":false,"GUID":"d8f8b0d6-d475-4578-9e36-9c503097f21b"},
]

now i want to build a tree-hierarchy using the tree , the hierarchy will show all the Departments >> under each Department to list the Categories >> under each category to show all the assets, but i am not sure how i need to join the above json data and submit it to the dataSource inside the https://gijgo.com/tree?

Thanks

Answer 1

Hi @john john ,

SharePoint Rest API didn't support to join and merge 3 list data relationship into one summary json data struct.

A workaround is to create all lookup field into a same list, like this:

Then refer this blog to build Tree View with Jsom code and jstree.js:

Display the Category list as a tree-view in Blog Site SharePoint

Here is a similiar question for your reference:

dynamic simple hierarchial tree view using Jquery to display sharepoint list items-Kindly help

Thanks
Best Regards

---

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.