Answered

Increment value field based on Field category

(0) Share

Report

Posted on by learnpowerapp

I have SharePoint list with Field names Category(choice column) and Value(number column). I am using this SharePoint list as data source in PowerApps. My requirement is whenever a new item is created for each category, value should increment respectively.

Example: If I create new item under category Product, it should value 001 and when I create new item under category Marketing it should value 001. Then next if I again create item under category Product, it should value 002

Category	Value
Product	001
Marketing	001
Product	002
Network	001
Network	002
Product	003
Marketing	002

I am new to PowerApps, any help will be greatly appreciated!

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 154,496 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @learnpowerapp ,

If you used this as the Default of the Category Text field in your Form, it should create the next value when one is not already present (new records) - Also Value is a very bad name for a field as it is a Reserved Word for many uses in Power Apps.

Coalesce( ThisItem.Value, Text( Value( LookUp( Sort( SPListName, ID, SortOrder.Descending ), Category = YourCategoryDropdown.Selected.Value ).Value + 1 ), "000" ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
learnpowerapp on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz , thanks a lot for your help, the above formula worked for my requirement to increment value based on Category. However, the values are coming up as 1,2 and not 001,002. Can you please help me with how can I set "00" as prefix in the above formula.

Additionally, thanks for correcting me with regards to column name 'Value'. I will change the name of the column accordingly.

Was this reply helpful? Yes No
WarrenBelz 154,496 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@learnpowerapp ,

Text(. . . . ., "000") puts it in this format - did you use the code structure I posted and where did not use it (please post your exact code in Text)

Was this reply helpful? Yes No
learnpowerapp on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz ,Here is my exact code:
Coalesce(
ThisItem.Value,Text(Value(LookUp(Sort('Inventory List',ID,SortOrder.Descending),'Category'.Value=DataCardValue8.Selected.Value).Value+1), "0000")
)

Here"DataCardValue8 " refers to Category field

Was this reply helpful? Yes No
WarrenBelz 154,496 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@learnpowerapp ,

To test the main bit, put this in a label

Text( Value( LookUp( Sort( 'Inventory List', ID, SortOrder.Descending ), 'Category'.Value = DataCardValue8.Selected.Value ).Value + 1 ), "0000" )

Was this reply helpful? Yes No
learnpowerapp on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz -The above formula works in making the text as "00001" but once the Value changes to double digit, the text is also changing(example: if my item number is 10 the value is changing to "00010"). In my case I want "0000" to be constant. So I tried Concatenate function in the previous formula you have given and it worked for me.
Concatenate("0000",Coalesce(
ThisItem.Value,Text(Value(LookUp(Sort('Inventory List',ID,SortOrder.Descending),'Category'.Value=DataCardValue8.Selected.Value).Value+1))
))
Thanks a lot for your help:)

Was this reply helpful? Yes No
WarrenBelz 154,496 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

@learnpowerapp ,

Sorry, but I am quite unclear now what you want. How can 10 be 0000 ?

Was this reply helpful? Yes No
learnpowerapp on at

Like (0)

Report
Copy link

Link copied!

@WarrenBelz - I want to showcase my Value field increment in this format for each Category.( where "0000" is a constant text before my Value)
Below I have taken example of Category: Product

Category
Value
Product
00001
Product
00002
Product
00003
Product
00004
Product
00005
Product
00006
Product
00007
Product
00008
Product
00009
Product
000010

Was this reply helpful? Yes No
Verified answer

WarrenBelz 154,496 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@learnpowerapp ,

Very unusual and changes the whole concept of string construction - you just need to add "0000" on the front of the number

"0000" & Text( Value( LookUp( Sort( 'Inventory List', ID, SortOrder.Descending ), 'Category'.Value = DataCardValue8.Selected.Value ).Value + 1 ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No