cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

SharePoint list find latest data

I've got a SharePoint list (30,000+ records) with dates, unique IDs, and a reported flow rate. Is there a way to perform a flow that would mimic a SQL query that returns the latest flow rate grouped by unique ID (updated date is the Max(Date) over the unique ID)

 

List:

ID     Rate   Date

1      0         2018-01-01

2      1         2018-01-02

1      3        2018-01-02

2      4        2018-01-03

 

Wanted:

ID     Rate   Date

1      3        2018-01-02

2      4        2018-01-03

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Hi @pt1,

 

If you want to include the data of the obtained item in a CSV table, you need to use List item ID (this is the unique one in the metadata), not the ID you mentioned, which needs attention.

Add some actions to the previous Flow.

Initialize a variable ItemID, save the obtained List item ID in the variable, and replace Compose with append to array variable.

17.PNG

Configure Filter array to configure the value of Get items in From field, The condition is that the variable ItemID contains List Item ID.

18.PNG

Use Select to get the fields needed for configuration, create a CSV table, and send it as an Email body.

19.PNG

Take a try.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Highlighted
Community Support
Community Support

Hi @pt1,

 

It seems that you want to group by ID and then find the item for each group's latest date.

I am confused about the ID column. Is this your custom column?

It seems that we cannot create a column with the name ID. And since it is a unique ID, why does a list have multiple items with the same ID value.

Could you provide more explanations about your needs? Or I understand that it is wrong.

I did a test on my side and created a UID column similar to the one you mentioned, like:

11.PNG

In addition to the method of grouping items and finding the maximum value of the date, you could refer to this method:

   Use variables to hold the values of all UIDs, then use union() function to remove duplicate values.

12.PNG

   Then use Apply_to_each to traverse the array, configure Get items-Filter Query to get the maximum value of the date in each group, and use Order by to get only the item with the largest date.

   You can also use Compose to verify that the item is expected. Note that union() function is written first in Expression and then filled in the field.

13.PNG

Please take a try and let me know if you have any question.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted

Thanks Barry,

 

It looks like the flow is working, but I don't seem to be able get the data out (ideal csv table for easy email). I've created another array variable to store the data from the compose statement, but I'm struggling to create the table. Any help would be greatly appreciated.

 

Thanks,

Paul

Highlighted

Hi @pt1,

 

If you want to include the data of the obtained item in a CSV table, you need to use List item ID (this is the unique one in the metadata), not the ID you mentioned, which needs attention.

Add some actions to the previous Flow.

Initialize a variable ItemID, save the obtained List item ID in the variable, and replace Compose with append to array variable.

17.PNG

Configure Filter array to configure the value of Get items in From field, The condition is that the variable ItemID contains List Item ID.

18.PNG

Use Select to get the fields needed for configuration, create a CSV table, and send it as an Email body.

19.PNG

Take a try.

 

Best Regards,

Barry

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Top Solution Authors
Top Kudoed Authors
Users online (11,589)