how to update multiple rows with a key value

[amyhsieh11]

Hi,

Could someone tell me how to update multiple rows with a key value?

Goal:

We need to update products' scheduled shipping date in our sales report once we receive a SOA from our vendor.

Problem:

In the sale report, the same product number will be listed under several PO number.

steps:

1. I filter the excel with the PO number which is the same as the one listed on SOA.

2. use step 1 outputs -> apply to each

3. update a row with the key: items('apply to each')?['Product number']

however, the flow didn't update the correct field if there was already have the same product name before it.

is there anyone can tell me how to create the flow?

thanks for your time!

[ScottShearer]

@amyhsieh11 

The easy way is to use a Filter Query in your List Rows action - you can specify multiple criteria.  However, you can't have any spaces in the column names.  Here is a link to a post on using OData filters.

Let me know if you need an example.

 If you need to keep the spaces, then see this post that explains how to add multiple criteria to a filter query action.

[amyhsieh11]

hi @ScottShearer 

thanks for your reply!

I follow your suggestion to revise my flow, but the result seems the same as previous setting. 😟

1. filter the excel by PO number (correct)

2. update the products' information which are under the PO number (incorrect)

→I thought it will just update the items which I filtered from the first step, but it seems that the flow still start searching from the first row of the excel...

could you give me some advice how to built the correct flow? 

[GeoffRen]

How are you trying to determine what row you want to update? In your example, what's the difference between the correct and incorrect rows?

[amyhsieh11]

Hi @GeoffRen 

I use AI builder to extract the information from PDF then update the rows according to the PO number.

the scenario is:

1. receive an email from our vendor with the attachment (SOA, sales order acknowledgement)

2. use AI builder to extract the information from SOA: Purchase order number, product number, scheduled ship date and total amount

3. we have a excel named 'sales report' which list all the items we have purchased.

4. update scheduled ship date into the 'sales report' excel

the difference between the correct and incorrect rows is 'the Order Number'.

what I test is:
I receive a SOA which for Order Number: PO20210217ft0004 and would like to update the scheduled ship date for PN: 111, 222, 333, 444 ,555 under that Order Number.

but the question is there is already have a row which PN is 222 but under different Order Number,
therefore the flow just update the rows for PN 222 (Order Number: PO20210119ft0002), but didn't update the rows for PN 222 (Order Number: PO20210217ft0004)

[GeoffRen · Accepted solution]

Ok, thanks for the extra information! According to this post, there's not a way to use the Update a Row action using multiple keys.

Fortunately you can do this using Office Scripts, specifically Office Scripts with Power Automate. This is the 'Run Script' action on the Excel connector. With Office Scripts you can use the 'Run Script' action to execute javascript against a workbook. So you can use a script to update the table based on multiple keys.

Here's a basic script that will do this for you, you just need to fill in the parameters when prompted when creating the Flow. You also might need to adjust the Idx variables in the script to match up with where your Product PN, ECV Order Number, Ship Date, and OE Numbers are in your table.

function main(workbook: ExcelScript.Workbook, sheetName: string, tableName: string, pn: string, orderNumber: string, shipDate: string, oeNumber: string) {
  const tbl = workbook.getWorksheet(sheetName).getTable(tableName);
  const rowCount = tbl.getRowCount();
  const values = tbl.getRange().getValues();
  const pnIdx = 0;
  const orderNumberIdx = 1;
  const shipDateIdx = 2;
  const oeNumberIdx = 3;
  for (let i = 1; i <= rowCount; i++) {
    if (values[i][pnIdx] === pn && values[i][orderNumberIdx] === orderNumber) {
      tbl.getRange().getCell(i, shipDateIdx).setValue(shipDate);
      tbl.getRange().getCell(i, oeNumberIdx).setValue(oeNumber);
    }
  }
}

[amyhsieh11]

Hi @GeoffRen 

I really appreciate your help! it work perfectly! 😍

[takolota]

See a similar batch update Excel template using office scripts here:

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Batch-Update-Excel/td-p/1624706

It’s already optimized for performance, error handling, & ease of use.

[Aleksandra1]

Heyy, I have the same problem, but I can not understand where I can write this script.

[GeoffRen]

Please follow the steps in this tutorial to create a basic script Record, edit, and create Office Scripts in Excel on the web - Office Scripts | Microsoft Learn

[royamaratu]

Good afternoon Everyone, I also have the same problem, when I have a key value that I want to update in more than 1 row for 2 or 3column, but only 1 column at the top is updated, how to solve this problem.
I want to update a row if the key value is 1, the status becomes Order.
For my current problem, when I update only rows in 1 column, not all columns that have key ID 1.
Example :

Thank you.

[Kannan_n · Accepted solution]

Hi Royamaratu, 

To solve your problem I got a method. If it is ok for you Use it. 

You want to create a support column in Excel Using 1,2 3, and 4 as a  series number.

Use this method it will work. If you are run directly in ID It will check first after in Excel update we have where ID =1 it will update so in 1st row we have ID=1 so each and every time it's updating in the 1st row.

To solve this we can create a support column using as Key value in the Excel update 

Thanks/Regards 

Kannan. N

[Mar]

Hi @amyhsieh11 

I have a similar issue, my flow only updates an ID the first time it appears on a sheet, the ones below it doesn't update. 

this is my current flow, it adds correctly. At "if no" I tried List and update like you did but it didn't work.

I want to update the date of closing of the sheet "24_05update2" at the corresponding partnumbers at the sheet resume (next figure).

I tried to use @GeoffRen  's solution, modificating to my information, but comes an error: 

Workbook.getWorksheet: The argument is invalid, missing, or has the wrong format. 

Could you give me some advice how to built the correct flow?

[GeoffRen]

It seems like the sheetName parameter you are passing to the script does not correspond to any of the sheets in your workbook. The sheetName parameter needs to be the exact same as the name of the worksheet in your workbook.