Conditional Expression for Field Rule Uniqueness Test

[debwelch]

I want to create a field rule uniqueness test based on three fields in my table. I can do it by typing arrdate+complex+unit in "value of the expression must be unique" box. But ... I want to make this test conditional on the unit field not equal to zero. I cannot find an expression that will make this work. Any ideas.

[MikeC]

Re: Conditional Expression for Field Rule Uniqueness Test

Deb,

Maybe

if(unit<>0, arrdate+complex+unit, ui_msg_box("Attention!","This is not a unique record and will not be saved!"))

[debwelch]

Re: Conditional Expression for Field Rule Uniqueness Test

Mike,

Thanks for the idea. I tried it and Alpha5 considers it an invalid or incomplete expression ... a common Alpha5 message along with incorrect data type. I typed your suggestion correctly and double checked open and close "()".

To better explain my problem, I can accomplish the field rule uniqueness test by creating an expression that concatenates the fields ... arrdate+complex+unit. This works fine. BUT ... I don't want to perform this test if the unit=0. In my application, unit=0 means the unit has not been assigned, therefore the uniqueness test is not wanted. I can have several unit=0s within a complex for a given arrival date. However, if a property has been assigned "unit<>0", the uniqueness test will prevent a double booking.

Here's the logic a want. Test the uniqueness of this expression "arrdate+complex+unit" if the unit<>0. Otherwise, skip the test and let me enter multiple unit 0s for the same arrdate and complex.

Any other ideas?

Deb

[Stan Mathews]

Re: Conditional Expression for Field Rule Uniqueness Test

You can't make the test of unit part of the uniquness validation but you can add a custom validation

unit > 0

[MikeC]

Re: Conditional Expression for Field Rule Uniqueness Test

can't make the test of unit part of the uniquness validation

Thanks Stan! I have been writing my own validations and such so I do not use many field rules (obviously!)...but thinking about it and reading the question over it makes sense to me--analogous to defining a word's meaning and using it as part of the definition!

[debwelch]

Re: Conditional Expression for Field Rule Uniqueness Test

Stan,

Thanks for the suggestions, but how do I create a custom validation for a uniqueness test. What is the function for "is this expression unique"? I've also thought about creating a field event, but I come up with the same question. What is the code Alpha5 creates for a field rule to validate a unique expression?

Deb

[Stan Mathews]

Re: Conditional Expression for Field Rule Uniqueness Test

You don't create "a custom validation for a uniqueness test".

You have your uniqueness test. Now create a custom validation for the unit field so that it can't be zero. Net result is the same?

[debwelch]

Re: Conditional Expression for Field Rule Uniqueness Test

Stan,

Your effort is appreciated, but I'm not communicating my goal. In some instances, I want units to equal zero (to be assigned at a later date). If units equal zero, I don't want to validate a unique expression with the unit as one of the components. Otherwise, I want the unique validation expression to be arrdate+trim(complex)+unit. The unique value validation test and the custom validation tests appear to be mutually exclusive of each other. Therefore, this does not solve my problem.

Thank you for your interest.

Deb

[Stan Mathews]

Re: Conditional Expression for Field Rule Uniqueness Test

I did not understand that the units field could be zero.

You will likely have to code the cansave event for your data entry form and use the KEY_UNIQUE() function to create your own validation. I believe this wouldn't be particularly hard to do.

In pseudo code...

IF units > 0
	'do nothing
ELSE
	IF key_unique("yourindextagname")
		'do nothing
	ELSE
		ui_msg_box("Record","Combination of fields is not unique")
		cancel()
	END IF
END IF

[debwelch]

Re: Conditional Expression for Field Rule Uniqueness Test

Stan,

Thanks for the code suggestion. I'll work with it and let you know the results.

Deb

[Stan Mathews]

Re: Conditional Expression for Field Rule Uniqueness Test

Looks like I was approaching this the hard way.

Create an index for your table with a filter like screenshot "index".

Create a custom validation for the unit field like screenshot "custom validation".

See if that takes care of it.

[debwelch]

Re: Conditional Expression for Field Rule Uniqueness Test

To Stan and all the others who have made suggestions,

I appreciate all your ideas, but so far none have worked and my client will not appreciate the bill for my effort. So I'll try to come up with a user work-around for this duplicate check problem.

I've followed some other threads on uniqueness tests and created an index call Dup_check using the fields that I want to test for uniqueness.

I've tried solving the problem with a field event dialog box (either OnSaveRecord or CanSaveRecord) using this code

If(key_unique("dup_check"),.f.,.t.) as a condition.

With conditional field events, you must invert the truth logic because the conditional expression wants a true result. So I tried to pop up a message box that says "Failed Duplicate Check". Just to be sure, I've tried it both ways and I still get the same results regardless of the truth statement. I get a failed duplicate check even when I shouldn't. By the way, I had simplified the logic and eliminated the unit=0 check, knowing the user could override the warning. But even the single argument does not work. Also, I always get a message 'FR:Rule_System is not an open window whenever I click on the little xy box. Regardless, I type the expression into the box without the expression editor and look at the code.

On a different tact, I've tried custom validation in the field rules and this works better than field events. I've been able to validate one expression or the other but not both. Can anyone see a problem with this logic?

If(val(unit)=0.or.key_unique("dup_check"),.t.,.f.)

The dup_check index logic works, but I could have accomplished that easily with the standard unique value expression field rule. However, when the value of unit equals 0, I still get a false result. If anyone sees a problem with my logic, I'm all eyes.

Deb

[debwelch]

Re: Conditional Expression for Field Rule Uniqueness Test

Well duh!

I finally came up with a field rule uniqueness test that works and it goes back to an earlier suggestion which didn't work because I used unit>0 instead of val(unit)>0. Here is the successul code.

if(val(unit)>0,arrdate+trim(property),.t.)

Is there a forest out there somewhere?

Thanks for all your ideas.

Deb

[MoGrace]

Re: Conditional Expression for Field Rule Uniqueness Test

Hi Deb,
Since your unit field is a character type and not numeric, unit > "0" should also work. Somewhere in here is a tip about setting numeric fields with default values of 0 so that they are not null. Also logical fields should have a default value set too like .f.