Field validations

[CALocklin]

RE: Field validations

As I think about this (guessing at exactly what you are doing), I'm wondering if your whole linking logic is correct.

For example, if you are working in a "child" table such as a list of specific tasks related to a vehicle repair, that "child" table (even if it is not currently part of a set) should probably not include the VIN number. Only the one linking field to the Job should be used because the Job would define the VIN.

JobTable
|
---1:1 -- Veh_Info - using VIN in JobTable and VIN in Veh_Info
|
---1:M -- Tasks - using Job# in JobTable and Job# in Tasks


Then if you want a list of jobs (such as previous and current) based on a specific VIN, just use a different set for the report:

Veh_Info
|
---1:M -- JobTable - using VIN

Cal Locklin
www.aimsdc.net

[Ed Barley]

RE: Field validations

Good Morning from the West Coast,

I should have zeroed in more.

A job_number = the sailing of a vessel and its voyage no.

IE: 56787 = Santa Maria Voyage 1492

What I am trying to prevent, is a duplication of the vin number on that particular voyage of the vessel. Not forever.

That piece of cargo could come back in the future. Lots of pieces of heavy equipment, excavators, front loaders, just to name a few,live a life of traveling around the globe sold and used many times. So it is possible to see that vin number a few times??

The vin number is a field in my main table, along with an invoice type number, that lets us receive this piece of cargo with a destination port and control number. All that works great, with a much help from this board. The child table only contains info in regards to weight, measurement, etc.

That is why I have always included the job number in the expressions. Maybe I should not have.

I am trying to understand indexing, and uniqueness the best I can.

I understand that indexing helps alpha look quicker for information.

Awhile back on the board someone helped me with using Key_unique, along with the index, as I wanted to use a custom message.

The problem of leaving the field blank, only comes up once in a great while. And only because of input error, by one particular input operator. Who enters data in the vin number field, tries to leave it blank, so the whole record is not lost, and then comes back and enters the correct vin. 2 problems came up; a. a blank record would pop up my custom message, and b. if the input operator entered data in the field, as soon as they did a save to leave the record blank, the deleted datawould pop pack in(if it was deleted, how did it pop back into the field, and where was it, so I could get it deleted once and for all??)

Ed

[martinwcole]

RE: Field validations

Cal, what about putting code in field rules in the canwrite field event for job number and vin number


in vin:
if tablecount("jobs","job_number" + quote(a_field_value)) " 0
ui_msg_box("Error","message here",16)
cancel()
end
end if

in jobnumber:
if tablecount("jobs","vin" + quote(a_field_value)) " 0
ui_msg_box("Error","message here",16)
cancel()
end
end if

[CALocklin]

RE: Field validations

That helps a lot. Yes, I can see why you need both the Job and VIN in one table.

Since the Job is the main linking value, I would assume that the Job is always entered before the VIN. A simple "expression must be unique" field rule should take care of the uniqueness issue IF only one record at a time is allowed to have a blank VIN. However, if multiple records are allowed to have a blank VIN, no true uniqueness test will work because it won't be unique.

An alternative might be require a VIN but tell users to use something like B1, B2, etc. instead of blank fields. Then a search could be done on left(vin,1)="B" to find all the "blank" records. (Of course, you would have to find some first character(s), and it may have to include one or more special characters, that would never occur in a real VIN number.) Another alternative is that most VIN numbers are probably much longer than 2-3 characters so any VIN number less than x characters could be considered "blank".

It shouldn't be very difficult to set up a saved query or index to show whichever one you choose - all VINs beginning with some letter combination or all VINs whose length is less than x.

Note: I'd prefer a query for something like this because (a) filters on indexes can sometimes be a problem and (b) the record won't seem to disappear from a browse when the correct VIN number is entered. With an index that includes the VIN number, changing the VIN will cause the record to suddenly jump off-screen when saved. Since a query is static, changing the VIN won't affect the location of the record in the query until the query is re-run. (Actually, if you press F9 to save the record, you will remain on the record but the adjacent records will change. If you press the down arrow to go to the next record, I think it skips two records down and the saved record goes off-screen. It's this confusion that makes me prefer the query.)

Cal Locklin
www.aimsdc.net

[CALocklin]

RE: Field validations

Both posted at exactly the same minute - amazing.

In this case, I don't think there should be a need to check the Job field because it should normally be the first field filled.

As to the tablecount() - it would probably work but it does a query and may take a long time on a large table. If the simple uniqueness test with "special blank values" won't work, I'd prefer to use dbcount() because it is based on an index and should run very fast even on large tables. However, my concern over indexes still applies.

Cal Locklin
www.aimsdc.net

[Ed Barley]

RE: Field validations

Wow,

I really appreciate all the advice.

I did not realize that a blank field could cause so much havic. I was under the assumption(not good), that when there is no data in a field as such, that looking for duplicate info would take into consideration that the field is blank, and if you had more than one blank field, that would trigger my error message. Not good if that is correct. I see why things were not right.

Thanks Tom for the different look at my use of using more than one field in the expression. I see the possibilities. Too many things to not be correct.

Am I looking at this wrong, is it just possible from this post, that I might need something simple like within the Job Number, that I do not want to use any previous entered values.
Could it be just that simple? Would that ignore blank spaces, and the only concern being not using a previous value within the job number? Or is that close or just the same as using unique?

