cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jayq1989
Helper I
Helper I

How do I create a flow to capture form response and update in excel?

Hi, I would like to create a flow to capture form responses to excel and so I am hoping to get some suggestions. 

1) Update a row with form details when both criteria matches (Project ID and Due Date)

2) Send an email when Project ID does not match with excel

3) Send an email when Project ID is correctly keyed but Due Date is not

 

jayq1989_0-1634028335209.png

 

I tried "Get a row", it did retrieve the row (Project ID), BUT, if I were to submit the form with the other date (Which in this list, 10 Oct 21), the email of "Wrong Due Date" will be triggered due to it only matches the 1st Project ID and not the both.

*My form is using project ID and Due Date as reference as 1 Project will have different due dates for each task.

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
eric-cheng
Solution Sage
Solution Sage

Hi @jayq1989 ,

 

Try the below

 

ericcheng_0-1634085729333.png

 

ericcheng_2-1634087879532.png

 

ericcheng_8-1634088353147.png

 

 

1. Use a List rows present with an odata filter against ProjectID 

2. Check the length() to see if any items were found.  If not, it means incorrect ProjectID was entered

 

@length(outputs('List_rows_present_in_a_table')?['body/value'])
 

ericcheng_7-1634088305689.png

 

 

3. If yes, use Filter Array to find Due Date.  Replace the bit in red with your dynamic content fields.  First is the Excel column.  Second is the MS Form data field.  

 

@equals(formatDateTime(item()?['DueDate'], 'yyyy-MM-dd'), formatDateTime(outputs('Get_response_details')?['body/r8c596ffe83a74f9b88a561066b79875b'], 'yyyy-MM-dd'))

 

4. Check the length() of the Filter Array output and check to see if any items were found.  If not, Due Date keyed correctly.

 

@length(body('Filter_array'))

 

ericcheng_5-1634088041800.png

 

 

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

Connect on LinkedIn

 

 

 

View solution in original post

3 REPLIES 3
eric-cheng
Solution Sage
Solution Sage

Hi @jayq1989 ,

 

Try the below

 

ericcheng_0-1634085729333.png

 

ericcheng_2-1634087879532.png

 

ericcheng_8-1634088353147.png

 

 

1. Use a List rows present with an odata filter against ProjectID 

2. Check the length() to see if any items were found.  If not, it means incorrect ProjectID was entered

 

@length(outputs('List_rows_present_in_a_table')?['body/value'])
 

ericcheng_7-1634088305689.png

 

 

3. If yes, use Filter Array to find Due Date.  Replace the bit in red with your dynamic content fields.  First is the Excel column.  Second is the MS Form data field.  

 

@equals(formatDateTime(item()?['DueDate'], 'yyyy-MM-dd'), formatDateTime(outputs('Get_response_details')?['body/r8c596ffe83a74f9b88a561066b79875b'], 'yyyy-MM-dd'))

 

4. Check the length() of the Filter Array output and check to see if any items were found.  If not, Due Date keyed correctly.

 

@length(body('Filter_array'))

 

ericcheng_5-1634088041800.png

 

 

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

Connect on LinkedIn

 

 

 

View solution in original post

jayq1989
Helper I
Helper I

Hi @eric-cheng,

 

I have a problem here when I try to filter using Odata. Any workaround? Thanks for your prompt response!

 

jayq1989
Helper I
Helper I

@eric-cheng 

 

Hi Eric! Thanks for your suggestions, I found the way to "replace" the Odata filter is by using filter array and it works now! Thanks for your help! 😄

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,679)