Uniqueness testing

[Richard Dean]

I have a database that has a custom unique testing for records that have and pupil code and year_of_test field so that no pupil can have two records of scores for any particular year. The problem I have is that if the pupils score is entered wrong then I have to delete that record and start again because the uniqueness test will not allow a record to chenge. My question is is there a way of disabling this uniqueness test when changing a record in a browse???

another point i have noticed is that a5 doesn't like spaces in directories. If you try to launch an file from a command line i.e. "excel c:my documents1998.xls" a5 says canot find file "my.xls" then "documents1998.xls" Any ideas? Similar problems? Moving the file to a dirctory line with no spaces solves the problem and so does putting the dos 8.3 name but both seem solutions for non long file name enabled programs but a5 can hadle spaces some times and not others!

[CALocklin]

RE: Uniqueness testing

Richard,

If I understand what you are doing, you are using sys_shell() to start Excel and open a specific file. The problem seems to be in the way [b]Microsoft Excel[b] does/doesn't understand spaces. In order to make Excel understand there are spaces in the file name, the file name must be enclosed in quotes.

I set up a shortcut icon on my desktop to try it out.
This works:
C:\Program files\Microsoft Office\Office\Excel.exe

This doesn't work:
C:\Program files\Microsoft Office\Office\Excel.exe e:\excel\copy of Soc_sec.xls

However, this fixes it: (added interior quotes)
C:\Program files\Microsoft Office\Office\Excel.exe "e:\excel\copy of Soc_sec.xls"

