Using previous primary key

[SteveGriff]

Hi,

I have a field called Order_ID that is a primary key, using auto increment. (i.e - serial number, next value, increment by blah blah).

But instead, for added flexability, I want to use lookup field, the Max(Order_ID) + 1, but that causes an circulatory error.

Is there any way to get around this?

Thanxs,

Steve Griff

[Ugo DI LUCA]

Hi Steve,

I had a discussion some time ago with a nice guy that uses this method. Not sure what kind of advantages you have with this. As a matter of fact, I posted a sample file that used this Max +1 in the sample section.

Look at multiple line items.

But this was just a workaround for the test file, sure not a recommandation, and you still would need a Unique_ID attached if you want to secure your files (imports/exports can pul the serials up...)

Hope this can help.

[Mike D.]

Steve,

If you use a self relationship you can certainly do this. Create a calc field, Constant = 1 and base a self relationship on it. Now you can have Max(Self_Constant::Order_ID)+1. I have also used this technique when the ID number is based on a year, such as 2003-001, 2003-002, etc. Then the relationship is based on the Year.

HTH, Mike

[BobWeaver]

There is one danger when using this method. If you are in a multi-user situation. It's possible for two users to create a new record at the same time and end up with the same primary key value. Because it's *possible* for it to happen, eventually it *will* happen, likely more often that you expect. I recommend that you either stay with the autoentered serial number, or a captkurt style unique ID.

Having said this, the reason for the circular relationship is because you are referencing the field in it's own formula. The simplest way around this is to create a second calculated field cMaxID with the formula:

Max(SelfJoin::PrimaryKey)

Then the PrimaryKey autoenter formula will be: GetField("cMaxID")+1