Vis FoxPro 6

[Phil Thoms]

I have a simple input form in which the user inputs invoice items. I would like to know if the invoice number field can be updated with a new invoice no. automatically. So if the last invoice no. used was 1105 the next would obviously be 1106 and so on. There can be several items for the one invoice no. or just one item. I know this can be done in the old FoxPro but is it possible within forms ?
Thanks.

 

[Mike Lewis]

Presumably, you have a table (a DBF file) that holds the invoice details.

If so, you could make the invoice number field an auto-increment field (set its data type to Integer (AutoInc)). You will also need to set its Next Value to the next number you want to use. You make both these changes in the table designer.

That will give you a new number every time you add a record to the invoice table.

If that doesn't fit in with the way you've planned your application, another option would be to simply add 1 to the previous number every time you produce a new invoice. To do that, you will need to store the previous number somewhere. The simplest way to do that would be to have a separate table, with one record. One field in the record would hold the invoice number, and you could use other fields for similar low-volume information, such as tax rates or whatever.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Mike Lewis]

Sorry, I didn't notice you were using VFP 6.0. Auto-incrementing fields only arrived in version 8.0. You will have to use my second suggestion.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

I don't use Mike's separate table approach, prefering to derive the new invoice number from the table I want to place it in.

I use a function like this:

FUNCTION GETNEXTINVOICENO
	PRIVATE m.OLDAREA, m.OLDRECNO, m.OLDORD, m.STRING
	m.OLDAREA = SELECT(0)
	SELECT INVOICES
	m.OLDRECNO 	= RECNO()
	m.OLDORD	= ORDER()
	SET ORDER TO INVOICES1
	** CODE
	GO BOTTOM
	m.STRING = RIGHT("00000000"+ALLTRIM(STR(VAL(INVOICES.CODE)+1,8,0)),8)

	SET ORDER TO (m.OLDORD)
	IF m.OLDRECNO > 0 .AND. m.OLDRECNO <= RECCOUNT()
		GOTO m.OLDRECNO
	ENDIF
	SELECT (m.OLDAREA)
	RETURN(m.STRING)

In the example, the invoice number is held in 'invoices.code', on which the table 'invoices' is indexed.

The number is padded out with zeros, but you could just use a string for it...

Good luck


Regards

Griff
Keep [Smile]ing

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

 

[Mike Lewis]

I don't use Mike's separate table approach, prefering to derive the new invoice number from the table I want to place it in.

Yes, that makes good sense - especially if there is no other reason to maintain the sort of single-record control table I had in mind.

The only small complication with Griff's approach is that, in a multi-user environment, you would have to apply a lock, to prevent two people grabbing the same number. But that's not a big problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[GriffMG]

Mike is quite right, whichever approach you take, you either have to lock your record or provide some other kind of semaphore to prevent duplication.


Regards

Griff
Keep [Smile]ing

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