If you search for SharePoint auto-incrementing column or SharePoint counter column you will notice that most of the examples use the old Workflow or Microsoft Flow to achieve that. In this blog post I will explain how to use PowerApps to do the same and even better.
The benefits of using this method are:
- Configurable counter (if you would like to skip or reserve some numbers, you can adjust the counter to anything).
- That counter list can have many configuration items for other custom lists and applications.
- No Microsoft Flow runs are used.
- Further customization is possible (e.g. reserved numbers).
The only downside I could find so far, when several users submit the form at once (literally within the same second), one of them will get an error message. So I’d recommend catching that error and let a user know to retry saving the form.
Please note that in this example I’m using a PowerApps form, things might be slightly different for a PowerApps standalone app.
Counters list
First, let’s create a Counters list that will have a single item. As I noted above it is possible to use the same list for other custom applications and lists. In that case you will have more than one item.
I’ve created an item with Requests title and 10 as a starting number to prove one of the benefits of this method.
If you use that Counters list for more than one application, I strongly recommend you Enforce unique values for its Title column. It is so that you don’t have counters with the same Title name as the latter is a key for lookups.
Requests list
The next step is creating a custom list itself. I called it Requests. The Title of that list will get an auto-incrementing number once a request item has been submitted. Another column is just a Text column to store the text of each request. The Title column must have Enforce unique values turned on.
Using Title column might not be the best as it’s a Text only column. If that doesn’t work for you, you might want to create a different column.
Then we let PowerApps create a basic form. It looks as simple as this:
Add the 2nd data source – a connection to the Counters list.
Code changes
4 changes for SharePointIntegration configuration. OnNew property:
Set(SPFormMode,"New");
Set(
varNumber,
Text(
LookUp(
Counters,
Title="Requests",
CurrentNumber+1
),
"[$-en-US]#"
)
);
NewForm(SharePointForm1)
OnEdit property:
Set(SPFormMode,"Edit");EditForm(SharePointForm1)
OnView property:
Set(SPFormMode,"View");ViewForm(SharePointForm1)
OnSave property:
If(
SPFormMode = "New",
Refresh(Counters); Set(
varNumber,
Text(
LookUp(
Counters,
Title = "Requests",
CurrentNumber
) + 1
)
);
SubmitForm(SharePointForm1),
If(
SPFormMode = "Edit",
SubmitForm(SharePointForm1)
)
)
2 changes for SharePointForm1. OnSucccess property:
If(
SPFormMode="New",
Refresh(Counters);
Patch(
Counters,
First(
Filter(
Counters,
Title = "Requests"
)
),
{
CurrentNumber: LookUp(
Counters,
Title = "Requests",
CurrentNumber
) + 1
}
)
);
ResetForm(SharePointForm1);
RequestHide()
DefaultMode property:
If(
SPFormMode="New",
FormMode.New,
If(
SPFormMode="Edit",
FormMode.Edit,
If(
SPFormMode="View",
FormMode.View
)
)
)
Title_DataCard1 data card Update property:
varNumber
RESULT
Thank you very much for this! This has saved me so much time exploring forums for a working solution.
Note to readers – make sure the column for Starting Number in your Counters list is a number column (not a text field).
Hi Imran and thanks for visiting my website,
There are easier ways to implement an auto-incrementing column but I do use my way in quite a few applications and it works.
Here you can see other ways:
https://powerusers.microsoft.com/t5/General-Discussion/Create-title-based-on-user-s-initials-and-a-successive-number/m-p/305282#M87762
I am having an issue. The number does not update. Instead of getting 11. I get 101. I copied and pasted the code above with no changes. I also change the CurrentNumber column from single line of text to number and still does not work. Any insight on this would be helpful. Thanks.
Hi Alex,
I wouldn’t be able to tell right away without looking at your code but logically you are getting 101 because you “add” 1 (text value) to 10 (text value). That’s how it becomes 101. So, somewhere there is the problem.
Hi thank you so much for this. I was trying to find a way using PowerApps for a while now.
However I found a slight issue. Let’s say I have 3 items in the list and I go back to attach a file to the first item, I’m getting an “undefined” error. I have to press the x to exit the form. If I refresh the page the attachment is there.
Other times if I press save while in edit mode, nothing happens.
Any idea why this would happen?
Hi Ryan,
I don’t think this error you are getting is related to that auto-incrementing process. All it does is updating another list and looking up the value. I do remember though I had some issues with attaching files, e.g. file is removed, but still visible before the refresh. It was not related to auto-incrementing though. It was almost 2 years ago.
Hi Pavel,
Thanks the the response. After a bit of tinkering, I realized that particular issue seems to be a page refresh issue. I added a line of code to reload the page if someone attaches a document and re-submits and that seems to fix the issue.
Kind of annoying that the page reloads in a new tab but at least it works 😀
This is an awesome post…thank you so much for it! I have an issue, though. I have form with about 20 values that are only visible/required based on other selections. I don’t see how that could be the problem, but I am not seeing the number get input into my custom number column when submitting the form. Not sure where to start with finding the cause. The form is a request form, and my custom number column is named “Request ID” in a list named “Request List”. I stuck with the list name of “Counters” from your example. Every submission just ends up with an empty “Request ID” field.
Hi Jason,
I know this might not be the most helpful answer but I always recommend making my example work first, then tackle your business process. Moreover, I haven’t used my own example since the time I posted it. Hopefully MS hasn’t adjusted anything on their end that breaks it.