Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Sanjay Sharma
  Where is Sanjay Sharma?
 Rishra
 India
 Sanjay Sharma
 Tags
Subject: RE: Check to prevent duplicate at Database
Thread ID: 331505 Message ID: 331567 # Views: 53 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 31, 2011 1:23:16 PM         
   


> >
> > The Candidate index on the column 'ktopl' is not working. There are four duplicate values 'CAPE'.
> > To find if a vornr is ok:
> >
> >
*****
> >  TEXT TO lcText NOSHOW PRETEXT 8
> >  CAPE | ADCU | 0014390000 | 0014399999 | Advance from customers|
> >  CAPE | ADIC | 0025010000 | 0025019999 | Advance Inter Company|
> >  CAPE | ADIN | 0025000000 | 0025009999 | Advance Inter Unit|
> >  CAPE | ADMS | 0025200000 | 0025299999 | Advance for Material/Service|
> > ENDTEXT 
> > rows=ALINES(aa, lcText, 1, '|')
> > CREATE CURSOR GL (ktopl C(5), ktoks C(4) UNIQUE, vornr C(10), bisnr C(10), text30 C(30)) 
> >  DIMENSION aa(4,m.rows/4)
> >  INSERT INTO GL FROM ARRAY aa
> > BROWSE LAST 
> > 
> > CREATE CURSOR Accounts (ktopl C(4), ktoks C(4), vornr C(10), company C(30))
> > INSERT INTO Accounts VALUES ('CAPE', 'ADIC', '0025010012','(Name of a company)')
> > INSERT INTO Accounts VALUES ('CAPE', 'ADIN', '0025000336','(Name of a unit)')
> > INSERT INTO Accounts VALUES ('CAPE', 'ADIC', '0014390059','(Name of a company)')
> > BROWSE LAST
> > *If you make ktoks a Candidate column you won't be able to insert a second 'ADIC'
> > 
> > 
> > SELECT T1.* FROM Accounts AS T1 ;
> > LEFT JOIN GL AS T2 ;
> > ON T1.ktopl=T2.ktopl AND T1.ktoks=T2.ktoks ;
> >  AND T1.vornr BETWEEN T2.vornr AND T2.bisnr ;
> > WHERE T2.vornr IS NULL ;
> > INTO CURSOR Invalids
> > BROWSE LAST
> > ****

> > This query will also return rows where ktopl or ktoks don't match the GL.
> > Note: BETWEEN is inclusive.
> >
> > As there seems to be a many-to-one relationsship between 'ktopl' and 'ktoks' they should perhaps be split into two tables with a reference to ktops in Accounts as a foreign key.
> > -Anders
>
> I am sorry. It seems that I could not decribe my requirement clearly.
>
> I would like to maintain a table record validation (or another) that a number range could be used once against ktopl and should not conflict with other record.
>
> Regards,
>
> Swarup Modak
CREATE TRIGGER ON some_table FOR UPDATE AS CheckRange(ktopl+ktoks, vornr)

* MODIFY PROCEDURE 

FUNCTION CheckRange 
LPARAMETERS searchfor ,new_vornr
*LOCAL Searchfor, new_vornr
*searchfor=ktopi+ktoks
*new_vornr= vornr
IF INDEXSEEK(m.searchfor,.F.,'GL','tag1') ;
 AND BETWEEN(m.new_vornr,vornr+1, bisnr) 
 RETURN .T. 
ELSE 
 RETURN .F.
ENDIF
ENDFUNC 


I don't understand in what table you're updating or inserting these values. If 'CAPEADIN' is a unique value in GL then you can't add another 'CAPEADIN'. If you adding yet another subcategory to the 'CAPE' group then you need to check it doesn't already exist.
As for the new vorno, what rule do you wish to apply?


-Anders

ENTIRE THREAD

Check to prevent duplicate at Database Posted by Swarup Modak @ 12/30/2011 3:00:15 PM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/30/2011 3:10:23 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 12/30/2011 6:53:47 PM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/30/2011 9:06:53 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 12/31/2011 4:28:26 AM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 12/31/2011 1:23:16 PM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 12/31/2011 7:24:41 PM
RE: Check to prevent duplicate at Database Posted by tushar @ 1/1/2012 5:45:49 AM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/4/2012 2:51:18 PM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 1/4/2012 3:32:17 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/8/2012 8:16:37 AM
RE: Check to prevent duplicate at Database Posted by Pete Sass @ 1/8/2012 4:32:19 PM
RE: Check to prevent duplicate at Database Posted by Swarup Modak @ 1/13/2012 8:55:58 AM
RE: Check to prevent duplicate at Database Posted by Anders Altberg @ 1/13/2012 1:04:26 PM