how to update multiple rows with a key value

(1) Share

Report

Posted on by 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!

update excel.png update excel-2.png

Categories:

General topics

I have the same question (0)

All responses (13)

Answers (2)

ScottShearer 25,277 Most Valuable Professional on at

Like (2)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
amyhsieh11 45 on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
GeoffRen Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
amyhsieh11 45 on at

Like (1)

Report
Copy link

Link copied!

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)

Was this reply helpful? Yes No
Verified answer

GeoffRen Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!
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); } } }

Was this reply helpful? Yes No
amyhsieh11 45 on at

Like (1)

Report
Copy link

Link copied!

Hi @GeoffRen

I really appreciate your help! it work perfectly! 😍

Was this reply helpful? Yes No
takolota1 4,978 Moderator on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Aleksandra1 254 on at

Like (1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
GeoffRen Microsoft Employee on at

Like (2)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
royamaratu 6 on at

Like (2)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No