Handling deleted records – use of index 1

[AndrewMozley]

We have a client who allocates account codes to his customers in the form Xnnn - so John Smith might have customer account S023, where the numeric part is the next available for the ‘S’ customers. This account number is printed on invoices &c.

One of the indexes on the customer table, XCUST, is this account code (there is also an index on the Unique ID).
When a new customer (Jones and company) is created we use the index on the XCUST table to find what the last used number was; we append a new record to the customer for account J123 (or whatever).

This has worked fine for years. Recently we noticed a problem that if the client deletes a customer (which can happen) who is the latest with his letter, and then create a new customer with that letter, the program uses the index to find the last used ‘J’ (which now appears to be J122) and try to create J123, there is an index violation.
There could be many ways of dealing with this - I believe we could :

* Change the index so that it only sees un-deleted records
* When creating a new account, check for deleted records and re-use the deleted one if it happens to exist.

There may be other ways, but I would hope not to have to re-write other parts of the system. Grateful for suggestions as to the best solution.

Thanks. Andrew

 

[mm0000]

SET DELETED OFF when you are searching for the last customer account number so that even if the last customer has been deleted it will find it and add one to the deleted number to get the next number. SET DELETED ON back on after getting the last number. There could potentially be problems if you reuse deleted customer numbers if there are transactions in the system for the deleted customer and you need to report on these transactions for some reason it will include transactions of the new customer also.

 

[GriffMG]

When adding a new account, you could switch the visibility of deleted records on, then find the last J (or whatever) and add one.

I would use something like this:

FUNCTION GET_NEXTID
	PARAMETERS m.LETTERCODE
	PRIVATE m.LETTERCODE,OLDRECNO,OLDAREA,OLD_ORD
	PRIVATE m.STRING
	OLDAREA = SELECT()
	SELECT ACCOUNTS
	OLDRECNO= RECNO()
	OLD_ORD = ORDER()
	SET ORDER TO ACCOUNTS1
	** ACCOUNTCODE
	SET NEAR ON
	SET DELETED OFF
	SEEK JUST_PAST(m.LETTERCODE)
	IF !BOF()
		SKIP -1
	ENDIF
	IF LEFT(ACCOUNTCODE,1) = m.LETTERCODE
		m.STRING = 	m.LETTERCODE+RIGHT("000"+ALLTRIM(STR(VAL(RIGHT(ACCOUNTCODE,3))+1,3,0)),3)
	ELSE
		m.STRING = m.LETTERCODE + "001"
	ENDIF
	SET DELETED ON
	SET NEAR OFF
	SET ORDER TO (OLD_ORD)
	IF OLDRECNO > 0 .AND. OLDRECNO <= RECCOUNT()
		GOTO OLDRECNO
	ENDIF
	SELECT(OLDAREA)
	RETURN(m.STRING)


FUNCTION JUST_PAST
	PARAMETERS m.STRING
	PRIVATE m.STRING
	m.STRING = LEFT(m.STRING,LEN(m.STRING)-1)+CHR(ASC(RIGHT(m.STRING,1))+1)
	RETURN(m.STRING)

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[GriffMG]

Alternatively, I would simply change the index to accept duplicates?

Make it REGULAR instead of candidate or primary

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[mjcmkrsr]

Hi,

- "Set Deleted Off" and "Set Deleted On" is the easiest way.

- Are you however aware that you may not have more than 1000 (000 - 999) customers per character. That might seem a lot at first glance, but over the time it isn't - in fact it limits the number of records of your table to 26000 customers (the deleted ones included) - I assume that you use only uppercase characters. Hence you might want to consider to extend the Account Code field to 6, 7 or even 8 characters depending on the number of records the table might have in the future.

hth

MarK

 

[mjcmkrsr]

...

Or you might consider this approach. You have a special table with 26 records (A_Z) and one more field iCACode (I). This (reference) table stays small (26 records), the search is very quick and you have full control.

LOCAL lcNewCACode

CLOSE ALL 

CREATE CURSOR csrCACode (cChar C(1), iCACode I)

FOR i = 65 TO 90
	INSERT INTO csrCACode VALUES (CHR(i), 0)
ENDFOR

LOCATE 

BROWSE NOWAIT 

LOCATE FOR cChar = "B"

Replace iCACode WITH iCACode + 1

lcNewCACode = cChar + PADL(iCACode, 6, "0")

WAIT WINDOW + lcNewCACode TIMEOUT 3


