PowerApps Excel Auto Increment Column

[@WaliSayed]

Thank you for the great tip for generating the ID inside the form, helped a lot!

[@ShanesCows]

Glad it helped!

[@dannipals79]

This might be helpfull for someone on 6:30 in Video use: Value(Last('Table'.Id) + 1 = If(AddGameForm.Mode = FormMode.New, Value(Last('Table'.'Id') + 1, Parent.Default) correct me if it's wrong... btw nice work Shane!

[@ShanesCows]

Thanks for sharing 😎

[@LPadillaCR]

Another hit! So much help packed in a 10 minute video! Really appreciate it!

[@ShanesCows]

Glad to help

[@TimHigbie]

Shane, you are awesome at breaking it down. Thanks.

[@ShanesCows]

Glad it helped b

[@Bekesam]

Great job! I was looking exactly for this solution. Thanks for sharing in detail!

[@ShanesCows]

Glad it helped

[@blazehawkins2759]

Thanks so much, Shane! Really appreciate the insights!

[@Gorganahfikark]

Love these vids and tutorials. Keep them coming.

[@ShanesCows]

Thanks. Will do!

[@moneebtrq]

Great video, I just have one question: the formula for ID increment checks the last record and then +1, but what if user deleted the last record? That means new record will have the same ID as the old record? For example Say the last ID today is 10, and user delete record no. 10, the new entry will have again record ID 10 (because it’ll do a 9+1). Thanks in advance 🙏

[@calebthompson4934]

Whenever I create a custom card by unlocking a locked card, I lose the update property. Consequently, I am not able to update my data source with any information from that card. I noticed that you cut video footage around 4:53. How would you resolve this problem? And how did you create a custom card that still held the update property for this functionality? And thank you for posting these videos they're really inciteful and helpful for learning all about PowerApps

[@ShanesCows]

I am not sure. I have never seen the update property disappear 😐

[@donnabussure3674]

Hi Shane. I am new to PowerApps and am building my first app. I enjoy your videos and the way that you explain the concepts very well and give good examples. I liked this video, and the incrementing column worked exactly as you said. However, I wonder if you could take this a step further. In the app I am building, I need a customized ID field, something like KMDR-200001, for instance. I have watched other videos, but they mostly use SharePoint. It's harder to find something that uses Excel as the data source.

[@erllonJosi]

Tks for explanation... regards

[@ShanesCows]

You are welcome

[@Mai-lr6yy]

Thanks so much... You are perfect 💪😍

[@ShanesCows]

Thanks ☺️

[@davidg7328]

Hey Shane nice video you are the best. I am having a Network error trying to patch muy autogenerated number, i am not using an editform.

[@ShanesCows]

Post your formula and I will take a look.

[@derek_shannon]

Hi, Shane, do you plan to make some model-driven app videos? Would be interesting to see how the "Keys" for model-driven CDS apps relate to this. You rock!

[@ShanesCows]

Hi Derek- I do. As for the Key property I asked the same question recently. It is mainly used when are using CDS with another tool that cannot use the guid key CDs uses. If you are just doing CDS with canvas or model apps you don’t need the key. Does that help?

[@derek_shannon]

For sure! It's not essential, but I was trying to figure out Keys while determining whether I could have projects iterate numerically, and subprojects alphabetically, to come up with a 1-A, 1-B etc key.

[@utoroprincewill7808]

Nice video, A quick question. What if on opening a form and the auto increment on the ID is last + 1 = e.g 9, and before you save, someone has already saved and the lastID is now 9. Will the form change from 9 to 10? considering that the first user has not closed the new form that was open before, that has the initial last value as 8.

[@ShanesCows]

Nope you will need to check if that happened. Use my new duplicates video for ideas on how.

[@h39dna84ncs7ds]

Nice video, this helped me out. Thanks

[@ShanesCows]

Awesome

[@nicolasl855]

Thanks ! it helps !

[@ShanesCows]

Awesome 😎

[@h39dna84ncs7ds]

@9:02 - I need to know about this sorting issue with numbers to show them in a true integer number order. I think it's called lexicographic but i can't fine anything about it. I can get it to work by changing my Excel column format to "Number", but this then breaks the increment feature. It can't find the "TicketID" column using DataSourceInfo.MaxLength formula. Any ideas?

[@ShanesCows]

Has to be a number column. Sorry. You will have to find a way to make the other pieces work.

[@leonlabuschagne5130]

Can i use Power apps to create a site inspection app. Whereby i put in a bunch of checks as well as tick boxes. with a location option. After filling out the inspection form and submitting it, is there a way for it to not override the current form but save a new form with all the data filled in?

[@ShanesCows]

Yes. If you check out this video I do an inspection app kzbin.info/www/bejne/hKjFopd3bs9re80

[@arunkumargangadharan6644]

Hai Shane, Whit this auto increment i need a prefix and the prefix should be based on my drop down. Example if drop down value is "XX" then my id should by "XX-11"(last data value +1).

[@davidniemeier2599]

This worked awesome so thanks. My Last() will only review the first 500 rows :( I increased to 2000 in advanced setting. Not sure what I will do when I reach that max!

[@ShanesCows]

If you get to more than 2000 items consider changing data sources. Sql or CDs would be my suggestions.

[@luisguillermoalfaro527]

Hi Shane, great job here. Hey, quick question: what will happen if multiple users hit add button at the same time? The last record will be loaded once so the excel probably has more records created before I click save on my session.

[@ShanesCows]

Yup you would have conflicts/duplicates. You will need to have logic to handle that or move to a better datasource like SharePoint

[@guillermohurtado3157]

@@ShanesCows Hi Shane, I am using SharePoint as DataSource, and still have duplicates with multiple users, how can you avoid this?

[@j.a.weishaupt1748]

Thanks! Works great.

[@ShanesCows]

Awesome

[@kirillperian]

Hi, Shane! Thanks for the video - very helpful! If I wanted to take it a step further and create a "dynamic" ranking, i.e. 1, 2, 3, 4 for my gallery records based on some kind of sorting criteria, for instance, datetime of survey responses, what would be the best way to implement this? So that if a record is deleted, the rank gets updated when the dataset is refreshed (so there is a contiguous set of rank numbers)? Using collection with some kind of counter formula? If there is a video already that you could point to, I would greatly appreciate it!

[@ShanesCows]

That is a pain to do. It is possible but you have to do some counting. Maybe one day I will get this covered. I need to show row numbers technique to do it b

[@zahirzahari8043]

Hi Shane, thanks for creating these videos, they really helped new powerapp users like me to get started. I tried creating a similar list like yours. I built an app from data like you did and tried to delete a data using the app. I was hoping to see that the ID still be in order. Ex. I deleted data ID 1 and was hoping that the data ID 2 and so forth be data ID 1 and so forth but it didnt. Initial Table ID 1 Pencil and Pen Red ID 2 House Boats Blue ID 3 Tank Green After deleting ID 2 House Boats Blue ID 3 Tank Green I tried to put some If() function in the mix to get the ID to be in order when a data is deleted but it got really complicated, i repeatedly get confuse with the If() functions and it doesnt work. Do you have a simplier way to do this? Or if you dont have a simplier way, how do you correct this? Thanks in advance

[@ShanesCows]

I don’t have anything to offer. It isn’t normal to need to renumber an ID. 😑

[@zahirzahari8043]

What if the I use running number instead of ID number? Would be possible to achieve it?

[@786nabeelquadri]

Hey Shane, awesome videos! Can you please do a video on how to use primary keys and foriegn keys in PowerApps using Excel! If you have already made a video on it, can you please mention the link! Thanks Again!

[@ShanesCows]

Probably not. Excel isn’t a great data source so I wouldn’t recommend it. Can you use a different datasource?

[@786nabeelquadri]

@@ShanesCows Hi Shane, I have never used any other data source, and I am a basic level designer, without the knowledge of coding. Which data source would you recommend for me? Thanks!

[@forgotrafe]

Great if you need to show the user a Reference number, for example, but for a unique ID for doing lookups/cross-referencing are you not better using PowerAppsId?

[@ShanesCows]

I think we figured out over email you can’t? 😀

[@alifpurnama3298]

hi shane, what if at the beginning of the number there is a letter like XYZ- ... how to add it? e.g. XYZ-1, XYZ-2

[@mrleoguz]

hello, from Chile, thanks x your videos. One question, if 2 or more users are using the Published App at the same time. How is it possible to Refresh before recording (so that the IDs are not duplicated)?, TKS.

[@ShanesCows]

There is a Refresh(datasource) function you can use.

[@mindjogger4012]

Shane , how can we record in the new columns? Scenarios is that there is fix set of questions and want to record responses in the separate column , is it possible ?

[@ShanesCows]

Yes look for my video on Patch.

[@PauloStecklow]

Hi Shane. What's hapen if more than one person is creating a new form? Is it possible both get the same number ID? If so, how to prevent this? Cheers

[@ShanesCows]

Yeah two people could step on each other. Best advice? Don’t use Excel use a SharePoint list instead. If not then you need to write error checking logic to check for conflicts

[@PauloStecklow]

@@ShanesCows I'm using SharePoint already. I think I'll create a calculated column in SharePoint to create this ID, based on ID list auto-created by SharePoint.

[@ReneLopez-vb6gb]

Hello Shane, thanks for your videos, in the same way that you have solved this problem, I am trying to solve one, which is surely because I have not managed to understand the syntax of the formulas, what I want to do is that some co-workers who will use the app, when creating a record I need to increment it one by one as you do in your example, but at the same time I want to concatenate the initials of the person who has logged into the app (these initials and the users are detailed in a table of users in onedrive excel), this to differentiate the correlatives of each record with the initials of the user, can you help me ?.

[@ShanesCows]

Rene it might be easiest to store the initials and the count in two separate columns. Then have a 3rd column that combines them. That way you can more easily use my example.

[@ReneLopez-vb6gb]

Thanks for answering Shane, the users table is an auxiliary table, which I use so that the users log in to the app, then with the initials that it brings from this user table + the correlative, it will be the ID of the record in another table of movements, for example, a user logs in and his initials are XH, then, these initials are saved in a variable, so that when the user saves his record in the movements table, the first ID would be: XH001 and the next XH002, with all the other fields that make up this record, another user who logs in, his initials are JG so his first saved record would be JG001, the second JG002 ...

[@imranfc]

Good knowledge

[@ShanesCows]

Thanks 😀

[@JCarterB]

Hey Shane, thanks for the videos. I was stuck at a problem with auto increment. If multiple people open the form at the same time, the autoincrement will assign the same ID to all the users. Making the ID field unique is ok, but when the form is too long, the error message at the time of submission is frustrating to the users. I created a separate list just to assign the new IDs as soon as the form opens but there is no way to track canceled or refreshed form IDs. Is there a novel method to counter this situation?

[@ShanesCows]

Best advice is use SharePoint not excel for the datasource. Other than that you need to write fancy logic to check for conflicts

[@maryamalsulami9671]

Please If I need to use last submission as a reference how to it would be ?

[@ShanesCows]

FormName.LastSubmit gets you the last record kzbin.info/www/bejne/hGmniKmJbLVsotk

[@Poetswar]

Hi Shane, couldn't two records end up with the same id if two user are adding data at the same time because the number is read, then incremented then passed back at the end.?.?

[@ShanesCows]

Yes. That is why I don’t love Excel as a data source.

[@Poetswar]

@@ShanesCows hahaha fair play.

[@bmhblake1]

@@ShanesCows Is there a way to prevent this? We currently have this issue in one of our projects.

[@holyonfire]

Thank you very much for your videos, Shane! At 7:33, if I did want "House Boats and cars" to become ID 9 (so, save a new record based on editing an existing record) and keep "House Boats" as ID 2 (so, preserve the existing record), how could I do that?

[@ShanesCows]

You would use Patch and tell it to create a new record instead of editing one.

[@holyonfire]

@@ShanesCows Okay great. I'll look into that. Thank you!

[@finnpower92]

HI Shane, this solution to use the "Last" is not delegable. So after 2000 records in your gallery, the auto increment wouldn't go higher than 2001. Have you come across any other solution to create this in any other way?

[@ShanesCows]

You can filter or sort your data and then use last against that. Though I am not super sure what is delegable against Excel. It isn't the best data source. 😐

[@finnpower92]

Thanks for the fast reply, really didn't expect it on a 5 yr old video. :) That is correct, I managed to fix my sharepoint based app. Instead of setting a variant to the last id of my data source (non-delegable), I first sort( my data descending by ID, and then choose the First ID (delegable). Awesome, thanks alot! I had been battling with this for weeks.

[@katiebeton2767]

Thank you so much for this video Shaun!!!! I've been struggling with this for months! How would I add a letter to the beginning of the number. For example T1. I've watched your other IF function video and tried to add "T" at the beginning of the formula however it breaks it.

[@ShanesCows]

Katie what is the function you are using to set the number? You should be able to just add "T" & to the beginning of it. Then remember to treat everything as text not a number.

[@katiebeton2767]

Thanks Shane. I managed to add the T however it stops the auto incrementing ID working. I just get T1 for every record. Any idea how to fix this? "T" & If(EditActiveProjectForm.Mode = FormMode.New, Last(ActiveProjects).Project_x0020_Ref + 1,Parent.Default)

[@kristytanner9320]

Hi Shane. I am using this auto Increment formula between a sharepoint list and powerapp form. The issue I am having is when new users were added their id number started at 1 and wouldn't join in with everyone else. So the last ID added was 20 and one user added an item the ID was 1 then another user added an item the ID was 1, the next time each user added an item the auto increment worked but still kept them separate almost like it was user defined. Do you have any ideas on why that would be?

[@ShanesCows]

If you are using SharePoint there is a built in ID column that will do all of this for you. No reason to do it yourself :)

[@kristytanner9320]

The issue we have is because we have been testing the system the ID column wont start at 1 and to my knowledge there is no way to reset it?

[@AdiCristea]

Pause at 9:51 and Graphs is in there, woohoo, that was my requirement. :) Have done some things, but still struggle to show full labels, can't find a way to maximize the x labels area. Also Security is on the list, currently I have an issue with Powerapps needing write permissions into a Sharepoint list, so even if I ring fence some areas to be seen/editable by a number of people, they can always go directly to the source and see/edit what is not meant for them. Any advice, please?

[@ShanesCows]

Love it. Sounds like you have done more with Graphs than I have. I need to get to work. :) Security. Yeah, with SharePoint this is no way getting around it. :( Boo. This is a case where you would need to move to CDS or SQL to get the better security you want. Sorry.

[@AdiCristea]

Thank you, kind sir. Had a similar answer thrown at me from a different source, so it must be true then :). SQL FTW. Regarding Graphs, yeah, pretty nifty feature, but it's still basic. Probably they are more looking to embed Power BI tiles than to actually provide a powerful graphics tool. And up to a point I can understand that. Great video, btw, I actually used the info today in an business app I built which will be pushed to the entire organisation. Pretty proud of it, however most of the things in the app are learnt from your lessons, with some modifications. I guess I owe you a few. Let me know when you're in the UK next time :).

