KEY_EXIST

[Peter Lear]

Hi,
I am having some difficulty in trying to prevent the entry of duplicate records in a database table. I have created a field called "Unique" which contains
Trim(Lastname)+substr(inits,1,1)+pcode - Post Code is the UK equivalent
of US Zipcode.
I have indexed the table on this field.
Within a script on my 'save' button I have tried the following:
tbl = table.current()
indx=tbl.index_primary_put("unique")
if key_exist("tv->unique",trim(tv->name)+substr(tv->inits,1,1)+alltrim(tv->pcode))
dim reply as n
then reply= ui_msg_box("POSSIBLE DUPLICATION", "This Name,Initial and Post Code already exist. Proceed",36)
if reply= 6 then
end if
elseif reply=7
goto end5
end if
oktosave = .t.
end5:
ui_msg_box("DUPLICATION OF RECORD", "Save this entry and check the Database",0)
goto start

It does not work - the message says that the index does not exist. What am I doing wrong?

Rev'd Peter Lear

[Ken L Herrold]

RE: KEY_EXIST

Peter:

this I queston this line...

if key_exist("tv->unique",trim(tv->name)+substr(tv->inits,1,1)+alltrim(tv->pcode))

I don't think the index tag name needs the 'tv->' prefix and should just simply be "unique" The script finds it to 'primary_put' it but cant find it with that prefix.

Give it a shot!

Ken

[Michael Humby]

RE: KEY_EXIST

Peter,

My first check would be
1.You wrote
if key_exist("tv->unique",trim(tv->name)+substr(tv->inits,1,1)+alltrim(tv->pcode))

Strip out the tv->
if key_exist("unique",trim(tv->name)+substr(tv->inits,1,1)+alltrim(tv->pcode))
2.
Why are you using tv-> for the fields if they belong to table.current()

Michael

[Robert Adler]

RE: KEY_EXIST

Rev'd Peter Lear

your examples are:
Trim(Lastname)+substr(inits,1,1)+pcode - Post Code is the UK equivalent
of US Zipcode.
I have indexed the table on this field.
Within a script on my 'save' button I have tried the following:
tbl = table.current()
indx=tbl.index_primary_put("unique")
if key_exist("tv->unique",trim(tv->name)+substr(tv->inits,1,1)+alltrim(tv->pcode))
----
your index uses the full pcode field. when you test for its existence you trim the pcode field. this could be the problem.

bob adler

[Ken L Herrold]

RE: KEY_EXIST

Missed that Bob

[whanigsberg]

RE: KEY_EXIST

Look in the 4.03 readme for a discussion of the key_unique function which is what you should be using. The document makes an explicit comparison of key_exist and key_unique. If you use the former you will encounter problems whenever you need to change a record.

Bill

[Peter Lear]

RE: KEY_EXIST

Most grateful for all comments. Will roll up sleeves and let you know in due course. Thanks Peter Lear

[Peter Lear]

RE: KEY_EXIST

Thanks for the replies.

I have now had a few minutes to play with the script. I cannot get it to run. The table is 'tv' and there is a field called 'unique' which is calculated as Upper(trim(name)+substr(inits,1,1)+alltrim(pcode)) - and I have looked on the browse and that gives the desired result. i have then indexed this field and called the index 'unique'. (I did all this before I knew of KEY_UNIQUE)

When i tried running:
if KEY_UNIQUE("unique") then - etc the message was - 'not an open index'. So I tried using index_get and that didn't work. This is what I find so infuriating about Xbasic. Nowhere can I find an explanation of the syntax that an ordinarily intelligent mortal can grasp.

In pseudo code, all I want to achieve is:

Does the combinatiojn of Name, first initial and postcode exist in the database - then show a message that this may be a duplicate record - BUT I still need to be able to save it - I may well have a Mrs V Smith living 2 doors apart - it has happened.

Help - your frustrated brother

Peter Lear

[Thomas Henkel]

RE: KEY_EXIST

Peter,

Just a Question. Pardon my ignorance. If you are entering the record, does the field yet exist BEFORE saving? If not, on depart from each field, save A_FIELD_VALUE to a variable, then build your "unique" key from the variables.
Use the variables to check against the key. My understanding is that the fields in the tv table don't really exist until they are saved. If I am wrong, please disregard my post.

Tom Henkel

[Peter Lear]

RE: KEY_EXIST

Tom
Thank you, I take your point. in fact I am including the a_field_value in the expression. However, I am having difficulty getting the a_field_value to work.

Peter

[Thomas Henkel]

RE: KEY_EXIST

hi, peter,

I am not sure it is necessary, but are you using capital letters for A_FIELD_VALUE? I remember we had some problems with A_FIELD_VALUE giving us the next or previous field, you may want to try the "can depart" event. I also, for debugging purposes, put a "ui_msg_box" with the value of the variable I assigned from A_FIELD_VALUE on each depart to see what was happening.

Hope some of this helps.


Tom

[Peter Lear]

RE: KEY_EXIST

Tom
I wasn't - I will try. The ui msg box is a good idea, as is the on_depart tip. Thanks - I'll give that a whirl.

Peter Pear

[CALocklin]

RE: KEY_EXIST

Peter, remember that the error messages are not always correct. Some errors don't seem to know what message to send so they just seem to pick the best they can find - which is usually misleading. I think in this case your problem may be that you are trying to 'change' the index while you are still in the "ENTER" mode and the system doesn't like that but isn't giving a clear error message.

Try changing the TBL = TBL.CURRENT() to TBL = TBL.OPEN( "" ).
This will open another copy of the table so that you can set the index and perform your search.

Remember to close the table when you're done.

Cal

[Peter Lear]

RE: KEY_EXIST

Cal

Thanks

Tom Cone is having a look at the dog's breakfast I am making. I will make the final solution known when he has had a moment to look.

Peter Lear

[Tom Cone Jr]

RE: KEY_EXIST

For those of you who have been following this thread, we worked out a solution using the exist() function in a script that is run when the user pushes the
Save Changes button.

The script validates key fields needed in each record;
Then builds a search key by concatenating values from three fields;
Then uses exist() to see if an entry matching the search key is already present in an index.

Two tricks:
1) the index definition must match the search key concatenation sequence exactly; and
2) the search must be done after changes to the record start, but before they're written to the table.

Efforts to use key_unique() in the Save Changes script failed. It seems to have been designed to be used when the focus is on a field in the form, and works great in a field rule. However, when focus shifts away from the 'current' field to a button on the same form, it can't be used to check whether the 'current' value matches an entry in an index list. I have supposed that this is because there is no 'current' value, once focus leaves the field. Makes sense in a way, doesn't it?

-- tom