cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
SamGibbs
Level: Powered On

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
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."

SamGibbs
Level: Powered On

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

SamGibbs
Level: Powered On

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
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,132)