Serial Number MAJOR Problems with unique value

[lazyyy]

I'm working on a database which contains a defined unique serial number yearly, but this unique defined serial number can be repeated the year after that.

for example:

year 2006

"SNumber" contains AB100, AB101 and it has to be unique during 2006 data entry.

year 2007

"SNumber" contains AB100, AB101 and it has to be unique as well.

The big question is, how do I defined a validation on field "SNumber" as unique for each year? considering the typing error during data entry, so validation for unique value has to be turned on.

Without the hassle having a separate database for each year?

Thank you all in advanced

[Vaughan]

Do NOT use this number as the primary key for relationships. Use a normallly-incrementing auto-entered serial number for primary keys.

Basically, you cannot validate this number unless you add a year code to it to make it unique.

This is a business prosess problem, not a FMP or database problem. Explain to the client the difficulty that their decision to use non-unique numbers is causing.

[David Jondreau]

Vaughn's right, you do need a unique auto-enter serial number to uniquely identify each record. However, that does not preclude you from creating the serial number you want IN ADDITION to the the unique one.

There's probably a more efficient way of doing this, but here it goes:

Create two new fields. One will be calculation field with a number result and global storage = Year(CurrentDate). The other is the a number field with auto-entry of Year(CurrentDate) (not global). Relate the table to another occurence of itself with the parent side using the global to connect to the other year field.

What you've done is allowed a new record to 'see' all the other records that have been created in the current year. Sort that relationship by date created descending and your auto-enter calc for your serial number will be something along the lines of:

SerialIncrement(childTable::SerialNumber;1)

[lazyyy]

Thanks for all your input,

So i can create an extra unique field for primary with no problem.

Also note that "SNumber" must be inputted manually. Now is there a way to make sure that a field is unique by calculation? let say UNIQUE only for within that particular active year?

I need this considering the typing error during data entry, so validation for unique value has to be turned on.

please help

Edited August 21, 2007 by Guest

[bruceR]

I think you missed the point.

Use an auto-increment primary key. It will be unique period, not unique within the year.

[David Jondreau]

Ah, didn't realize the Serial Number was to be hand entered and you need the validation. In that case, you still need a relationship so that the new record can 'see' all the other records from that year, but not itself.

Create a field with auto enter of Year(CurrentDate) and use that as one of your criteria. Then create a NOT criteria where your unique id (the one everyone's telling you to make) does not equal the unique id of the other Table Occurence.

PatternCount( List(selfJoin::SNumber); SNumber; ) will tell you if the number they've entered matches a number from the current year. Wrap that in a Case() statement with a 0 as the result and I think you're good to go.

This assumes all SNumbers are of the same length (otherwise substrings will produce a hit ie AB10 will register as existing if AB101 exists).

[Vaughan]

You can field-level validate the hand-entered serial number as long aas there is another field that contains the year. Set up another field that auto-enters the contatenation of serial+year and validate this to be unique.

[lazyyy]

"field-level validate"

thats the validate that I need.

so I have 2 fields

SValidate = SNumber+Year

and

SNumber

so after selfjoin SValidate

how do I validate Snumber?

Please help

[Vaughan]

No need for a self-join to validate the serial number.

The database has a SNumber field, this is the hand-entered number. The database also needs a Year field, which has the year entered. (It could be an auto-enter.)

The SValidate field needs to be an auto-enter field. The calc it auto-enters should be:

Let (

[

trigger = SNumber & Year

] ;

SNumber & "|" & Year

)

This forces the auto-enter to update when either the SNumber or Year fields change.

The SValidate field needs to have field-level validation set for unique.

The SNumber and Year fields need to be validated for not empty.

[David Jondreau]

:iagree:

Infinitely more helpful advice here. Ignore the self join bit.

[comment]

The trigger part is not required, since the fields are already referenced in the calculation itself. An auto-entered calculation of:

SNumber & "|" & Year

should be quite sufficient.

[lazyyy]

I tried to create 2 fields

SNumber

SValidate (autoenter calculation + unique validation)= SNumber+Year

The problem with the autoenter calculation is that, when the "SNumber" is modified, "SValidate" unique validation somehow stop working.

is there a way around this?

Thanks all

[comment]

In Auto-Enter options, uncheck the "Do not replace existing value..." option.

[lazyyy]

This Works PERFECTLY!!!

THANK YOU VERY MUCH!!

I love all of you!!!

No need for a self-join to validate the serial number.

The database has a SNumber field, this is the hand-entered number. The database also needs a Year field, which has the year entered. (It could be an auto-enter.)

The SValidate field needs to be an auto-enter field. The calc it auto-enters should be:

Let (

[

trigger = SNumber & Year

] ;

SNumber & "|" & Year

)

This forces the auto-enter to update when either the SNumber or Year fields change.

The SValidate field needs to have field-level validation set for unique.

The SNumber and Year fields need to be validated for not empty.