[@haleighfritts7701]

Thanks for your video! I used this formula and other variations I found on the internet but I keep getting the same error. I have 612 records so the next one should be 613, but instead with this formula the ID column for a new request auto populates with 568 and I can't figure out why. Any ideas? I already changed the delegation from 500 to 2000 in the settings.

[@ShanesCows]

I am not sure. I have never seen this not work 😐

I used the same method for auto increment column in Excel, but I get stucked in 498, I changed the delegation to 2000, but the issue persist. that's my code for the increment, If(FormularioCalidadLineasEnvasado.Mode=FormMode.New;Last(CalidadLineasEnvasado).U_NX_Code+1; Parent.Default) I really apreciate it if u can help us ​ @@ShanesCows

[@alcado253]

Hi Shane, thank you very much for your tutorials. In this one I noticed at 03:10 that when you edit a product's datas, the iconedit open a form with all fields already filled by the datas of this product. What is your code for iconedit OnSelect ? Because mine edit the datas by opening an empty form : EditForm(NewLeakForm);;Navigate(EditFormScreen; ScreenTransition.None)

[@ShanesCows]

You have to set the Item property for the form not the button. I think this video will help you. kzbin.info/www/bejne/r4WXmHqMqtOIZtE

[@alcado253]

Big thanks !

