Unanswered

Show only latest records from Dataverse Table

(0) Share

Report

Posted on by addy2019

153

One of my Dataverse table has multiple versions of each ID with different LastModifiedDate. Is there a way to create a View in Dataverse Table to show only latest version for each record?

I'm using this data in Powerapps I tried doing same from that side but without using GroupBy & Distinct it doesn't seems possible & which also creates Delegable issue.

Thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (1)

Report

Hi @addy2019

You can simply use Last or First to get the record for the same.

First(SortByColumns(TableName,LastModifiedDate,Descending))

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Follow me on Twitter : @rampprakashd
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
addy2019 153 on at

Like (0)

Report

Hi @rampprakash ,

As I mentioned I need latest of every record (ID) not the FIRST record

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hi @addy2019

We are Using Sort By Descending and taking the First Record,

Else you can use Ascending and Take the Last Record:)

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Follow me on Twitter : @rampprakashd
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
addy2019 153 on at

Like (0)

Report

Hi @rampprakash ,

Let me explain again, after Sorting (Asc or Dsc) if you do First() it will give you ONE record only. If you do FirstN() it will give you as many records as you mention. My Problem is not that.

I have big dataset with less than half a million records with unique ID's I want latest record for each ID.

Was this reply helpful? Yes No
xxxx12345 19 on at

Like (0)

Report

Hi iam facing the same issue have you found out the solution?

Was this reply helpful? Yes No
xxxx12345 19 on at

Like (0)

Report

hi @addy2019 @rampprakash
i want to get the latest record of each user or id only to display in the table.Such in case every user creates 3 to 4 items in a day means i want one recent item of every user to be displayed

Was this reply helpful? Yes No
addy2019 153 on at

Like (0)

Report

Hi Ram,

This project of mine went into standby due to other priorities therefore I couldn't follow up. But before that I tried many different options which I couldn't recall now. But I'll be resuming it soon & will start from where I left off. Meanwhile I kept it open just in case someone has any inputs. I'll share with you my findings once I resume but I hope you'll find your answers before that.

Sorry to disappoint you.

Thanks
Apoorv

Was this reply helpful? Yes No
john_wilder_ds 4 on at

Like (0)

Report
Hi @addy2019 / xxxx12345,

I do this using a Spark SQL query, joining the table back onto itself to compare. In the example below I use the 'versionnumber' column as the comparison but you can use the same logic with the 'LastModifiedDate' column too.

SELECT t1.*
FROM YourTableName t1
LEFT JOIN YourTableName t2 ON t1.id = t2.id and t1.versionnumber < t2.versionnumber
WHERE t2.id IS NULL
To explain a little further, what we are saying here is:
For each record, check the table again and look for other records with the same ID and a higher version number.
(or later Modified Date in your example)
If there are none of these (t2.id IS NULL) then we know we have the most recent version of this ID.

Hope that helps. Please mark as Answer if you are satisfied with this response.

Thanks!

Was this reply helpful? Yes No
john_wilder_ds 4 on at

Like (0)

Report

@rampprakash See also above 🙂

Was this reply helpful? Yes No
tagiobahee 2 on at

Like (0)

Report

This works. Tanks.

nnkb24.pdf

Was this reply helpful? Yes No