Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Announcing Category Subscriptions!
News and Announcements icon
Power Platform Community / Forums / Power Automate - Building Flows / Updating a row if a ce...
Power Automate - Building Flows
Suggested answer

Updating a row if a certain cell is empty

(0) ShareShare
ReportReport
Posted on by CU10101858-0 21
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:
Business Process Flows
  • CU10101858-0 Profile Picture
    CU10101858-0 21 on at
    Updating a row if a certain cell is empty
    Thanks for the help! Yes, that is exactly the problem, the material ID is not unique. However, because of the way I need this to work I can't assign each item a truly unique ID. Ideally, I would like to be able to update a row in the excel file based on two key criteria: 1) Material ID and 2) The Time In column being empty. It's starting to seem like that isn't possible....
  • Suggested answer
    Pacel1 Profile Picture
    Pacel1 79 on at
    Updating a row if a certain cell is empty
    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
  • Pacel1 Profile Picture
    Pacel1 79 on at
    Updating a row if a certain cell is empty
    It will be hard to help You, without see all flow, and actions that You used to use :(
  • CU10101858-0 Profile Picture
    CU10101858-0 21 on at
    Updating a row if a certain cell is empty
    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.
  • Pacel1 Profile Picture
    Pacel1 79 on at
    Updating a row if a certain cell is empty
    If you leave only this.  
     
    TimeIn eq ''
     
    you have same error? 
  • CU10101858-0 Profile Picture
    CU10101858-0 21 on at
    Updating a row if a certain cell is empty
    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.
  • Pacel1 Profile Picture
    Pacel1 79 on at
    Updating a row if a certain cell is empty
    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 :) 
  • CU10101858-0 Profile Picture
    CU10101858-0 21 on at
    Updating a row if a certain cell is empty
    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 '''.
  • Pacel1 Profile Picture
    Pacel1 79 on at
    Updating a row if a certain cell is empty
    Cell formating should not be problem, could you share a screen with your query? 
     
    and part of getRows output contains TimeIn?
  • CU10101858-0 Profile Picture
    CU10101858-0 21 on at
    Updating a row if a certain cell is empty
    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?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Announcing Category Subscriptions!

Quick Links

Kickstarter Events…

Register for Microsoft Kickstarter Events…

Tuesday Tip #12 Start your Super User…

Welcome to a brand new series, Tuesday Tips…

Tuesday Tip #13 Writing Effective Answers…

Welcome to a brand new series, Tuesday Tips…

more Community News

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 144,940

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,523

Leaderboard

Featured topics

Start and wait for an approval action

Product updates

Microsoft Power Platform Community release plans