Creating an auto-increment number

[ViCas]

I am creating a Purchase Order system. The PO Number should be in this format PO2012-00000001 and incrementing by one for each order. The next number will be PO2012-00000002 and so on. When 2013 rolls around (provided the world doesn't end on December 21, 2012) the number should be PO2013-00000001 and so on. The number should start all over again. I have tried several different methods but none of them seemed to work. I can either change the year or the number but not both. I can set to change the year but all PO's within the year has the same PO Number. In other words, for 2012, they are all PO2012-00000001 and doesn't increment. If I set to change the number it ignores the year. POs for 2013 will be PO2012-xxxxxxxx - incremented numbers. If the last number for Year 2012 was PO2012-01838548 then when 2013 rolls around it will be PO2012-01838549. I have tried different settings, masks, auto-increment (on and off), etc. This is my last hurdle in the PO system. I am trying to make it as automatic as possible.

Any help, thoughts, ideas or brainstorms will be appreciated.

[Stan Mathews]

Re: Creating an auto-increment number

The scenario you describe is not within Alpha's field rules capability to deliver without help. You will need to code your own function to generate the value.

[ViCas]

Re: Creating an auto-increment number

Thank you very much, Stan. That was my feeling and it is good to have someone confirm it. I felt I was not looking in the right places. Thank you once again.

[Stan Mathews]

Re: Creating an auto-increment number

I don't know if you need any further help with this but here's my take. For a table named aitest, po number field po_number, this seems to work as a data entry simple default expression.

FUNCTION new_po AS C ( )
	new_po = ""
	last_po_ty = alltrim(tablemax("[COLOR="#FF0000"]aitest[/COLOR]","substr([COLOR="#0000CD"]po_number[/COLOR],3,4) = cyear(date())","[COLOR="#0000CD"]po_number[/COLOR]"))
	if last_po_ty = "" 'none yet this year
		new_po = "PO"+cyear(date())+"-"+"00000001"
	else
		new_po = increment_value(last_po_ty)
	end if	
END FUNCTION

Default value is just new_po().
Set at start of data entry.
Skip set to .T.

[Stan Mathews]

Re: Creating an auto-increment number

Sample table and importable function attached.

aitest.zip
new_po - 02-15-2013 -1.txt

[SNusa]

Re: Creating an auto-increment number

In the interest of being thorough, here's a link to other options too... http://msgboard.alphasoftware.com/al...Year-and-Month

[reddy2011]

Re: Creating an auto-increment number

I think Stan's answer is perfect to close this conversation

[abiodunode]

Re: Creating an auto-increment number

Hello Stan,

The solution you gave is for dbf table. How would the code look like if using mysql. I am in a fix to implement it in mysql.
Thanks.

Abiodun

[Stan Mathews]

Re: Creating an auto-increment number

If I knew anything about sql in general or mysql in particular I'd be glad to help. Sadly, not able to.