Form > excel > Approve

[T-Ang]

I have a form Users are filling out when they have a request, then their responses are getting sent to an excel sheet. Within my excel sheet I have multiple sheets where I lookup information based off what the User entered into the form. 

I'm trying to create a flow where the user fills out the form (I have another flow where the when the response is submitted a new row gets added to excel), then an approval gets sent to the Manager. But within the email I want to provide information from the excel sheet. The issue is that all rows are getting sent to the manager every time the form gets filled out 

What formula can I use within "List rows present in a table" to only send 1 row for approval, basically every time the form gets filled out I want the approval being sent with the information from excel sheet as well. 

[ScottShearer]

@T-Ang 

I would first try using an OData filter query to retrieve the single row in which you are interested.  If you click on Advanced options in the List rows action, you'll see the option for Filter query.  Here is a post that shows how to use OData filters with SharePoint - Excel is very similar.

If that doesn't work for you, try using a Filter array action.

Please post here with screen shots of your Flow if you have additional questions.

[leo85]

Hi @T-Ang ,

the action "List rows present in table" retrieves all rows within a specific table. So a loop is created and an approval sent for each row.

As I understand there is only one row for each time a user fills in the form, correct?

Why are you using "List rows present in table"? You can use the action "Get a row". This will get you one specific row from the table. For this you need a key column in your table and a key (from the form) to identify the correct row.

Alternatively, you can use the "Filter" action from the "Data Operation" connector. You can filter the output from "List rows present in a table" to return only that row, which fits the filter criteria. Note: The "Filter" action returns an array, so you still need an apply to each loop even if you only return one row.

Regards,

Leo

--------------------------------------------------------------------------
If I have answered your question, please mark my post as a solution
If you have found my response helpful, please give it a thumbs up

[T-Ang]

Thanks so much for the idea of using "Get a row" instead. I'm using the Time submitted at the key value. But for some reason it is not working. Do you happen to know why?

[ScottShearer]

@T-Ang 

By default, when you retrieve dates/times from Excel they are returned as numbers rather than as a date.  To have a date returned, set the Date/Time format in the List rows action (under advanced) to ISO 8601.

[T-Ang]

Thanks for that note! 

I used "Get Row" and changed the Date Format to ISO 8601, but it's still not matching.

In my excel sheet anytime a row gets added it gets assigned the following number. Do you know if this is something I could use to get the row information sent out?

[ScottShearer]

@T-Ang 

Please post a screen shot of your Flow with the details showing.  Also, I had suggested "List rows present in a table".

[T-Ang]

When I use list row I get all rows on the excel form. I'm not sure what expressions to put for the filter query. 

Heads up - this is my first time ever using power automate and I don't have any coding skills.

When using "Get Row" I was using key column & key value for the submitted time. 

[leo85 · Accepted solution]

Hi @T-Ang ,

in your first message you wrote: "I have another flow where the when the response is submitted a new row gets added to excel".

Just to understand this. You have two flows. One (flow A) that writes information to the excel sheet and one (flow B), which sends an approval with the information from the excel sheet based. We are currently looking at flow B. Both start when a response is submitted for the same form, right?

If this is correct, the current setup will not work, because when the form is submitted you cannot be certain that flow A runs and finishes before flow B.

You could add a "delay" of a few minutes right after the trigger, to wait for the first flow to finish. Then you could use the "Response ID" as the key value. So you would add an additional column to your Excel table and have flow A write the "Response Id" into this column. Flow B can then identify the row by using the "Response Id" (see screenshot). But this will only work if flow B gets the information from Excel after flow A has finished. To me, this solution is not very clean, but requires the least amount of changes.

Another option would be to combine both flows into one flow. Trigger when a response is submitted. Add a row with the data to the Excel sheet. Get the row from Excel. Send the approval. (To me it is not really clear what the Excel is doing. Is this a kind of lookup? If you just add a row to Excel and afterwards you get the same information, you shouldn't need the Excel at all before send the approval.

[v-jefferni]

Hi @T-Ang ，

You can also call flow B from flow A using the HTTP request action in flow A, so after flow A is finished successfully it will call the other flow. Please refer to below video form Shane Young:

https://www.youtube.com/watch?v=umBegXooiKU&t=643s

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[leo85]

Note that these are premium connectors. So you need to have/buy a premium license to use them.

---

You can also call flow B from flow A using the HTTP request action in flow A, so after flow A is finished successfully it will call the other flow.

---