Update Item in SharePoint List

[Anonymous]

I have a SharePoint List that I'm trying to compare the items to an Excel spreadsheet. If an Item in the Sharepoint List has an Empty Value or incorrect I want to update just that column for the item.

Excel:

Customer                        Region

Acme                               NA

Acme Brick                      APAC

Acme inc.                        NA

SharePoint List

Customer                       Region

Acme                              NA

Acme inc.                       CAN

Acme Brick                     

Expected Result

Customer                     Region

Acme                             NA

Acme inc.                       NA

Acme Brick                    APAC

The issue I'm facing is when the Flow runs it is creating duplicate entries and overwriting the other items in the SharePoint List

Actual Result:

Customer                       Region

Acme                              NA

Acme                              NA

Acme                              NA

[ScottShearer]

@Anonymous 

One way of accomplishing your goal is to use an expression in the Update Item action for each column.  The expression uses an if expression and checks for a null value.  If it is null, then use the value from Excel.  If not, use the existing SharePoint value.

I used variables for clarity in my example. The Excel and SharePoint values that I want to reference did not show up in Dynamic properties while I was in the expressions editor.  So that I didn't have to reference the columns manually inside of the expression, I declared variables and set them before the update item action.  My expression is in the comment area of the update items action. 

Also, I tried to use the null operator in my expression and it just would not work properly - that's why I have two single quotes.

[Anonymous]

@ScottShearer 

I've tried to put a Flow together like you have listed below but, not having any luck.

A couple questions:

• Would you place this under the "If no"?
• What would you use for the Condition statement or should I use something else?
• I tried to Set the Variable in the "Apply to Each" section but, it wont let me set a variable in this section
• Am I on the right track for the flow lay out

[Anonymous]

Here's a quick sketch of an idea I just had.

1. Initialize two array variables.
2. For each row pulled from the Excel worksheet, build a custom object, and append to one of the variables.
3. For each row pulled from the SharePoint list, build a custom object, and append to the other variable. Be sure one of the elements in your object is the item ID.
4. Iterate through your SharePoint variable, and filter the Excel variable to match the customer.
5. If the value of the region field in the SharePoint value is different than the value of the same field in the Excel variable, then update the SharePoint list item using the ID in your SharePoint variable and the region value in your Excel variable.

Now, I'm assuming your customers are unique, so a filter of the Excel variable on customer name would always return zero or one result. The loop should run quickly in this case. If multiple results start coming up (i.e., you have more than one row of data in Excel for a customer), then the corresponding item in SharePoint would end up with the region value of the last matching Excel row.

[ScottShearer · Accepted solution]

@Anonymous :

Here are a few answers:

• You need to initialize the variables near the top of your Flow.  Then, you can set their values inside of a loop
• I set my Flow up as a copy of the one that you originally posted.  All I did was add a couple of variables and add the expression
• The decision as to whether to place your update actions on the yes or no side of the condition depends entirely on your condition logic - there is no right or wrong way

Here are scrren shots of my entire Flow.  Please not that I used an OData Filter in my Get Items action - I assumed that you had done the same.  If not, you need to.