Compare New Excel Spreadsheet to Primary Spreadsheet in SharePoint Online

[lardo5150]

I exported data from our internal tool.

I uploaded this spreadsheet to SharePoint Online so that a few of us can review it and add Comments.

The spreadsheet does not have a Comments column when exported, so we created it after we uploaded it to SharePoint (not sure if that matters, but its still the same Table).

Here is what I am trying to do:

Everyday I want to export new data and import it into this main spreadsheet.

90% of this data will be the same.  TicketNumber, Engineer, etc.

But key things will be different.

CaseAge will be different.  Obviously if the same row exists from the previous day, this number for CaseAge will now be a day bigger.

So i want to compare the two spreadsheets.

Trying to compare like this.

New Spreadsheet Data - Ticket Number and Engineer = Ticket Number and Engineer in Main Spreadsheet on SharePoint online.

New Spreadsheet Data - Case Age > Main Spreadsheet Case age

If that condition is true, I am trying to update the row in the Main spreadsheet, but not the entire row, just Case Age.

If those do not match, that means there is a new case in the list, and we should add it to the main spreadsheet.

This is what I have.  When I ran it, it did not update anything.  But it seemed to go through each line in the main spreadsheet, and created a new line for every entry.  can't figure out why it did that.

[lardo5150]

Bumping as I still need assistance

[abm]

Hi @lardo5150 

The flow build looks ok to me. I can see below three conditions you are using for checking the match.

It must be one of the condition is not working as expected. Keep only the first condition and see if that works or not. Then add next condition to extend the check one at a time. Let me know how it goes. Most likely the CaseAge comparison. Try to convert the values using to an int() expression.

Thanks

[lardo5150]

ok, will adjust the conditions.

Any idea how to stop it for creating a new entry for every existing entry?

It looked like when it ran last time, it would check each line, and if the entry did not match, it created a new entry every time, which is why it was taking so long to run.

It should see if there is an existing entry in the entire list, if it does not match any of them, then add the new entry 1 time.

[lardo5150]

so when I try to convert the cell values to integer to compare, when I create the convert on the primary sheet, it automatically adds in an APPLY TO EACH.

Just by looking at this, it looks like it is going to do the same thing as before.  It will check it againts every single line.  if it matches, it will update, but if not, it will add a new line for each existing line in the sheet. 

How do i get around this but convert both cell values from each spreadsheet to integer?

[lardo5150]

Forgot to mention, the condition 2 at the bottom does not allow me to compare the two OUTPUTS from the compose, only in the second APPLY TO EACH, which is where I will run into the problem I mentioned above.

[lardo5150]

@abm 

So here is what i have.

The problem is, it will check each line, and when it does not match, it will add a new entry.

that is sort of correct.  it should do it only once after it checks all of them, not each time.

How can I prevent it from doing it each time.

[lardo5150]

@abm  here is what I came up with.

File is created

grab file data

create a variable for string for numbers that do not match

List rows with new spreadsheet (new data)

Do an apply to

get rows from the PRIMARY spreadsheet

create an output converting the caseage cell number from the NEW spreadsheet into a number

Apply to each for the list of rows on the PRIMARY (it did this automatically when i tried to convert to int)

create an output converting the caseage cell number from the PRIMARY into a number

I then compare NEW vs Primary

if all matches, I need to update the row (here is where I get an error, more on that in a second)

If it does not match, I add the PRIMARY ParatureNumber (basically a ticket number) to an array

At the end, I compare the ParatureNumber of the Array with one from the New Sheet.  If it does NOT match, I add that new line into the Primary spreadsheet.

The problem is UPDATE a row.

I basically just want to update two cells within a row, caseage and daysidle.

I got an error stating that no row was found with ID 166

166 was the new updated CaseAge i was trying to update in the Primary.

(I was testing with a single line excel spreadsheet since it was taking so long to go through everything).

How do I properly setup UPDATE ROW?

[abm · Accepted solution]

Hi @lardo5150 

It's difficult to give you an exact answer. Generally for update excel sheet you need to match the key column and value. Is that value exists? Check the run history. If you still need help then could we have a quick call and screenshare?

Thanks

[lardo5150]

@abm  You actually got me pointed in the right direction and I figured it out.  i also simplified it by using arrays.

I just needed a second set of eyes on it to get me thinking a different way.

Thanks again!!!!!