Need Help Making A Record Unique

[Randy Hyland]

I have three fields in a file: "Part Number", "Revision", and "Lot Size". The first two are character and the third ("Lot Size") is a numeric. What is the best way to insure that upon entry of a new record or changing an existing record that the combination of all three fields are unique (in other words if the same combination already exists in another record do not allow the enrty)? I would like to see a message when leaving any one of the three fields that tells of the duplication.

I tried custom validation on "Part Number + Revision + str(Lot Size)" This works for the first two fields but does not work for the "Lot Size" field. The error message is "Validation Expression Does not Evaluate to a Numeric Value". I guess that this is because the field is numeric and Alpha wants the expression to evaluate to the same data type. I thought of using a character field but this is a pain because I have to use "Val(Lot Size)" every time I want the value. Is there an easy way to do this? I would appreciate it if somebody could shed some light on this problem.

Thanks,

Randy Hyland

[forskare]

RE: Need Help Making A Record Unique

That's because you're tryiing to mix apples and oranges. You will need to convert the numeric field to a character field. BTW, why is the Lot Size field numeric? Fields should only be numeric IF they are doing math calculations. Lot Size doesn't seem to me that it's a mathematical equation.

kenn

[Randy Hyland]

RE: Need Help Making A Record Unique

Ken,

Thanks for the reply. I will be doing calculations on lot size when I release work orders against the quote.

I was also having some trouble with the custom validation because the part number, revision, and lot size are in a parent table which is linked to a table called operations which stores all the steps necessary to manufacture the item. The problem is that each time I add a child record to the operations table, It tells me that the record is not unique. I am assuming that this is because it is trying to save a composite record in which that same part number, revision and lot size already exists for a previous operation. If you think of the parent file as a header and the child as a line file, I only want the system to prevent dups for the entry in the parent table not also every time I add a new child record.

To futher explain, It is as if I have an order header file and an order line file. I want to not be able to dup the order number in the order header file but the order number can be repeated many times in the order line file, with different line numbers (or in my case operation numbers). What seems to be complicating things is that I am trying to key off of the combination of three fields (part number+revision+lot size) instead of only one field. I do not think Alpha likes that approach, I am not sure, or I just don't know the right way to handle it.

[forskare]

RE: Need Help Making A Record Unique

I just don't know the right way to handle it.

Could be. Have you examined the AlphaSports database that ships with A5? I susect it is set up real close if not exactly for what you want. There are numerous invoices for each account number plus numerous line items for each invoice number. All unique fields are one field, not a combination.

You also might get other ideas on things to try.

kenn

[Selwyn Rabins]

RE: Need Help Making A Record Unique

create an index on the expression:

"Part Number + Revision + str(Lot Size)"

then, in the table's "CanSaveRecord" event, put

if exists("Part Number + Revision + str(Lot Size)" ,yourtablename, yourindexname) = .t. then
ui_msg_box("Error","Data not unique")
cancel()
end if

[Peter.Wayne]

RE: Need Help Making A Record Unique

at the risk of being presumptuous, i believe selwyn has made a small mistake. you should put the cancel()before the ui_msg_box() informing the user of the error, otherwise in some cases A5 gets confused about which window (your form or the msg box) should receive the effect of the cancel(). so the script should be

if exists("Part Number + Revision + str(Lot Size)" ,yourtablename, yourindexname) = .t. then
cancel()
ui_msg_box("Error","Data not unique")
end if

[JohnZaleski]

RE: Need Help Making A Record Unique

Peter,
Thanks for that little tidbit. Answers a few old unexplainable situations.
John

[russ Boehle]

RE: Need Help Making A Record Unique

If you will ever need to do a look up based on your 3 criteria, you also might find it helpful the define the index as:

UT(Part Number)+" "+UT(Revision)+" "+UT(str(Lot Size))

The reason for this is that if you do a lookup on:
Part Number + Revision + str(Lot Size) Alpha won't be able to tell where one field ends and the next begins. With the upper-trim you are trimming blank spaces from the fields and adding 1 space between them so if you do a lookup you can type in something like:
12345 45 12 for your three parameters and Alpha will find the correct record.


Russ