Generate incremental request ID based on lockup value using Power Automate

[Muralibk_sp]

We have list name "Location Data Entry" list, and column "Location Request ID" which need to generate auto ID incremental of puls 1 when new items submitted on SharePoint list.

There is lookup column name "Location Data".  Data as below

Title                Value 

India               IN

South Africa   SA

England          EN

West Indies    WI

Now When user submit item on "Location Data Entry" and selected "South Africa" from lookup column. In this, "Location Request ID" need to generate as "SA-001". Incase if "SA-001" is already exist on the SP list means, Flow need check if request ID already exist or not and then it need to increment as "SA-002" like.

Similar way, if user select "India", then "Location Request ID" need to generate as "IN-001" only not like "IN-003". So for each column based on selection "Location Request ID" need generate Like SA-001, SA-002, IN-001, IN-002, EN-001, EN-002 etc. 

Please guide us, under get items which filers need apply. Your suggestions or guidance's are highly appreciated. 

[David_MA]

Is there a reason you cannot use the internal ID column for this purpose? If you want to pad it with leading zeros until you get over 1,000 items, you can use the expression formatNumber(triggerBody()?['ID'],'000') to achieve this. You can combine it with a concat expression to add the value and hyphen before it.

[Muralibk_sp]

We cannot use internal ID column because our "Request ID" need to generate based on the lookup value selected. For Example, If user select lookup value as "England", then flow need run and update "Request ID" as "EN-0001" for newly created it. When second item created and user select lockup value as "India", then flow need to start and update "Request ID" as "IN-0001". Thats why internal ID cannot be use. Please suggest any other ideas. 

[David_MA]

Unless someone can think of a better way, I don't see any easy way to do this except:

• Create four new lists in your SharePoint site. One for each of your four locations. It just needs to have the title column and nothing else. You can set any default value you want for the title.
• Then in your flow, add a switch based on the region.
• In each switch, point to the corresponding list and create a new item in that list.
• You can then use the ID from the corresponding list to generate your unique IDs.