Field validations

[Ed Barley]

Hello Everyone,

There is a field in my main table, that is named "Vin_Number". It needs to be unique(no duplicates).

The Index on the table is named "look_dupes" and is ALLTRIM(JOB_NUMBER)+ALLTRIM(BOOKING_NO)+VIN_NUMBER

The custom validation in field rules for the field "Vin_Number" is Key_unique("look_dupes")

Also is field rules for the field the uniqueness test is also ALLTRIM(JOB_NUMBER)+ALLTRIM(BOOKING_NO)+VIN_NUMBER.

The field Vin_number is not required in field rules. It can be blank if there is no vin number.

The application reacts as though when this condition is met, and the field Vin_Number has two blank entries, my custom validation message associated with the key_unique pops up and will not allow the operator to go past the Vin_Number field(even though the vin number is not required).

My question is, how do you make the field not except duplicate vin numbers, but at the same time if the field is blank, not trigger the no dupes???? I think that is what is happening.

Any help would be appreciated.

Thanks

Ed

[Rawlings]

RE: Field validations

Hi Barley,
In addition to your current validation expression include this other expression to it: .AND.ISNOTBLANK("VIN_NUMBER")
This should solve your problem. Good luck.

RAWLINGS

[Ed Barley]

RE: Field validations

Hi Rawlings,

I understand what you have given me, and will give it a try. Makes sence.

Thanks Ed

[Ed Barley]

RE: Field validations

Hello,

The only way I could get the code eidtor to accept the expression, was this way:

isnotblank("Vin_number")+alltrim(Job_Number)+Vin_Number

It seems(??) to work when making a new record. When you hit enter on the field "vin_number" with a dupe the error message pops up like it should. If the space is blank, the record is accepted.

What is happening, and it was probably happening before, and I never noticed it, is that when a input operator does a change record, and the "vin_number" field is blank, I get the error message for the key_Unique expression.

So still stumped as to how to prevent this from happening???

Thanks

Ed

[Edward Larrabee]

RE: Field validations

Using ALLTRIM() and similar functions in an index expression can cause problems for some applications. For example ALLTRIM("ABC ") and ALLTRIM("ABCD ") result in keys of different lengths.

[Ed Barley]

RE: Field validations

Hi Edward,

I will test what you have given me in the application.
Thank You

Ed

[Ed Barley]

RE: Field validations

Hi Edward,

I will test what you have given me in the application.
Thank You

Ed

[Ed Barley]

RE: Field validations

Hello All,

Still having problems. The field is "Job_Number"

The validation below works fine, except for a change record sequence.

Using the expression below in field rules with the custom message:

ISNOTBLANK("Vin_number") .AND. KEY_UNIQUE("Look_Dupe")

And the index "Look_Dupe" = JOB_NUMBER+VIN_NUMBER

If I do not put the expression in field rules, the field functions as it should with saves, change records, etc.

The minute that I apply the custom validation with Key_unique in field rules, and say, you want to delete information in the field, when you do a save, the information that was deleted in the field pops back in by itself. If you do a change record again, delete the information, and add new information, then delete this new information, as soon as a save is done, the new deleted information pops into the field. So in essence, you can never leve the field blank again.

I thought I remember reading a post a couple of years ago, about something similar, but could not fine it.

Does anyone have any suggestions.

Thanks Ed

[CALocklin]

RE: Field validations

I can't tell what you really want from reading this.

You start with, "There is a field in my main table, that is named "Vin_Number". It needs to be unique(no duplicates)." But your description of the index and validation rules does not support that statement.

You define an index which includes 3 fields then, for some reason, you add a validation rule to the Vin_Number field that says the index with 3 fields has to be unique. That won't necessarily make the Vin_Number unique because only the combination of the 3 fields would have to be unique. You could have the same VIN number many times as long as it was used with different values in the other two fields.

So, do you want the VIN number to be unique or the combination to be unique?

