In my personnel database I have an element, FullName, which contains the combined strings of FirstName and Lastname.  I want this element to be not-gray read-only (it is filled by programming).  Of course, I also want it to be unique, so that both Stef and Michelle don't try to add the same new hire when he comes on board.

"Unique" is one of the selections of the Read-Only property selector.  However, it renders the element writable.  If I then use programming to make it read-only, i.e. ReadOnly(Fullname,2), I then no longer get warnings of non-unique values.

Is there some way these two attributes can live together in harmony?

I see what you are saying, but I'm not sure how to go about checking uniqueness in code.

Whoa.  Now that I'm going to have to study.

I'll give it a shot tomorrow.  Thanks for your help today, much appreciated as always.

2.0 also has @IsUnique()

-Ray

OK, I'm working with @IsUnique.  Here's the code I'm trying.  It's not working and I can't see why not:

var vFirstLetter as string
var vFullName as string

// Capitalizes the first letter of the First Name

vFirstLetter = @Left(FirstName, 1)
vFirstLetter = ToUpper(vFirstLetter)
FirstName = @REPLFIR(FirstName, vFirstLetter, vFirstLetter)


// Creates vFullName from both name parts
IF NOT FullName = (FirstName + " " + LastName) then
	{
	vFullName = (FirstName + " " + LastName)
	}
// Checks to see if it's unique
If @IsUnique(FullName, vFullName) = 0
THEN
	{
	@MsgBox("This name is not unique.", "It already exists in the database.", "Please change the name or discard this record.")
	Clear(FullName)
	ThrowFocus(LastName)
	}
ELSE
// Sets the Full Name
	{
	FullName = vFullName
	} 

This sets FullName even if it already exists.  What have I done wrong?

Taking out the throwfocus and the Clear (which is not needed anyway) get the acknowledgment down to one click.

But I do think the values in the book are switched.  A return value of 1 is indicating a non-unique value, though the book says it should be 0.  Unless I've done something that is causing the switch.  In any event, this code works:

var vFirstLetter as string
var vFullName as string

// Capitalizes the first letter of the First Name

vFirstLetter = @Left(FirstName, 1)
vFirstLetter = ToUpper(vFirstLetter)
FirstName = @REPLFIR(FirstName, vFirstLetter, vFirstLetter)


// Creates vFullName from both name parts
IF NOT FullName = (FirstName + " " + LastName) then
	{
	vFullName = (FirstName + " " + LastName)
	}
// Checks to see if it's unique
If @IsUnique(FullName, vFullName) = 1
THEN
	{
	@MsgBox("This name is not unique.", "It already exists in the database.", "Please change the name or discard this record.")
	}
ELSE
// Sets the Full Name
	{
	FullName = vFullName
	} 

I cannot get @IsUnique to fail.  That is, I am unable to meet the condition 0.  I've tried using both the variable and using a "specific string".  I've put the test into an element (LastName) that I know contains the string "Miara" (several extant records contain this) and even specifying "Miara" as the string to test in that element, as so

If @IsUnique(LastName, "Miara") = 0 

does not find it to be non-unique.

On the flipside, testing for =1 works every time, unique or not.

I'm baffled.  Time for a fresh cup of coffee, maybe take a look at some of the Maxim pictures of Sarah Silverman, or maybe I'll balance the checkbook.  Nah, Silverman it is.

Yes, when I did a straightforward comparison to an existing value.

I then copied some code into customers.db and did some tests.  It appears that the combination
(Firstname + " " + Lastname) is introducing a problem.  Perhaps it's the quotes?  This is what I did:

on-change of First element

var vFullName as string


// Creates vFullName from both name parts
IF NOT City = (First + "r" + Last) then
	{
	vFullName = (First + "r" + Last)
	}
// Checks to see if it's unique
If @IsUnique(City, vFullName) = 0
THEN
	{
	@MsgBox("This name is not unique.", "It already exists in the database.", "Please change the name or discard this record.")
	}
ELSE
// Sets the Full Name
	{
	City = vFullName
	} 

I then entered PA into First and IS into Last (PARIS is an existing value in the city field of a record).  No error message, and City filled with "PAr".  Deleting PA from First, no error message, and City filled with "rIS"

It's stumbling over the concatenation.

Hello Scott,

Do you have more than one form in the application you are working in?

-Ray

I'm officially giving up on this.  Thanks to everyone who pitched in to help, Erika, Ray.  I'm going to set it unique but writeable in properties and move on to some other project.  Thanks again.

Yeah, OK, thanks Bob, that sounds good, I'll try that.

I did try one other thing after lunch: I made a completely new application with one database and one form.  Three elements: Firstname, Lastname, Fullname.  Programmed Lastname to concatenate on-change and test for uniqueness.  Went into preview, added 1 record.  Advanced, added the same data...and the test for uniqueness worked and I got the error message. 

When I use the exact same programming in my Personnel database, which also has just 1 form and the three same elements, it fails.  So clearly I've got some gremlins in Personnel.  I've Writelined the values of all the elements and the variable and they're exactly what I expect them to be.  Is there a way to Writeln the integer that is returned from @IsUnique?

But anyway, I've wasted most of the day on this and I'm very frustrated, and since it's really not vital in the long run because uniqueness can be set in properties, I think I'll move on.  Thanks again to everyone who tried to help, your support is much appreciated.

Thanks Bob, I'll try that, see what it shows me.

Here's something else I'd like to try.  When FullName changes, I'd like to do a lookup into the database using its value as a key, into the same field.  Is that possible, or would it be whatchacall "recursive"?  If I create a variable I could use @Xlookup.  I'm thinking of something like this:

on-change event of FullName

var vFullTest as string

NotifyForm(0) // Initialize save state

If @IsNew then
vFullTest = @XLookup(@FN, FullName, "Employee!FullName", "FullName")
	IF @error then
		{
		Writeln("Lookup Failed")
		}
	ELSE  // I'll cross this bridge when I come to it; so far every lookup has failed