Create a Unique ID using custom Autonumber columns in SharePoint

[Music] hi everyone John day here there seems to be some challenges with creating Auto IDs or reference columns with Auto generated numbers now office 365 SharePoint Online already provides this feature but the requests are about better administration of them such as adding a prefix or suffix to them or starting or restarting the numbering system which does seem to be a bit of an issue seeing as I'm going to be creating order IDs myself for my booking system I thought I'll do the videos on that first and I'm going to do two videos on creating Auto numbers the first video will show a tidy way to take advantage of the built-in ID column in case that's all you need if you need to administrate them manage your own and that will be including adding prefixes restarting the numbering that will be the second video and that will allow you to centrally manage all your ID columns across any lists any sites that you're gonna use ok so let's give it a go I'm going to start this demonstration totally from scratch so everything I do you can see is out the box and is the default settings so I'm gonna start with a brand new list which I'm going to be using as my bookings list for booking events or booking something special so I'm gonna do a site contents click and then click new click lists and screen recordings and as a typical generic list I've got a title column I have other columns sitting in there as well which all the intrinsic and built-in columns go to the settings gear cog go to NIST settings and here you can see I've got the other columns available there's no fancy columns that I'm looking for that also generate but there is one so if I go to the views and modify the the fork view that I've got called all items you'll see that there is an ID column that's been in there for ages it really has been there for ages and and it's a great one to use but it's kind of restrictive it's quite limited in what you can do with it and let me give you a quick demonstration so if I tick that now and click OK there you go it's my ID column 1 what is gonna create new item let's call this one you looking for Bob but notice the ID doesn't appear in the forms of the users can't manipulate it or change it well they also can't see it and that's because the ID hasn't been generated yet he won't generate an ID and said he absolutely knows that I intend to save and keep this record otherwise if I entered 17 items and click cancel 17 times auto-generated 17 numbers that would never ever be used so the idea is that when I click Save only then does it put all the data in that it generates the ID and plops that into the ID column and this is where it creates the problem because what I would like to do is create a column that would take the ID number and make it go that one step further maybe I want to format it so it's a six digit number maybe I want to format it so it has BK as the prefix to turn it into a proper booking reference number if you like so I could add a column and I could for this example I'm going to show you what doesn't work and people have tried doing is they would use the old adage of the calculate columns I've got some more calculate column there it is I'm gonna just going to call this one calc ID without the caps lock on let me go calculate column but your notes is that on the formula to the right of that it doesn't show the ID column there's a lot of columns that are in that list that I can't see there now if you've seen a previous video I actually showed how to create your own business generated version numbering system so you use the ship when intrinsic one which belongs to SharePoint in is for shares purposes and then you could use like a base version maybe the document is version 10 in its old eg drive and when you upload it you'd like it says stock version 11 they know so that was what I did on the video and I use sort of working with the version column which is built into SharePoint so in the same respect I can even though I know the ID isn't listed I know it exists I can pull it in convert that into a text field and tell it I want it to be one two three four five six digits long so I'll put six zeroes now if you've never seen this before the text functions varies for a man you can do it in Excel and it converts a number to texturing and then allows you to format the texturing in in a sort of a particular way and then at the beginning of that I'm just gonna tell it to display the world letters BK and pose and it's this I'll just basically concatenated BK is a prefix and got it to six digits and now this all looks good you think ah this is brilliant this is exactly what I want until you then think right I'll go got the column built now let's go and create some more items so you go create new more item click Save and that happens why did that one work and that one didn't because when I create the column that number already existed when you click new and you build a brand new item when you click Save the calc column calculates however the ID isn't generated at that point so there is no if I click save the calculations done first then the ideas generated so you're thinking Wang on it then if I just go in and click the item edit the item change in some way now that I've changed the item surely that calculation will recalculate that's what it does you know you'd recalculate but what it does it only recalculates if the values it's pointing to change or as far as is concerned the booking ID has not flagged up don't forget although the booking ID was created afterwards when I edit this form there's no flag to that ID column to say I have been changed so therefore the calculate ID looks that kilometres well there's no flag saying I've been changed it hasn't changed at all I'm not going to recalculate and that's the problem this is not the end of the world okay what we have to do is we'll have to build a column make it a texturing if you like and we can then pour the ID number in convert it to texturing and spit it into a regular text column but the calculation process will be done through power automate so I'm gonna create a new column I'm gonna make this a single line of tape because I want it to end up like this with all the text in there and the six digits so I'm going to call this the PA ID the power automate ID and again the column names just for the demonstration so again I'm gonna make the single on the text and on a click Save okay so it's the paid column so now when I click new and don't worry the paid column is in their auction explain that with the problems without a minute but the idea is if I click a title what I get to do is save the item and if this is a new item at the moment I save it I like power to make to trigger a flow that then manipulates the ID and sticks it in my new column under the new format let's give that a go so what I need to do I'm going to keep that sitting in the background there I'm going to go to my app launcher and launch power automate I'm just gonna right click and do no tap just to be safe SuperDuper and I'm gonna click create to create new one I'm not gonna use the templates I know exactly what I want to do I want this to be automated run on the back of me clicking the Save button so I'm gonna have an automated flow I call this generate PA ID and then in the choose your flows trigger I'm going to type in the data connector I want which is SharePoint this gives me all the triggers are available but what I want is to trigger on only a new item so if I can see it there it is when an item is created it's a filter it down a bit more so when an item is created not created or modified only when it's created click and then create okay so I'm gonna build a new power to make it tell it the site and the the list that I want to work with for the auto ID so if the site you're looking for whether it's SharePoint site or connected to teams it doesn't matter but if that site is not in the list then all you got to do is just go back to the SharePoint site right click on the little logo in the top left corner there and copy link address and then when you return that's power to mate don't type it or paste it in the box there because that box at the top there is a search box to search for the existing lists what you're doing stair to scroll to the bottom and say I'm teaching you a new URL by entering a custom value and then you pasted him okay somebody fat for click give it a few seconds because if it's a brand new site it's not seen before might take a little while for it to generate this names but then will come up all the lists that you've got and I'll forget this my bookings this so when an items created in that list I want this power automate to run what I want to do is I only get that item so I'm going to create a new step quick search for SharePoint I'm gonna start with update item first okay update item same again I need to tell it which site and lists this particular edges working on because I might be working on multiple SharePoint this I'm not now hopefully at this point the site you're working on should be listed if it hasn't it does take a while just repeat the previous step and paste the URL as a custom value I've already got it in here so I'm gonna click it again pick the list name and it's now asking for the ID where the ID of the item I want to update is the one that I've created so I want to grab the ID from this trigger so I'm going to go into the item box here look for the matching when an item is created there's only one but I always do this is a reference because it just keeps pace you always check as you build more more actions there's the ID I want to just plop that in there you'll notice then underneath that I then got the columns that I want to update now if the column hasn't been changed you just put its field name is dynamic content in there to say keep it's like saying keep the title one the same and the paid I want to take the ID number and manipulate it like I did with the previous one I want BK and then I want a six digit number functions are kind of similar to excel that was designed delivery to be like that and you do that through the expression so forgotten expression there we go and then I'm gonna say I want the expression to be first of all that concat thing because I want the BK the beginning so the concat function is a good way to do this so in the concat and I say well I like it to start with and then in single quotes you put the prefix that you'd like at the beginning of your number there we go I'm just gonna go the other side of that single quote comma and then tell it what I want to pulling as the the ID but I want to format that ID in a particular way so we're use the text column that doesn't exist here but instead I use the format number okay same sort of arguments I put in so I'm gonna put in the open parenthesis bracket specify the field or the value that I wish to format again that's the ID one so while my cursors inside those new parentheses brackets I'm gonna click on the dynamic content and go looking for the ID there it is okay when you've done that just check to make sure where your cursor is you want it before both of the closed parentheses brackets because we're still going on with this format number function so after the ID and that square parameter bracket put in a comma and then we're going to specify how many digits very much like you saw on my first very quick demonstration so inside single quotes though I put in one two three four five six almost six digits you could put in as many zeros to represent how many digits long you want your code to be if I click OK that adds a nice little magenta colored expression in there always check your flow check as well if again here is in there at this particular point it could well be with the expression and you've got a typo in there is something okay so I've got it to now update the item and I've asked it to update the paid with my new format number so we're gonna save this I'm gonna keep the power to make tab open just in case any bugs any anything's I've gotta fix now gonna go back to my ship when this I want to see if my paid one works so i'm gonna click new I'm gonna put in a title you looking for Bob whatever data you don't fit in notice the paid column does appear don't worry about that I'm gonna fix at the moment but if I save that out first the paid column doesn't seem to look like it's working I've just done it but it hasn't worked your peril tomates can take up to five minutes to run so it's not gonna run immediately now that can be a bit confusing if I just give a bit of time press f5 and keep pressing f5 although I'm not gonna at the video run for five minutes this is unavoidable but this I will keep pressing refresh they already didn't have to wait that long it has generated the number they're gonna it has worked buying point now there's my concern number one when they create a new item nothing appears in the paid how does the end user this fitting the stuff in no there's not a bug and go and try and change it or try not batesy or kiri fixing it so little thing I do and it's up to you if you want to do this I'm going to click on the drop down box I'm going to go into the column settings I'm gonna edit this paid column and I'm gonna put in a default value put in anything you like there but it's gonna be like a placeholder that lets them know the numbers not there there so I'm gonna have it appear in square brackets saying getting new ID so this is kind of a little thing to say we are generating it and to let them know it hasn't worked it's it's text it's been stored in the text box but the peril to mate when it runs will overwrite that so I'm gonna save that and I'm gonna see if that works so I'm going to click new and if I just put in some text into the title and if I clicks you can see it's already got the getting new ID for a click Save I see the getting new ID so I think ok it's working I don't have to worry about that and got to change it anyway and then over time eventually the power automate will run and that getting new ID will be replaced now they're probably phone and good I'm trying get the number back to the person I've got or email it to them tell them we can create an autumn power to mate that once the ID is created then we can have you automatically send the email to the user and confirm oh by the way your booking has now been given an ID number here it is and just to confirm you've now booked so there's you know it's not the end of the world we can have this all automated even if the use of themselves is feeling that booking in we've given the information that it's been processed okay here's my final problem with it though when I create a new item were still if I edit the item my end users can change I've just go around that calc ID thing which wasn't working and that we've found that we've still got another problem it's not a problem it's not a problem here's how you get around there this is where we can use a couch ID and make it work to our advantage so I'm now going to remove that calc ID because I don't want it we've now seen that that paid works and the best way to do that I think is actually for them this settings I'm going to go to the same here list settings there we go and then I'm gonna click on my calc ID I don't need any more and you would not have a crater that hopefully you would have ignored that bit that was a demonstration I'm gonna delete that calc ID instead I'm gonna create another calculate column so click create column and this will be called the proper name booking ID this is the one I want the user to see so I wanted to have this presentable proper proper name it is a calculated column but all its gonna do is grab my paid so you can say equal and I'll double-click on the paid single owner text okay go to the bottom click OK go to back to bookings I've now got two of them now I'm going to turn the paid off eventually in the list but what's most important is when I click new a when a new item is generated the booking ID doesn't appear if they edit any one of those bookings the booking ID cannot be changed if I click that pencil and go hi heart's concern it's not letting me change it so all I've got to do is hide the paid column from my list and then I need to remove the paid column from the form but I'm gonna just gonna get rid of that paid one first I'm gonna go into my list settings and I'm gonna go back into my view and I'm gonna remove the paid I forget the paid is the generated one by a power automate cool there we can is that now disappears then and more importantly I've got to hide it from the form there's two ways you can do this I'm gonna show you a way without power apps which is probably better so to do that list settings I need to customize that column and hide it now the way you do that is by going into the this localized if you like is is initialized content type the content type is these are the template structure of where the columns are and how you can sort of imagine from a slightly higher level now to do this you need to go to your Vance settings right at the top of the Advanced Settings you tell it I want to manage my content types they allow management content types so that's a yes go to the bottom and click ok what that does is it displays a new structure inside your list settings called content types here is your instant your instance if you like of the content type specific for this list so if I click on the item you will see the columns that are in there the paid one is the one that there is risk of being changed by the users so I'm going to click that and here I say I want this column to be hidden so it does not appear in forms so I want to click OK this is all just a nice clean way of doing it without I'm the kind of power apps and I customize it's just a nice clean way and it stays that way so if I now go back to my bookings so I can see the paid there I can still tweak it work it as the owner of the site known of the list but as an end user when they go in and create a brand-new item or edit an item is generated automatically and it's safely preserved from end-user tweak enos it won't let me change it why didn't these other ones change because my perrault mate only works on new items if you're building it list you're doing it from scratch again that done before you actually make it go live anyway so that shouldn't be a problem so be like magic like this and again because it's a nice lovely little column idea I can drag that around put it in any water I want so I can even have that booking ID write the beginning here we go fantastic just the way we like it now don't forget there's a pot to video to this but in the meantime if you have any questions about this video or any other video that I've done please add them into the comments you can contact me through all the social media addresses that I've got on the screen there and don't forget to click Subscribe so that you can be kept up to date with all the new videos that I've released so hope that's useful to you stay safe have fun and look out for video part 2 [Music] you

Channel: John Day Q&A

Views: 17,256

Rating: 4.9854546 out of 5

Keywords: Education, Microsoft, Office 365, User Support, Training, How To, Learn, Understand, Power Automate, Autonumber, reference, field, ID, ref, number, unique, original, automated

Id: 35FHdGKTeZE

Length: 23min 46sec (1426 seconds)

Published: Wed Jun 17 2020