auto increment

[don parkman]

I have a purchase order table. The first field is the purchase order number and the 2nd field is the item number of the purchase order. I need to set the item number field rule to auto increment starting with 1 each time the purchase order number changes. (I guess generally auto increment is at the table level but I need to auto increment at the record level)

[Selwyn Rabins]

Re: auto increment

I have a purchase order table. The first field is the purchase order number and the 2nd field is the item number of the purchase order. I need to set the item number field rule to auto increment starting with 1 each time the purchase order number changes. (I guess generally auto increment is at the table level but I need to auto increment at the record level)

you want records like this:

purchaseOrderNumber    lineItemNumber
1                                 1
1                                 2
1                                 3
2                                 1

this can't be done using a built in autoincrement field. you would have to write xbasic to do this.

an autoincrement field each record has a higher value that the previous one.

[don parkman]

Re: auto increment

I understand. (Looking up the definition of monotonic on the Web helped) My production manager and I have been able to develope a darn good indented bill of material inventory system by using just the control panel and action scripts without learning xbasic. But, I guess there no better time than now to start learning.

Thanks Selwyn.

[Tom Cone Jr]

Re: auto increment

Don, you may be able to achieve this using a calc field rule for the lineItemNumber field. Perhaps the dbmax() can give you the highest lineitemnumber for a given purhcaseordernumber. You could increment that value to get the next lineItemNumber when entering a new item.

If you put the purchaseorders in one table and the items in a second you could use a two table set. The auto inc field rule could assign the next purchaserodernumber to the parent table record. Then each line item record (in the child table) could use a calc field rule to compute the line number.

I think by combining orders and items in the same record you create redundancies in your data, and make implementing your desired pattern of numbering more difficult.

[don parkman]

Re: auto increment

Thanks Tom. I will try dbmax() in the morning. That looks like a good starting point. I will take your counsel on splitting the data into 2 tables the next time I build a similiar db but my current table setup is working very well except for having to manually input line item numbers and I need to finish out some other projects

[barry685]

Re: auto increment

Add a Last_line_Num field to the order header.
When adding line item records update the line item record with Last_line_num +1
on the condition it's current value is 0 (only when it is being created)
And update the order header With the same calculation.
This can be doe with AS

[don parkman]

Re: auto increment

Barry - Sorry, but I have just now been able to get back to this problem. I can't completely follow your solution. Can you look at attachment and tell me where I am going wrong. (BTW, you have a great looking Web site)