Filter Data seen in Gallery

[SteveGetts]

Hello, 

I'm wondering if anyone is able to offer some support. 

I'm wanting to filter a gallery view so that it shows the most recent record associated with a client profile.

I currently have a 'Client List' Table (Parent) with a one-many relationship with a 'Appointment Detail' table (Child) both in SharePoint. There will be multiple entries in the Appointment Details table that will be associated to a record in the Client List. 

(Please see attached Screens of both table)

Currently in the gallery view on my app, it will show all entries in the 'Appointment Detail' table. (see attached Screen) 

How do I filter this gallery view so that it only shows the most recent record for each client.  

Thanks for the support 

[WarrenBelz]

Hi @SteveGetts ,

You did not say how the items were linked, but I assume it is Name (a bad name for a column and I also see it is a Lookup column which are not the best choice if using Power Apps and complicate things here). Are you want the most recent record for every client to be in the Gallery, or do you simply wish to display the most recent record for a selected Client ? The first one is not easy.

[SteveGetts]

Hi @WarrenBelz 

Thanks for the reply. 

Ideally I want to be able to see the most recent client for every client. As you assumed the tables are linked by the name column which is a look up column. 

If this configuration is incorrect and could be done a better way I'm happy to do so. I'm currently quite new to this so still trying to learn best ways to do things.

I did read somewhere that I should have an ID column on my client list that is unique to each client, and then link the tables via this column. Would this make things easier? 

Any support is very much appreciated. 

[rubin_boer]

hi @SteveGetts 

With the data in the third picture please try:

AddColumns(
    GroupBy(
        SortByColumns(
            yourDataSource,
            "yourGroupByColumn", //date in your name (reconsider this name)
            Descending //as you want the latest date
        ),
        "yourGroupByColumn",
        "Appointments" //name for the group
    ),
    "Dates", //add a column for dates
    First(Appointments).date,
    "Costs", //add a column for cost
    First(Appointments.cost).cost
)

In your gallery with the datasource above:

add the labels

1. Thisitem.Name

2. Thisitem.Dates

3. ThisItem.Cost

Result

I simulated your datasource here

ClearCollect(
    someStuff,
    {date: Today(), name: "Joe", cost: "$100.00"},
    {date: Today()-1, name: "Joe", cost: "$300.00"},
    {date: Today(), name: "Pete", cost: "$20.00"},
    {date: Today(), name: "Susan", cost: "$500.00"}
)

Item for the gallery

Hope it helps,

R

[rubin_boer]

hi @WarrenBelz , sorry didnt see you already responded i started and left and came back. @SteveGetts you in capable hands here. enjoy the app.

[WarrenBelz · Accepted solution]

Hi @SteveGetts ,

A couple of things - yes to your question on linking by ID - I do this for all of my "child" lists as it is a unique value and cannot be changed in the parent, so the child records will always belong to one specific record.
Lookup columns are good for SharePoint if you want to use that interface in Grid View or the standard SharePoint edit form, but add considerable complexity and in many cases restrictions to what you want to do in Power Apps. In most cases, you can simply store the "primary" field data in a Single Line of Text field in SharePoint and generate the list of values in your drop-down (or whatever you are using to choose them) from the second field data. You can also easily get other related data with a LookUp function in Power Apps.
If you do all of this, (I will call the numeric linking field IDRef here), the structure for the gallery Items would be something like this (pretty much what @rubin_boer posted)

AddColumns(
    GroupBy(
      Sort(
         YourSecondList,
         ID,
         Descending
      ),
      "IDRef",
      "Data"
   ),
   "OtherField1",
   First(Data).OtherField1,
   "OtherField2",
   First(Data).OtherField2
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[SteveGetts]

@WarrenBelz @rubin_boer 

Thanks for your support here I will take on board your suggestions and see if I can figure it out. 

Thanks Again!