Need help with uniqueness test

[mmaisterrena]

Re: Need help with uniqueness test

I strongly suggest you add a new calculated fiel to your child table in wich you combine the Parent id with the child id (Eg. Parent_id + child_id), and then just validate by checking that the value of this new field "doesnt exist" on the table.

[Rich Hartnett]

Re: Need help with uniqueness test

Actually I tried that already and couldn't get it to work, but maybe I was doing something wrong.

At your suggestion I'll try it again. Thanks.

[Rich Hartnett]

Re: Need help with uniqueness test

Well Mauricio I did as you said, but I'm not having any luck. I created a calculated field in my "Persons.dbf" table called "link_Code" which adds two fields together: alltrim(LinkingKey)+(Code).

The result normally looks something like "S001V-1" (S001 + V-1)

Then I set the Cross-File Grid Validation rules to test that field for uniqueness by specifying that the value in the field "Must Not Exist." (See attached photo)

I then tested it by entering "S001V-1" in the test area, and it gave me the error message which said that the value already existed. Then I used entered "S001V-2," and it said "success." (V-2 is not already in use in LinkingKey #S001.)

So it seems like it works, but when I published it and tested it on the web, the form still accepts duplicate "Code" designators, with no error messages whatsoever.

Any other thoughts?

[Tom Cone Jr]

Re: Need help with uniqueness test

Richard, are you sure that the uniqueness validation field rule works at all in a web app?

Check this thread:

http://msgboard.alphasoftware.com/al...ht=field+rules

You probably need to do a basic search in this forum for other threads discussing validation sequences for web apps.

[mmaisterrena]

Re: Need help with uniqueness test

Richard have you tried to set the validation directly on the field rules?

[Rich Hartnett]

Re: Need help with uniqueness test

Richard, are you sure that the uniqueness validation field rule works at all in a web app?

Well yes and no. (Hi Tom) I am aware that only some field rules work in web apps, and I have used them extensively. In this particular case, I have been able to make a simple validation work within the web component's own built-in validation section, by simply stating that a particular value MUST NOT EXIST at all, within the "Persons_ncpd" .dbf table, and it works exactly as it should. On the other hand, in this case I only want the application to check for duplicate data on records within the same LinkingKey, and that is where I am having my problems.

I strongly suggest you add a new calculated field to your child table in which you combine the Parent id with the child id (Eg. Parent_id + child_id), and then just validate by checking that the value of this new field "doesnt exist" on the table.

Mauricio I tried that, and it works correclty if you enter the new record on a desktop application, but on the web, it lets duplicate data get entered into the table. I think the problem with this suggestion is that it requires a calculation to take place before it checks for duplicate data, and since nothing happens until submitted over the web, the table accepts the duplicate data before it can get the calculated results in the field which is being checked. As such, by the time the calculated field has finished calculating, the duplicate data as already been inserted into the table, and has effectively bypassed the validation protection. (Just my observation.)

Incidentally Tom, I did read the other post you listed above with info from Steve Wood, but it really doesn't answer the question as to validations on the web, except to say, "some work, some don't."

I think we need some input from a "higher authority!" LOL

[mmaisterrena]

Re: Need help with uniqueness test

Got an idea

how about doing something like this on the BeforeValidateNewRecord event

'this will look for the person id on the table filtered for the report id
lookup("TableNameHere","report_id="+quote(DataSubmitted.Report_id_inGrid),DataSubmitted.person_id_inGrid)

'You need an error handler so that when the above script doesn't finds 'somethig it will skip the redirect to page comand
on error goto Continue_here

CurrentForm.RedirectTarget= "Error_duplicate.a5w"

Continue_here


'is just an idea you will have to refine it to make it work

[mmaisterrena]

Re: Need help with uniqueness test

but maybe you wont have the report id at that point so maybe try on another event

[Steve Wood]

Re: Need help with uniqueness test

I'm sure this is answered somewhere below, but is this a Grid or a Dialog?

[Rich Hartnett]

Re: Need help with uniqueness test

Grid

[Steve Wood]

Re: Need help with uniqueness test

Forget Validation or Field Rules. Here is exmple code for the CanUpdateRecord. You'd do the same for the CanInsertRecord if you want to protect new records:

function CanUpdateRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p )
with PageVariables

if DataSubmitted.FNAME=DataSubmitted.LNAME
	Result.Cancel = .t.
	Result.ErrorHTML = "Names can't be the same"
end if

end with
end function

[Rich Hartnett]

Re: Need help with uniqueness test

I'm not sure I understand what this is doing? It looks like it's checking to make sure the last name and first name are not the same. If so, how would I apply this my situation?

I'm checking one field ("Code") to make sure the data doesn't already exist, but only within the current set, as referenced by the linkingkey.

[Steve Wood]

Re: Need help with uniqueness test

Sorry, I didn't read down far enough. Can you just auto-increment the Person code so it never dups?

[Rich Hartnett]

Re: Need help with uniqueness test

No, but let me explain the situation fully so it makes sense.

This is a police report application. The officer starts a new report, and the parent table (case number, date, time, etc.) issues a new linking key.

The officer then goes to the "Persons" child table, and starts to add related people.
The first "victim" is V-1, then V-2, etc. Suspects are S-1, S-2, etc., and there are other designators.

These "Person Codes" should never appear twice within the same report, hence the relationship to the linking key. The code "V-1" could appear 100's of times in the "Persons" table, as long as each time it is associated to a different linking key.

So my problem is that I need the validation to say "MUST NOT EXIST," but only within that particular set of records, that have the same linking key.

[Steve Wood]

Re: Need help with uniqueness test

Another thought, something like this in the cansaverecord event where findcode is an index on your Linking field + code field.

function CanUpdateRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p )
with PageVariables
if dbcount("mytable","findcode",DataSubmitted.LINKFIELD)+DataSubmitted.CODE)+ > 0 
	Result.Cancel = .t.
	Result.ErrorHTML = "That code already used"
end if
end with
end function