LOCATE FOR cChar = "J"

Replace iCACode WITH iCACode + 1

lcNewCACode = cChar + PADL(iCACode, 6, "0")

WAIT WINDOW + lcNewCACode TIMEOUT 3


LOCATE FOR cChar = "S"

Replace iCACode WITH iCACode + 1

lcNewCACode = cChar + PADL(iCACode, 6, "0")

WAIT WINDOW + lcNewCACode TIMEOUT 3


LOCATE FOR cChar = "B"

Replace iCACode WITH iCACode + 1

lcNewCACode = cChar + PADL(iCACode, 6, "0")

WAIT WINDOW + lcNewCACode TIMEOUT 3

CLOSE ALL
CLEAR ALL

 

[Mike Lewis]

I agree with all of the above, especially with setting DELETED on or off as appropriate, and also maintaining a separate table for the numbers.

But another way would be to use a unique number for the whole of the index, rather than just for individual letters. So, instead of S001, S002, ... T001, T002, ..etc. you might have S001, S002, T003, S004, A005, and so on.

I realise that this might be difficult to do retrospectively, and it might mean that you exceed three digits for the numbers. But it is still worth considering.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Actually, I think the ideal approach would be to use a purely numeric ID, and to use an Integer (Autoinc) to generate it. That way, you are guaranteed that there will be no duplicates, and there is virtually no extra work involved when adding new records.

If your users (or their customers) are accustomed to the Snnn approach, you could easily pre-pend the first letter of the surname for display purposes. So, if customer Smith has an autoinc ID of 123, then 123 would be the value stored in the ID field (and the index), but you would still display S123 on invoices, forms, reports, etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[mjcmkrsr]

...

and a demo

LOCAL lcC2Search, lcNewCACode

CLOSE ALL

CREATE CURSOR csrCustomer (cName C(20), cCACode C(7))
INSERT INTO csrCustomer VALUES ("Doschke Olaf", "")
INSERT INTO csrCustomer VALUES ("Kramek Andy", "")
INSERT INTO csrCustomer VALUES ("Kramek Elizabeth", "")
INSERT INTO csrCustomer VALUES ("Lewis Mark", "")
INSERT INTO csrCustomer VALUES ("Lewis Mike", "")
INSERT INTO csrCustomer VALUES ("Mozley Andrew", "")
INSERT INTO csrCustomer VALUES ("Smith Andrew", "")
INSERT INTO csrCustomer VALUES ("Smith John", "")

CREATE CURSOR csrCACode (cChar C(1), iCACode I)

FOR i = 65 TO 90
	INSERT INTO csrCACode VALUES (CHR(i), 0)
ENDFOR

SELECT csrCustomer

SCAN
	lcC2Search = SUBSTR(cName, 1, 1)
	
	SELECT csrCACode
	LOCATE FOR cChar = lcC2Search
	Replace iCACode WITH iCACode + 1
	lcNewCACode = cChar + PADL(iCACode, 6, "0")
	
	SELECT csrCustomer
	Replace cCACode WITH lcNewCACode

ENDSCAN

BROWSE 

CLOSE ALL
CLEAR ALL

 

[Chris Miller]

How are you doing it up to now? What's your code? You must use the index descending and then seek just the letter, right?

I second mm0000: Nothing you change in data can change already printed documents, so you usually don't recover an already used number.
And others already pointed out to SET DELETED OFF, just don't recall the record you find, if it's deleted, always use the next number for the new customer.

Whether an index only seeing undeleted records is possible? I think you can define FOR NOT DELETED(), the indexes in the table designer also allow the filter option for a primary index. It can cause other headaches later, though, because setting order to such a filtered index you don't see deleted rows even with SET DELETED OFF, as you only see records in the index and that filters out deleted rows.

 

[AndrewMozley]

Thank you all for your replies and guidance. I agree that the simplest solution is to SET DELETED OFF when I check to see what the last number used is, and to choose a number for the client after all those for his letter, whether deleted or not. So I am pretty much following the advice of mm0000 and Griff's code example.

I note your point about having more than 999 customers with the same initial letter. In fact the system does cater for that, and goes on from J001 . J066. . J123 . . J998, J999, to J1000 if necessary. But it hasn't happened yet.

Yes, Chris - I find the last use Annn record by searching for B and skipping one letter back (except at BOF()).

