cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
satya
Level: Powered On

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
Andy_Tuke
Level 8

Re: Auto increment column

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

Super User
Super User

Re: Auto increment column

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"))})

satya
Level: Powered On

Re: Auto increment column

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"))

Rick72
Level 10

Re: Auto increment column

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

sheet
Level: Powered On

Re: Auto increment column

Hi @Rick72 

 

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

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,454)