Updating a row if a certain cell is empty

(0) Share

Report

Posted on by CU10101858-0

Hello,

I'm using a Microsoft Form with Power Automate to update a material tracking excel sheet. It's currently structured this way:

1) When a new response is submitted

2) Get response details

3) Condition: "Move Material To" is equal to "Removal from Freezer"

4) If yes: Add a row into table recording the current time

5) If no (for when material is returned to freezer): Update a row based on the Material ID

Material ID	Time Out	Time In
abc	12/3/2024 12:14:00 PM	12/3/2024 12:45:00 PM
abc	12/3/2024 13:30:00 PM

When return to freezer option is run it overwrites the "Time In" any previous rows for that material ID. I really need it to only update the row if the material ID matches AND the "Time In" column is blank. I've tried multiple approaches (AND conditions, list rows present in table with filter query), but none of them have worked so far.

Thank you!!

Categories:

Building flows

I have the same question (0)

All responses (11)

Answers (0)

Sort by

Pacel1 79 on at

Like (0)

Report
Copy link

Link copied!

Odata filters in excel actions can be problematic when column names have a spaces.

at the begining try to get all rows, and check output to verify column names coresponding to column names in excel.

so the odata filter should look similar to this:

MaterialID eq 'abc' and TimeIn eq ''

Was this reply helpful? Yes No
CU10101858-0 21 on at

Like (0)

Report
Copy link

Link copied!

Thanks! When I used the filter query it was fine with finding the correct column but I kept getting a syntax error for TimeIn eq ''. I'm not sure if maybe this is because the TimeIn field in excel is formatted as a custom date not a text field?

Was this reply helpful? Yes No
Pacel1 79 on at

Like (0)

Report
Copy link

Link copied!

Cell formating should not be problem, could you share a screen with your query?

and part of getRows output contains TimeIn?

Was this reply helpful? Yes No
CU10101858-0 21 on at

Like (0)

Report
Copy link

Link copied!

I can't screen share because of work policies but I can try my best to explain.

The "List rows present in a table" action has the filter query with UID eq 'xxx-xxx-xxxx-xx-xx_xxxxxxxx_x and Time In eq ''.

When I try to run this I get the following error: "Syntax error at position 50 in 'UID eq 'xxx-xxx-xxxx-xx-xx_xxxxxxxx_x' and Time In eq '''.

Was this reply helpful? Yes No
Pacel1 79 on at

Like (0)

Report
Copy link

Link copied!

Im pretty sure that Your error is connected with space betwen Time and In :)

Best and fastest way to make it work, is to rename column in excel file :)

Was this reply helpful? Yes No
CU10101858-0 21 on at

Like (0)

Report
Copy link

Link copied!

Updated test.

Filter query: UID eq 'xxx-xxx-xxxx-xx-xx_xxxxxxxx_x' and TimeIn eq ''

Error: Invalid filter clause: unsupported operation. Only single 'eq', 'ne', 'contains', 'startswith' or 'endswith' is currently supported.

Was this reply helpful? Yes No
Pacel1 79 on at

Like (0)

Report
Copy link

Link copied!

If you leave only this.

TimeIn eq ''

you have same error?

Was this reply helpful? Yes No
CU10101858-0 21 on at

Like (0)

Report
Copy link

Link copied!

With just the TimeIn eq '' it runs sucessfully, but it is still updating the incorrect row. It does not update the row with the TimeIn cell blank it updates the previous row which has something in that cell.

Was this reply helpful? Yes No
Pacel1 79 on at

Like (0)

Report
Copy link

Link copied!

It will be hard to help You, without see all flow, and actions that You used to use :(

Was this reply helpful? Yes No
Suggested answer

Pacel1 79 on at

Like (0)

Report
Copy link

Link copied!

I fired up PowerAutomate, and I already know what your problem is :)
If I understand correctly, materialID is not unique within the entire list. So the Update Row Action applies it to the first record encountered.
You should create a column in Excel that will contain a unique key for each element.
This way, after filtering the list, you will be able to use this key to update a specific cell.

As for the filtering itself, there is indeed a problem, especially when searching for empty ones. To get around this, first filter MaterialID, then use the filter action to filter empty records

Was this reply helpful? Yes No