Power Platform Community Forum Thread Details

Power Automate: Send Email to Manager Based on Matching QGCode Between Two SharePoint Lists

Hi,

I have two SharePoint lists:

1. Accommodation Safety Assurance Review (ASAR)

This list is used to record accommodation inspection scores. Each inspection record contains:

QGCode

Property Address

Region

Multiple inspection score fields (dropdown values 1, 2, or 3)

2. Address Book

This list contains:

QGCode

Property Address

Manager Email

Other property information

I have already configured the ASAR form so that when a user selects or enters a QGCode, the Property Address and Region are retrieved from the Address Book successfully.

What I am trying to achieve

I want a Power Automate flow that:

Triggers when a new inspection record is created (or modified) in the ASAR list.

Checks whether any inspection score equals 3.

If no score equals 3, the flow does nothing.

If a score equals 3:
- Read the QGCode from the ASAR item.
- Find the matching record in the Address Book list using the same QGCode.
- Retrieve the Manager Email from the matching Address Book record.
- Send an escalation email to that Manager.

Example

ASAR List

QGCode	Fire Safety Score
R060	3

Address Book

QGCode	Manager Email
R060	manager@company.com

Expected result:

Flow finds QGCode R060 in Address Book.

Retrieves manager@company.com.

Sends the escalation email to that manager.

Current Issue

The flow triggers successfully and the condition runs, but I am struggling to retrieve the correct Manager Email from the Address Book and pass it into the "Send an email (V2)" action.

I am currently using:

Plain Text

first(body('Get_items')?['value'])?['Manager']

after a Get Items action, but I am not sure if my Filter Query or expression is correct.

Can anyone provide an example of the correct:

Get Items Filter Query

Expression to retrieve the Manager Email

Send Email configuration

for this scenario?

This version clearly explains your lists, the business requirement, what you've already achieved, and exactly where you're stuck.

Provide your feedback on BizChat

Categories:

Building flows

If you still need assistance with this, I suggest cleaning up your post if you want to get a response if what I suggest below is not what you are asking. I would also tighten it up to focus on the issue you are trying to resolve, as I don't understand why you are asking people to provide feedback on BizChat to help you write a clear post here.

Please share a screen shot of your flow that focuses on the issue, which I think is getting the manager e-mail from your address book. It seems that the common denominator between your two lists is a GC Code. If so, I would modify your first (ASAR) to add a look up to the Address Book list so people choose the GC Code from it. When you add it as a lookup, include the ID value. For example:

When you do this, the ID to the item in the second list will be available in the dynamic values from your trigger action. You can then simply use a Get item action to get the item from the address book list based on the ID to retrieve the e-mail address.