Create a Unique ID using custom Autonumber columns in SharePoint 

You are welcome !

Glad it was helpful!

You say the sweetest things !! 😁😁😁

You are very welcome.

I said the same thing , even chatGPT 4 didn’t guide me to this channel but God. Thanks 🙏

Thank you Gen. Elias

Glad it was helpful!

@@JohnDayQA I also started looking but at the time I found the video, I think it has a lot to do with how we do the search, I was lucky, I liked it because the video was very clear without being tedious, thank you very much.

Thanks Veeramani, Glad it helped!

Very welcome

You're welcome. Just ask if you need anything else. You can aso reach me on Facebook, Twitter and LinkedIn. Just look for "JohnDayQA"

Sorry to be off topic but does someone know of a tool to get back into an instagram account..? I stupidly lost my account password. I appreciate any help you can give me!

Thanks David

You are very welcome !

You are very welcome

You're so welcome!

Glad it helped

You are welcome!

Glad it was helpful!

Thanks for the sub! Let me know if you need anything in particular. I'll do my best.

Hi Kevin. This is more involved and I have covered this on a follow up video that shows how to centrally control autonumbers inclduing how to reset them and start them from any number you wish. It's a long video becasue it's involved but it does exactly what you need. Link: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-zFmVxlS8xX8.html

No problem Dave. If you want to email me the issues you are having to jday@daytamod.info, I will look into this and provide a solution or at least open the doors and get you in the right direction.

If you figure this out please update me, this is similar to my problem creating a folder with the generated "Autonumber" And i would also prefer to mail out number.

Make sure you are using the the correct action to retrieve the "Metadata" of the library as well as the properties (your custom columns). This gives you access to what we call the intrinsic data of document including the ID, Title, Modified, Modified By, etc.

Yep, Just apply the column name into the concat function in place of the 'BK' value

if you wanted to start the ID Column from a specific number. watch the follow on video "Advanced Autonumber Column Management." It's a long one, but does exactly what you are looking for. Link: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-zFmVxlS8xX8.html

Hi Trynie, We'll do a couple check points along the way just to make sure everything is correct. 1: Make sure the trigger at the top is 'when an "item" is created' and not 'when a file is created'. 2: Same again. Make sure the action underneath is 'update "item"' and not 'update file' (you may already have done this so forgive me for being over thorough). 3: Make sure the same site address and list name is in both steps (this won't prevent an ID field popping up but it's good to check. 4: If the dynamic content is still stubbornly not showing the ID, click the expression tab at the top of that pop up box and type in the following. triggerOutputs()?['body/ID'] Give that a try and let me know how you get on.

If you have Facebook, you can send me some more detail on the current Power Automate and I can take a look at it. Contact me on facebook.com/johndayQandA

1: Above the Update item action add a new step with a control connector called "Switch" 2: In the Switch "On" parameter, add the Subject field. 3: Underneath, in the Case step, add the value of the Subject. 4: Drag the Update Item action inside the Case. 5: You can add as many cases for each Subject you want to check. Then duplicate the update item into each Case and modify the prefix in the concat as you wish. Hope that helps.

Oooh, good question. Not sure if you are aware but since August 2020, there is now a "Create a Folder" action in the SharePoint Data connection but I have never looked to see if you can pass a Dynaimc Value. I'll take a look and put a video up on it.

​@@JohnDayQA Yes, I am aware that there is a "create a folder" action. But since the "autonumber" does not exist yet i have problems. The next thing i tried was to use the same dynamic value from "autonumber" but since i save the folder in another location that did not work out. That would be very helpful. I been looking through your content, great work!

@@danielnilsson2930 I have not ignored your comment. I've been dealing with Covid issues, however I am on the case and I may have a solution. I will be testing this thoroughly this week and get back to you.

@@JohnDayQA I just want to add that i really like SharePoint/teams and i would say after looking at your videos and after i salved what this video is about i developed a lot. I have turned many at our company from being against SharePoint to now prefer it after i set it up in a more "simple" way And now i do contribute way more to my company and utilize the capabilities in a whole other level. With that said. THANK YOU and i Hope you feel better!

Hi Mamunar. Ideally your ID's for list content should be developed in new lists with new items. There are two options: 1: Change the trigger of the flow to "When an item is created or changed". 3: Add a condition to the flow that checks if the ID column is empty, if not it canignore the actions. 2: Then, when you edit and update the items as they are needed the ID will be applied. If there are too many items, consider creating a new list with its Power Automate flow in the same way then copy or move the items from the old list to the new one.

@@JohnDayQA hi John, i'm following the steps, why the error message shows"actions in this flow may result in an infinite loop...."?

@@xiboxibo3711 There is no loop in this flow. If you have a do...until block in there for any reason, make sure you have a condition that will exit . Otherwise, if you have set the Trigger to "When an item is created or modfied" then everytime you update the value, the Power Automate flow may trigger and run again. To solve this, add a "Terminate" control action as a new step at the end. Set the status to Succeeded to the Action report shows a positive reason the flow was terminated.

