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

[sonia909]

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!

[sonia909]

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 

[cwebb365]

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

[cwebb365]

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

[sonia909]

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]

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 ;). 

[cwebb365]

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

[sonia909]

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 

[cwebb365 · Accepted solution]

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]

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.

[cwebb365 · Accepted solution]

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.