Hi John, I was searching low and high, for days without any luck and thanks to UA-cam Gods to direct me to your video. I was stuck at a pivotal moment in my project and I really needed a customized column name for my automation to work as designed. Massive thanks to you!!!
I agree with all the below comments - by far the simplest and most effective solution I could find after much trawling. Has worked really well for my list - many thanks, John.
Fantastic tutorial, very clear, no jumping around and perfectly understandable verbiage. Excellent guidance, thank you for assisting with this deliverable.
@@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.
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!
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'))
Hello John - from France, many thanks for your great video. I'm not really good in English but your video is very clear and useful. I will be able to propose a good list to my colleagues. Again many thanks for this good job and your help.
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: ua-cam.com/video/zFmVxlS8xX8/v-deo.html
Fantastic video, John! Have you done a video on emailing the autonumber after it's generated? My current flow is emailing the number before it is generated. Sounds simple, but I'm having troubles . . . Thanks!
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.
Hi John. I tried it but it does not show the dynamic content for the id created for the 'when and item is created' field. How do I get the id in the update item if the dynamic content does not work?
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.
This is very useful, just want to know how do you restrict content type in the columns, for example, I want the users to enter time as 13:10 nothing else.
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))
This is really helpful, however I'm getting a bit stuck trying to use the new designer when I get to the power automate step. Any chance you have updated guidance on what to do there? Thank you!
Thank you, this was perfect for me. Like that you add "tips and trick" How do i make a flow that use "Autonumber" as name when creating a folder located in "/Shared Documents/" i can't get it to work. I am getting an error that the number does not exist (can i put some delay or similar?). What i'm trying to do is to when attachment's are added they saved in another folder, and i want that folder to have the autogenerated number. (i manage to save the attachments to a new folder, did this in another flow) And again, thank you!
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.
@@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.
Hi John, very informative video. Is there a way to create a calculated field that purpose is to auto increment, but reset every year. I want to incorporate this into a custom ID that I am trying to create in SharePoint list. Any information would be greatly appreciated. Thanks!
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".
Can you index the new BookingId Column - ie so i can write a caml query for a range of bookingIDs (1-5000)to keep under the dreaded 5000 list view problem?
OK this is a nice learning. But what about if we want to have again auto generated column but which is not linked to the ID column. Because now we have a column which is linked to this specific ID column and we receive exact number like 1, 2, 3 and so on. If we have a column whit already entered numbers and we want to start the count like from 000022 whit increment by 1 digit to make again a unique number list like 000022, 000023 and etc? How it can be done ?
Hi John. I played around whith what you are showing here, and I was impressed. I am trying to embed these steps in a broader script that starts of with a new item that is created on the list by receiving an email in a certain mailbox. I get stuck, because prior to your first step (when an item is created), I have another sharepoint step. And the Update Item step never seems to trigger when I test. Any ideas on this subject? I will keep testing myself and if I find a solution, I will post it here. THX. Wouter
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
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.
Hi John, I'm having some trouble with a similar issue, after moving access tables on to Sharepoint, the Autonumber ID column from the original table now no longer updates when a new record is added. This means the record can't be edited, do you have any suggestions for this?
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.
HI ALL: Someone posted this comment recently. As I was responding, it was removed. "...when i did this I get an error: OpenApiOperationParameterValidationFailed. The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: The API operation does not allow writing a value for parameter 'item/ImplementationTeam[0]/DisplayName'. This parameter is read only." If anybody is experiencing this, the most common reason for this error is becasue one of your columns contains multple values (such as People-pickers, Choice, Lookup, Managed Metadata, etc). You cannot run update item in simplicity with these columns, instead you have to add each value into an array and then add the array to the update item action. I did have a video on this a year back but it has somehow disappeared. In the meantime, check out Laura Rogers' recently added a post to her blog showing how to do this. In the meantime I will find the original video and get it back online. Laura Rogers Link: www.fabozzi.net/power-automate-this-parameter-is-read-only/
Friends there's an easier way to do this, just create a new column and put this in the Format Column / advanced: { "elmType": "div", "txtContent": "='Prefix-'+padStart([$ID],6,'0')" }
Great video John! very helpful! Is there a way to do this with a document library? Have tried triggering on creating a new file in a folder, and updating the file properties (PAID text field), however no ID field, Title or other Dynamic data are available.
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.
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.
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.
Great tutorial John, thanks! Question, how can I get rid of "Title" item? It's quite annoying cuz I want it auto-incremental field type, just like ID, I want to avoid people putting strange titles, long ones, etc. Is there any way to do that? Thank you!
Hello John this was very useful, however I had one of my users delete a record and now there is one off, I am looking but wanted to see if you could offer some assistance on how to setup this with adding a plus one to keep the number current. I have tried a couple of different ways and really still new to this. Thank you and keep up the great work!
Anybody plz help me. I m adding some info to a custom list without signing in. And, I would like to get ID/ reference number when I save the item as a popup. Thanks in advance.
Hello John, the video is fantastic! But I have done the process and I don’t know what is wrong that the generated ID doesn’t appear, it is like if your PAID column is always empty. Do you know which could be the problem? Thank you
Hi John, I want to create a ID based YYYY-000-IT. YYYY, ID, Department. Follow the guidance I created the following expresion: =concat(TEXT(triggerOutputs()?['body/Created'],"YYYY")&"-"&text(triggerOutputs()?['body/ID'],000)&"-"&"IT"). When I am trying to save the automate, an error of invalid syntax was obtained. Your feedback and guidance will be highly appreciated.
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.
Hello John, this is amazing for someone who is new (like myself) I managed to give document ID to individual emails (being saved in sharepoint) - my question is - is it possible to create document ID based on the subject? as I want to be able to create a minor version for all the replies/forwards for the same subject (of the email) instead of generating incremental number like instead of DOC1002, DOC1003, DOC1004 - I would like to create DOC1002.1, DOC1002.2 for email subject 'HOLYMOIL' for example no matter who the sender is as long as they are responding to the same email.
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.
Hi @AmandaHarris, At least 60% of the videos on UA-cam are "stolen, inspired by, influenced by" other UA-cam 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.
I searched and read multiple forums. I think this was the best. Thanks John. Much appreciated.
You are welcome !
1000 cool points awarded to this man for a very useful video.
Glad it was helpful!
Hi John, I was searching low and high, for days without any luck and thanks to UA-cam Gods to direct me to your video. I was stuck at a pivotal moment in my project and I really needed a customized column name for my automation to work as designed. Massive thanks to you!!!
You are very welcome.
I said the same thing , even chatGPT 4 didn’t guide me to this channel but God. Thanks 🙏
I agree with all the below comments - by far the simplest and most effective solution I could find after much trawling. Has worked really well for my list - many thanks, John.
John... Fantastic Video. You explained the process perfectly. Please keep making the videos!
Thank you Gen. Elias
Thank you, thank you, thank you! MVP. It worked! If you are not patient, you will leave halfway.
Fantastic tutorial, very clear, no jumping around and perfectly understandable verbiage.
Excellent guidance, thank you for assisting with this deliverable.
great video, this helped me build my first Powerautomate flow for a SharePoint List - felt weirdly giddy when it worked as intended. Big thanks!
Hi John. Thank you so so so much for posting this video!!!!! great content and you made my day today as I was looking for this for almost 5 hours.
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.
Excellent - thank you - just stumbled across this video and answered a number of questions I had
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!
Hi John this is very useful and will definitely come in handy. Looking forward to more great videos
You say the sweetest things !!
😁😁😁
Thanks, John, it's the best illustration I could find on youtube for this topic. simple and easy :)
Brilliant explanation and it worked like a charm. Please keep doing more videos. Thanks again !
Thanks Veeramani,
Glad it helped!
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'))
Hello John - from France, many thanks for your great video. I'm not really good in English but your video is very clear and useful. I will be able to propose a good list to my colleagues. Again many thanks for this good job and your help.
Excellent work! Spent hours looking for this! Thanks so much.
Hi John. Fantastic solution. Thank you for sharing.
Very welcome
You are a life saver John, Truly !!!!
Thanks David
Thank You sir , this was really helpful and nicely and explained
Glad it helped
Perfect! Thank you for sharing. Big help!
You are very welcome !
Very good. many Thanks John.
You are very welcome
Thank you so much this was extremely helpful!
You're so welcome!
Just tried this today. Thanks very much works well.
Excellent , Thankyou
You are welcome!
Hello John, this is very helpful to a nubie, however I am wanting to start a number say 10,500 and increment from there?
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: ua-cam.com/video/zFmVxlS8xX8/v-deo.html
You are my new hero. Thank you for this amazing video. Subscribing cause you are that great!!
Thanks for the sub! Let me know if you need anything in particular. I'll do my best.
Thank you for sharing
This was very useful. Thank you so much for sharing
Glad it was helpful!
thanks thanks thanks, just what need, you have a new subscriber
Yeah. Great video. Top marks.
Fantastic video, John! Have you done a video on emailing the autonumber after it's generated? My current flow is emailing the number before it is generated. Sounds simple, but I'm having troubles . . . Thanks!
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.
Greate and nice explanation Thank You
Excellent just what I needed 🙂
Useful and clear, thank you.
Hi John. I tried it but it does not show the dynamic content for the id created for the 'when and item is created' field. How do I get the id in the update item if the dynamic content does not work?
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.
This is very useful, just want to know how do you restrict content type in the columns, for example, I want the users to enter time as 13:10 nothing else.
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
Thanks man. Very good!
I loved it! Thanks
This is really helpful, however I'm getting a bit stuck trying to use the new designer when I get to the power automate step. Any chance you have updated guidance on what to do there? Thank you!
How do you do all the red mark ups (boxes, lines - @ 11:44)?
Great video can I ask if the BK prefix could be replaced using a value enter in another column as the prefix?
Yep, Just apply the column name into the concat function in place of the 'BK' value
So helpful thank you!!
Thank you, this was perfect for me.
Like that you add "tips and trick"
How do i make a flow that use "Autonumber" as name when creating a folder located in "/Shared Documents/" i can't get it to work.
I am getting an error that the number does not exist (can i put some delay or similar?).
What i'm trying to do is to when attachment's are added they saved in another folder, and i want that folder to have the autogenerated number.
(i manage to save the attachments to a new folder, did this in another flow)
And again, thank you!
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 John, great video. how would i populate items thats already exist in a list with an ID? not just new items?
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.
Hi John, very informative video. Is there a way to create a calculated field that purpose is to auto increment, but reset every year. I want to incorporate this into a custom ID that I am trying to create in SharePoint list. Any information would be greatly appreciated. Thanks!
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".
well done, thanks!!!
Can you index the new BookingId Column - ie so i can write a caml query for a range of bookingIDs (1-5000)to keep under the dreaded 5000 list view problem?
OK this is a nice learning. But what about if we want to have again auto generated column but which is not linked to the ID column. Because now we have a column which is linked to this specific ID column and we receive exact number like 1, 2, 3 and so on. If we have a column whit already entered numbers and we want to start the count like from 000022 whit increment by 1 digit to make again a unique number list like 000022, 000023 and etc? How it can be done ?
Thank you very much 🙏
Hi John. I played around whith what you are showing here, and I was impressed. I am trying to embed these steps in a broader script that starts of with a new item that is created on the list by receiving an email in a certain mailbox. I get stuck, because prior to your first step (when an item is created), I have another sharepoint step. And the Update Item step never seems to trigger when I test. Any ideas on this subject? I will keep testing myself and if I find a solution, I will post it here. THX. Wouter
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
Good morning, I keep getting the formula contains a syntax error or is not supported.
Send me a screenshot if you can darlene, and I'll see if I can help fix the error.
Thanks John! Do you have a solution for putting phone numbers into a pre-formatted solution xxx-xxx-xxxx ?
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.
Hi John, I'm having some trouble with a similar issue, after moving access tables on to Sharepoint, the Autonumber ID column from the original table now no longer updates when a new record is added. This means the record can't be edited, do you have any suggestions for this?
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.
HI ALL: Someone posted this comment recently. As I was responding, it was removed.
"...when i did this I get an error: OpenApiOperationParameterValidationFailed. The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: The API operation does not allow writing a value for parameter 'item/ImplementationTeam[0]/DisplayName'. This parameter is read only."
If anybody is experiencing this, the most common reason for this error is becasue one of your columns contains multple values (such as People-pickers, Choice, Lookup, Managed Metadata, etc). You cannot run update item in simplicity with these columns, instead you have to add each value into an array and then add the array to the update item action. I did have a video on this a year back but it has somehow disappeared. In the meantime, check out Laura Rogers' recently added a post to her blog showing how to do this. In the meantime I will find the original video and get it back online.
Laura Rogers Link: www.fabozzi.net/power-automate-this-parameter-is-read-only/
Friends there's an easier way to do this, just create a new column and put this in the Format Column / advanced:
{
"elmType": "div",
"txtContent": "='Prefix-'+padStart([$ID],6,'0')"
}
Great video John! very helpful! Is there a way to do this with a document library? Have tried triggering on creating a new file in a folder, and updating the file properties (PAID text field), however no ID field, Title or other Dynamic data are available.
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.
I really could not get this to work John Day. Has things changed since you made the video?
Hi, can I do this without Power Automate?
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, it doesnt mater if my list have a choise column in the trigger to update element?
Mine is not working
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.
Great tutorial John, thanks! Question, how can I get rid of "Title" item? It's quite annoying cuz I want it auto-incremental field type, just like ID, I want to avoid people putting strange titles, long ones, etc. Is there any way to do that? Thank you!
Hello John this was very useful, however I had one of my users delete a record and now there is one off, I am looking but wanted to see if you could offer some assistance on how to setup this with adding a plus one to keep the number current. I have tried a couple of different ways and really still new to this. Thank you and keep up the great work!
How to create different emails based on value of columns in list
Can I make that unique ID become the edit link in the list?
Hey need help how to achieve this on existing line item
Anybody plz help me. I m adding some info to a custom list without signing in. And, I would like to get ID/ reference number when I save the item as a popup. Thanks in advance.
Hello John, the video is fantastic! But I have done the process and I don’t know what is wrong that the generated ID doesn’t appear, it is like if your PAID column is always empty. Do you know which could be the problem? Thank you
Nice video, thanks
Hi John,
I want to create a ID based YYYY-000-IT.
YYYY, ID, Department. Follow the guidance I created the following expresion: =concat(TEXT(triggerOutputs()?['body/Created'],"YYYY")&"-"&text(triggerOutputs()?['body/ID'],000)&"-"&"IT"). When I am trying to save the automate, an error of invalid syntax was obtained.
Your feedback and guidance will be highly appreciated.
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.
Hello John, this is amazing for someone who is new (like myself) I managed to give document ID to individual emails (being saved in sharepoint) - my question is - is it possible to create document ID based on the subject? as I want to be able to create a minor version for all the replies/forwards for the same subject (of the email) instead of generating incremental number like instead of DOC1002, DOC1003, DOC1004 - I would like to create DOC1002.1, DOC1002.2 for email subject 'HOLYMOIL' for example no matter who the sender is as long as they are responding to the same email.
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.
Hi John, Thanks for video...once number number update then i want to share eamil to team...please help me for the same
I can't believe I just saw someone rip your video step-by-step and then claim it as their own idea. No respect between UA-cam Creators.
Hi @AmandaHarris,
At least 60% of the videos on UA-cam are "stolen, inspired by, influenced by" other UA-cam 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.
Not working for me, following exactly the same. :/
If you need any assistance with this, give me a shout and I'll be happy to help.
well I use this =TEXT(Created,"mmddyyyy-hh-mm-ss")