How to Create Custom AutoNumbering Columns in SharePoint List

[Music] hi and welcome to microsoft power tools channel where you learn everything about analytics automation and productivity in this video i'm going to walk you through my learning experience on how to create autonomy column in sharepoint well i call it a learning experience because i will share with you you know what i learned what i tried out while trying to create this and that way you were able to learn different things and find out how i was able to solve the challenge i call it a challenge by the way should it even be a topic to be discussed yes it should don't but it should why am i saying this if we look at this hard new column if there is anything like coulomb type auto number there won't be need for us to be discussing this i guess microsoft theme does not plan microsoft list sharepoint list to be a database service or two so and because it is not then we don't even have the auto number every day it has other columns that sort of make what we have in database but auto number is not there there are a lot of use cases that will require that you create a custom auto number i have one which is what i i'm just taking an exact to walk you through this through this video all right so let me go straight and explain this what do i want i want a custom case id i want to create auto number column that is called case id if i create a new item here i'm just going to enter something customer name or la lachaze what is the subject it's just an inquiry which date is today what time let's go to chinese time to 3 a.m okay and status see open i'm just no attachment i'm going to say awesome but where's the height i need a unique id not just one two three i need something to be able to track this all right while i was thinking through this challenge i just remembered wow there is a column in sharepoint list that is unique and is heidi let me go for this color how do you even see those columns first you can click on end of this column and go to column settings you see show or add columns so you'll find out that there are other columns that are collected when you create a sharepoint list they are called metadata the one i'm interested in is this id by the way i've renamed my title i've renamed my title customer name this id i click on id and click apply so i had this showing awesome so i'd be easier but i need more than this so what if i can customize the id let me try to edit this oops the id is not here because auto auto generated you can't even edit it but what i need is beyond just one two three i need something like case meaning cs slash the subject slash the date slash the ideal to maybe four digits how will i be able to generate that all right when i found out about this i then said to myself one other thing i noticed about id is that id is generated after the item has been created take note of that the ig id will be generated once the item has been created even though it seems everything is landing the same time but no when i create a new item shortly i'm going to show you even though you are seeing why they almost at the point of seeing the everything i've entered but the id is only generated when the item is created so you can have separate ids the same is unique all right let me tell you the second option that i tried out so i went ahead and create a custom column calculated column which i call calculated color wow this seems to be an interesting one i click on more and here you can see calculated column options no normally column name i call this cac case cast case id this case id and i'm going to make it calculated column calculator column will allow you to calculate a column value based on the value of other columns so i'm able to put all together my ids all the things i need there and create it and this is similar to excel i'm going to write formula like in exam simple and straightforward equal sign i have to start that remember every formula in excess starts with equal sign then i'm going to because i want to have cs to signify case i want to have slash so i have to concatenate slash and slash the color as well so i have to concatenate with this and remember how to add slash by okay so this is the way to add the slash what do i want to have next i want to have subject i need to concatenate okay subject i'm going to double click subject because i have it here it's also going to be dynamic if i double click it to insert subject all right so let me concatenate i had another slash okay concatenated assign and this time around okay this add another slash all right what do i need next i want to format the time the date launch so i have to concatenate again and this time around because i need just the day the month and year but not in full the year just like maybe 21 20 21 instead of having 2021 i just have 21 to signify the year and remember the date lodge has an extension that's date and time i just want to format it so test will help me to do that the test function so i'm going to just write the test and open and double click the date the date launch to insert it so i have it there now i'm going to put comma and specify the format so i need dd slash mm slash yy and i'm fine i'm going to quote it again and close i have one more thing to go i'm going to concatenate which is the id i need to not have a unique id counter a counter so to say and my high d coulomb is already a counter for me but this time i don't want a counter one two three you know going like that what if it is one one should be up to four digits so zero zero zero one when it gets to ten zero zero one zero that's the kind of thing i need to i can also format the id i'm going to do that using text open the bracket the id is there so i'm just going to type id it it's not on this column list but it's already there in the sharepoint list and i'm going to format this and put four zero first row means i need four digits all right great so let's just go ahead and click ok when i did this i believe i have solved the problem i was so happy oh did i miss something out oh i means a slash so how do i do this let me just go straight to these list settings under the settings i'm going to click on that calculator where's my case id and come to the formula i need to conquer yeah before the last test i just need to concatenate my slash and then add another concatenation yeah so i can i'm fine now click ok go back to mini when i did this you can see wow this is great i believe i've solved the challenge right why i didn't know that i have two more steps to take all right so what's the disadvantage let me double click on edit interestingly i cannot edit it so that's fine then i've solved it the id is also here because it's uniquely generated under backend all right so let me create a new item new item and this time around let me add other rounds the subject here is complete the date lodge is maybe another day here 29th and the time i'm just going to make the time 19 which is 7 pm click okay so i'll be there status is to open i'm going to click on save wow i think we are done id is 2 but the case id so it has adapted unique yes 29 wow but look at it zero zero zero zero where's the two and that was what i was saying the other time that the id is created when the item has been created so when the item was being created the case id was part of the column to be created and because the id column has not been generated it's going to have you know it's going to append zero and it is after it is there that we cannot have the id attached to that particular item so this is not going to solve a problem that was the same way i thought i've solved it all right so i now thought of it what if i can use power automate to do this thing because for power automate it takes some time so once i create a new item it takes some seconds before it can be created and that seconds the id will have been appended wow that is it okay let's go that route i need to delete this our case id how do i have to go back into these settings um go to list settings locate case okay case id i've just scroll down here and click on delete i don't worry i know i did a lot of work to crack it and yet i couldn't but it's not addressing my challenge now you know so let's go ahead and create a new column i'm just going to create a test and i'll call this test um case new case let me call it new case new case i'm going to click on save all right so this column exists but i'm going to use automate to automatically update the column anytime an item is created but how am i going to help people to know that okay an item is being generated so what i can do is to add a default value so i'll come to column settings and edit and add default value and say and put other dots say generating case id all right i have generating case id i'm going to save so by default when i create a new item by default is going to show generating case id all right so let's go ahead and create our power automatic flow i'm going to create a new flow automatically flow and the trigger is going to be when an item is created on the sharepoint list i'm going to call it um case id generator case id generator so this is going to help me to generate case id similar to the formula that we wrote in sharepoint list we're going to do similar thing here the first thing here is to locate the site that i'm talking about which is training and what is the name of the list here is a mini casemaker then the next step is to update item so i'm just going to have update item so whenever a new item is created get triggered then updates they have that particular item and we are focusing only on a column and that column is new case id all right so i have to also select the list to be updated the site is training and the list is mini case id what is the id so that is not just going there to update several items i'm gonna focus on id so that's the id is the id that i got triggered so title title is actually the name i shouldn't be compulsory so i have to change it search for title because it's returning it as title as right so let's have try to every other thing is fine except the generating case id that i need to edit and i need to put it as i want it to be so i'm going to proceed s remember this time we don't need to be quoting you know slash the subject okay so the subject here we're going to have subject and subject value because it is choice option subject value is delete value it's not just the subject i'm going to add subject i'm going to put a slash then i need to format my date launch so i need to format that launch to do that i'm going to because it's daytime format date value for my daytime rather i'm going to open up this so here i'm going to go back to dynamic content and look for date launched i'm going to look for okay this is dead lord go to the end of it all right then come here put comma then open a quote i need to specify the format of that date i remember its date and time i just need to reduce it we just did and this time i need your dd slash mm yy fine that is my date okay it has been inserted and under slash and this time around i need to format the id remember i need to have it at four digits or you know like that and to do that we use text but here we're going to use format number it is format number that we do the equivalent work of text in sharepoint list so i'm going to open a bracket then locate that id i'm gonna just search there's the id all right so here i'm gonna here i'm just gonna put my code and type zero four times to show the uh the number of digits that i want to format it to and click on ok so it has been inserted we are fine i'm going to click on save when i create new items it's saving value saved so let's create new items i'm going to create new items uh the customer name i'm going to just put a large charge again all our chances back then was the subjects complaints did launched we're fine with this time just going to put it on 13. okay status is open you design see new case now saying generating case id i'm going to click on save do you see that the id unique id has been created to be but the new case is saying that generating case id so people think something is happening at the back and of course something is happening the sharepoint the power automate is getting triggered and is picking up the id is coming back to the sharepoint list to populate the new case with that case id that is going to solve my problem so let's wait and have it populated great we have it populated unfortunately the date format did not work so i'm going to go back now and edit it coming back to format date time i need to know exactly what the error is okay i see that i miss out one word the the quotes which i need to put there my day i think i did that today that load i have dd mn slash yy so i have it back now i'm going to click on update updated i'm going to click on save um i will run this so i can take effect and it has run before automatic okay resent succeeded i'm gonna run it test so it runs successfully let's go back to the sharepoint list i'm expecting to see okay great so we can see 27 0 5 21 003 which is fine right i thought i've solved the problem let's double click and see okay great everything is fine now but the challenge here is i can't edit the new case wow i think it should be also generated and unique and then it shouldn't be a tea table you know so that i can be able to track and base on that case id but if it's changed then it can cause issue so how do i solve this oh god i don't know how to go about this again i just have to close wow oh it has changed how do i go about this now you see that is how terrible it can be all right so how do i worry about it let's create a new item first i just need to see that wonderful you know case id i've been able to generate advance this time please don't complain just make an enquiry okay truly she's make oh she's complaining again and the date is fine the ladies find the status let's just close it before she even open that complaint so we're gonna wait you see generating case id as with here we're gonna see the case id being populated i just checked out the flow and i realized it failed and i just tried to i didn't do anything serious down to just re entered my former daytime i didn't know why it was just throwing some error but i've fixed that you shouldn't have issue i'm going to enter a new record i don't want to run the flow to auto populate i just want to enter a new record so that i can see how he's been populated without rerunning the florida field all right this time around let's enter sergio john the subject is inquiry no longer complained and the date is 28 the time is i just put out 7 a.m and we are fine then status is going to be open then click on save alright so we have schedule and is generating case id awesome we are finally good to go as you can see it's been populated did i say we are good to go let's see so now that i'm i'm editing this column so we have it now wow oh you see editable i i don't want to touch it all right don't forget you said the table all right so what do i do and that was the last attempt that i make and that's to create and you know uh calculated color which draws peak value from this particular new case so i'm going to add a new column and go straight to my column call this case id that is the case id that people will see and it's going to calculate a column this time around is just i'm going to just select new case so any value you see in new case just take it that's your value click ok remember the benefit of calculated column is you cannot you see the case id you cannot touch it like you cannot edit if i try to double click now you will see that for this case id i cannot touch but for the new case i can touch all right so how do i then avoid all these possibilities of people touching it don't forget we have that trick i can go back to column settings and click on show or hide columns i'm going to hide the id i don't need the id again the new case i will hide it as well i'm just going to click on apply great so right now the case id is going to be auto generated and you won't be able to edit it i can also drag it and move it uh first here enter a new record and for customer name ojo allabi the subject is gonna be a complaint don't worry we have solved this time around i'm making it on saturday i can put indeed there and put the time as maybe 17 that's 5 p.m okay status is open i'm going to click on save so let us wait and see it's saying generating case id okay i'm patient i'm going to wait and i'm going to see awesome did you notice that so sometimes it can take up to one or two minutes for the case id maybe less than that less than two minutes for the case id to be automated to be updated but you see we have it now we can see the case id when i double click there's no way i can't edit this case id even though you can edit this guy okay so the next thing is how do we manage this new case id so that it's not visible which i'm going to show you now there are more than two ways that you can use to hide the so to say where is it the the high data we you know that is editable which is the new case so uh you can either customize this and edit in power apps so that people interface with power apps or so another video the video after this i'm going to show you how to do that where people use you can use power apps to hide some feeds that when you're feeling it it's not going to be you know obvious to people but i'm going to show you more not so technical but an easy way i won't say it's easy but another option for you to learn and how do i do that go to this jailbox here and locate list settings under list settings go to advanced settings and you will see this option allow management of content of content types say yes and scroll down to click on ok what that we do is it will include a new set of lines and that's this line that you have here sorry you have this content type which you included here and that allows you to folder configured or customize your content i'm going to click on this item we should show the list of the columns that i have you can see all the columns now i'm going to click on the new case which is the one that people can actually edit i'm gonna click on editing so this column should be eating and will not appear in the forms click ok what that means is when people are feeling it do not appear but that doesn't mean the column does not exist so i'm gonna go back to mini casemaker now great so let's try to click double click again to check if you can edit yes we can id but where's the new keysight is not here and we can touch this column all right this is how i was able to create my autonomous customized auto numbering column not that difficult it was because it's my first time remember now that i know how to create it i can easily just go ahead what are the steps just go ahead create a column empty column add the default text value and go ahead to power automate and write your expression so auto populate that column then create another column which is calculated column just pick the value from where you are populating it make sure you also hide that column in the next video like i said i'm going to return back to what we had before and show you how to you know edit you know very short how to use power power apps to hide that feed thank you and bye for now

Channel: TheOyinbooke

Views: 56,313

Keywords: sharepoint, sharepoint list, autonumber, hot to create auto number column in sharepoint, theoyinbooke, microsoft sharepoint, office365, microsoft 365, sharepoint site, create column in sharepoint, create sharepoint list, microsoft list

Id: lTJ04gBVV1A

Length: 23min 0sec (1380 seconds)

Published: Tue Sep 07 2021