Get ID from SharePoint list where fields match

[Siddz]

I'd like to return the ID from the row in SharePoint List #2 where the "Employee Name" field matches the "Employee Name" field in List #1. I'll need to store that ID (to use for dynamic content as a parameter in a URL).

Here is the general outline of the process:

• Get items from List #1
• Get items from List #2
• Look up from List #2 where the "Employee Name" field matches the "Employee Name" field from List #1
• Return ID of that row
• Store ID for reference in email body

Both "Employee Name" fields are SharePoint person fields. 

It seems pretty straightforward. I'm guessing I'll need to use a filter query, but I'm not sure how to construct it.

[Rhiassuring]

Hi there, 

What's the trigger? If it's on List #1, you'll already have employee name - so all you need to do is use a Get Items on List #2 with the filter query of Employee Name eq 'Employee Name' (where the second employee name is dynamic content from the List #1 output for Employee Name, and the 's are written around the purple bit.) 

Then you'll just use the ID. You can put the ID into a variable. 

Are you doing this for one as triggered, or for multiple employees from List 1 in one run?

[Siddz]

Thanks, @Rhiassuring.

This is a scheduled flow on List #1. For the filter query on the Get Items for List #2, do you know how I would store the ID after filtering the Get Items for List #2?

Not that it's relevant here, but the getPastTime() expression is:

getPastTime(1,'week','MM-dd-yyyy')

[Rhiassuring]

Hmm, well, it depends what you're going to do with it. I see you're going to use it as a parameter for a URL, but, will you have all of the collected IDs in a single URL parameter, or will you generate a URL for each person dependent on that? 

If the latter, then I'm guessing you're also going to want to connect the ID you get to the email address, so you'll probably want to build an array. 

So if you initialize an Array at the top of your flow, then after your Get Items, you can do "Append to Array" and do something like this:

{

"Email":"dynamic content of users email",

"URL":"www.URL.com/whatever?ID=DYNAMICCONTENTID"
}

That way, outside of your apply to each, you can send the email to each person ............... ..........but now that I've said that, I don't know why you wouldn't just send it right there in the loop, and not need to hold the data at all.

Errr. I've overcomplicated it. Can you tell me what your URL outcome looks like? Is it 1 URL per person, sent to each person? Is it 1 URL per person with all URLs in a single email with their name? What does the output look like?

[Siddz]

Thanks, @Rhiassuring.

• The first Get Items returns a handful of employees from List #1 (using a filter query).
• The second Get Items returns all employees from List #2.
• Both lists have an identical column called "Employee Name" (SharePoint person field)
• I'd like to get the List #2 row ID for each employee returned in List #1.

My goal is to place a link to the employee's record (in List #2) so I can dynamically put that ID in the body of an email action. The ID will be part of a URL parameter so Power Apps can deep link to a specific record.

// Would like to dynamically insert the employee's ID from List #2
<a href="https://www.powerAppURL.com&Parameter=ID">View Employee's Profile</a>

[Rhiassuring]

Okay - and, will there be multiple in a single email? Ie, 

<a href="https://www.powerAppURL.com&Parameter=ID">View Sidzz' Profile</a>
<a href="https://www.powerAppURL.com&Parameter=ID">View Rhia's Profile</a>

[Siddz]

Thanks, @Rhiassuring.

Each email will have a single URL:

<a href="https://www.powerAppURL.com&Parameter=ID">View Siddz's Profile</a>

I'd like the scheduled flow to generate one reminder email per employee from List #1. 

[Rhiassuring · Accepted solution]

Oh perfect, then, you don't even need to retain your data or store it for later. Do it right in your loop.

Get Items (with the filter query from earlier), then right in your email, you can put the dynamic content of the ID into the HTML, and your Employee Name Email into the "To" field. 

It'll end up looking like this (but I'm using Created By Email / DisplayName in place of where you'd have employee name)

You don't really NEED that last apply-to-each, if you want to spend the time and tack the [0] on to the end of each thingy, but .... it works, and it does no harm.  But really, that last ApplyToEach will only have a single output per person.

[Siddz]

Thanks, @Rhiassuring. I appreciate your assistance!

I think it's getting there, but there's an issue with one of the "Apply to each" loops.

List #1 has six total items (rows) whereas List #2 has 25 items. Instead of generating six emails as intended, the flow is generating 25 emails (I replaced the email address for "Employee Name" with my email address for testing purposes, so I received 25 emails instead of the employees).

I will keep tinkering with it, but I welcome any advice. Thanks!

 

[Siddz]

Thanks, @Rhiassuring!

Got it working by adding a filter query when retrieving List #2:

EmployeeColumn/EMail eq 'Employee Email'

// "EmployeeColumn" is the person field from List #2
// "Employee Email" is the employee's dynamic content email address from List #1

Appreciate the help!

[Rhiassuring]

OH so there are multiple entries in List #2 for the Employee Name?

Wait I'm confused, I'm definitely missing something. 

6 employees in list #1. 

25 employees in list #2. 

If there are only unique employees in List #2, I wouldn't expect 25 emails, I'd expect 6. 

If there are multiple entries for employees in List #2, then you'd have multiple IDs, which means.. multiple URL? 

hahah sorry if I'm missing something obvious..

[Rhiassuring]

OH

Okay your update (you posted at the same time as me haha) makes sense. 

HOORAY!

[Siddz]

Yeah, I think we were posting at the same time. 🙂 

I apologize. I meant to say List #1 has six employees who match the filter criteria from the first Get Items. The list itself has hundreds of rows.

I couldn't figure out how to grab the ID for the records in List #2, but your approach worked great!