Customise and manage Autonumber ID columns in SharePoint
HTML-код
- Опубликовано: 6 июн 2024
- This is the follow-on video showing you how to create a Central ID management system for all your ID number needs in SharePoint. Now you can freely customise your reference ID numbers with prefixes, suffixes, any number format you want and you can reset the autonumber any time and with any starting number. I'm looking to win some brownie points here. :-)
I am sorry it is 45 minutes long, but it is worth it for those of you wanting to completely manage all your ID fields in one place.
See first video here: • Create a Unique ID usi...
IN THIS VIDEO
============
01:06 - Reset ID number using a new list (option 1).
06:06 - Reset ID number in an existing list using a central ID Manager (option 2).
08:25 - Part a: Add Prefix and suffix to an ID (Central ID Manager)
09:23 - Part b: Apply Format masks to an ID
14:40 - Create the Flow to generate an ID with Prefix, suffix and Format mask.
37:52 - The correct variable data type (float) to use with SharePoint numbers.
42:09 - Change the starting ID number (test).
![TRASH or PASS! Eminem ( Houdini ) [REACTION!!!]](http://i.ytimg.com/vi/cB1WrZcMwMc/mqdefault.jpg)
Great - exactly what I needed! Thank you!
Fantastic Tutorial! Been looking for a solution like this for a while and perfectly fits my needs.
Thank you I've been looking to do something like this for a while.
Excellent video, this is really going to help manage our unique references for our new system. Thank you!
Excelente video gracias John , saludos desde Chile.
I have put this idea forward to our IT team. They love it, and they are now making a central ID system for our document management. Thank you x x
That is awesome! Hope it all works Min.
Hi John, This is great presentation. Clear, well explained and easy to follow. Thanks👍
Glad it was helpful!
Hello John - This is excellent and well presented. Loved the review of the failure detail. Great tutorial and very useful.
Many thanks!
Great video. For whatever reason i couldn't type FormatNumber and then pull in the ID, so i used the option available under Expression called "Format data by examples" and it provided this, which worked perfectly:
concat('Case ', formatNumber(float(items('For_each')?['ID']), '000000'))
Hi John, Thank you so much! these 2 videos are very usefull for me. I gonna update my flow right now due a request to reset the Id number hehehe
Fantastic!
thank you John
This is great, thanks! How do you handle when you have update item for a multiselect field that is set as required? Unfortunately, I'm not able get this to work correctly without overriding the original data in those fields.
Technically, if you are updating the property with it's matching dynamic property in the Update action, it should work, regardless of it being a required field. The data is stored as text using a semi-colon to separate the multiple choices, so it's putting back what it originally had. If the property is empty in the update action,naturallyit is like saying "leave blank" and that will conflict with the required option. If this is not working for any reason, could you send me a screenshot of the update action and I will take a look at it.
Hello John. Very nice learning! I have create mine database but found something should be repaired. I have managed to create two same unique IDs... guess because when theflow is running and some one create in the same time a record it can take the same ID number and then in the records we will have two identical numbers. How to avoid duplicate numbers? There must be some way or block the process while it's running and another user can't use it? Or there is another way?
Hi John, this works great for Items in a List, Thanks! What about Files in a document library? Can I make the new created files name (Or some Content type in the row) be generated from the same ID's List? I'm having trouble getting it to work... Looks like the ID's List can't be selected..
wow thank you so much for the tutor! but what if the sharepoint list hit by more than 10 person in same time john? will the booking number got duplicated?
Mr. Day, thank you very much for this presentation, it has been very helpful for me to develop a SharePoint List which autogenerates a self-configured unique number. I have an additional question regarding the Get ID Information step. In the IDs list generated during the presentation, the ID number from that list is hard coded into the Get ID Information flow step. Can it be a variable?
I'm wondering how you would go about having the ID selected in the IDs list based on input from a field in the Bookings List? For example, let's say that you add a field to the Bookings list for the location of the booking, like Manchester (MAN), London (LON), Paris (PAR) and New York (NYA). When the user enters their booking for Manchester the ID would be MAN001, for London it would be LON001, PAR001, NYA001. Then the next time a MAN, LON, PAR or NYA was added it would be the next number for that location, i.e. MAN002, LON002, PAR002, NYA002. Essentially, you'd be able to look at the bookings list and see (based on the Booking Number) how many bookings you have in any of those locations.
Basically, the *ID field in the Get ID Information Step Flow would be kind of like a VLOOKUP or INDEX/MATCH in Excel where it would see what the user entered in the Bookings List and find that related value in the IDs list find the number for that location, add 1 to it, then format it with the proper prefix/suffix and put it in the Bookings List then update the next number in the IDs list for that location.
Create multiple rows in the ID generator list for the main one. Add a column called Identifier. Now in Power Automate you;'ll need to get items not item, and then filter on the MAN. PAR, NYA in the new column in the ID Generator list. The rest is the same, grabbing the next ID, and incrementing the next one, etc, etc,.
@@JohnDayQA Hi John, can you do a quick vid on doing this please?
Hi John. This was fantastic! Do you have any tips on having issue with creating two items during first flow run giving them the same number.
Jaakko,
This was my mistake not showing in the video how to change the Concurrency Control. In the trigger at the top select Settings and set the Concurrency control to On, and drag the slider down to 1. This means only one record is updated at a time removing any issue with unique IDs.
@@JohnDayQA Thank you John! This solved my issue. Another question perhaps you could help me out with is: Having prefix consist of multiple variables which are used based on users choice on different dropdown columns, for example user chooses from first dropdown "item1" and from second dropwdown "item2" and with these choices prefix should be "12", meaning not values of the choices but somehow perhaps using arrays with key and value. Do you have any experience or tips for this?
I ended up solving this using the "Switch" control using multiple cases.
Hi John, Great tutorial, really clear and exactly what I have been looking for. However I have noticed something that I would like to fix but don't know how (I'm still in the early learning stages of Power Automate). When list items get created before the last one triggered the script, I end up having 2 or more items with the same ID. I can manually adjust this with the hidden PAID column but if others are also creating items this could end up being quite messy. Do you know if there is a way to tell Power Automate to after creating the new ID to check if it already exists and if it is to run the same script again. I would think that this way if there are multiple items created simultaneously or close to each other the script will keep running until it has a unique number. Appreciate your view on this. Cheers!
Jaakko,
In the trigger at the top select Settings and set the Concurrency control to On, and drag the slider down to 1. This means only one record is updated at a time removing any issue with unique IDs.
@@JohnDayQA Thanks for your response John, much appreciated! Didn’t know there was such an easy fix. Really helps with keeping unique IDs when multiple lines are created simultaneously or short after one another.
Many many thanks!
Thanks realy helpfull, only one remark, when you copy several docs at once they all get the same number
1: Go to your Power Automate flow and edit the settings for the event trigger at the top.
2: Enable the Concurrency control (allows you to choose how many times the flow can run at once).
3: Set the concurrency level to 1.
This foeces the flow to run once at a time rather than multile times. This will force the ID's to be unique each time.
Brilliant. When I create the Power Automate flow for each ID is there a way of copying an existing one without building it from scratch?
HI Amanda. Yes, you can save a copy of a flow in Power Automate, and then you amend the trigger and actions so it is pointing to the new column / list / site.
This looks like a great idea to manage IDs for various entities. I'm just wondering if this handles concurrency well enough? Say we have two users, each creates one booking at the same time. The flow may then triggered at the same time. However, if User A's flow calculates the new ID first, then user B's flow also calculates the new ID before User A's flow updates the new ID back to the IDs list, they would both end up with the same ID, wouldn't it? Of course chances of that happening is quite slim, but when (if) it happens, that would create an headache. We would need to somehow lock the IDs row to ensure only one flow is access it at any given time but that would add a lot more complexity to the system. 😅
Set the Concurrency setting in the Trigger to 1, and it works perfectly well. You want Power Automate to run each new item one at a time to manage the ID's.
@@JohnDayQA Thank you very much!
Great material! Thank you for this. I just wanted to check if it possible to make Flow recognise how to increment ID values.. For example, if two items are created, then one would get ID = BK0001, and the other item will get ID = BK0002 ?
found a solution. thank you again for this brilliant tutorial!
Hope your solution worked. Normally you go to the settings of trigger in the flow and enable the concurrency and set the value to 1. This ensures every ID gets a unique number/
WOw! Great presentation! Watching from Philippines. Is there's a way to shorten the time of Customized ID to show? Because it doesn't reflect immediately and i have to reload/refresh the page before the customized ID to show. If you already have a video on this please share the link please. Thank you so much.
up
This is a great tutorial. I've been asked to make a ticket ID system reset every day so the first case created is say 2111010001 where 211101 is YYMMDD and the 0001 is just formatted number. Should i create a scheduled flow that runs every morning just to update the CurrentIDNumber field to 0 ? Or is there a better way ?
Thanks!
That sounds like a good way to do it. Use a Power Automate variable to convert the current date to your format (e.g. formatDateTime(now()),'yyyyMMdd') place that in a concatenate to append your ID to the end. To ensure it is stored as a "sharePoint number" use the float() function and place all of your concatenated data into it. Otherwise SharePoint will not accept the resulting value.
Awesome tutorial ! Btw, what if there are several prefix like BK, LA, AC and more ? Do we need to make condition or how ?
There are several ways you can do this. But, this is why I add the Site and Column values in the Title, so I can use just one Power Automate for all the prefixes.
1: For each prefix you want ti use, add an item in the IDs list specifying the prefix and make sure they all have the exact same title value.
2: In your Power Automate flow, replace the "Get Item" action that is pointing to the ID list, with "Get Items" (plural to get multiple items) and filter on the Title name rather than the ID (the video just used ID to make it less complicated).
The list where the auto-generated numbers are being added has to have some column that has values that determine which prefix to add. Let's say its a column called "Type". If type is Booking then use the "BK" prefix, If it is Account then use "AC" prefix, blah blah blah.
3: Underneath the new get items, add a Switch control and add a "Case" fro each vaue in the "Type" column that determines the prefix. Inside each Case, create a action to filter the Get Items you did in step 2, to find the item matching the prefix you want.
4: Do that for each case.
5: The two set actions will get the ID and prefix, suffix etc from the filtered prfix item. Leave those two actions undernath and outside your new Swicth Action.
6: Remove the filter you applied to the get items list (or rerun the filter to get all the prefix items).
5: The Update ID Information box needs to go inside a new "For Each", so you can apply the new ID number into each prefix of the same list.
Obviously this is a more complex requirement, but hopefully you can follow this guide and get it to meet your needs, If not, let me know and I'll add this to my list of "videos to do."
@@JohnDayQA Hi John. Txs for this fantastic tutorial. I need something like above here, so I'd like to know if you've had allready the time to make a video around these multiple prefixes? Txs in advance for the answer.
@@JohnDayQA Love to see this video John, thank you
@@JohnDayQA Hi John, thanks for this video, I have used it as a bases to add a "document reference" against document libraries instead of lists, what I was wondering is would the above work to amend the the folder name, that the file is created in to this "document reference" (bk) ideally it would be great pull "site name" "folder name" and then the automated value?
@@JohnDayQA Is it possible to create a video showing these steps? I'm not sure if I'm applying it correctly.
Hi John, thank you for the video, just wanted to ask
I have different attributes and have been trying to create IDs based on attribute type in the Sharepoint List. Below is an example of the outcome I want to achieve,
1. (Company Name)-(Booking A)-0001
2. (Company Name)-(Booking A)-0002
3. (Company Name)-(Booking B)-0001
4. (Company Name)-(Booking A)-0003
5. (Company Name)-(Booking B)-0002
And so on so forth as new attribute type of booking comes it should start from 1 and then 2 and so on. I would like these to be mutually exclusive in the same list.
Is this possible in Sharepoint List?
At the moment I can only have a series like below
1. (Company Name)-(Booking A)-0001
2. (Company Name)-(Booking A)-0002
3. (Company Name)-(Booking B)-0003
4. (Company Name)-(Booking A)-0004
5. (Company Name)-(Booking B)-0005
Hi, bit late. This can be solved 2 ways:
(1) Add aCalculated Column: write a formula and insert the columns you want displayed, something like this: ="[Company Name Column]"&"-"&"[Booking Column]"&"[ID COLUMN], text(0000)"
(2) Power Automate: have the column be populated by Power Automate by "When an item is created (sharepoint list)", next: "update an item (sharepoint list)", and fill in the custom ID column by adding your other columns in order from the Dynamic Content list. For the last 4 numbers, add the Expression: formatNumber(triggerOutputs()?['body/ID'],'0000'). If you have changed the name of the original ID column, you have to edit the formulas/expression to compensate.
Thank you for this tutorial, I have a case which I want the numbering to be divided into 3 parts. First part, will be the place ID (which is a drop-down menu) second will be the asset type number(which is a drop-down menu but with its name) third will be numbering. So, the prefix should be pulled from the form also the suffix How this can be done ?
Sounds like a concatenation (CONACT() ) for data from both lists, so another variable would be needed here. You are pulling the data from the lists you need, so create a string variable and add a dynamic content formula to concatenate it.
John, this was really useful but i have a question regarding using the sharepoint function "creating AND updating" an item. I did use that and i can get the flow to function, but i do get a warning that says that it may go into infinite loop. What i am trying to do is all you have shown when creating an item. Then i have the possibility where a user can update that item (i.e unique case#). I would like to keep the original info and add the new with the same ID number but possibly only adding a suffix. In that way i can track the history for a case via Power BI. I gueass it is a code i need to do or a new step but i am a bit lost. So to be clear: i followed your tutorial as shown in the video but used create and update item but do not know how to finish the update steps... :-)
If you include updating an item, as Power Automate runs it will update the item, triggering Power Automate to run again, and update the item, and on and on it will go creatingyour infinite loop.
Just after the trigger you will need to add a "condition control" action that checks the current value of that ID field, if it is already populated as you require then add a terminate control action to stop the loop without updating anything, this will prevent the flow from triggering again. Modify the status to Succeeded, so the flow report shows a successful output.
I hope that helps. Any issues add comments or find meonf facebook.com/JohnDayQandA.
@@JohnDayQA Thanks John. I understand. For now i have just used "Create Item" and it works as expected. Nice feature! What i was aiming for is probably a step too far. I was planning to use the suffix as a tracker each time a change/edit is done to my item. E.g on creating an item the user will receive a ID (XXX00001.0) and on later edits the suffix will only change, not the rest ((XXX00001.1). The idea was that it will also create a new row in the sharepoint list and therefore i would have complete history of development of items and can analyse that over time via powerBI. Might be more useful ways of doing this. For now i have only added 4 new columns in my list (the ones i require users to change on edit) and made those new columns as retain version text in sharepoint (comments). Not sure how useful this will be when there are many comments over time (e.g 4 comments pr month) but in that way i might bypass the issue with massive amounts of rows
Excellent tutorial. How do you deal with resetting the number if an Item is deleted. Example I delete the last item in the list how to i go back one in the numbering automatically so I can maintain sequential numbering.
You shouldn't really. Indexing is designed to uniquely identify every item, even if an item was created in error and then deleted moments later. Your audit may want to know about such items. That's the golden rule in the audit of data and information management. If you do - for any reason - require removed numbers to be reused again, you open a world of confilcts. What if you create item 0023, deleted it and someone creates 0024? How do you allow a new item to reuse 0023 again, skip 0024 and go to 0025? If you are the only ne creating the items, you will need to manually reset the number before creating an item again. In reality you are doing something that is not standard to a workflow r process, so the process should not include such shortcuts.
Sorry, probably not the answer you wanted, but I tend to stick to safe processes in videos. :-D
@@JohnDayQA What you say is spot on true. The auditing trail is important and you reply gives me food for thought. Thank you for replying. I loved the tutorial.
John, i have an issue that i cant find a solution for. I get the following error message (see below). To be sure that there is no hazzel with my lists i have created a new sp list and a new ID list in acc to your video (has worked before on another lists). Tehe flow fails because it expects a different value on the last string in concat. I cant figure out why. It is set up as explained in your video and i see no reason why this should not work (Unable to process template language expressions in action 'Sett_FormatMask_med_Prefiks_og_Suffiks' inputs at line '1' and column '21019': 'The template language function 'formatNumber' expects its third parameter to be a string which indicates the locale for locale-specific formatting. The provided value is of type 'Null'. )
John. i did find a workaround. I dropped the local statement from the concat and that went thorugh ok. Don't know why that failed in the first place but i am not going to use suffix so this is ok. Thanks again for providing these videoes. Really good stuff
That's very weird. I would love to take a look at your Power Automate code to see whatr I can find.
If your happy with the way it works though, that's fine, but give me a shout if you need further help. Sorry for the delay in my reply. Work is picking up again, which is always good.
Great video, I have a problem when multiple users are working on the same list. The numbering gives serveral times the same number. Any idea how to solve this?
1. Select ... and then select "Settings" for the trigger at the top.
2. Enable the Concurrency control
3. Set the Concurrncy control to 1. This only allows the Flow to run one at a time, ensuring you have a unique number for eacf item.
In the trigger at the top select Settings and set the Concurrency control to On, and drag the slider down to 1. This means only one record is updated at a time removing any issue with unique IDs.
Where did the ID column come from in the IDs List. at 14:57 it is not there, then at 15:22, it is there.
In fact it is the other way around but I get what you are saying.
At 14:57 it is there in the "Bookings" list, which is a column I need to store the ID for each item. Bookings is an example list that requires a custom unique ID.
At 15:22 you are looking at a different list called "IDs" (check the list heading above the list) and centrally stores the current ID number for every list like bookings.
The fact you did not spot it, means I did not visually show that I had switched lists - only verbally. My apologies for that.
I'm trying generate IDs based on values in another column.
I have a 'Month' column with values: JAN,FEB,MAR
I need IDs like JAN_001,JAN002, FEB_001, FEB_002.....
If after a long time, I enter a record for MONTH=FEB, it should generate FEB_003 and not FEB_001 again.
If you are using a date column then you can pre-convert the value you want in a SharePoint calculated column. Let's say you have a column called Review which has dates. Create a calculated column called ReviewMonth and type in the following formula
=UPPER(TEXT([Review],"mmm"))
This will grab a three letter prefix of the dates month and convert it to Uppercase characters. Just append the ID to the end.
i don't seem to have "get item" as an option? only "get item". not sure what to do? can you help plz
Which action you get depends on which action, or trigger you used. get Item means you connected to a list. Get document would mean, you connected to a library.
There is a big problem with this flow. We tested with my users and when many people added new bookings at the same time, then workflow gave the same booking number to 4 users and then failed. Do you know how to manage a queue of running the flow?
Hi Svetlana,
Yes you can set the flows "Concurrency to 1" and my apologies I really should have included that in the video for highly-used booking structures.
1: Go to Power Automate and edit the flow.
2: On your trigger at the top, click the 3 dots.
3: Click settings.
4: Enable Concurrency Control
5: Set the level of parallelism to 1
This will ensure flows run one at a time and they will manage the numeric control better. I hope this helps.
@@JohnDayQA Thanks for this hint! I tested and it is really working. But interesting that the numbering is not always in the right sequence, example, I have got NR 105, 104, 103, 106, 109, 107, 108, 110. But at least they are all unique.
@@svetlanavelizanina9036 That will be down to the Power Automate. whatever order you add items to a list, we cannot control which order Power Automate will process them. I agree it is very odd that an item added 1st could be the 3rd or 4th processed flow inthe queue and I'm not entirely sure why that is the case. Something for Microsoft to explain :-D.