“I need to find the last value for an entry in a SharePoint list to increment its id for a new item, how do I recognise the last item with Power Automate?”
In most cases when you use SharePoint list as an information storage, you can utilise the existing item ID. It’ll be unique, it’ll be incremented by 1 for each new item, and you can use it to build more complex IDs. But that’s not true if you store various data in the same SP list. For example, if you store entries from various MS Forms in the same list with unique, custom IDs – Form1_ID, Form2_ID… The default ID will keep incrementing for each item, which would leave gaps in your IDs sequence. The only solution would be to get the last ID in the desired sequence, and increment it. And for this you need to find the item with the last ID.
Get only the last item
To get the last item you’ll need a lookup to the list. But don’t take all the items in the list, that would be more than you need. You need only one specific item. The one that’s part of the desired sequence, and that has the highest id. That’s three things to configure in the ‘Get items’ action.
Take only items from the desired sequence
As already mentioned, you want only items from the same sequence. Configure the ‘Filter Query’ in ‘Get items’ to take only the items where the ID contains or begins with a specific string, e.g.
substringof('Form1', CustomID) = the column CustomID contains string 'Form1'
or
startswith(CustomID, 'Form1') = the column CustomID starts with string 'Form1'Note: CustomID is the internal name of the column with the custom ID.
Sort the returned items from newest to oldest
Since you want the latest item, with the highest id, you should define order of the returned items. ‘Get items’ has a field ‘Order By’ that allows you to do that. The safest way to get the latest item is to use the standard SharePoint ID = the higher the ID, the newer the item. Define the filter to return items in descending order by their ID.
ID desc
Get only the latest items
The ‘Get items’ will return the right items, in the right order, but it’d still return more items than you need. You need only 1 item, the last one. And that’s the last step, configure the ‘Get items’ action to return only 1 item. And since you sorted the result from the newest to the oldest, the 1 returned item will be the newest one.
The ‘Get items’ action above will do a lookup to the TestList. It’ll search for items whose CustomID column begins with the string ‘Form1’. Once it gets those items, it’ll sort them by ID, and return only the 1 item with the highest ID. You can take the result, extract the ID number, increment it, and create new item with the new ID.
Don’t worry about the ‘Apply to each’ it’ll add, since it’ll return just 1 item, it’ll run only once.
Summary
When you need to get the last item from a SharePoint list in Power Automate, the easiest way is to do it all in the ‘Get items’. The action itself gives you all the tools you need – you can filter the items, order them, and limit how many items it should return.
You can follow the same process also if you want just the last item in general, just skip the ‘Filter Query’ part.