Autofill columns in List

[smajic175]

Hi

how can I make that when I create a new Item in a List, the OrderingNr. field is automatically filled with the number that comes after the last entry?

Many thanks in advance for your help

[#2]

In SharePoint I use the ID column which comes with every new list and automatically generates an increment by 1 when you create a new entry. You could either use the ID value as is or go and generate a calculated value based on the ID.
The nice thing about the ID column is that the ID stays unique even when you delete items.

[#3]

That I know, but I need the first number to be 2001 and every other should be increased by 1.
If there is no solution with sharepoint, can you make that with PowerAutomate?

[#4]

@smajic175 

In your SharePoint list create a new column. Choose the "Calculated" as column type.

In the "Formula" box put 

="2001 "&ID

You could enter basically anything as the string. "&ID" will just attch the value in the ID-column to that string. Hope that helps. 

[#5]

@reinerknudsen 

Thanks I tried and get the following message.

[#6]

@smajic175 

From your list click the gear icon (left upper right in the header bar) and select "List settings".
You will see the list of your columns. Below the list you see "Create column"; click that.
Now enter the name of your new column and check "Calculated".

Then find the "Formula" box and enter your string and attach "&[ID]" to it.

Let me know if that works.

[#7]

@reinerknudsen 

Hi I tried and put in following ="2100"+ID

and it works partially, since when I add a new Item to the List it shows following, see the last Item added, it shows then 2100 instead of 2342

[#8]

Please read my instructions again.
It's not
"2001"+ID (which probably converts the string to a number and then adds the ID) but
"2001" & [ID] - the "&" is a string operator, whilst the "+" is a math operator. Plus the columns content is return but the columns name in square brackets [ and ].
Please try again

[#9]

@reinerknudsen 

Hi now I did what you said,

and put in ="BN.210-"&[ID]

still shows this 

the brackets [] get lost every time I save and close the settings

[#10]

You realize that you are trying to create a column for type "single line of text", don't you? That'S not what I was talking about. I think you still try to create a column from the fancy UI view which doesn't work for "calculated" columns because they aren't offered as an option here.
Okay, let's try another approach:
From your list select "Add column" and then select "More...". You are now taken to the column creation. (See my screen shot above)
Give your column a name and select "Calculated" and add the formula to the "Formula" field.
Save the column (save button is at the very end of the screen; you might have to scroll.

[#11]

@reinerknudsen 

I thik you did not get me I did create a new column as Calculated.

[#12 · Accepted solution]

@smajic175 
Ok, let me see if I get that right. 

You created a column "Bestell-Nr." to hold the combined String "BN210-1". Correct?

Do I get it right that you renamed the "ID" column into "Bestellnummer"?

I setup a list as I suppose you did. Here is what I noticed:

I currently entered item 3. You see the formula doesn't work. Even after reloading the list it won't change.

Now I go back into the list settings, open the settings for the column and do nothign else but save it again:

And then this happens:

Don't ask me why. It is really weird. 

Tried it again and it won't work again. So I changed the formular, saved the column;changed the formula again and saved it again. And it would work again. That is all but satisfying. 

Have you thought of replacing the standard edit form with a PowerApp (Integrate | PowerApps | Integrate Forms). You could add a field there which does the calculation for you with even more options of formating the string. Just a thought.

[#13]

You cannot use a calculated column based on the ID at creation as the ID does not exist.

I found this video which uses Power Automate to generate a custom reference.

https://youtu.be/35FHdGKTeZE

I think the same YouTuber has another video which generates the reference using a separate list, whereby you effectively have a single table which contains multiple ID formats for different applications that gets incremented automatically.