How to compare two lists to update? One from sharepoint, and one I received by email.

[carudev]

Hi folks! 🙂

I would like to know how to compare two lists - without using external connectors - in order to update.
I have a list that I receive by email daily and I want to use that list to update a list on sharepoint.

• What I built was:

For each row in the table that I received by email and for each row in the Sharepoint table I will compare if the rows have the same ID, if they have the same ID I will update the row.

But in this way, despite working and doing what I want, it is not very applicable, because I have one ForEach within another ForEach inside and this will give several days of execution.

Inside the second ForEach I compare the two tables: if the IDLinha of the Sharepoint list is the same as the IDLinha of the table that I received by email, then I update the item.

I am updating via REST because my table is a little big.

So, how to do this without using a ForEach inside the other?  And without spending several days running?

Note: my table comparison ID is not the ID that Sharepoint automatically creates, it is a column called "LineID" that I use to compare with the table that arrives by email, because in this table - which arrives by email - does not exist the automatic ID from Sharepoint, right.

Ps: I have an advanced automate license. If it helps anything

Thank you guys! 😄

[Paulie78]

Before answering, may I ask, how many records in the SharePoint list?

[carudev]

Hi dear @Paulie78! 😄
The Sharepoint list has about 5k lines, and it can increase over time. The list that arrives by email will also have more or less that, 5k or 6k.

[Paulie78]

The part I don't understand is why you have two apply to each loops. This is how I see it, but perhaps I am misunderstanding...

• Get the list in the email with the IDLinha ID's
• Go into an apply each loop of the IDs.
• Perform a Get Items action and use a oData query to only return rows (hopefully only one) where the IDLinha matches the current IDLinha from the email.
• If the number of results is 1, then update the item.

You don't need to cycle through the SharePoint items if you can get directly to the one you need with an oData query.

Did I understand it wrong?

[carudev]

So, I was stuck with that here: when I am going to use the item update function, it asks me to pass the sharepoint ID - that id that the sharepoint automatically creates - but, it is not this ID that I use in the comparison, I use the IDLinha, then I look for the IDLinha and if I find it I update. But the automate forced me to have a forEach when I took the IDSharepoint field:

[carudev]

An example from my sharepoint list:

ID is the automatic Sharepoint id
IDLinha is the ID I use for purchase, this one exists in the sharepoint and in the table I receive by email.

[Paulie78]

yes, that is simply because Get Items always returns an array, even if there is only a single result. An array will always force you into an apply each. But you can check the length of the response, if it is equal to one, then use the first expression to take the first and only element of the array, and create an object from it. Then there will be no need for the additional apply/each.

[carudev]

Dear @Paulie78 ! Thank you for always being so helpful ❤️

I found a solution, see if it makes sense to you:

First I use a Filter Array to select from the Sharepoint list which line is the same as the current line of my forEach.

I do this by searching for where the current IDLinha is equal to the IDLinha there in Sharepoint.

Then I take this line that I found in my Filter Array and I get its ID (this is the sharepoint ID)

And then I update the item using the item's sahrepoint ID that had the same "IDLinha". 

And it works when I test with a few rows, but when I try to test with the entire table I have a 400 error, bad request.

{

 "status": 400,

  "message": "The type SP.ListItemEntityCollection does not support HTTP PATCH method.\r\nclientRequestId: 8f0c408d-24cd-4e15-803d-3460780851aa\r\nserviceRequestId: 27d1bf9f-704a-0000-b7cf-3c9b6f1350d4",

  "source": "https://****************************/_api/web/lists/GetByTitle('PeopleSoft')/items()",

  "errors": [

    "-1",

    "Microsoft.SharePoint.Client.InvalidClientQueryException"

  ]

}

That's all folks! ❤️

Thank you! may the force be with you 😄