cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sonia909
Helper I
Helper I

How to show the latest record when ID repeats multiple times?

I have scenario where I need to pull the max(timestamp) for records with the same id without running into delegation issue.

I first filter the data set based on the selection in the dropdown (state). I'm stuck on grabbing the max(timestamp) from a group (group is based on ID).

 

Here's an example:

ID     STATE       TIMESTAMP     Row ID

1      VA            1-1-2021             1

1      VA            1-1-2022             2

2      VA            1-1-2021             3

2      VA            1-1-2022            4

1      MD           1-1-2019           5

1      MD           1-1-2022           6

 

I need the gallery to show the records in bold only. Each time a record updates, the latest version needs to appear up. 

My data is stored in SharePoint. 

 

Any help with this is highly appreciated! Thank you!

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Ok, that's a good thing then. It gives us a way to reduce down the ForAll records we need. Is it ID 1-25 for every state? Or if 1-25 is a dynamic data we can pull from we can use that too, but for 1-25 we can do the following to get this. 

So This works in my test setup, you'll have to tweak it to Match yours, But it's close. 
For the ONCHANGE of the dropdown I have this. 

Clear(colDataState);
ForAll
(
Sequence(25,1) As StateID,
Collect(colDataState,LookUp(SortByColumns(colData,"TIMESTAMP",Descending),State = Dropdown1.Selected.Value && ID = StateID.Value))
)

coldata is your datasource with all the TIMESTAMPS

coldataState needs to be set to gallery Items. 

 

This basically is looping through the 25 options, and then grabbing the newests one via the sortbycolumns TIMESTAMP Descending, so it takes the top one of those matches. 

 

Hopefully this will get you there, seems to do as you want on my test end. 

View solution in original post

Yeah, just on your Save button, assuming it's not a form after the Patch? If it's a form then the "On Success" property of the form. just add the same code as the OnChange. If it's a Patch just add semi colon ; after the Patch and then the OnChange code from dropdown. Should rebuild / refresh the collection. 

 

If it doesn't quite refresh, then you can do a Refresh(datasource); before the OnChange code so it'll refresh the data with the new record in it before rebuilding collection. 

View solution in original post

30 REPLIES 30
cwebb365
Super User
Super User

So on your Filter, if it's like this 

Lookup(datasource, State = dropdown.selected.value)

You could do something like 

Lookup(SortByColumns(datasource,"TIMESTAMP",Descending),State = dropdown.selected.value)

This should grab the first record when duplicates exist, and the sort puts newest on top. You could also use First() in place of lookup too, but it's the same thing essentially. 

sonia909
Helper I
Helper I

So the solution works partially, it's only returning the max timestamp for only group 1.

 

Here's an example of the dataset:

ID     STATE       TIMESTAMP     Row ID

1      VA            1-1-2021             1

1      VA            1-1-2022             2

2      VA            1-1-2021             3

2      VA            1-1-2022            4

1      MD           1-1-2019           5

1      MD           1-1-2022           6

 

Lookup(SortByColumns(datasource,"TIMESTAMP",Descending),State = dropdown.selected.value)

Gallery now only displays this:

1      VA            1-1-2022             2

 

it's missing, group 2 max for VA, etc. 

 

Thank you for your assistance! @cwebb365 

 

I should of mentioned I'm applying the code on Items property and I'm using data table to display the output.

Yeah that makes a big difference 🤣

 

So is the data you are getting from this have more columns? Or are you just trying to return the latest rowID for the item? 

The columns I'm trying to display are:

ID, Timestamp, Description

I created same version of the current page but instead of using a Data Table, I created a gallery but the output result is the same. It's still returning just the max for the selected state, instead of max of each group ID

I know you can do it with a ForAll, but that's not ideal but may work for you. I'm trying to find a "Faster" / "Efficient" way if it's possible, but in the meantime, you can use something like this to get your results. On the onselect of your drop down selection put:  

ClearCollect(colDatasource);

ForAll
(
GroupBy(DataSource,"STATE","GroupedState"),
Collect(colDatasource,Lookup(SortByColumns(datasource,"TIMESTAMP",Descending),State = dropdown.selected.value)
)

Then in your gallery or table use colDatasource. 

Scratch that. I forgot GroupBy isn't delegable. 

How do you populate your dropdown selection? You can just use this. 

 

 

ClearCollect(colDatasource);

ForAll
(
<dropdownControl Items Propterty>,
Collect(colDatasource,Lookup(SortByColumns(datasource,"TIMESTAMP",Descending),State = ThisRecord.Value)
)

 

 

Can't remember exact syntax, but basically you point it at the column in the dropdown. If it's a single column could just be Value, but might be a column name in there. This will just loop through your States, do a query for all 50, which shouldn't take too long. Then populate your gallery. 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,619)