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

Mike8
Level 10

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
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 425 members 4,776 guests
Please welcome our newest community members: