test for unique

[bobsk]

i have 75 fields in a table. how can i prevent a duplicate record on any of the fields. a1 a2 a3 a4 a5 a6 b1 b2 b3 b4 b5 etc. if a1 = q000007 how can i prevent b5 on a different record but in the same table from having that value?
Thank you,
Bob

[Raymond Lyons]

Re: test for unique

I'll let someone else deal with your question. But if the field names in your question are real, be aware that "a5" at one time was a reserved word and as such should be avoided.

Ray

[bobsk]

Re: test for unique

Thanks Raymond,
So far I have not had any issues. I am in too deep to get motivated to change it but I will stay aware that might happen. I am able to prvent double entry right now because if a value exists anywhere on the form I won't let the user move on. *any(A1,"Q00").AND.A1<>var->vname1.AND.right(alltrim(A1),7)<>var->vidcall but now i have 3 separate forms and need something to reach thru the whole table.
Thanks for the advice

[MikeC]

Re: test for unique

Bob may already have his answer but will post a possible solution (am sure there are other ways too) for future people's use.


This script could be placed on the CanArrive of a button or....

Using the function fetch_loc_next()

Define a variable as character to equal the new entry. Use say vValue.

May need to place a t.fetch_first at the beginning of the script.

dim t as p
dim Loc_Flag as L
t=table.open("your_table")'or use table.get("your_table") if table already open
Loc_Flag=t.fetch_loc_next(vValue)

if Loc_Flag then
  ui_msg_box("Attention!","This value cannot be used as it results in a duplicate value.",UI_OK)
  cancel
  end
end if

t.close() 'Don't use if using table.get()

With large tables this may end up being fairly slow and do believe LQO can NOT be used with it so another method would have to be used in that case.

[Tom Cone Jr]

Re: test for unique

Bob, any time I find myself searching laterally through all the fields in the same record I become suspicious my data structure could be improved. Here it seems to me that you could put each field in its own record. An indexed search through the new table (for a duplicate field value) would be virtually immediate.

[bobsk]

Re: test for unique

Thanks Tom,
The problem here is I have a calender that I take the entered data like name, date and time from and use it in another database. If I made each field a record how could i place them on a form at the same time? I se what you mean it would be easy to control duplicates. I will give it some thought.
Here is a screen shot.

Thank you,
Bob

[bobsk]

Re: test for unique

Thanks Mike,
I will give it a try. It sounds like that is what I need.
Bob

[Tom Cone Jr]

Re: test for unique

Bob, one idea would be to take a record that looks like this:

Fieldname, type, width, value
a1,        c,      10,   "q000007"

and put it in a table like this:

Fieldname, Type, Width, value
Fld,        C,      2,      "a1"
Fld2,       C,      10,     "q000007"