One way is to create a text string type column and use Data Validation. For example : 1: Create a column called "MyTime" 2: Select the Column Validation at the bottom of the column settings. 3: In the formula, type (or copy and paste): =IF(AND(LEN([MyTime])=5,MID([MyTime],3,1)=":",VALUE(LEFT([MyTime],2))

@@JohnDayQA Thank you

Jason, what you are looking for is a flow to reset the number rather than a calculation. The reason is (a) this is a process of the data not logic or validation of it. And (b) you'll need something like a boolean or true/false variable to mark when the number is reset each year so it only does it the once. You can use a scheduled trigger that fires on the 1st of January each year and only fires once. Let me know if you need help with it.

@@JohnDayQA Hey John, thank you for the response I appreciate it! I am still stuck. The field I need to look at and base the flow on would be a field that contains the date the sample is logged in. I was thinking of somehow using an if statement that checks the year of the incident comparing it to the current year. If the log date and current year are the same counter=counter +1. if not counter= counter. I would define counter =1 initially. Is this a somewhat reasonable approach?

@@jasonnguyen8111 Using a base vs current sounds good. Adding a column in the ID Hub list called "LastRun" would store date of the last flow. Like you said you just get the Year([LastRun]) compared toYear of current date. If the year is different reset the ID to 1, To make sure the flow only does this once a year in case two mebers add new records within the same time, in the Triggers settings, enable the concurrency and set the parallelism to 1 so the flow only runs once at a time stopping the ID from being reset multiple times in succession.

@@JohnDayQA Thanks John, I appreciate your responsiveness! I have a calculated column storing the log year and now I need to make a flow that creates the autoincrementer in another column correct? Right now I want create the individual columns for the different parameters of the sample ID and then at the end concat them all to display under a field named "Sample ID".

Hi Jeff, personally I wish there was a pattern matching tool in SharePoint to do this. So you have two options. 1: Apply a pattern into the column field via Power Apps. 2: Add column validation to the column in SharePoint. Add a logical formula where TRUE is the correct format you want. For example try: =NOT(ISERROR( SEARCH("???-???-????",YourFieldName)=1 )) Where YourFieldName is the name of the column. If you get stuck, let me know. I'm sure I have an old video covering this lying around. If not I'll make a new one up.

I have the same issue even if I export the Access tables directly rather than a query.

Hi Dan, The Autonumber generation in Access and every other database app or server is built into the code, not into the field. When you export the data you are exporting just the numbers it generated. Think of the Autonumbering in Access as a Macro in the Macro objects. So when you export data into SharePoint from anywhere, you will need to create your own procedures, whether it be Power Automate like this video, or code. Hope that helps.

Send me a screenshot if you can darlene, and I'll see if I can help fix the error.

Choice columns are data collections so you need to grab the Column value, so if the column is called Subject, you need the Subject Value property to make it work.

Hi Andrés. Yes. Generating autmatic ID's is a process not a calculation, so, to do it safely it must be a managed workflow process, whether by Power Automate, Power Apps or code. If you were doing the same thing in Excel it would need to be a Macro which is a VB code equivalent to this process.

Hi @AmandaHarris, At least 60% of the videos on RU-vid are "stolen, inspired by, influenced by" other RU-vid videos, blogs, posts, bulletin boards. Social Media allows us to share but makes your intellectual property vulnerable to theft. I have seen my ideas in my vdeos regurgitated on so many other videos. Then again some of the tiny bug fixes in my vdieos were inspired by others. The difference is, I name-drop them 😁. In the case you are referring to, this was a matter of innocent ignorance from a very wonderful person who did not think first. He got a lot of abuse for it and I really feel for him as he seems such a nice huy. On one hand it is flattering that someone feels my idea is worth stealing, but then I see the monesiation aspect. Money made from my channels, goes back into my channels for viewers. If you want to use social media, you have to be prepared for pros and the cons.

You have used a mixture of SharePoint column formula syntax and flow expression syntax. You haven't specified where you are placing this formula so I will answer both situation just in case. IF A FLOW EXPRESSION -------------------------------------- 1: You cannot use "simplified Concatenation" in an expression (amersands - &"-"&). Seeing as you are using a Concat function you can use commas to separate each argument. 2: Use formatNumber rather than text function. 3: The 0000 format in the second TEXT function was not cantained within quotes. 4: Make sure the case of your functions match (text is written lowecase). Your formula should look something like this; =concat(text(triggerOutputs()?['body/Created'],'yyyy'),'-',text(triggerOutputs()?['body/ID'],'000'),'-','IT') IF A SHAREPOINT COLUMN FORMULA ------------------------------------------------------------- 1: You cannot grab data within Flow action or trigger ( triggerOutputs() ) in a column as the flow is not running at that time and the info does not exist. You can however grab the data directly from the list/library using [Created] instead. 2: You do not need to use simplified concatenation (ampersands) if you are already using the CONCATENATE funtion built into SharePoint. Your formula should look something like this; =CONCATENATE(TEXT([Created],"yyyy"),"-",TEXT([ID],"000"),"-","IT") I hope one fo these help in some way.

If you need any assistance with this, give me a shout and I'll be happy to help.