Create a CSV table from two sharepoint lists based on a reference ID

[lui]

I really need help!! I have two lists that share a unique ID .

List A includes the following columns

• ID
• Full Name
• DOB
• City
• State

List B includes the following columns:

• MasterID
• Gender
• Languages (Choice multiple selections)
• Phone number

In Flow, i want to create a csv table and a SELECT table with information from both lists based on the ID/MasterID

is this possible? Im having a hard time doing this. 
Ideally, i want my table to have all columns

• ID
• Full Name
• DOB
• City
• State
• Gender
• Languages (Choice multiple selections)
• Phone number

[v-litu-msft]

Hi @lui,

It is possible and I create a flow according to your requirement:

The Flow is a little complex, so I Export it as a zip file and uoload it on my github, you can download it and import it in your environment, please change the connection before use it:

https://github.com/AdrainTu/MS-/commit/59fd71a8fe3e3504f87c7ef5e9035260de9e5cc6#diff-9f3cbc519d8f802... 

Run result:

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lui]

@v-litu-msft  How can you upload a flow? is it possible to share screenshots of all steps? 
Thank you again

[lui]

if it's possible, can get a little more detail on the action steps

[lui]

@RezaDorrani  youve helped me in the past and hoping you can help me with this critical roadblock. Do you know how i can join/combine the dataset from two lists to create one CSV table? thank you.

[v-litu-msft · Accepted solution]

Hi @lui,

I export the Flow as a zip file and upload into my Github, and you can click it and download it.

Ok, I will Share my steps, and descrip it in detail, please follow these steps and try it:

Step 1: Create variables to hold these data get form two SharePoint lists and for temp. Array variable Information, store the items get from list X; Object variable TempObject, temp store the object during merge; Array variable Total, store the final array; String variable LanguageTemp, store the language values.

Step 2: Get items from List X, create Append to array variable to store them into Information variable. Then Get items from List Y.

Step 3: Create Apply to each 2, put the value of Get items of list Y; Create Apply to each 3, put the value of variable Information.

loop though each item of List Y, if current item's Master ID is equal to information ID, setProperty of current item of variable to add Gender column and set the Language column as blank:

setProperty(setProperty(item(), 'Gender', items('Apply_to_each_2')?['Gender']),'Languages','')

Then Append to String variable to put these Language values into the variable.

Step 4: After the Previous use the compose to put the value of LanguegeTemp variable into the TempObject:

setProperty(variables('TempObject'), 'Languages',variables('LanguageTemp'))

After that, Append it into Total variable and add PhoneNumber property, then empty the LanguegeTemp variable:

setProperty(outputs('Compose'),'PhoneNumber',items('Apply_to_each_2')?['PhoneNumber'])

Step 5: create the CSV table by using Total variable.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lui]

Wow thankk you so much @v-litu-msft  .. I have a couple of more questions before i can finish this flow: 

1. How do i add another column/field to List Y?

2. What if i want to add a choice column to List X, how would you append the strng variable?

Thank you

[v-litu-msft]

Hi @lui,

1. Where you want to add the column into List Y? The SharePoint list Y, or the array of list Y in the Flow?

If you want to add the column into SharePoint list Y, you just create a new column into the List;

If you want to add a new column into the array in the Flow, like 'Gender' and 'PhoneNumber', you just need to do is repeat the setProperty expression.

2. Just like I do in the Language column.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lui]

Would it be possible to show me as an action step? I want to add a choice column (‘Teams’) to the first list X but not sure how to connect it to the append array variable.

Second part is to add a string/text column (‘hometown’) to list Y and add it to the Total variable.

Thank you again!!

[lui]

@v-litu-msft only reason im asking is because when i try adding the new columns, im not sure how to add it back together to your original actions. Hope you can assist me! thanks

[v-litu-msft · Accepted solution]

Hi @lui,

Please use following expression to instead the red circle part in the picture:

setProperty(setProperty(setProperty(outputs('Compose'),'PhoneNumber',items('Apply_to_each_2')?['PhoneNumber']),'hometown',items('Apply_to_each_2')?['hometown']),'Teams',items('Apply_to_each_2')?['Teams']['Value'])

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lui]

@v-litu-msft , I'm getting the following error: 
'Value'])' cannot be evaluated because property 'Value' cannot be selected. Please see https://aka.ms/logicexpressions for usage details.'.

Dont I need to append it to a string variable first? It would be helpful to see the steps. Thank you

[v-litu-msft]

Hi @lui,

The error will happen when the property name is incorrect. 

Could you please tell me which property happened?

Actually, if you not mind the Order of properties, you can add these string property firstly.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ubaid2]

Hi @v-litu-msft ,

I am having the same scenario and I have build the same flow from here, but the issue which I am facing is that in listx I have 9000 records, it is taking to much time to run flow I have tried to filter items from list y and then get items from the ID of list y but when I add that get item it goes in to apply to each and flow gives error how can I fix it.

Thanks