Add calculated output based on 2 lists

[Krcmjaster]

I am have 2 SharePoint lists, one contains training list (multiple lines) and another survey results (one line per user). When an item is created in the survey results, I want to trigger a flow that will combine training list and results into a new list (based on the results I want to create a custom list of training for users).

Survey Results:

Name	OneNote	Excel	Word
name1	5	2	4

Training list

Training	Training Link	Skill
1	l1	Excel
2	l2	Word
3	l3	Excel

Output list (desired)

user (from survey)	training	Training Link	Skill	Priority (from Survey results)
name1	1	l1	Excel	2
name1	3	l3	Excel	2
Name1	2	l2	Word	4

The main thing would probably be to pivot survey results as they don't fit in the current form. Below would be the main goal in transforming the survey results and then I want to use sort of a Join or Lookup to combine it with the training list:

Name	Skill	Result
name1	OneNote	5
name1	Excel	2
name1	Word	4

Basically for each user that completes the survey I want to duplicate training list, add a column with their email for each row, and another column with the results form survey. One note would be that result could be duplicated. As there a multiple training for each skill

[Mark-Shenouda]

Hi @Krcmjaster 
Hope you are well, 

Just to make sure I got your inquiry correctly 
You want to combine the data of the two lists in a third list as shown in the third table ..
Is there a relation between these two lists? 

I do not see a trainingID in the survey results list, I believe we might need a relation so that we can get the survey information from the Training and add them in the third list 
Can you provide more information about this point.
Thanks 

[Krcmjaster]

@Mark Thanks for responding. Yes, combine the two and upend outputs to third. I don't have unique ID, I would connect them based on the Skill name. I have improved the initial question for easier understanding so feel free to check

[Mark-Shenouda]

Hi ,
So as long as there is one survey per skill , then we could try simple implementation as below 

1- When Survey response is created

2- You will have the fields value for each column (First List)

3- Use "Get Items" to get the Training by using the Filter Skill eq 'Excel' and limit rows to 1 
4- Now you have the Data of the excel training record , 
5- Use action "Create Item" to create the item in the third list , now you have the data of you need 

6- repeat step 3 to 5 , for the other skills 

Let me know if these steps are clear or you have other questions.

[Krcmjaster]

@Mark-Shenouda thanks. There might be something there. I don't have have one Skill Result per Training relationship. There can be one or multiple trainings for each skill, e.g. 3 trainings for excel, and all of them should have the same result in the column.

However, I could use filter, as you mentioned and then add "for each" loop to add line by line items in the sharepoint. 
Only one issue I have is how to create a filter query in the "Get items" step. Especially if I want to use OR. I can't find it in the example

I tried this but getting an error:
Column1 eq "Excel" OR Column2 eq "Sales" 

I am not automatically getting Column names, only from previous step output.

[Mark-Shenouda]

Hi @Krcmjaster 
Can you try this expression
Column1 eq 'Excel' or Column2 eq 'Sales' 

[Krcmjaster · Accepted solution]

I manged to get what I wanted. I actually created a loop that would filter list for each skill and add one row at a time, combining it with the survey results. I would then replicate this for each skill. Only thing that is bothering me is that I created the whole flow, but I added some new columns afterwards and flow is not automatically refreshing the updated list. Or the Flow can't deal with the images at this time