cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

filter gallery by most recent entry in another table

Hi all,

 

I am not sure if this is possible based on my current set up as I can't seem to work it out. I have included a simplified example with dummy data in the picture below. I want to be able to display a gallery for users, based on the top table (table 1) where they have a matching record in table 2 that has a status of 'Current'. To complicate this it needs to be only when 'current' is the most recent record in table 2. Does anyone know of a solution, or where this has explained before?

 

Thanks,

Sam

 

Capture.PNG

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: filter gallery by most recent entry in another table

@SamGibbs 

Here's how I would create the Collection that you were attempting to write.  I did manage to put some test data in there and it seemed to work OK.

 

ClearCollect(
    myJoinedTables,
    Filter(
        AddColumns(
            Table1,
            "Status", 
            LookUp(
                SortByColumns(Table2,"ID",Descending,"Status Change Date",Descending),
                ID = Table1[@ID],
                Status
            ),
            "Status Change Date", 
            LookUp(
                SortByColumns(Table2,"ID",Descending,"Status Change Date",Descending),
                ID = Table1[@ID],
                'Status Change Date'
            )
        ),
    Status="Current"
    )
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: filter gallery by most recent entry in another table

@SamGibbs 

In your problem there are 2 table:  Table1 (ID, Name) and Table2 (ID, Status, 'Status Change Date').  You want to show only the records in Table2 for a matching person in Table1.  I will assume the matching person is selected via a ComboBox.

 

Create a ComboBox and use this code in the following properties.

AllowMultipleSelections: false
DisplayFields: ["Name"]
Items: Table1

 

Then create a Gallery and put this code in the Items property

Filter(Table2,ID=ComboBox1.Selected.ID)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Highlighted
Helper II
Helper II

Re: filter gallery by most recent entry in another table

Hi,

 

Thanks for responding. I don't think I have explained it correctly in the initial post. The objective is for the gallery to 'Show records in table 1, where there is a matching record in table 2 (ID), and the (status) = "current", and where "current" is the most recent record'.

 

I have tried using a collection to pull all of table 2 where status = "current", but can't work out how to refine this so that the collection only shows the most current.

 

It might be better to find the most current record for each ID, and then only show the ones where the status = "current", but I can't figure out how to do this.

Super User
Super User

Re: filter gallery by most recent entry in another table

@SamGibbs 

Here's how I would create the Collection that you were attempting to write.  I did manage to put some test data in there and it seemed to work OK.

 

ClearCollect(
    myJoinedTables,
    Filter(
        AddColumns(
            Table1,
            "Status", 
            LookUp(
                SortByColumns(Table2,"ID",Descending,"Status Change Date",Descending),
                ID = Table1[@ID],
                Status
            ),
            "Status Change Date", 
            LookUp(
                SortByColumns(Table2,"ID",Descending,"Status Change Date",Descending),
                ID = Table1[@ID],
                'Status Change Date'
            )
        ),
    Status="Current"
    )
)

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Helper II
Helper II

Re: filter gallery by most recent entry in another table

Hi,

 

Thanks for you help, it works great, although I have ended up tweaking the data storage to make it a bit easier for myself anyway.

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (4,317)