Combining Sharepoint Lists

Hi @gsol 

Hopefully I understand your requirement correctly, I build 2 lists to match your examples:

First is a list of employees with contracts as a lookup (I added the contract days as part of the lookup)

Then a contract matrix showing the contract type and its days:
​

Depending on the number of contracts you are talking you might need to have this workflow run multiple times as I think you could only process 10k actions so it is best to keep the limit to run 1k records at a time as we will need some looping to do this.

Lets start by using a query list action to get 1k items that have no contract end date specified and store them in an object called Unchanged Items:
 

The reason I added the lookup field into the main list is because this is something that returns when you perform a query so you can see here Contract Matrix: Contract Length is available, this will save us having to perform a lookup to get this value: 
 

So lets add the Unchanged Items into a loop for each:

 

If you check the output variable for the lookup field it will be a text string, we need to change that into a number to use it in the next action so lets use a convert a value action, storing that in a temp variable:

 

Now we have that value as a number we can use the Add time to date action to add the new Temp Days value to the contract start date, storing the output in a new date time variable named Temp Date:
 

Now all we need to do is update that item with the new calculated date value ensuring that we set the condition to update the item with the ID in the current loop:
 

It is not required but I added a log to instance to show the workflow running:

 

It might take a time to run the workflow and you may need to run it multiple times but once completed you should have a finished list. see below the workflow instance:

 

Caught it before it finished all items:

But this is the finished output:

Here is the workflow export key if you would like it, please be aware it does expire after a few days.

kLBP56WnKiLshNSGTf2VYKqbkxtYK4DrbjkYcnUTd8ArXsSbn

Jake

Hello Jake thank you!

However, my list already exists and has no lookup, can i still use your example and adapt it to my list where there are no lookup fields? 

Thank you

hi @gsol 

Yes but you will need to alter the workflow slightly and it will make it run slower. If you only have a fixed number of contracts then it might be better to load the values into the workflow its self or into tables inside NAC as this will reduce the number of API calls made to sharepoint.

If not, inside the loop you will need to add another query a list action to check your contract list for an item with the matching contract, the output of that will contain the days you can use in the add date action.

Jake. 

Yes, the issue being that my list already exists and has over 5k entries, i don’t think i will be able to replace every Contract Type item by a Lookup Item