How to get Next number of ID column and display on Customized PowerApps Form

[MSD52]

Hi,

I have a SharePoint List and did customized with PowerApps Forms, I have added SharePoint list default "ID" Column and added to the Form and it is working as expected and storing list items in sequence in "ID" column 1,2,3..so on.

Here I need to Display next "ID" number on the form when user is filling form from that list.

Example: In list there are 3 items that means in "ID" column the number is 3 so when user comes and fill the new item so in "ID" column datacard it should display automatically 4 as it is next item.

How to show this one. Please need assist on this.

Thanks.

[v-jefferni]

Hi @MSD52 ,

Please try:

First(SPlist).ID +1

Best regards,

[MSD52]

Hi,

I have put this code on "Text" of DataCardValu but when I edit the Item and click "New" item also it is displaying 2 but in my ID column in the list I have already 2 is there now it should display 3  

Thanks.

[seanbrogan]

Instead of 

First(splist).ID+1

Try

MAX(splist).ID+1

[MSD52]

Hi,

It is also giving error like, Expected number. We expect a number at this point in formula.

Here I want to store and display like TI001,TI002,TI003...etc. in that "ID" column. How can i achieve.

I need to concatenate "TI" with "001" and so on...How can it is poassible

Thanks.

[seanbrogan]

Because you are using a form you will have to add your own custom card. Normally a card in a form will be bound to a column on the SharePoint list. By adding a custom card you can create one that is not bound to a column on the SP list.

Then for the default value of that unbound card you can use... "TI" & Max(SPlist).ID+1

[Drrickryp]

@MSD52 

Last() and Max() are not delegatable.  When you exceed the row limit in the settings, the numbers will be inaccurate.  use First(Sort(splist, customID, Descending)).customID+1 for the next one.  This will work for any number of records. 

[MSD52]

It is displaying as expected But when editing the existing list item it is taking again same number.

For example: If i put this formula First(Sort(splist, customID, Descending)).customID+1, when I clicking on "New" in the list it is giving +1 item as expected but when edit the already existing item in list then also it is showing same number.

Do we need to write the formula based on Form Mode. How can we write.

I need to display Like "TI001" how to concatenate based on form mode TI+001 i.e TI001 and sequence would be TI002...so on 

I got reference formula below, How to modify this one to achieve TI001,TI002,...So on  

If(Frm_02.Mode= FormMode.New,Concatenate("TI",Text(Right((Last('SPlist').'ID'),Len((Last('SPlist').'ID'))-3)+1)),Parent.Default) 

[Drrickryp]

@MSD52 

Set the Default property of the control or the Update property of the card to

With({nextid:First(Sort(SPlist, ID, Descending)).ID+1},
Coalesce(Parent.Default,Concatenate("TI",Text(nextid,"000")
)

and disable the card or hide it to prevent users from changing it. 

Coalesce() reads the elements from left to right until it finds a nonblank.  Since Parent.Default is always blank in a new form, then the formula for the next TI is the result.

The formula above will work no matter how large the dataset becomes. 

[MSD52]

When I Put this below code on "Text" property of the DataCardValue on Default "ID" column it is not storing TI001,TI002...so on, It is storing 1,2,3..only and when editing list item also it is displaying on the form numbers only not TI001,TI002..etc. 

With({nextid:First(Sort(SPlist, ID, Descending)).ID+1},
Coalesce(Parent.Default,Concatenate("TI",Text(nextid,"000")
)))

And also i tried, Created one Text column in the same list and kept the above same code changing column name in "Default" of DataCardValue then it is giving the value TI001 can not be converted to a number when i clicking on New item in the list.