[@GuerrerosdeCristoec]

hello. thanks for yours videos. i have a problem with my app in powerapps. in my app the departament dropdown componet dont show me a list from sharepoint (I send you a photos) but test in powerapps works very well and insert data in my sharepoint list... Do you have any idea why this happens?

[@ShanesCows]

I don’t know. Is it a permission issue?

[@LAKUNSTA]

How do you tackle duplicate id number when multiple users save at same time

[@ShanesCows]

Paint in the backside. I did it for one customer and I ended up with a lot of error checking logic. It worked in the end but it was not fun

[@LAKUNSTA]

Shane Young I bet. Will you like to help me with a on a project I’m working on?

[@andres.biarge]

what about concurrency? If two people add a new row to the excel, the Last() will be the same number for the two users. So, you'll end up with two new rows in your Excel file with the same ids

[@ShanesCows]

Yup. Just one of the reasons Excel is not an awesome datasource

[@praveencb16]

Hi Shane, I am using SharePoint list customize form and creating an auto increment request#. The method you provided works, but if two persons simultaneously try to create an item and when tried to save, it shows an error that the number is not unique. Seems both the items pulls the same last request number. Need you help please to resolve this issue.

[@praveencb16]

Any help, greatly appreciated.

[@ShanesCows]

My best advice? You a different data source? Excel is not a good data source. Sorry.

