Match a Microsoft Form submission to a table in Excel and run the condition if there's a match

[chintan_patel]

Hi

I have a Microsoft Form set up where I am requesting a first name, last name, email address and an ID value which is a number for an event I am holding. Only authorized members can attend this meeting, and I authenticate this via the ID value that's captured and look it up according to an Excel spreadsheet I have stored in OneDrive for Business.

I want to use Power Automate to do the automatic authentication by taking the ID value from a Form submission, match it to the values in a table in an Excel spreadsheet stored in OneDrive for Business. If the ID matches, Power Automate must send an email to the recipient providing details to the meeting, i.e. the meeting link. If the ID does not match or is not found, the recipient gets an email stating what's the next procedure (a manual verification step - not created in Power Automate). The ID is a numeric value. It's captured as a numeric value on the form. The table has the IDs stored, formatted as "General".

I added the following steps:

1. When a new response is submitted
2. Get response details
3. List rows present in a table
   1. I specify the ID value from the Form under the "Filter Query" field
4. Apply to each control inserted
   1. Select an output from previous steps
      1. I choose the ID value from the Excel table.
   2. Condition control inserted where the ID value from the Excel table is equal to ID value in the Microsoft Form submission.
      1. If yes, send an email with the meeting link.
      2. If no, send an email with alternative instructions.
5. End

When I test the flow, I get an error at the "List rows present in a table" step. Under "Inputs", I can see the ID value captured under the "Filter Query" field. Under "Outputs", an error is shown. The error is reproduced below:

BadRequest

Status Code: 400

Headers:

Key	Value
Pragma	no-cache
x-ms-request-id	55ead164-c257-48e2-94ea-a...
Strict-Transport-Security	max-age=31536000; includeSub...
X-Content-Type-Options	nosniff
X-Frame-Options	DENY
Timing-Allow-Origin	*
x-ms-apihub-cached-response	false
Cache-Control	no-store, no-cache
Date	Mon, 27 Jul 2020 14:36:37 GMT...
Content-Length	500
Content-Type	application/json
Expires	-1

Body:

{

  "status": 400,

  "message": "The $filter expression must evaluate to a single boolean value.\r\n     inner exception: The $filter expression must evaluate to a single boolean value.\r\nclientRequestId: 55ead164-c257-48e2-94ea-a0e8240e9dd1",

  "error": {

    "message": "The $filter expression must evaluate to a single boolean value.\r\n     inner exception: The $filter expression must evaluate to a single boolean value."

  },

  "source": "excelonline-we.azconn-we.p.azurewebsites.net"

}

Then the flow stops working after this.

Any advice would be appreciated as to why this flow is not working?

[#2]

Hi @chintan_patel 

Have you tried removing the filter from List rows present in a table?

Your condition already matches the ID from the form response to the ID listed in your spreadsheet so the filter is not needed.

Let us know how you go?

Cheers

Damien

[#3]

Hi @Damien Rosario 

Thank you for responding.

When I remove that filter from List rows present in a table , the Apply to Each control goes into an infinite loop where I get bombarded with infinite emails from the If no condition. I had to stop the flow and disable it else it would have been catastrophic.

Regards

Chintan

[#4]

Hi @Chintan Patel 

If it gives you the right result but continues in an infinite loop, try a Terminate action (set it to Succeeded) after the Send email action.

That'll kill the workflow after the first execution which should give you the desired email outcome and also stop getting spammed.

There's other ways to get around this, but this is the first thing that popped into my head that's easy to implement.

Cheers

Damien

[#5]

Hi Damien,
I`m facing the same problem, however, I`d like to send an e-mail in both situations: either the ID matches or not.
Could you please, share some flow that addresses this issue?
Thanks in advance