Thank you all for your code examples, and - Mark (mjcmkrsr) - I am very flattered to be in the same pantheon with Andy Kramek and Mike Lewis!

Best wishes. Andrew M.

 

[GriffMG]

Hmm would J1000 sit in the right place to then supply J1001


Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[AndrewMozley]

H'mmm. Perhaps not. Maybe safer to go from J999 to J1001

 

[GriffMG]

If you get to J900 I suggest you change every existing J to J0nnn
You only need do it for the letters getting close to J999

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

You're talking about sort order now, right?

The problem with numbers is they must all be right aligned to sort correctly, J1000 sorts in directly between J100 and J101, and J1001 doesn't help to get past J999. normal sorting just looks strictly from left to right, not from right to left.

The simplest would be to not combine this in one field. Then you could sort by custletter+padl(custno,6), which works for up to 10 million per letter but can still print only padding up to whatever number of digits is necessary, even just J1,J2...J10,...J100 without leading zeros can be printed and still be sorted correctly by this separation of the customer number parts.

Chriss

 

[GriffMG]

Yes, I was talking about sort order, because it's normal to find the next 'serial' number by going to the end of the list of existing ones and adding one.
The existing approach will yield 1000 for every entry after 999, because - as Chris says - 1000 is not going to be at the end.

There are other ways to find the next number - if you yield J1000, simply double check that by seeking it and then add one and seek that... worst case I guess you search for another
999 entries or so!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[mjcmkrsr]

...

if you don't want to have leading "0", just leave them out by checking the length of the str(numeric) field like

LOCAL lcC2Search, lcNewCACode

CLOSE ALL
CLEAR ALL 

CREATE CURSOR csrCustomer (cName C(20), cCACode C(7))
INSERT INTO csrCustomer VALUES ("Doschke Olaf", "")
INSERT INTO csrCustomer VALUES ("Kramek Andy", "")
INSERT INTO csrCustomer VALUES ("Kramek Elizabeth", "")
INSERT INTO csrCustomer VALUES ("Lewis Mark", "")
INSERT INTO csrCustomer VALUES ("Lewis Mike", "")
INSERT INTO csrCustomer VALUES ("Mozley Andrew", "")
INSERT INTO csrCustomer VALUES ("Smith Andrew", "")
INSERT INTO csrCustomer VALUES ("Smith John", "")

INDEX on cName TAG cIName
SET ORDER TO cIName

CREATE CURSOR csrCACode (cChar C(1), iCACode I)

FOR i = 65 TO 90
	INSERT INTO csrCACode VALUES (CHR(i), 998)
ENDFOR

INDEX on cChar TAG cIAlphabet
SET ORDER TO cIAlphabet

SELECT csrCustomer

SCAN
	lcC2Search = SUBSTR(cName, 1, 1)
	
	Replace cCACode WITH UpdateCACode(lcC2Search, .T.)

ENDSCAN

BROWSE TIMEOUT 2

INSERT INTO csrCustomer VALUES ("Smith Kathy", "")
lcC2Search = SUBSTR(cName, 1, 1)

Replace cCACode WITH UpdateCACode(lcC2Search, .T.)

BROWSE TIMEOUT 2

INSERT INTO csrCustomer VALUES ("Lewis Kathy", "")
lcC2Search = SUBSTR(cName, 1, 1)

Replace cCACode WITH UpdateCACode(lcC2Search, .F.) && Yields error

BROWSE TIMEOUT 2

INSERT INTO csrCustomer VALUES ("Lewis Jane", "")
lcC2Search = SUBSTR(cName, 1, 1)

Replace cCACode WITH UpdateCACode(lcC2Search, .T.)

BROWSE 

CLOSE ALL
CLEAR ALL

*****

FUNCTION UpdateCACode

LPARAMETERS tcC2Search, tlTrue
	
	IF !EMPTY(tcC2Search) AND USED("csrCACode") AND tlTrue

		= INDEXSEEK(tcC2Search, .T., "csrCACode", "cIAlphabet")
		Replace iCACode WITH iCACode + 1 IN csrCACode
		lcNewCACode = csrCACode.cChar + PADL(csrCACode.iCACode, [highlight #FCE94F]LEN(ALLTRIM(STR(csrCACode.iCACode)))[/highlight] )

	ELSE
		lcNewCACode = ""
		= MESSAGEBOX("Please call IT-Department", 16, "Update CA Code", 3000)
	
	ENDIF 

	RETURN lcNewCACode

ENDFUNC 

*****