Unique Calculated Field, revisited.

[Beltronics]

I found some info on this in the Version 6 Forum and got most of my answers.
But:
I created a calculated field in a table. It is a combination of part number and vendor. alltrim(str(vendor_id))+alltrim(pn). Then, I created an index for that field called part_ID. Then, on the form CanSave Event I put this code.

tbl = table.current()
if key_exist("part_id",tbl.part_id) then
cancel()
ui_msg_box("Oops","You already have this PN for this Vendor. Press ESCAPE to Exit")
end if

I have two problems.
The first is that although the "cancel()" command is in the code, the result is that it leaves me in the Enter mode so I had to add the message Press Escape to Exit.

My second problem is that now I can't edit the record because it tells me I already have this combination.

How do I get past those two issues?

[G Gabriel]

Re: Unique Calculated Field, revisited.

tbl = table.current()
if key_exist("part_id",tbl.part_id) then
topparent.cancel()
ui_msg_box("Oops","You already have this PN for this Vendor.")
end if

[Beltronics]

Re: Unique Calculated Field, revisited.

Thanks but, that wasn't much help.
I changed the code, as you suggested.
tbl = table.current()
if key_exist("part_id",tbl.part_id) then
topparent.cancel()
ui_msg_box("Oops","You already have this PN for this Vendor.") end if

Now, I still cannot edit the record for other values in the table. I get the "You already have this PN for this Vendor." message. Also, when I try to enter a new record with the same pn and vendor_id, it get the error message but it saves the record anyway. I'm worse off now than before. Before, if I pressed ESC, I at least aborted the record entry.

[G Gabriel]

Re: Unique Calculated Field, revisited.

disregard.. duplicate entery.

[G Gabriel]

Re: Unique Calculated Field, revisited.

I still cannot edit the record for other values in the table. I get the "You already have this PN for this Vendor.

Isn't this the idea? to prevent usage of the same PN?

If not, you can do:

tbl = table.current()
if key_exist("part_id",tbl.part_id) then
ui_msg_box("Oops","You already have this PN for this Vendor.")
end if

This will produce the message, but allows you to enter any value.

[Beltronics]

Re: Unique Calculated Field, revisited.

The idea is this.
Table has fields:
Part Number
Vendor
Qty in stock
Description
etc.
I don't want the user to be able to enter an identical part number/vendor combination.
When they do, I want to abort the enter process and cancel anything they entered.
BUT, I want them to be able to edit the form and change the Qty in stock of an existing record without getting the error message on save.

[G Gabriel]

Re: Unique Calculated Field, revisited.

Got you.
Put the script on the candepart event of those two fields (part number/vendor combination. ), not on the CanSave of the form

[Beltronics]

Re: Unique Calculated Field, revisited.

You mean
I should put the same script on the candepart event of both fields? That way it doesn't matter which field is entered first?

Problem is, vendor_id is not one of the fields on the form. Vendor Name is but not vendor_id which is used by Alpha but hidden from the user.

Update: I Put that code on PN and on Vendor Name which looks up Vendor_Id. Every time I try to enter a new record, I get the message that I already have that combination. It's probably looking at a blank pn and a blank vendor_ID??

[G Gabriel]

Re: Unique Calculated Field, revisited.

It's probably looking at a blank pn and a blank vendor_ID??

Change your index to say:
blank pn<>"".and.vendor_ID<>""

[Beltronics]

Re: Unique Calculated Field, revisited.

I'm not sure what you are telling me to do??

[G Gabriel]

Re: Unique Calculated Field, revisited.

Your expression:
key_exist("part_id",tbl.part_id)
uses an index "part_id".
Change that index's filter to say:
blank pn<>"".and.vendor_ID<>"" (or if you already have a filter, add these two elements to it), that way when you enter a new records and these fields are blank, the result of the expression will be false (does not exist).

[Beltronics]

Re: Unique Calculated Field, revisited.

When I do that, as soon as I enter that in the filter slot, I get a message, Argument is incorrect data type.

Let's take a different approach. If I change the Part_id from a calculated field to a user entry field, make it a required field, make it a unique field and set the default value at end of data entry if not touched by user. Default value to be
alltrim(str(vendor_id))+alltrim(pn)

Then, I go to my Vendor field (not vendor_id) in the form and define an OnDepart event which sets the value of vendor_id using action script. (PN has already been entered by this time). As soon as I do that, I get an error message OnDepart of Vendor field. So, that seems to work except I would like to change the error message to mean something the user can recognize instead of an Alpha5 Script error. I had Alpha change the action script to inline basic to see if I could change the message but the result was something I could not make heads or tails of and the error message is not defined there.

Might this be a better approach for me? I just am getting nowhere with the calculated field approach.

[G Gabriel]

Re: Unique Calculated Field, revisited.

When I do that, as soon as I enter that in the filter slot, I get a message, Argument is incorrect data type.

isnotblank("pn").and.isnotblank("vendor_ID")

[Tim Kiebert]

Re: Unique Calculated Field, revisited.

Jim,

From the help on key_exist()

Note : If you want to define a Validation Field Rule to prevent the entry of a duplicate record, use KEY_UNIQUE(), not KEY_EXIST().

[MikeC]

Re: Unique Calculated Field, revisited.

In response to your other question mentioned, whenever a field rule is used and an error is produced, the error message will be an Alpha one. The new "Branding Tool" Alpha sells may change the "Alpha" name on the message but am unsure if you can change the message with it. Otherwise if you wanted your own error message to appear you will have to use other events such as a form's CanSave event and write your own error trap there.