Sharepoint 2013 lookup relationship

Question

We have 2 lists 1- Assets: list of IT assets ex: laptop 2- Employees I added a lookup column under the [Assets List] referring to the employee name in [Employees List]. Then I created a workflow using the designer which will take the Asset tag# that has been assigned to an employee and added it under the employee info in specific column. So we can do tracing from both lists. Up to this point everything works great but I am facing one issues.

The workflow gives error when adding more than 1 employee using the lookup. How to fix this? OR is there a way other than a workflow to show the lookup relationship between an Asset and an employee lists (dynamic)? So when we delete the lookup value from one list it will be deleted from the other enter image description here

Answer 1

If I understand your issue correctly you want to create a many to many relation similar to relation databases in SharePoint list.

And your ultimate goal is ability to query assets for specific employee or find all employees with specific asset.

If above is correct what you can do is create three lists:

1. Assets -This list will contain all unique asset information
2. Employees - This list will store the employee information
3. Assets Own by Employee - This will store relation between assets and employees. So list will contain multiple entries for one assets and or employee. Your workflow should update this list.

Then you can use SharePoint 2013 REST API with expand operator to do a query data from all the list in single query. Here are some links for the same:

https://sharepoint.stackexchange.com/a/111483

https://sharepoint.stackexchange.com/a/118659

http://www.andrewconnell.com/blog/Applying-Filters-to-Lookup-Fields-with-the-SP2013-REST-API

I hope this helps.