How to do auto increment through foxpro 9 according to group

[Niki_S]

I have a table like below,
nParaID cParaNo cParaCD cFtyCD cInvNo
218 1 VTMC VTM 230/S/20
218 1 VTMC VTM 230/S/20
218 1 VTMC VTM 230/S/20
218 1 VTMF VTM 45
218 1 VTMF VTM 45

I have a foxpro9 form with cFtyCD and cInvNo. I need to get auto increment number for cParaNo field after I insert cFtyCD and cInvNo.
Now I want it to be like,

nParaID cParaNo cParaCD cFtyCD cInvNo
218 1 VTMC VTM 230/S/20
218 1 VTMC VTM 230/S/20
218 1 VTMC VTM 230/S/20
218 2 VTMC VTM 230/S/20
218 1 VTMF VTM 456
218 1 VTMF VTM 456
218 2 VTMF VTM 456


can someone please help me to do this.

 

[GriffMG]

Your second series makes no sense to me, in that there are duplicate entries.

Please check and come back to us

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

I would have expected something like this:

nParaID cParaNo cParaCD cFtyCD cInvNo
218 1 VTMC VTM 230/S/20
218 2 VTMC VTM 230/S/20
218 3 VTMC VTM 230/S/20
218 4 VTMC VTM 230/S/20
218 1 VTMF VTM 456
218 2 VTMF VTM 456
218 3 VTMF VTM 456

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

I second Griff. And as all nParaIds are 218 this seems to be a foreign key, a detail table with a parent table in itself. So there's something missing to give us a better overvieew.
If you have multiple groups that speaks for a three table solution like this:

main records table
group records table
detail records table.

For example in a bill of materials kind of data these could be
product -1:n-> productmodules -1:n-> modulematerials

It's not necessary to store a sequence number in the modulematerials, which would start at 1 with each module of each product. Whenever you fetch the modulematerials of one productmodule you have the subset of just its materials and could then use the RECNO() of this single module result cursor or view for that. To ensure the materials are loaded in a given sort order you could save the views RECNO() into a view field and define a combination of productmoduleid and sortorder to be unique.

What you can't have is autoinc resetting for each module in modulematerials, so you have to make it a field you take care of by your application logic rather than the database feature of autoincs.

Chriss