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!

 

 

30 REPLIES 30
sonia909
Helper I
Helper I

No worries.

 

So if this is the data set, it needs to show the ones in bold. Each state has id 1-25 and for each ID I need the max(timestamp).

 

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-2020             3

2      VA            1-1-2021            4

1      MD           1-1-2019           5

1      MD           1-1-2022           6

2      MD           1-1-2018          7

3     VA             1-1-2022          8

3      VA            1-1-2021          9

25       VA          1-1-2020         10

User can select a state from the dropdown and the gallery should show ID 1-25 for that state but only the latest for each ID.

 

If VA is selected then gallery should display the following:

ID   State     Timestamp   

1      VA          1-1-2022

2      VA          1-1-2021

3    VA             1-1-2022

etc..

25     VA           1-1-2020

 

@cwebb365 

OK, I think I got it now. Let me come up with something.

So, the data for the state, can it grow very large? 

yes, for each state ID 1-25 get updated so data gets over 2000 pretty quick.

 

But for now if there's a quicker solution I can apply for records less than 2000 then that works too. I started patching data to a new list which contains only 2021 and this year data. Ideally, I would like the solution to work for large data sets. 

 

cwebb365
Super User
Super User

Hmm, So let me ask you this. Is ID 1-25 static? Just has a whole bunch of versions? Just trying to maybe come up with a logical way to process, since not having a Distinct() delegable function makes things really hard. But if it's always 1-25 we could just look through 1 thru 25 ID for that state and get the highest values really easily with a sequence. Anyway, sorry for all the questions but trying to formulate a workable plan, if it's possible ;). 

 

I guess what I'm asking, is what is the relationship or what Feeds the ID column. 

sonia909
Helper I
Helper I

Each state will always has ID 1 through 25. The ID is patched in when user presses save button. User does not enter in the ID, instead it's getting pulled in based on the ID user clicks on in the Data table. 

 

The code to patch ID is:

ID: DataTable.Selected.ID

 

Let me know if there's anything else I can clarify. 

 

@cwebb365 

 

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. 

sonia909
Helper I
Helper I

So this worked great! Thank for your time and help on this. 

 

Is it possible to "refresh/reload" the collection on the save button so user doesn't have to refresh the entire page to see the latest entry they make. At the moment, if user updated ID 2, they have to refresh it to see the new entry. FYI- the app does not navigate away from the home page which contains the data table and submit button. It's basically a one page app. 

 

&& if they refresh, it defaults to the first state in the dropdown. Ideally after refresh it should stay on the state user had selected.

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. 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Ideas

Check out the New Ideas Site

We are excited to announce a new way to share your ideas for Power Apps!

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