Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Pete Sass
  Where is Pete Sass?
 Marathon, Ontario
 Canada
 Pete Sass
 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: 331576 # Views: 53 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 31, 2011 7:24:41 PM         
   


> I have a table like the following:
>
> chart of accounts | gl account group | number of G/L account (lower area limit) | number of G/L account (upper area limit) | long text
>
> 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 / Servic
>
> I am unable to maintain checking to prevent any duplication between "number of G/L account (lower area limit)" and "number of G/L account (upper area limit)" against Chart of accounts at the database level.
>
> Thanks.


Hi,
After reading the 6-7 threads let me clarify what I think you maybe saying and correct me if I am wrong.
I am assuming there is one table involved holding your main set of GL account records. There can be a main GL record that can have sub GL account records added in under this main record, but only between a lower and upper account number range for the main GL account.
Is this description correct??
If this is in fact the case with your main GL top-level record you need in this record two integer fields to hold the lowest and highest GL number that can be accepted within this grouping.
So in this way when you try and add a record within this grouping you can validate if the new GL account number is BETWEEN the low and high allowable number range.
I mention BETWEEN as there is a VFP function called BETWEEN()

Determines whether the value of an expression is inclusively between the values of two expressions of the same type.

BETWEEN(eTestValue, eLowValue, eHighValue)

llIsBetween = BETWEEN(Your new Number, Your low number range, Your high number range)

If the new number is between the lower and higher range llIsBetween will hold the value of .T. if the number is not within the range llIsBetween will hold the value of .F.

So this handles your first validation need to ensure the new record is between the desired number range. If the number is not between the range provide a user MessageBox to try again.

The second thing you need is to check for a duplicate number entry within the low and high range limits of the grouping. I do not understand your accounting structure, but you should be able to within a group "lets say there is no index key" perform a loop to test for the duplicate entry prior to issuing a TABLEUPDATE() command.

If there is a duplicate number then a MesasageBox could provide the user info. that this number entered already exists.

Now if this is the logic you are after, this will not be hard to write this logic right in the Save button on your form after entering a new GL number. I would get the logic working 100% in the Save button first to ensure it is working as expected. Once you get the logic nailed down then you can go ahead a write a couple of UDF procedures to be called from within the database itself if you wish to perform the same logic. In the database you can call your custom UDF's using the Insert and Update triggers for the table involved.

Don't forget you need to handle this logic when adding a new number and if you allow editing of the record numbers, you need to handle this with an edit operation as well.

Pete "the IceMan", from the Great White North of Canada.
www.marathongriffincomputers.com

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