So the trick is "How to add interior quotes to the sys_shell command" and that is easy - just precede the interior quote with a backslash like this:
sys_shell( "C:\Program files\Microsoft Office\Office\Excel.exe \"e:\excel\copy of Soc_sec.xls\"

Cal

[CALocklin]

RE: Uniqueness testing

Oops. Sorry, forgot to close the parenthesis. That last command should have been:
sys_shell( "C:\Program files\Microsoft Office\Office\Excel.exe \"e:\excel\copy of Soc_sec.xls\")

[CALocklin]

RE: Uniqueness testing

I hate it when I do that - I forgot there is one more quote at the end to close the string. One last time:

sys_shell( "C:\Program files\Microsoft Office\Office\Excel.exe \"e:\excel\copy of Soc_sec.xls\"")

[CALocklin]

RE: Uniqueness testing

Richard, I was hoping someone else would answer your question about uniqueness but, since they didn't, I did a little testing...

I defined a table with 4 fields: Student(char), Year(char), Test_type(char), and Grade(Numb). Then I added a field rule to the Year field which says, in effect: "Student + Year must be unique". This method worked fine for me. It keeps me from adding a second record for the same student and same year but allows me to change the score whenever I want.

The one drawback is that the error message when creating a duplicate record simply says "Value for this field must be unique: Year". This can be a bit confusing for users if they don't already understand that the problem is really the combination of student and year.

If this method won't work in your case, either give me more details, post a script/db here, or send it to me directly.

Cal

[whanigsberg]

RE: Uniqueness testing

Richard,

Cal may have given you what you need but I have a question. Have you specified a validation expression to enforce your uniqueness rule and if so have you used the key_unique() function. This is meant for such situations and may be better in some subtle way than Cal's solution. I believe it would give you the opportunity to show a custom message when the record was not unique.

Bill

[Richard Dean]

RE: Uniqueness testing

Thankyou Cal and Bill.

To Cal the excel line worked and so does the uniqueness testing (as you said it would) the only problem is the same one that I hadn't used the standard uniquessness testing before. That is, I was using Bill's suggestion (key_exists but to the same effect) because I wanted a more useful message as the user (my mom) may have panicked at the one you get without the custom validation.

To Bill the key_unique does the same job as what I was using (key_exists) but is easier to follow so thanks for that but it still has the problem about not beeing able to change a record that had been entered wrong.


Thanks again and any other sugestions will be appreciated

[Richard Dean]

RE: Uniqueness testing

I have found this command from using script recorder

:DefaultBrowse_AVE:Tables:AVE.enter_begin(HONOR_FIELD_RULES)

which seems to indicate the there is a way of not honouring the field rules? any ideas?
.NOT.honour..... doesn't work

Thanks

[CALocklin]

RE: Uniqueness testing

Richard,

Here's a section from the documentation. It refers to a True/False flag. I tested "Honor_field_rules" in the interactive script editor and apparently it just a built-in variable that is always True.

Basically, this means that your default is to NOT honor the "UI Level" field rules so it probably won't help you.

Syntax:

"Tbl>.change_begin( [Flag] )

Description:

Initiates a Change operation on the current (most recently fetched) record in the table referenced by the specified table pointer (). You complete the Change operation by calling .change_end() using the same table pointer. Flag is an optional parameter. If Flag is .T., then "UI level" field rules are enforced when the record is changed. If Flag is .F., then Alpha Five does not enforce these "UI level" field rules. "UI level" field rules are rules such as trigger events, capitalization, minimum and maximum values, masks/templates, lookup fillins etc. "Engine level" rules, such as auto-increment values are always enforced. If flag is ommitted, it is assumed to be .F..

[whanigsberg]

RE: Uniqueness testing

Richard

>>the key_unique does the same job as what I was using (key_exists) but is easier to follow so thanks for that but it still has the problem about not beeing able to change a record that had been entered wrong.

Check the readme for version 4.03 for a detailed and important description of the difference between the Key_exists and Key_unique functions. Essentially, Key_unique will allow changes to the record (what you need to do) and Key_exists should not.

If you install the expression under custom validations you will be able to supply a custom message to display when the rule is violated. But you must do this under custom validations rather than "value of expression must be true" (or some such).

Bill

[Richard Dean]

RE: Uniqueness testing

My mistake, i had got the expression under custom validations, but as i was using the key_exists I also had to use a NOT. so I just changed the key_exist for Key_unique meaning that when i tried to change a record it wouldn't let me, but if i had tried i would have been able to input a duplicate record!! Whoops!!!

I have renderred my mistake and all is fine except that i have looked though all the readme, readme403, help and xbasic help and nowhere does this key_unique function get a mention, but then thats probably me beeing silly again.

Anyway thankyou all!

[whanigsberg]

RE: Uniqueness testing



Richard:

>>thats probably me beeing silly again.

I wouldn't say so. I always overlook the readme and it is hard find things in there even when you know they are in there somewhere. Use the pdf version and you can search for something.

I went lowtech and created a handwritten index of important topics with their pages (I hand paginated the document!).

What you want to read is the second item under New Features in 4.03 on pg. 31. I have copied *most* of it below.

Bill


KEY_UNIQUE() Function
A new function, key_unique() has been added. The syntax is:
Key Exist Flag = KEY_UNIQUE(IndexTagName)
The function searches an index (IndexTagName) to see if the key value for the current record already exists.
If the key is unique, then Key Exist Flag is TRUE; otherwise, it is FALSE. This function is useful for checking
if a record with a certain key value already exists in the current table.
Contrast Key_Unique() with Key_Exist(). Key_Exist() can be used to check keys in external tables as well
as in the current table. However, if you use Key_Exist() on the current table, it will report that the current
record�s key is a duplicate key when you are in change mode and you save the record. Key_Unique(), on the
other hand, does not include the current record�s key value in the search for duplicate keys.
Therefore, if you want to check if the current key exists in a Validation field rule, you should use
key_unique(), not key_exists().
Important Note Regarding Use of Key_Exist() and Key_Unique() in a Validation Field Rule.

Prior to V4.03, you could use the following expression in a validation field rule:
.not. key_exist(�lastname�, �lastname�)
(where �lastname� was both the name of the index tag and the field).
This would prevent the user from entering a duplicate lastname.
However, in V4.03 this field rule will not allow you to make any edits to the record after it has been saved.
Even a change to a unrelated field, such as changing the firstname field from �Tom� to �Harry� will not be
allowed. This is because in V4.03, Alpha Five evaluates validation field rules for all fields when a record is
saved, regardless of whether the user actually typed a new value into the field. Therefore, when you try to
save the record after making a change to a field the .not. key_exist(�lastname�,�lastname�) validation rule
will fire and it will not be satisfied because the key value for the current record will already exist in the
index. In V4.02 and before, since the user did not modify the lastname field, the validation rule did not fire,
and the record could be saved.