Jump to content

Validate that a calculated field is unique


This topic is 5672 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 5672 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.