[@praveencb16]

@@ShanesCows I'm using SharePoint as the data source and still have the problem.

[@jayantmaggoo5756]

hi i wanted to know that i need a edit form in which when i submit it . the form should autmatically bring up the next student record to be edited . kindly help.

[@mthompson6782]

Shane, Can you do this for Sharepoint online as opposed to excel?

[@ShanesCows]

With SharePoint Online you can use the ID column that is built in. :)

[@sethbass5611]

@@ShanesCows What about an auto increment column based on two different columns, for instance [Office]&[AutoIncrement] ( CityA1001, CityB1002, etc...) Where the [AutoIncrement] starts at a user defined number and the City is from a Choice Column. Using SharePoint Lists and Flow. I have searched for this and haven't seen the solution in Flow, but can find several for legacy sharepoint designer and workflow. Your video's are great, you explain items in a easy manner to digest.

[@fabianetzaelhernandezespin5446]

Hi, i need view the last row with this function, i used Last(LookUp and Last(Filter but don´t works

[@ShanesCows]

Last(datasource) should get it.

[@fabianetzaelhernandezespin5446]

@@ShanesCows thank's. i need the last row of column km when i selected a car in my droplist, look : Last(LookUp(Datos_Solicitud;Vehiculo= vehiculos.Selected.Automovil;'Kilometraje final')). 'Kilometraje final' but it doesn´t works

[@stevebell9050]

Hi Shane, if you have more than one user entering data this produces duplicate ID's. Is there around this?

[@ShanesCows]

Long story short you would have to code for it. Anytime in PowerApps, you have multiple people editing/creating data at the same time and there is the potential for conflict you have to solve the problem on your own. For this one... I would probably.... umm... I don't know off the top of my head, to be honest. Excel just isn't meant to be a data source like it is used so much for. I guess what you could do (I am really trying to give you an answer) is set the ID to a variable when they press New. Then the only way you would have a conflict is if two people managed to hit New at the exact same time. Sorry I am not more help.

[@Crockerfeller]

In this case I think using a sharepoint list column with an auto-incrementing ID column is easier to implement. Using this method, PowerApps doesn't have to set / create the ID, its done by the SP list.

[@metalsnake00]

Shane pls help!!!! is there any video you have where you can make default Title column in Sharepoint List to generate unique ID number?

[@ShanesCows]

I don’t think so. SharePoint has a built in ID column that is always unique. That is typically what I use.

[@metalsnake00]

@@ShanesCows i found the solution after hours of watching your videos, so what i did is, i made Flow to update the Title column to like NUM-OO-(ID), made it not required to have value and hide it from form :D, but this is all cause combining your videos with different case's and came to this

[@franciscojavieravilasanche5405]

Hello Shane, This video solved one of the multiple puzzles that i'm facing in the creation of my first app. I want to use the Auto Increment Column when new users are registered and when the existing users edit some fields on different days but I want to keep the existing data in some columns (eg. Name, EmployeeID, Position, Area). Let´s suppose I have 3 existing users that need to report daily the usage of certain resources of the company. First record in the morning when they come in and then in the night when they leave. If I use the [ + ] Icon it will only auto increment when new user is registered. Is there any way, any other icon or button, that allows me to do the auto increment but keeping the data in some columns of the existing users. I'm using SharePoint instead of Excel. Below I'll give you an example of what I want to do... or get when I export the list to an excel file. Let´s suppose I have 3 existing users that need to report daily the usage of certain resources of the company. First record in the morning when they come in and then in the night when they leave. In this example, they all have 100Resources at the beggining, Manager should use 20 per day, Secretary should use 10 per day and Technicians should use 25 per day (this may vary according to the data submitted by each user). Day1 AutoIncrementColumn1, John, EmployeeID1, Manager, Security, ResourcesAM100, ResourcesNight80 AutoIncrementColumn2, Mary, EmployeeID2, Secretary, Security, ResourcesAM100, ResourcesNight90 AutoIncrementColumn3, Mike, EmployeeID3, Technician, Security, ResourcesA100, ResourcesNight75 Day2 (Employee2 is not coming to the office) AutoIncrementColumn4, John, EmployeeID1, Manager, Security, ResourcesAM80, ResourcesNight60 AutoIncrementColumn5, Mike, EmployeeID3, Technician, Security, ResourcesAM75, ResourcesNight50 Day3 (New Employee) AutoIncrementColumn6, John, EmployeeID1, Manager, Security, ResourcesAM60, ResourcesNight40 AutoIncrementColumn7, Mary, EmployeeID2, Secretary, Security, ResourcesAM90, ResourcesNight80 AutoIncrementColumn8, Mike, EmployeeID3, Technician, Security, ResourcesAM50, ResourcesNight25 AutoIncrementColumn9, Chuck, EmployeeID4, Technician, Security, ResourcesAM100, ResourcesNight75 Day4 (Everybody submitted a report) AutoIncrementColumn10, John, EmployeeID1, Manager, Security, ResourcesAM40, ResourcesNight20 AutoIncrementColumn11, Mary, EmployeeID2, Secretary, Security, ResourcesAM80, ResourcesNight70 AutoIncrementColumn12, Mike, EmployeeID3, Technician, Security, ResourcesAM25, ResourcesNight0 AutoIncrementColumn13, Chuck, EmployeeID4, Technician, Security, ResourcesAM75, ResourcesNight50 I´ve tried to be as clear as possible, I hope you understand me. Thanks in advance.

[@ShanesCows]

If you write the logic you can pass the store value from one item to the next. You just need to set the new form to use the saved data as the default values.

[@sreejishnushylajan6536]

Hi, good morning. Thanks for informative videos. Can you explain powerapps SharePoint auto increment....

[@ShanesCows]

SharePoint automatically has a column called ID that does autoincrement. :)

[@bgergoe]

What this video doesn't cover is when your datasource has a non-editable ID column which you may not want to display, and maybe some records were already deleted, so the IDs aren't even consecutive. In that case you can use this formula: AddColumns( RenameColumns( Sequence( CountRows(YourTable), 1, 1 ), "Value", "Rank" ), "RealID", Last( FirstN( Sort( YourTable, NumericColumn, Ascending ), Rank ) ).NumericColumn )

[@madihasalman1]

is it possible to create the customerID like 14000 instead of 1

[@ShanesCows]

Yes. Just start with a bigger base number.

[@297156]

Do you have example with chart?

[@ShanesCows]

I don’t. Sorry.

[@julietzhang]

Can you please make more content on making apps with CDS?

[@ShanesCows]

Yes. I need to do this. Must do this soon. 😻

[@jayveepacanza]

Awesome Job!. It seems to solve my problem but I want a Custom Auto. For Example: My ID is started in "2019-07-0096" and when I create a New document should build to "2019-07-0097" the "2019" is the current year and "07" in the month and "0097" is autoincrementing. The Year and Month based on the current date. I hope you might solve my problem. Thank you

[@ShanesCows]

Look for my video on Strings for an idea.

[@corby8678]

wow ..

[@ShanesCows]

😎

[@adf1483]

Não entendo o Ingles mas conseguir fazer o exercício \\ I don't understand English, but I can do the exercise

[@ShanesCows]

Impresionante

[@HiteshGulati]

Your videos are really very helpful. But please stop having the intro, that really sucks the energy out of me.

[@ShanesCows]

Sorry you don’t like it but 200+ videos now I don’t see it going away. Sorry. Lots of other people love it. You could always skip to about 30 seconds in. That is usually we’re I start the Hi my name is Shane.