Increment a col integer against a related row value

[JamesWB]

Hi, I have a table Hazard that I want to create an increment row value in, using the formula (MAX(Hazard[Hazard Number]) + 1).

Hazard rows are Keyed to the table Machine. I want to have these integer increases tied to the specific machine in Hazard.

I'm a bit puzzled about which function to use. I basically want to select rows tied to a particular machine and increment the value in the Hazard Number field each time a new hazard is added, but each machine ID has its own list of hazards.

Will much appreciate any help.

thanks

James

[TeeSee1]

Assuming below, I believe this is what you wan to do

Hazard  [table]

hazard id: key

machine id: ref to machine id in table Machine

Machine [table]

machine id: key

related Hazard: automatically created reverse reference

hazard Number: a Virtual Col, expression COUNT([related Hazards])

[JamesWB]

Thanks for the reply.

Yes, I want to pull out rows from the Hazard table keyed to the MachineID column and then count those rows, then add 1.

I have a Hazard table with the key column HazardID and the Ref column Machine.

I want to count the rows against a specific Machine when adding a new Hazard and then increment by 1 into a virtual "Hazard Count" column.

I realise I need Count(some value) but I'm not sure how to only pull out the records relating to the current Machine selection.

Thanks.

[TeeSee1]

Not sure what you want..

You want Hazard Count in a Machine view or Hazard View?

I assumed you'd want it in a Machine view, in which case, the number of related hazards to the machine row you select shows exactly those hazards related to the row's machine ID.

Count is automatically incremented as soon as you add a new hazard.

Am I missing something??

[JamesWB]

When I'm on the Add Hazard form, I want to have a counter that counts how many Hazard rows already exist for the selected Machine and then increment it to generate the Hazard Number.

Hazards belong to Machines, they are 1 to many where 1 Machine has many Hazards.

So the formula I need must be something like (in logic):

When adding a new Hazard row to the Hazard table against a related Machine >> Count the number of rows already in Hazard, where Machine IDs are the same as the currently selected Machine >> Increment by 1 >> Load the resultant value in Hazard Number field in the Hazard table.

Here is my Hazard table:

[TeeSee1]

I believe this is what you want.

Then,

For the VC in the Hazards: COUNT(FILTER("Hazards", [machine id] = [_THISROW].[machine id]

For the VC in the Machines: COUNT([Related Hazards])

[JamesWB]

OK thanks, I'm getting closer!

My code attempt is now:

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

but this gives me the problem that editing existing ones gives them the incorrect number. How do I test if it's a new one or an existing one and only add 1 for the new ones please?

thanks for your help

James

EDIT

Oh dear, this is obviously more complicated than I realised, if there are no records, it loads the Counter as zero from this formula, but I need the first one to be 1 and you can't apparently put an initial value in a formula-driven virtual column.

There must be an elegant solution to this. Basically I need a counter that starts at 1 and then each time a new record is added, makes them 2,3,4, etc. I can see that potentially another issue is what happens if one gets deleted. The initial numbering sequence needs to stay in place if that happens.

It seems like this is something that is actually pretty complex.

EDIT

I'm also hitting the problem that it doesn't write these out on a template with each value, it just stores them all with the most recent Count value.