My guess is that the Job_Number + Vin_Number should be unique. This would avoid the possibility that someone could add the same Job and VIN with a different booking number. I could be wrong since I don't know exactly what a "booking" is - I'm just guessing that one VIN with one Job should not have two bookings?? However, it would be very possible to have multiple jobs for one VIN; especially over a period of time. (I've developed an app for tracking auto body repairs but have not dealt with "bookings" in the process.)

Also, if you want to allow multiple blank VIN numbers but otherwise make them unique, it would require something besides a standard uniqueness test. However, I'll wait to work on that one until I understand exactly what you want.

Cal Locklin
www.aimsdc.net

[Ed Barley]

RE: Field validations

Hi Cal,

I rally appreciate you taking the time to help.

After I went back and looked at my origional post,as a little time has passed, it was a little confusing. I want to make is simpler.

I would like just to use Job_Number + Vin_Number. Where within the Job_Number, the Vin_Number (1) Would pop up with a message saying "You have entered a Duplicate Vin Number!". (2) Of course not accept a duplicate.

Below is where I am at since Edward added to this post.

My Index is just "Job_Number+Vin_Number". It is named "Look_Dupe"

In Field rules I just have the custom validation running with "Key_unique("look_dupe"). With the above message.

Noting at this time, seems to work correctly. I have gotten totally confused. It seems like every combination of expressions or lack of expressions just will not work as I have explained above.

I played with "isnotblank() .AND. Job_Number+Vin_Number", It seems to work for awhile, then reverts back to I think paying attention to the blank vin numbers. With the biggest problem being, when a change record is made, and then the decision is made to leave Vin_Number blank, the field will not allow a blank record. It always hangs on to the last change record information in the field.

So ??????? Totally confused.

Thanks Ed

PS I have Key_Unique running on a couple of other fields, and it works like a charm. So this even confuses me more.

[G Gabriel]

RE: Field validations

Ed:
""My question is, how do you make the field not except duplicate vin numbers, but at the same time if the field is blank, not trigger the no dupes???? I think that is what is happening.""

You are making this a lot more complicated than it has to be.

You want to:
1-Enter a VIN number in a field
2-Make sure that this VIN number is unique, or
3-The VIN number could be left blank.

Foreget about all the expressions and the combinations of fields and all the other confusions and custom validations.

In the field rules, validation, uniqueness test: choose Value of field must be unique. That simple !
Gabe

[Tom Cone Jr]

RE: Field validations

Ed, it seems to me the problem is in confusion about your goal. With a compound key there are several possibilities. Perhaps you can think through each one and then tell us how you want the data entry to work for each:

1) Job_number not blank, Vin_number not blank
a) Job_number unique, Vin_number unique
b) Job_number unique, Vin_number NOT unique
c) Job_number not unique, Vin_number uniqiue
d) Job_number not unique, Vin_Number NOT unique

2) Job_number blank, vin_number not blank
a) Job_number unique, Vin_number unique
b) Job_number unique, Vin_number NOT unique
c) Job_number not unique, Vin_number uniqiue
d) Job_number not unique, Vin_Number NOT unique

3) Job_number not blank, Vin_number blank
a) Job_number unique, Vin_number unique
b) Job_number unique, Vin_number NOT unique
c) Job_number not unique, Vin_number uniqiue
d) Job_number not unique, Vin_Number NOT unique

These complexities drive me to use simpler index keys for reasons I hope are self evident.

-- tom

[Ed Barley]

RE: Field validations

Hello Tom & Gab,

Thanks for your input.

Before we go from here, these are the events of the data input form.

The form is filtered with a variable, from an xdialog, that equals the Job_Number. The form is modal. So the form opens up filtered by the Job_Number.

This is where "I" may be way off. And I need to learn something from this.

From here if it is a duplicate vin number within the job number(that is already filtered), I would like a custom message to come up saying "You have entered a duplicate vin number!"

Have I used key_unique() wrong, where the index should be just the vin_number and not include the Job_number????
Being the input form is already filtering by the job number?

Ed

[G Gabriel]

RE: Field validations

Ed:
""From here if it is a duplicate vin number within the job number(that is already filtered), I would like a custom message to come up saying "You have entered a duplicate vin number!"""
Alpha will do that for you with no added programing on your end once you set the validation rules as I proposed previoiusly. If you enter a VIN that is a duplicate, a msge will pop up indicating just that and prevent you from entering the duplicate number. No need to re-invent the wheel.

""Have I used key_unique() wrong, where the index should be just the vin_number and not include the Job_number???? ""

I am not really sure what Job_number has anything to do with your objective of insuring that VIN_number is unique? VIN number, if you are refering to a car's VIN number, like SSN, DNA, finger print ..etc. is a unique item that occurs only once in the universe. That's why, I could not quite understand why are all these expressions and field combinations? The uniqueness of the VIN number is not dependent on the job number. Each and evey number is unique regardless of the job number. As such, you need not test the uniqueness of the job-number. If nothing else, it's reduntant, confusing and provides no validations whatsoever.
But to answer your question, which is only of achademic nature at this point: yes, you should have used vin_number only.
Gabe

[CALocklin]

RE: Field validations

The form is filtered by the job number.

Does this mean the job number should always be unique? It would make sense to me and, if so, that may make things much easier. In that case, make the Job Number itself unique. (If it is defined as an auto-increment field, that will force it to be unique.)

At that point, each job number would presumably have only one VIN number so now the problem is solved because you can't put two VIN numbers in the same job.

However, if job numbers are not unique then it's a whole new ballpark. In that case it's important to know if one job number can have multiple records with multiple VIN numbers and, also, if it's possible to have one job number with multiple records that have blank VIN numbers. (Neither one makes much sense to me but then I don't know exactly what you are doing either.) If one job number can have multiple blank VIN numbers then it may be necessary to build a UDF to solve your problem.

Cal Locklin
www.aimsdc.net