Need help with uniqueness test

[Rich Hartnett]

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

Steve you mean "CanUpdateRecord" instead of "cansaverecord?" And is "findcode" a field where I have the LinkingKey + Code already added together?

[Steve Wood]

Re: Need help with uniqueness test

I dashed out for a while -

In the function dbcount() the second argument is the Index Name and the third is the key value. So, I propose you create an index on your Persons table that concatenates linking field + code. That table may contain many V1's but should never contain more than one "100000V1", if "100000" is linking field. So, if...

dbcount("mytable","findcode",DataSubmitted.LINKFIELD)+DataSubmitted.CODE) > 0

..is TRUE, then there already is one record with that LINKFIELD+CODE and it would prohibit it being saved or updated (CanInsertRecord and CanUpdateRecord, respectively).

But I'd figure out how to autoincrement each value. Don't know how without spending some time on it, but it could be worked out. Right now you are forcing your users to know and pay attention to previous record values and enter the appropriate one. That's not something all users do well.

[mmaisterrena]

Re: Need help with uniqueness test

Is this a single Grid or there are two linked grids?

[Rich Hartnett]

Re: Need help with uniqueness test

Actually this application consists of 7 linked grids, but in this instance, I'm only concerned with the grid which is displaying data from one table called "Persons_ncpd." The Persons_ncpd table has many fields, but for purposes of this thread, I'm only concerned with two fields: "LinkingKey" and "Code." (Code is a unique value that identifies the "person." V-1, V-2, S-1, S-2, R-1, R-2, A-1, A-2, A-3, etc.)

And again, my problem is that I need to prevent the user from applying the same Code to two different people in the same report. So for example, if a particular report has the LinkingKey of S001 assigned to it, the following values would be permitted:

S001 V-1 = S001V-1 Victim #1
S001 R-1 = S001R-1 Reporting Person #1
S001 W-1 = S001W-1 Witness #1
S001 W-2 = S001W-2 Witness #2

But the validation should reject the input from the user if he tries, for example, to use the code of "V-1," again for a second person, as "V-1" has already been used within this report.

S001 V-1
S001 R-1
S001 W-1
S001 W-2
S001 V-1 ---- "REJECTED - VALUE MUST BE UNIQUE"

On the other hand, the value of "V-1" could appear 100's of times in the Persons_ncpd table, as long as it is related to a different LinkingKey value each time. The two values together (LinkingKey + Code) makes the "Person Code" truly unique. And that works fine on the desktop, the problem is getting it to work over the web.

[Steve Wood]

Re: Need help with uniqueness test

Rich,

See attached table and grid example. The solution proposed in my earlier post works. Review the index on the table and the CanInsertRecord event.

[mmaisterrena]

Re: Need help with uniqueness test

is the "linkfield" the field tha you use to connect betwwen grids?

[Rich Hartnett]

Re: Need help with uniqueness test

Success! Steve's method works like a champ. (Thanks Steve)

Once again, thanks everyone for the assistance. This board is worth its weight in gold.