Incrementing counter

[JamesWB]

I've been scratching my head trying to solve what sounds simple - having a visible counter field that increments by 1 each time a new record is added.

(table is Hazard, counting column is Counter (represented by thisrow here) and Edit_count is a ChangeCounter column.

I tried a simple expression:

(COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine]))) + 1

but this doesn't work when the column is edited, it continues to increment with each edit.

So I added a ChangeCounter column called Edit_count and tried a formula like this:

IFS(

[_THISROW].[Edit_count] <1 ,COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 1,
[_THISROW].[Edit_count] >=1 , COUNT(FILTER("Hazard", [Machine] = [_THISROW].[Machine])) + 0

)

Which also doesn't work - it sets the Counter value to zero on the first record. Bizarre because it should be 1 by all logic. Other variations result in the Counter field disappearing from the form/detail views.

When the code does work and a value is assigned to Counter, it typically resets when Saved to Zero.

I think the problem must be that there is some need for a start read and then an end value reset when Saving? I don't know how to do this.

Or is there a better solution altogether for this problem?

[SkrOYC]

Serial Numbers, If You Must - Google Cloud Community

[JamesWB]

Looks scary! I'll try it.

[JamesWB]

Looking at that, it probably doesn't help with my problem. I need to not increment it when there's only an edit to an existing record and increment it when a new record is saved. I'll try it, but looking at the way that works, I suspect it will still increment on an edit.

[SkrOYC]

No Initial Value gets executed when there is an edit...

[JamesWB]

Ah OK, the penny drops. Thanks.

[Suvrutt_Gurjar]

Could you open your case a little more?

Is the understanding correct that you are trying to count the records for each machine in the Hazards table.

However this counter is in another table and these two tables are not reference to each other?.

Is this understanding correct?

If so, you could make the Hazard table as child table of the other table where you have the counter.

The ref column expression in the Hazard table can be SELECT( The Parent Table[Key Column], [Machine]=[_THISROW].[Machine]) 

This will ensure you always create a common reference to all the Hazard table records being added for the same machine.

Then your counter column expression in the parent table can be COUNT([Related Hazards]) where [Related Hazards] is the reverse reference column in the parent table. It will be a VC.