Compare 2 SharePoint lists , and create item not there

[Sahara22]

Hello everyone, 

I have been struggling for days and I hope someone can help me.

I have two SharePoint lists. Each list has a name. I need list number one to search list number two by name and if list number one name is not in list number two , create an item in list number three from list number two.

thank you for your help in advance. 

[Sahara22]

Any help would be great please! 

[AdrianTorres]

@Sahara22 I think you’re gonna have to give an example with some random list items to go through of the process you want to do.

[v-jefferni]

Hi @Sahara22 ,

Below is a solution that uses XML to compare two arrays and get differences. For your question, after getting the array of different items, apply to each value and create new items in list3.

Solved: Re: Compare data between sharepoint list and excel - Power Platform Community (microsoft.com...

Best regards,

[S-Venkadesh]

Hi @Sahara22 ,

To demonstrate the logic, let's consider two lists:

List1 - Employee (Columns : Title (Built in Column))
List2 - EmployeeTasks(Columns : Title (Built in Column), Task(Single line of Text)

To achieve the desired outcome, follow these steps:

1. Start a new automated cloud flow in Power Automate and set the trigger to "When a new item is created" in Employee (List1).
2. Add a "Get items" action to retrieve all the items from EmployeeTasks (List2) with a filter query to match the Title from List1 with the Title in List2.
3. Create a new variable using the "Compose" action to calculate the length of the output array.
4. Use a "Condition" action to check if the length of the output array is less than 1 (if less than 1, then it means no matching record exists).
5. In the "Yes" case of the "Condition" action, add a "Create item" action to create a new item in EmployeeTasks (List2) with the same Title from List1 and the relevant Task.

Refer to the figure below for a visual representation of the flow.

Best Regards,
Venkadesh Sundaramurthy

[Sahara22]

Thanks @S-Venkadesh and @v-jefferni ,

So i need to search for names, sometimes the names may have something extra at the end like, John smith @123 , I need to use contains . so Employee name "contains" employee  name. 

basically if names in list A is not in list B, create an item in list C, after done it sends a email of the list C  then gets deleted after the email is sent and starts fresh the next day. 

I tried this filter but i still get duplicates, please see below :  this is a scheduled flow . 

[S-Venkadesh]

Hi @Sahara22 ,
To match text that contains a specific substring in a FilterQuery, you can use the following formula 

substringof('searchtext',FieldName)

E.g.

Please hit thumbs up, if it helps!

Best Regards,
Venkadesh Sundaramurthy

[Sahara22]

Ok will try this now , Also @S-Venkadesh  can I do this was a schedule? or has to be create an item? i just notice i get to many loops

[Sahara22]

It's not working @S-Venkadesh 

I get an error : 

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'length(outputs('Get_items_2')?['body/value'][0]['field_1'])' cannot be evaluated because property 'body/value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

[Sahara22]

any ideas?

[takolota]

@Sahara22 

Did you try

length(string(outputs('Get_items_2')?['body/value']))

??

[Sahara22]

Thanks for your help @takolota , but still error: 

Unable to process template language expressions in action 'Compose' inputs at line '0' and column '0': 'The template language expression 'length(string(outputs('Get_items_2')?['body/value']))' cannot be evaluated because property 'body/value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

[takolota]

@Sahara22 

Okay I thought part of that may be the values reference, try…

length(string(outputs('Get_items_2')?['body’]))

[Sahara22]

Thank you again but says expression invalid , sorry i'm a newbie with this stuff lol 

[S-Venkadesh]

Hi @Sahara22,
I noticed that the "Get Items" action has been added twice in your flow, which could cause unexpected iterations. To avoid this, I suggest you rewrite the flow as shown in the attached screenshot.

Please refer to the visual representation of the updated flow below.

Once completed, your flow should look like below. 

Please hit thumbs up, if it helps!
Best Regards,
Venkadesh Sundaramurthy

[Sahara22]

Thank you @S-Venkadesh , substring didn't work, I have to look for names that match, not the title because it may cause a duplicate, the reason why their are two get items because its a scheduled flow not when an item is modified. 

[Sahara22]

Any Idea's please? 

[S-Venkadesh]

Hi @Sahara22 ,

The title used here is for demonstration purposes only. This approach can also be applied to the Name column.

If I understand correctly, you would like to apply the same approach to the Name column. Could you please specify the column types in List1 and List2?
I will attempt to create the scheduled flow with the appropriate column types.

[Sahara22]

@S-Venkadesh  Thank you so much , both single line of text. One column is named EMP_ID (field_5)(master list) and Second list searching master list is EMPLOYEE NAME  (field_1).  Names are written , SMITH, JOHN and is the master list sometimes names are written SMITH, JOHN @123 , thats why i need to use contains because its not always the exact match. Then whatever is on the master list that's not in the Second list, create item in a NEW third list called "NEW EMPLOYEES" . hope this helps

[Sahara22]

any idea's?