Calculated Field Uniqueness Oh My!

[Chris A]

Well day two searching through Calculated Fields and check for Uniqueness and have failed to find what I need or contact a few of the people in that thread.

I have three fields "Code", "TS_Number" and "Job_number" that I combine into one field called "All3". In field rules under "Field Types" "All3" is setup to be a calculated field and I have an expression that combines the above three fields.

Up to here everything works fine.

What I want to do is test "All3" for Uniqueness. The Validation field rule does not work when the field type is a calculated field. In some of the previous threads people talked as if the Validation field rules still worked. Others talked about putting unique validations on the individual fields. Yet others talk about indexes and events.

I think that the indexes and events thing might work, but have failed to get enough detail to reproduce what I need. I have the index built (Not sure if it is correct) and had a "exist(All3,"jobs_test","Dup_Number")"function in the events under the "OnSaveRecord" again not sure if I did it right as A6 gives me an error stating that "Variable "All3" not found".

Attached is a very simple striped down version that I have been trying to get to work so I can add it to another database.

[Stan Mathews]

What I want to do is test "All3" for Uniqueness. The Validation field rule does not work when the field type is a calculated field.

- you don't need to test "All3" for uniqueness. You can use the

"Value of expression must be unique" - Use this setting when you want to build an expression to determine if a value is unique. The field for which this rule is defined is usually included in the Unique expression. The value that results from this expression must be unique for each record in the table; if not, the record cannot be saved.

using the same expression that calculates the contents of the "All3" field.

I think. Will experiment with your sample.

Later: My research with the "Value of expression..." seems to work only when the field being validated is not itself a lookup field. You may want to pursue other avenues.

[fddizon]

maybe you can calculate your combined field through form level. make your field user entered at fieldrules and and from there you can set your field rule for uniqueness test.

i hope this might help you out...

Francis

Well day two searching through Calculated Fields and check for Uniqueness and have failed to find what I need or contact a few of the people in that thread.

I have three fields "Code", "TS_Number" and "Job_number" that I combine into one field called "All3". In field rules under "Field Types" "All3" is setup to be a calculated field and I have an expression that combines the above three fields.

Up to here everything works fine.

What I want to do is test "All3" for Uniqueness. The Validation field rule does not work when the field type is a calculated field. In some of the previous threads people talked as if the Validation field rules still worked. Others talked about putting unique validations on the individual fields. Yet others talk about indexes and events.

I think that the indexes and events thing might work, but have failed to get enough detail to reproduce what I need. I have the index built (Not sure if it is correct) and had a "exist(All3,"jobs_test","Dup_Number")"function in the events under the "OnSaveRecord" again not sure if I did it right as A6 gives me an error stating that "Variable "All3" not found".

Attached is a very simple striped down version that I have been trying to get to work so I can add it to another database.

[Tom Cone Jr]

Chris,

Here's one approach.

1) change your index tag definition for the All3 field to include ALL records

2) delete your OnSaveRecord field rule event script

3) use this script in the CanSaveRecord field rule event:

tbl = table.current()
if key_exist("dup_number",tbl.all3) then
   cancel()
   ui_msg_box("Oops","Duplicate key")
end if

-- tom

[Chris A]

Tom could you explain a little more on your #1? I am not sure if I know how to include ALL records.

I understand 2 and 3 they are straight forward.

[Tom Cone Jr]

Chris,

"All3" is an index tag you have defined for the table. If you inspect your definition (in the index builder) you will see that you told Alpha Five to include only unique keys in this index list. I'm recommending you change this definition to include all keys in the index list. Change the definition and the rebuild the index.

-- tom

[Chris A]

How simple, I should have seen that myself. Thank You very much!
This application works just the way I hoped and wanted.

[Chris A]

New Curve ball. I have created a po_header and po_item linked together by a po_set. With this I have also created a job_set linking the po_item to job_test. Po_item also has "all3" in it. Now the event that checks for duplicates is picking up the "all3" in the po_item and not allowing changes. Is there a way to not check for duplicates in the po_item but only the job_test tables? Can the following be modified?

tbl = table.current()
if key_exist("dup_number",tbl.all3) then
cancel()
ui_msg_box("Oops","Duplicate key")
end if

[Chris A]

Tom could you take one more look at this. All the items that you gave me work fine. Now I have added another table with the same information and it thinks there is a duplicate when there really isn't. Thanks

[Beltronics]

Re: Calculated Field Uniqueness Oh My!

Chris

How did you ever make out with your unique calculated field? I found this thread while trying to do the same thing. I want to provide a unique calculated field which is a combination of vendor_id and PN. I set up a calculated field in the "parts" table called "part_id" defined as alltrim(str(vendor_id))+alltrim(pn) and updated the table. Now, I need to catch any entries into the parts table that are duplicates of part_id.

do I put code on the CanSaveEvent as follows?

tbl = table.current()
if key_exist("dup_number",tbl.part_id) then
cancel()
ui_msg_box("Oops","Duplicate part")
end if

[Beltronics]

Re: Calculated Field Uniqueness Oh My!

Answered my own question. I tried the code and got an error because I had not defined an index for part_id. When I did, it seems to work fine. Thanks Chris and Tom too.

OOPS: I spoke too soon. The CanSave script works well in identifying that I have a duplicate but it leaves the form in the ENTER mode instead of cancelling entries and bringing me back to the original VIEW mode. If I try to go to another record or do anything, it keeps telling me I have a duplicate. I have to press the Escape key and answer the question "Do you want to lose your changes" in order to get out. When I click OK on the ui_msg can I automatically cancel changes and get out of the ENTER mode?

[Beltronics]

Re: Calculated Field Uniqueness Oh My!

This isn't working. Now, I can't edit a record. It says I already have this unique field. Duh, of course I have it but I want to edit it.