web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / how to lookup second r...
Power Apps
Answered

how to lookup second record

(0) ShareShare
ReportReport
Posted on by Sahara22 568

Hello,

 

I have two records for the same Employee's name. if i always want to lookup the second record in a table, how do i do that?

 

I tried this and it didn't work: 

 

Last(LookUp(Table1,AUTONAME_3.Text in EMP_NAME).FINAL_LOCATION)

 

Any help would be great! thank you! 

Categories:
Building Power Apps
I have the same question (0)
  • BCLS776 Profile Picture
    BCLS776 8,994 Moderator on at

    Lookup only returns the single, first record that matches your criteria, so you need to put a Sort on your Table1 to get that second record to the first position.  

  • Sahara22 Profile Picture
    Sahara22 568 on at

    Wow thats sounds quite complicated. 

     

    How about if i use a galley to show both records, can i do that? if so, what function would I use?

  • BCLS776 Profile Picture
    BCLS776 8,994 Moderator on at

    Yes, if you use a Filter instead of a Lookup, you can return a set of records that can be used in a gallery.

     

    Using the Sort on the Lookup isn't hard once you see it. Post a bit more info about how to distinguish which of the two employee records to use and we can give you some example code.

  • Sahara22 Profile Picture
    Sahara22 568 on at
    DEPTWORKGROUPRDATEEMP_RIDEMP_IDEMP_NAMEPOSITIONREG_RELSTART_DTEND_DTFINAL_LOCATION
    rr07/05/2021 MONrrANrr2021/07/05 12:30:002021/07/05 20:30:00P_5P_C
    rr07/05/2021 MONrrCARrr2021/07/05 12:30:002021/07/05 20:30:00P_5P_C
    rr07/05/2021 MONrrMARK rr2021/07/05 12:30:002021/07/05 20:30:00 

     

     

    rr07/05/2021 MONrrPUrr2021/07/05 21:00:002021/07/06 05:00:00P_3P_
    rr07/05/2021 MONrrSZrr2021/07/05 21:00:002021/07/06 05:00:00P_3P_
    rr07/05/2021 MONrrMARKrr2021/07/05 21:00:002021/07/06 05:00:00

    P_3P_

     

     

    Based on these two records in the same table I need to list all EMP names and FINAL location based on "MARK" which is (AUTONAME.TEXT)

     

    So i need to see: 

    EMP Name:   Location: 

     

    AN                   P_5P_C

    CAR                  P_5P_C

    PU                     P_3P_

    SZ                    P_3P_

     

     

    any idea how to do that ?

  • Sahara22 Profile Picture
    Sahara22 568 on at

    also, I don't mind doing two galleries if that works 

  • BCLS776 Profile Picture
    BCLS776 8,994 Moderator on at

    I didn't realize you have two tables of employees and that one of the employees appears in both tables - have I got that right?

     

    Also, are the columns exactly the same in each table? If so, put them together first with a: ClearCollect(colBigTable, Table1, Table2). Then you can sort and filter colBigTable to work with a single gallery.

     

    I don't see any difference between the two records for Mark in your example above - is there another column to use to decide which record is the right one? Maybe a "date entered" column so we can use the most recent entry?

  • Sahara22 Profile Picture
    Sahara22 568 on at

    Its just one table but "mark shows up twice" and works two shifts and two groups of people. I need to list all the people that mark work with the whole day. columns are the same. 

     

    Difference is the "Location" mark works in P_5P_C and P_3P_

     

    sorry for the confusion 

  • BCLS776 Profile Picture
    BCLS776 8,994 Moderator on at

    OK, to make a list of EMP Name and Location for all people who work with Mark (and including Mark), try the following code:

    Clear(colOutput);
With(
 {FilterRecords: Filter(Table1, EMP_NAME = "MARK")},
 ForAll(FilterRecords As CoWorker,
 ForAll(Filter(Table1, RDATE = CoWorker.RDATE) As Employee
 Collect(colOutput,
 {
 EMP_NAME: Employee.EMP_NAME,
 Location: Employee.Location
 }
 )
 )
 )
);

    The results are stored in a collection called colOutput.

     

    Is that what you are after?

  • Sahara22 Profile Picture
    Sahara22 568 on at

    Sorry I'm not a pro at power apps but where would i put this code? On a gallery option?

     

    also EMP_NAME is based on the user, it automatically pulls the name so it wont always be mark. I have it based on a label "AUTONAME.TEXT"

     

    For : 

     

    CoWorker,
    Employee.EMP_NAME,
    Employee.Location

     

    is that something that it will create? I don't have those specific functions 

     

    thanks again for all your help! 

  • BCLS776 Profile Picture
    BCLS776 8,994 Moderator on at
    @Sahara22 wrote:

    Sorry I'm not a pro at power apps but where would i put this code? On a gallery option?

     

    also EMP_NAME is based on the user, it automatically pulls the name so it wont always be mark. I have it based on a label "AUTONAME.TEXT"

     

    For : 

     

    CoWorker,
    Employee.EMP_NAME,
    Employee.Location

     

    is that something that it will create? I don't have those specific functions 

     

    thanks again for all your help! 

    You can put the above code into the OnSelect property of a button, and then pressing the button will create the colOutput collection. Then, set the Items property of a gallery to colOutput. You can replace "MARK" with AUTONAME.TEXT to make use of the label you have set up for searching.

     

    The CoWorker and Employee references are record identifiers that help make the code a bit clearer and preserve context. You don't need to add additional mentions of these for the code to work.

     

    Also, it is free-typed, so there's a chance of an error in there. Another community member may chime in with a more elegant solution, but this should get you closer.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Season of Giving Solutions is Here!
Explore Copilot Studio Forums, Blogs & Galleries!

Quick Links

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 740 Most Valuable Professional

#2
Michael E. Gernaey Profile Picture

Michael E. Gernaey 342 Super User 2025 Season 2

#3
Power Platform 1919 Profile Picture

Power Platform 1919 268

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans