I have this code for checking duplicates. I believe it was from Bill Downall a good while back..
*(This code is made to check for duplicate values in a table. It will prompt you for the table and column name. The column name should be a column in the table you are refering to that is unique. (the one column that should no have duplicate values -- primary key or ???).
ONE MAJOR NOTE:::: This will only reflect accurate results if all indexes have been removed. ) CLS LABEL checkanother SET VAR vm_tblname TEXT = NULL SET VAR vm_colname TEXT = NULL DIALOG 'Enter the Table name (F3 for a list) ' vm_tblname vm_endk 1 AT 13 15 DIALOG 'Enter the Column name (F3 for a list) ' vm_colname vm_endk 1 AT 13 15
SET VAR vm_pausemsg1 TEXT = ('Checking ' + .vm_tblname)
PAUSE FOR 1 USING .vm_pausemsg1 AT CENTER CENTER
BROWSE ALL FROM &vm_tblname WHERE &vm_colname IN (SELECT &vm_colname +
FROM &vm_tblname GROUP BY &vm_colname HAVING COUNT (*)>1)SELECT COUNT(&vm_colname) INTO vbadcount IND vi1 FROM &vm_tblname + WHERE &vm_colname IN (SELECT &vm_colname FROM &vm_tblname + GROUP BY &vm_colname HAVING COUNT (*)>1)
CLS
WRITE ' '
WRITE ' '
SET VAR vmsg1 TEXT = ('Duplicates in ' + .vm_colname + ' = ')
SET VAR vmsg2 TEXT = (CTXT(.vbadcount))
SET VAR vmsg TEXT = (.vmsg1 & .vmsg2)
SHOW VAR vmsg
CLEAR ALL VAR
CLS
SET VAR vmsg3 TEXT = 'Do you wish to check another table? '
DIALOG .vmsg3 vresp vkey YES AT 7
IF vresp = 'YES' THEN
GOTO checkanother
ENDIF
CLS
CLEAR ALL VAR
RETURNCreate a view in this manner...
CREATE VIEW chkdupes (uniqformula) + AS SELECT ( (CTXT(ColorNum)) + (CTXT(Partnum)) + (CTXT(Partratio)) + + (CTXT(Oilratio)) ) FROM ColorFormulas
Then check the view with the code above or a rendition of it.
Mind you, none of this is tested. Just a thought line.. and this is where I usually get into trouble.. ha,ha..
Jim
At 03:39 PM 8/10/2004, you wrote:
I have two tables which together, comprise formulas for making ink. I would like to set up an eep to run when a new color gets assigned to be sure the formula isn't already assigned another number. Here's an example of the data.
Table: ColorNumbers ColorNum Inkloading CY001CL 1.5
Table: ColorFormulas ColorNum Partnum Partratio Oilratio CY001CL 1212XY 10.5 NULL CY001CL 775258 2.5 NULL CY001CL 80888 NULL 80.00 CY001CL 80253 NULL 20.00
The combination of all the rows in ColorFormulas along with the InkLoading in ColorNumbers makes the formula unique. I can declare a cursor, convert the numbers to text and do a concatenation to get a text value representing the entire formula, but was wondering if there was an easier way. Thanks Dawn
