Add incremental ID for each Sub-Category of a Category

(1) Share

Report

Posted on by ragnezza

Hello - I have the below data source, where each new Category has always the same combination of SubCategory (Sub1, Sub2, Sub3):

Category	SubCategory	ID
AAA	Sub1	100
AAA	Sub2	101
AAA	Sub3	102
BBB	Sub1	103
BBB	Sub2	104
BBB	Sub3	105
CCC	Sub1
CCC	Sub2
CCC	Sub3

I'm trying to add an incremental ID for each Sub-Category anytime that a new Category is added (in this case 'CCC'), starting from the current highest ID (105).
This is what I'm using:

Set(Max_ID,Max('My_Data_Source',ID_Field));ForAll( SubCategory As this, 
Patch('My_Data_Source',{
    Category:this.Category,
    SubCategory:this.SubCategory,
    ID: Max_ID+1
    });

The problem with this formula is that it duplicates the new ID for each combination:

			Category
			SubCategory
			ID
		
			CCC
			Sub1
			106
		
			CCC
			Sub2
			106
		
			CCC
			Sub3
			106
		
Any help would be much appreciated.
 
Thanks!

Categories:

Building Power Apps

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Suggested answer

Vish WR 2,253 on at

Like (0)

Report
Copy link

Link copied!

Hi

One way to fix this is to use Sequence():

Set(Max_ID, Max('My_Data_Source', ID_Field));

ForAll(
Sequence(CountRows(SubCategory)),
Patch(
'My_Data_Source',
Defaults('My_Data_Source'),
{
Category: "CCC",
SubCategory: Last(FirstN(SubCategory, Value)).SubCategory,
ID: Max_ID + Value
}
)
)

Sequence(n) generates numbers: 1, 2, 3…
Value acts as your incremental counter
Max_ID + Value ensures IDs increase per row:
106, 107, 108
Last(FirstN(...)) picks the corresponding SubCategory row

ForAll() does not guarantee sequential execution, so relying on variables like Max_ID + 1 inside the loop will always produce duplicates.

Below is my Sample test result. I have created a dummy collection to test and validate

App onStart

// Step 1: Create initial dataset

ClearCollect(

colMyData,

{ Category: "AAA", SubCategory: "Sub1", ID: 100 },

{ Category: "AAA", SubCategory: "Sub2", ID: 101 },

{ Category: "AAA", SubCategory: "Sub3", ID: 102 },

{ Category: "BBB", SubCategory: "Sub1", ID: 103 },

{ Category: "BBB", SubCategory: "Sub2", ID: 104 },

{ Category: "BBB", SubCategory: "Sub3", ID: 105 }

);

// Step 2: SubCategories to insert

ClearCollect(

colSubCategories,

{ SubCategory: "Sub1" },

{ SubCategory: "Sub2" },

{ SubCategory: "Sub3" }

);

// Step 3: Add new Category with incremental IDs

Set(varNewCategory, "CCC");

Set(varMaxID, Max(colMyData, ID));

ForAll(

Sequence(CountRows(colSubCategories)),

Collect(

colMyData,

{

Category: varNewCategory,

SubCategory: Last(FirstN(colSubCategories, Value)).SubCategory,

ID: varMaxID + Value

}

)

);

Add a label control and add the code below in the Text property

Concat(

colMyData,

Category & " | " & SubCategory & " | " & Text(ID),

Char(10)

)

Result

AAA | Sub1 | 100
AAA | Sub2 | 101
AAA | Sub3 | 102
BBB | Sub1 | 103
BBB | Sub2 | 104
BBB | Sub3 | 105
CCC | Sub1 | 106
CCC | Sub2 | 107
CCC | Sub3 | 108

Please ✅ Does this answer your question 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 answering Yes to Was this reply helpful? or give it a Like ♥

Visit My Blog My Tech Space LinkedIn

Was this reply helpful? Yes No
Suggested answer

11manish 1,937 on at

Like (0)

Report
Copy link

Link copied!
The best approach is to let the data source generate IDs, instead of creating them in Microsoft Power Apps.

Recommended Options:

Use SharePoint auto-generated ID (simplest)

Use Auto Number in Microsoft Dataverse (best for enterprise)

Avoid:

Manual ID generation using ForAll, Max(), or counters

These can cause duplicate IDs and issues in multi-user scenarios

Was this reply helpful? Yes No
ragnezza 14 on at

Like (1)

Report
Copy link

Link copied!

Thanks for your reply, this is almost working.

By removing "As this" I'm not able to associate a Value to Category (Category: this.Category returns an error), as this value is not hardcoded but it's coming from a field in another Dataverse table. How can I retrieve that 'Category' field?

Thanks

Was this reply helpful? Yes No
Vish WR 2,253 on at

Like (0)

Report
Copy link

Link copied!

Hi @man_cobb

If I require a unique running number for a sub-category, how can an auto-generated number be generated for SPO and Dataverse?

if no category, yes, auto number is the best and most efficient option

Was this reply helpful? Yes No
Vish WR 2,253 on at

Like (0)

Report
Copy link

Link copied!

Hi Ragnezza

Not sure where and how you retrive your category .

You can try the below option

Category: drpCategory.Selected.Category

or

From a lookup (Dataverse relationship)

Category: LookUp(
CategoryTable,
CategoryID = SomeID
).Category

Example

Set(
varCategory,
LookUp(
CategoryTable,
CategoryID = varCategoryId
).Category
);

Set(varMaxID, Max('My_Data_Source', ID_Field));

ForAll(
Sequence(CountRows(SubCategory)),
Patch(
'My_Data_Source',
Defaults('My_Data_Source'),
{
Category: varCategory,
SubCategory: Last(FirstN(SubCategory, Value)).SubCategory,
ID: varMaxID + Value
}
)
);

Was this reply helpful? Yes No