cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Auto increment column

Hi,

 

i have a column with name number and type of the column is single line of text in sharepoint List. need to increment column value as 2018-001(year-number). if the year changes, then the next created value will be like 2019-001. the colums values should look like below

Number(singleline of text)

2018-001

2018-002

2018-003

2019-001

2019-002

2020-001

the above process should be in powerapps 

5 REPLIES 5
Highlighted
Resolver IV
Resolver IV

You could create 2 columns in your SP List, "Item_Year" and "Item_Index"

In your gallery or anywhere in your app you can display them combined simply by 

Item_Year & "-" & Text(Item_Index,"[$-en-US]000")

If you use a form to create, set the Default of the datacard (the datacard, not the control on the card) for them as

 

Item_Year

Year(Now())

Item_Index

If (
    CountRows (Filter(DemoList,Item_Year=Year(Now())))=0,
    1,
    First (SortByColumns(Filter(DemoList,Item_Year=Year(Now())),"Item_Index",Descending)).Item_Index + 1
)

This second one is the only complicated bit.  What is does is first check if there are any records already with the year set to the current year.  If there aren't then it sets the index of the new record to 1.  If there are, it then filters the list to only return the records for the current year, sorts those records by the Item_Index column in descending order so the highest index is a the top, takes the first item of those records, returns just the Item_Index field and then adds 1 to it.

 

Hope that makes sense.  This does break delegation processing for SharePoint in numerous places but thats another issue

 

Andy
Read my new blog at -
www.powerappssolution.com

Highlighted
Solution Sage
Solution Sage

Hello,

You can also try this:

Gallery1.Items = Collection1
inside Gallery1 --> Label1.Text = ThisItem.ID

Button1.OnSelect =
Collect(Collection1,{ID:Text(Year(Today())&"-"&Text(CountRows(Filter(Collection1,Left(ID,4)=Text(Year(Today()))))+1,"[$-en-US]000"))})

Highlighted

Hi Andy,

thanks for your help.

i have used below formula in Number column Default function, its giveing result as expected

 

If(Year(Now()) > Year(DateTimeValue(Left(Last(SortByColumns('listname',"Nummer",Ascending)).Nummer,4))),Concatenate(Text(Year(Now())),"-001"),Text(Left(Last(SortByColumns('listname',"Nummer",Ascending)).Nummer,4))&"-"& Text(Right(Last(SortByColumns('listname',"Nummer",Ascending)).Nummer,3)+1,"[$-en-US]000"))

Highlighted
Impactful Individual
Impactful Individual

Hi @satya,

 

As a small performance improvement: I see you execute three times Last(SortByColumns('listname',"Nummer",Ascending)).

 

You could also do that once by assigning it to a variable and use that variable. The formula gets a bit more readable too.

 

Question:

How do you make sure that no two entries are made at the same time in the SharePoint list meaning that there are two items with the same number?

 

Robot Happy Rick

Highlighted
Frequent Visitor

Hi @Rick72 

 

Was wondering if there is any way to avid two records with same number?

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (13,126)