Thanks Ed

[G Gabriel]

RE: Field validations

Ed:
I am responding to my e-mails and then I will be in my way out to taking a test this afternoon, so I am writing this in a bit of a hurry and without the benefit of reading many of the previous postings. I apologize if any of this was said already:
I am gathering that you want to be able to either:
1-enter a unique value for VIN number within a certain job_Number (that VIN_Number could be used though for other Job_Numbers but only once in any given Job_Number), or
2-leave the field blank
I don't think a combined-field index will help you (I am thinking fast here). Forget about all the previoius attempts you made in that regard.
This is not as easy as it looks, but one easy way to do it is:
1-Make sure in the order of data entery (tab order) the Job_number is entered before the VIN_number
2-In the VIN_Number field rules, Validation , uniqueness test: choose "Value of expression must be unique"
3-Use the following expression: alltrim(Job_number)+alltrim(VIN_number).
This will work and will allow you to enter blanks without error messages.
Gota go.
Good luck
Gabe

[CALocklin]

RE: Field validations

3-Use the following expression: alltrim(Job_number)+alltrim(VIN_number).
This will work and will allow you to enter blanks without error messages.

I think you forgot about the "s" on "blanks".

I didn't actually test it but I don't think this will work for entering multiple blank values. If the expression must be unique but the above works with blank VINs, then this is the same as saying that "Job_number" is different than "Job_number". To put it another way, all entries in the following list (Job_Number+VIN_Number) do not appear to be unique:
ABCD 1234
ABCD 3872
ABCD 8356
ABCD
ABCD
ABCD 4975
ABCD
ABCD 7589

Cal Locklin
www.aimsdc.net

[G Gabriel]

RE: Field validations

Cal:
""I didn't actually test it but I don't think this will work for entering multiple blank values""

You make a good point, but if you test it, it works. I suppose, it has to do with the way alpha tests for uniqueness in combined-field values when one field is blank. Somebody in alpha must be quite a visionary and thinking way ahead ! Or, sometimes the best discoveries happen by accidents !
Gabe

[CALocklin]

RE: Field validations

Interesting. Always good to learn something new.

[G Gabriel]

RE: Field validations

Actually, I have to make a minor modification to my previous comments:
This behavior suggests to me that Alpha does not regard blank as a value, and as such you could enter as many blanks as you wish whether the field validation is based on a single field or a combined-field expression.

Perhaps a null value would not be included in the test for uniqueness. I am glad alpha does that, It makes sense.
Gabe

[Ed Barley]

RE: Field validations

Hello Gab & Cal,

I have been following your additions to the post.

I have tested the below expression with my custom error message, and just with "The value of expression must be unique" using the expression with and without alltrim,in field rules.

alltrim(Job_number)+alltrim(VIN_number)

That was over last weekend. At that time the results seemed inconsistant. Which led me to this post, in the first place.

So I will give the above a try again in field rules, just in case I was asleep at the wheel.

Thank you for all the input.

Ed

[G Gabriel]

RE: Field validations

Ed:
I believe you might have used it, but you also mixed it up with a custom validation using Key_unique("look_dupes").

Actually that raises an interesting question for alpha:
One would think if someone chooses an option from the window: Uniqueness test, then the custom validation should be obviated, else if you choose one of those items, then from the custom validation enter yet a set of validating expressions, then which ones does alpha use for validation ?? the one from the uniqueness test? the ones from the custom validation? both?
Gabe

[CALocklin]

RE: Field validations

It probably uses both but I'm not sure which one is evaluated first. (My guess would be that the custom validations run last. I'm sure a test could be run and I probably did it a long time ago - like in version 4 or earlier - but I don't have the time right now.)

You certainly want the ability to have custom validations that run regardless of the uniqueness test.

It might also be possible (although I can't think of a good reason right now) to have a situation where the regular uniqueness test tests for one thing and a custom validation checks for another type of uniqueness. In that case, if Alpha checked to see if there was a regular uniqueness test and skipped any custom validation that had "key_unique()" in it, then the second and different uniqueness test wouldn't run.

Alpha has to allow for all possibilities and leave it up to the developer to get the right combination to do the job. (I often try to develop apps that check for as many user mistakes as I can but it's not possible to check for everything. The user has to take some responsibility.)

Cal Locklin
www.aimsdc.net

[G Gabriel]

RE: Field validations

Cal:
My thinking is:
1-If you are going to use custom validation: then you could incorporate whatever expression you would have used in the uniqueness test in the custom validation. Remeber, custom validation allows you to use multiple expressions.

2-On the other hand, if all you need is check for uniquess, then simply use the uniquness test window options.

Therefore, you would use one or the other, but both ?? That's what I meant by one obviating the other.

I suppose, somebody could argue that, you could put an expression in the uniquness test, and some others in the custom validation which might not be any different than putting all the expressions in the custom validation. Only questions remain: which ones alpha validate? and whcih one alpha validate first? and what if there is a conflict amongst these expression?

Incidentally, quite possibly the reason why the original scheme did not work for Ed is because of a conflict in those expressions: while key_unique might evaluate to true, the other expression might evaluate to false. So, now what? what if one expression evaluate to true and the other to false? do they all have to evaluate to true for the validation to qualify? This is getting confusing by the minute.. I am going to quit ! Hope someone from alpha clarifies this.

Gabe