cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ccgvanders
New Member

Update column in an Excel row from simple Form submission not working

I am attempting to use a Form submission to update the Completed row in an Excel table for the relevant row, as shown below. When John Smith completes the form, I want the content in the final column of the table to me updated from Not Yet to Complete

 

Excel table with sample dataExcel table with sample data

I am using the responder's email from the form submission, converting to all lowercase and then trying to use it as the key value against Username in the Excel table to find the right row. However, I keep getting an error that: no row was found with Id 'joh.smith@email.com' . This is despite certainty that there is a row with that exact email/string in the Username column.

 

I am not sure what I am doing wrong.

 

Here is what my flow looks like:

 

flowpart1.png

flowpart2.png

If someone can suggest what I am missing or doing wrong, that would be great.

1 ACCEPTED SOLUTION

Accepted Solutions

@ccgvanders You can use list row present in a table then inside Apply to each use update row in which key value should come from values from list row present in a table. Pls use filter query to avoid unnecessary looping.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

View solution in original post

6 REPLIES 6
Hardesh15
Super User
Super User

@ccgvanders 

Approaches-

1. Share your toLower() expression.

2. If you can try with unique ID. Pls do.

3. Use list row present in a table and then use compose -->pass Username from list row present in a table-->Run flow see output.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

ccgvanders
New Member

@Hardesh15 

1. Here is my toLower() expression:

toLower(outputs('Get_response_details')?['body/responder'])

 

2. I tried using Unique ID as the key column and entered '12345' my Unique ID directly into the Flow (as below). 

flow-uniqueID.png

The result was the message below.
ccgvanders_0-1634937948476.png

3. When using list rows present in a table and compose, as you asked, it seems to have only returned the first 256 rows. My actual data has 1995 rows. Why is it only returning the first 256?

 

Here is my flow test result for list rows present and compose. I have checked and the outputs for list rows present in a table only has first 256 rows of my table data listed.

ccgvanders_1-1634939239872.png
ccgvanders_2-1634939273982.png

 

@Hardesh15 

A search about the 256 rows online helped me a little. I think I need to enable pagination and put in a value of 3000 or something so it includes all my rows and not the default 256 only.

 

ccgvanders_3-1634939697718.png

However I only seem to be able to change this setting for list rows present in a table. The pagination setting is not available in settings for update a row. If you can suggest how I tell the flow to look across all 2000+ rows to find the right row to update, that would be helpful.

@ccgvanders You can use list row present in a table then inside Apply to each use update row in which key value should come from values from list row present in a table. Pls use filter query to avoid unnecessary looping.

Please 'Thumbs Up' the posts that helped you and 'Mark as Solution' if my post answered your question.

@Hardesh /Gopenly.in

View solution in original post

ccgvanders
New Member

Thanks @Hardesh15 , very helpful. I feel I am almost there.

 

I am not sure how to include a filter query, but I am hoping that it would make the flow more efficient. Currently, with your help, it now works as expected in terms of updating the table. However it takes a little over 10min to complete. Will filter query improve this?

 

I am not sure how to add the filter query, or where. My guess is that I add it in the advanced options for list rows present in a table, in the filter query field. However, I am unsure exactly how to code/write the query correctly. I tried a few things and it keeps giving me an error.

 

How would I filter Username in list rows to only return exact matches for my variable lowerUsername?

All good! I needed to include single quotations around my variable name. Adding the filter query cut my 10min flow run time down to 2secs. Thanks so much. 👍👏

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,739)