Validate that a calculated field is unique

[djlane]

Another user previously asked this question but I did not quite understand the answer given by T-Square : "create a self-join on the calculated field, and use validation to test whether there are entries on the other end of the relationship.

"

What I wish to do is prevent a user from putting the same item number twice (ie on two line items) during order entry. I have created a combined field of order_number&Item_number... but how do I use it to stop the user entering the duplicate??

thanks

[aaa]

Hi, djlane!

Here is attachment how to do.

Unique.zip

[Søren Dyhr]

I'm not sure it's the correct answer, to the question ...what you do aaa could be done with checking the Unique checkbox in the validation, there's no particular need for the counting field!

I would say that T-Squares recommandation, is a little iffy - what if the calc'field is unstored? A lot of things could find their way into a calc'field making storage and indexing unpredictable, say Get( functions or design-functions - and there goes your foreing key.

What we need to know here which and why calc's results needs to be unique??

--sd

[djlane]

Hi aaa,

Thank you, that works very nicely.

One question, why is the validation formula < 1 instead of < 2 ?

I can see that it works but I don't see why. I want to allow at least one but not more than one occurance. I would have thought that that would be < 2 instead of < 1 ??

thanks

david

[djlane]

Hi Soren,

There is no unique tickbox for a calculation field, that's the problem.

The item number cannot be unique as it will be common to many orders. Its the combination of item_number&order_number that needs to be unique, hence the need to validate a calculation field.

thanks

david

[aaa]

Hi aaa,

Thank you, that works very nicely.

One question, why is the validation formula < 1 instead of < 2 ?

I can see that it works but I don't see why. I want to allow at least one but not more than one occurance. I would have thought that that would be < 2 instead of < 1 ??

thanks

david

When i did it for me i was surprised too.

I litle thinked about it, but can't answer.

Then i decided: "Works? Let it work." :

I will think about it once more.

[Søren Dyhr]

why is the validation formula < 1 instead of < 2 ?

Since the present record is committed, or in fm6 terms attempted left is it already among the one's the key will get.

There is no unique tickbox for a calculation field, that's the problem.

Not true, ID's are bound to be stored and for this purpose is autoenter calc'/text fields just fitting the bill. However is calculated ID's unless extremely crafty measures are put into the formula, less than ideal in networked solutions. Since two concurrent entries are likely get the same ID despite such measures you have thought about, use the intended approach - serial numbers!

--sd

[aaa]

It seems that validation works before refreshing Relationship. And this is the reason why <2.

I triesd =0 works correct too, but =1 is not correct.

[djlane]

Soren,

Thanks for the explanation re the validation formula.

The order number is a unique serial number. Two concurrent users cannot generate the same serial number, therefore the combination of order_number&item_number cannot be generated by two concurrent users.

dl

[Søren Dyhr]

Just a quick question.. doesn't it occure that the same item_number occures twice firstly as standalone and perhaps the second time as part of a kit/set?

The order number is a unique serial number. Two concurrent users cannot generate the same serial number, therefore the combination of order_number&item_number cannot be generated by two concurrent users.

So the validation is to ensure that the some item doesn't occure twice in the invoice... in this case is both fields stored so the selfjoin will work, but you cause yet another index to build.

I wonder why it's important, the portal as such sucks for printout anyway, so you could choose to make the invoice a sub-summary report!

--sd

[djlane]

Soren,

No there are no kits or sets. Only individual items.

The reason the order_number&item_number need to be unique is that they are used in a relationship to another file.

dl