Solved: Re: How to show the latest record when ID repeats ...

sonia909 · ‎08-23-2022

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!

cwebb365 · ‎08-24-2022

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

cwebb365 · ‎08-24-2022

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

cwebb365 · ‎08-23-2022

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 · ‎08-23-2022

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

sonia909 · ‎08-23-2022

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

cwebb365 · ‎08-23-2022

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?

sonia909 · ‎08-23-2022

The columns I'm trying to display are:

ID, Timestamp, Description

sonia909 · ‎08-23-2022

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

cwebb365 · ‎08-23-2022

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.

cwebb365 · ‎08-23-2022

Scratch that. I forgot GroupBy isn't delegable.

cwebb365 · ‎08-23-2022

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.