Lookup validation to allow exceptions

[Marilyn Wallace]

I want to be sure that new entries to a table that contains persons names are not duplicates. Sometimes there are legitimately two people with the same name. If it is the same name but is a legitimate entry I want to be able to allow the entry.

It was easy to do in Alpha 4 Field Rules - I could select the lookup validation "doesn't exist" and allow exceptions="yes".

I haven't found a way to do that in Alpha 5 v6.

Thank you for your solutions.

[Bill Warner]

Here's one way to do it:

In field rules under Validations, Uniqueness Test, you could select "Value of expression must be unique", and then you could make the expression evaluate to the name field and one other field that would be different for the two identical names (i.e., address).

[Marilyn Wallace]

Want to accept/reject after notification

Thanks for your thoughts, Bill, but that won't do what I need. Perhaps I need to explain further what I am trying to do.

The Alpha 4 set-up was perfect. The first field we entered in a new record was the last name. It was set up (Lookup validation=Does not exist) to give us a message if the entry was not unique. (something like "Entry is not unique") This alerted the person entering the info to check the table to see if the person was entered previously. The person may have moved and the old record may show a different city, different phone, etc., so even though other fields may be different it still may be the same person. If, after looking at the other records we feel it is a new person we want the new record to be accepted. (Allow exceptions=yes)

So the key elements are: Alert us if the last name is not unique. Allow duplicate last names if we want the new record to be accepted.

It was built into Alpha 4 and I was surprised it was not in Alpha 5 - or did I overlook something obvious?

[Bill Warner]

OK, I understand better what you want. Unfortunately, this is one of several neat features that A4 had that A5 doesn't.

However, it can still be done (I've done it for several customers). If you are familiar with Xbasic, you can put the following script in the CanWriteField event of the LASTNAME field in field rules:

ln = a_field_value

if exist(ln,"table_name","index_name")
ui_msg_box("DUPLICATE NAME","This name is a duplicate.",48)
end if

In this script, the variable ln captures the value you have entered in the field. Then the script looks in the table TABLE_NAME, using the index INDEX_NAME (indexed on the last name field), to see if the value of ln matches any records. If it does, a message box appears. Once the user clears the box, he can leave the value, or change it. In other words, it warns about a duplicate, but doesn't prevent it.

[Marilyn Wallace]

Thank you!

Thanks for pointing me in the right direction. It was apparent that your solution made sense, but I couldn't get it to work at first.

I finally got it to run by changing the second line to read:

if exist(ln.value,"table_name","index_name") Note: Added ".value"

So for my table it reads:
if exist(ln.value,"p_basic.dbf","Lastname")

Perhaps Alpha will consider building this capability into a future release. Evidently a number of us miss it!

Thanks again, Bill!!